Thursday, August 17, 2017

Set up automated mysql/mariadb backup

We are going to use mysqldump for this automated backup exercise.

Create a read only user in mysql to do the backup, in this case, backupuser
# mysql -uroot -p
mysql> grant lock tables, select on *.* to 'backupuser'@'%' identified by 'password_of_your_choice';

Test doing mysqldump using the new user
# mysqldump -ubackupuser -p --all-databases > /tmp/mysql_all_db.sql

If everything is fine, and the mysql_all_db.sql is created successfully, proceed on giving the backupuser passwordless access to mysql. Add below lines to /root/.my.cnf
user = backupuser
host = localhost
password = "password_of_your_choice"

Test whether you can still do mysqldump without the -u and -p options
# mysqldump --all-databases > /tmp/mysql_all_db.sql

If everything is running fine, now is the time to add to crontab
# crontab -e

Put below lines to run the mysqldump command at 11 PM every Saturday, and the filename will be datestamped as well
* 23 * * * Sat /usr/bin/mysqldump --all-databases > /backup/mysql_all_db-$(date +%Y-%m-%d).sql