Is possible to backup database online using rsync?

Some people tell me that the backup database on-the-fly using rsync is possible. I think it is possible but not safe because some log, buffer... is not flushed. Database is being recovered from the backup would not consistent.

Actually I am not sure why the backup using rsync is not safe. Could anyone explain me the underlying of mysql for the reason ?

When it comes to InnoDB, you are right to be concerned.

Here is a Pictorial Representation of InnoDB (Made by Percona CTO Vadim Tkachenko)

The whole left side of the diagram represents InnoDB's moving parts in memory. The critical piece here is the InnoDB Buffer Pool

The InnoDB Buffer Pool holds three things

• Data Pages : If a page is dirty, it is eventually written to its .ibd file

• Index Pages : If a page is dirty, it is eventually written to its .ibd file

• Secondary Index Changes : Dirty index pages related to non-unique indexes are written to ibdata1

Any page that has been updated is also written beforehand into the Double Write Buffer inside ibdata1.

If you rsync everything in datadir, you are chasing too many moving files

• ibdata1 itself has moving parts

• Double Write Buffer

• Inset buffer

• Rollback Segments

• Undo Logs

• Redo Logs (ib_logfile0 and ib_logfile1)

• Every .ibd file being updated

If you had to rsync your datadir to back it up and you attempt to setup it and start mysql, the best case scenario would be crash recovery, an initiated repair of data and index pages using the Double Write Buffer and the Insert Buffer and some playback from the Redo Logs. This will roll forward whatever data can be saved via crash recovery.

It would be fair to say that rsync could possibly miss dirty pages that have not been written the Double Write Buffer and the Insert Buffer if the rsync got passed it already.

I have written posts about doing rsyncs for backup

• Aug 19, 2012 : How do I replicate a MySql server's data without using mysqldump?

• Jun 17, 2011 : What is the difference between XtraBackup and rsync?

• May 23, 2011 : How can I move a database from one server to another?

If you want to try to rsync, you could use the script from How do I replicate a MySql server's data without using mysqldump? but you need to properly flush all moving parts

About 10 minutes before the backup, set buffer pool to have dirty pages flush aggregsively

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Then, just before starting the rsync, run these commands

FLUSH LOGS;
FLUSH TABLES WITH READ LOCK;
SELECT CONNECTION_ID() ID_With_Lock;
SELECT SLEEP(86400);

You could run the rsync at this point, but any new transaction will be held up in the Buffer Pool and will not commit to disk until you login to MySQL in a separate session and kill the process ID (ID_With_Lock).

EPILOGUE

It is possible to have a good rsync backup if handled with great care. Percona XtraDB Cluster has 3 modes of adding new node to a Galera Cluster (xtrabackup, mysqldump, rsync)

If you are not that confident doing rsync backups, you are better off just doing mysqldump --single-transacton and retrieving binlog events to copy data and roll forward to what ever point-in-time you want. You could also resort to Percona XtraBackup and let it do the heavy lifting.

StackExchange Database Administrators Q#91287, answer score: 12