using rsync to backup mysql

Question

hi,

I use the following rsync command to backup my mysql data to a machine within the LAN network. It works as expected.

rsync -avz /mysql/ root:[email protected]:: /root/testme/

I just want to make sure that this is the correct way to use rsync.

I will also like to know if the 5 minute crontab entry for this will work.

Answer 1

Hi,

I don't really know about your rsync command, but I am not sure this is the right/best way to make backup with MySQL ; you should probably take a look at this page of the manual : 6.1. Database Backups

DB backups are not necessarily as simple as one might think, considering problems suchs as locks, delayed write, and whatever optimizations MySQL can do with its data... Especially if your tables are not using the MyISAM engine.

About the "5 minutes crontab" : you are doing this backup every five minutes ? If your data is that sensible, you should probably think about something else, like replication to another server, to always have an up-to-date copy.

Answer 2

1. don't use the root user of the remote machine for this. In fact, never directly connect to the root user, that's a major security leak. In this case, simply create a new user with few privileges that may only write to the backup location
2. Don't use a password for this connection, but instead use public-key authentication
3. Make sure that MySQL is not running when you do this, or you can easily get a corrupt backup.
4. Use mysqldump to create a dump of your database while MySQL is running. You can then safely copy that dump.

Answer 3

I find a better way of doing backups of MySQL is to use the replication facility.

set up you backup machine as a slave of your master. Each transaction is then automatically mirrored.

You can also shut down the slave and perform a full backup to tape from it. When you restart the slave it synchronises with the master again.