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 "fullmysqlbackup.sh" (the only file I have to run directly), and a helper file called by fullmysqlbackup.sh.  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 "fullmysqlbackup.sh" and contains the following (adjust the path to the location of the second file, rsyncmysql.sh):

#!/bin/bash
echo "FLUSH TABLES WITH READ LOCK; SYSTEM /root/crontasks/rsyncmysql.sh; UNLOCK TABLES;" | mysql -uroot -p<password>

The other is called rsyncmysql.sh, and contains this:

#!/bin/bash
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 fullmysqlbackup.sh at a time when there is little traffic.

Comments

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

CAPTCHA
This question is to prevent automated spam submissions.
      _              _      ____                  
__| | __ _ / \ | __ ) __ _ _ _
/ _` | / _` | / _ \ | _ \ / _` | | | | |
| (_| | | (_| | / ___ \ | |_) | | (_| | | |_| |
\__,_| \__, | /_/ \_\ |____/ \__, | \__,_|
|___/ |___/
Enter the code depicted in ASCII art style.