How to do backing up mysql databases with Rsnapshot

What is Rsnapshot?
From the official website: “rsnapshot is a filesystem snapshot utility based on rsync. rsnapshot makes it easy to make periodic snapshots of local machines, and remote machines over ssh. The code makes extensive use of hard links whenever possible, to greatly reduce the disk space required”.
Setup mysql client
Install mysql-client if not installed, this will provide the mysqldump utility
apt-get install mysql-client
cp /usr/share/doc/rsnapshot/examples/utils/backup_mysql.sh /usr/local/bin/
Make sure your backup scripts are owned by root, and not writable by anyone else.
chown root.root /usr/local/bin/backup_mysql.sh
chmod o-w /usr/local/bin/backup_mysql.sh
This scripts is designed only to back up all databases to a single file.
Edit script
nano /usr/local/bin/backup_mysql.sh
replace
/usr/bin/mysqldump --all-databases > mysqldump_all_databases.sql
with
/usr/bin/mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases > mysqldump_all_databases.sql
If you need to dump a single database use line below
/usr/bin/mysqldump --defaults-file=/etc/mysql/debian.cnf DATABASE > DATABASE.SQL
Setup Rsnapshot
Add script to rsnapshot
Edit rsnapshot config
nano /etc/rsnapshot.conf
Under BACKUP POINTS / SCRIPTS add following
backup_script /usr/local/bin/backup_mysql.sh localhost/mysqldump
Test config
rsnapshot configtest
Links
- https://rsnapshot.org/