Simple mysql backup via rsync

Here is a simpler alternative to my incremental backup described here.  

This method is simpler to implement, and has the advantage that the backup can be used (restored) as-is.  Also, the mysql administrative database is backed up.

The idea is to directly backup the databases using rysnc.  rsync is remarkably efficient and will only transmit the deltas (changed portions of files).  Furthermore, the backup data that is transmitted will be compressed.  

To carry this out, I use 2 files "" (the only file I have to run directly), and a helper file called by  The databases are flushed and placed under a read-only lock during the backup run.

So here are the instructions:

make 2 files.  One is called "" and contains the following (adjust the path to the location of the second file,

echo "FLUSH TABLES WITH READ LOCK; SYSTEM /root/crontasks/; UNLOCK TABLES;" | mysql -uroot -p<password>

The other is called, and contains this:

rsync --delete -vza /var/lib/mysql root@<remote-backup-server>:/root/backups/  | tee /root/crontasks/mysqlbackup.log | mail -s "mysql backup" <your email address>

Adjust the password, filenames, server name, and email addresses in these 2 files to match yours, and give both files executable permissions.

You can use cron to execute at a time when there is little traffic.


Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <p> <br> <img> <alt> <h1> <h2> <h3>
  • Lines and paragraphs break automatically.

More information about formatting options

This question is to prevent automated spam submissions.
  ___          _   _            _____ 
|_ _| ___ | | | | __ ___ |_ _|
| | / __| | | | |/ / / __| | |
| | | (__ | | | < \__ \ | |
|___| \___| |_| |_|\_\ |___/ |_|
Enter the code depicted in ASCII art style.