Backup MYSQL with rsync

Databases
1

Hi all…

I want to be able to backup my mysql server to a local machine using rsync/ssh. While there are many tutorials on how to do this, my concern is more of data corruption.

  1. Will using rsync on a LIVE mysql server affect the LIVE server files at all? (I don’t care if the backup version does not have the latest mysql entries and is a little out of sync).

  2. Will the backed up database be stable? (if for instance both rsync and mysql are using the same file at the same time…not even sure if this can happen at the OS level)

thx for any illumination on this topic…
Reply With Quote

2

I don’t think you will get good results using rsync, you should instead look into using the mysqldump command. Then you could rsync the folder containing the dumps to your local server.

I would say almost certainly you will end up with corrupted data using any other method.

If your database is small then the dump process is virtually unnoticeable. If its a bit bigger you should look at converting your tables to InnoDB to prevent row locking.

3

Also, the good idea is to think about master-slave replication.