Is possible to backup database online using rsync?

Question

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 ?

Answer 1

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.

Answer 2

Take a look at my answers to similar questions here, here and here. Basically, it's very simple - a database is a dynamic entity with components on disk, in RAM and in the CPU at the same time. There are all sorts of buffers and caches being filled, emptied and flipping between disk, RAM and CPU.

InnoDB is an MVCC engine which allows backup software to take a "snapshot" at a particular moment in time (and allow the db to keep functioning while the backup is running). With MyISAM, the database cannot be active while a backup is being taken.

Now, if you simply take a copy of the database files, buffers and caches which should be on disk in order for a CONSISTENT database copy to be made (i.e. one which is coherent at a given point in time) are NOT (necessarily) on disk. This is why there are various utilities (Percona's XtraBackup and Amanda Network Backup) have been developed which take care of ensuring that a backup will be consistent and will not be corrupted and will work on a restart (and be of use in a PITR - Point In Time Recovery - scenario).

If nothing else, ask yourself the question as to why very intelligent people have gone to the trouble of writing these programmes instead of just relying on rsync - why not just do a simple cp?

Answer 3

The backup is safe to create, but not to use to restore and start the database, the gist being the buffers and cache and in flight transactions being processed. I won't spend time on more details as others have provided adequate links.

However while not your exact request understanding rsync backup logic could still be helpful to you. For instance this on-the-fly (what I tend to call an rsync hot copy) is usable to to get a large portion of the rsync completed ahead of time. Then stopping the database to quiesce the buffers and caches to disk allows the next rsync to get a "cold copy" you can restore from and start the database cleanly. This cold copy is a Delta of the hot copy, so it is drastically reduced in time. The more.common method is using mysqldump and syncing that to another location, then later dumping again and the rsync to the same remote destination file will take dramatically less time.