How can I back up my MySQL/MariaDB databases daily?

To enable daily backups of your MySQL or MariaDB databases, please first grant your vproXXXX user read access to all databases. To do this, log in to MySQL as the root user using the password you know, and then execute the following GRANT statement:

root@vpro0209:~ # mysql -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
[...]
MariaDB [(none)]> grant select, show databases, show view on *.* to 'vpro0209'@'localhost' identified by 'SECURE-PASSWORD';

Next, as the user vproXXXX, create a MySQL configuration file to allow access from the nightly job without having to enter the password:

[vpro0209@vpro0209:~]$ touch .my.cnf
chmod 600 .my.cnf

Write the following content to this file:

[client]
password=SECURE-PASSWORD

For the password, we recommend generating 20–30 random characters, e.g., using

[vpro0209@vpro0209:~]$ pwgen 30 1
asheeNeet8OoWee7suso6VeetheeSh

You can now enable daily backups. To do this, create the file /etc/periodic.conf.local and add the following variables:

daily_mysql_backup_enable="YES"             # Enable daily backups
daily_mysql_backup_user="vproXXXX"          # Mandatory local vproXXXX user
daily_mysql_backup_dir="/var/backups/mysql" # Optional backup directory - default: /var/backups/mysql
daily_mysql_backup_days="7"                 # Optional number of days backups are kept - default: 7

If you want to enable backups on an existing proServer that was originally shipped without this feature, one additional step is required:

[vpro0209@vpro0209:~]$ sudo cp /usr/local/etc-sample/periodic/daily/801.mysql /usr/local/etc/periodic/daily/

That’s it. Our script will now take care of the rest for you.