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

[mysqldump]

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