Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We are in the process of switching to a new RAID array and need to copy our MySQL InnoDB database to the new RAID array while minimizing downtime (50GB database). I understand that using rsync on a running MySQL database will not produce a usable copy of the database. However, can I get an exact copy by using rsync on the running database, then completely shutting down mysqld, and running a final rsync? Are there any possibilities of data corruption in this case, if I ensure that mysql is not running with the final rsync is run?

share|improve this question

1 Answer 1

up vote 7 down vote accepted

What you are describing should work, however my advice for backing up any database using a filesystem-level backup is that unless the database vendor has approved procedures for doing otherwise you need to quiesce the database before performing a filesystem-level backup (That means "shut the damn thing down").

What you're doing will take advantage of rsync's delta transfers and effectively copy the "shut down" database in a slightly shorter period of time since it will just be delta-copying the parts that changed.

As with any procedure, before doing this in a production environment you may want to contrive a test case (create a MySQL instance and a DB, back it up, make some changes, shut it down, do your final sync & make sure it works on the cloned box).

share|improve this answer
4  
This is exactly what I have done for years. In addition to what you have in your answer, I usually keep innodb_max_dirty_pages_pct at 0 so that the flushing of the InnoDB Buffer Pool is faster. That way, 1 or 2 less rsyncs to worry about and a faster mysql shutdown to accommodate the final rsync. +1 for this !!! BTW Setting innodb_max_dirty_pages_pct is not as necessary for MySQL 5.5 since the InnoDB Buffer Pool flushes better and faster. –  RolandoMySQLDBA Sep 29 '11 at 15:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.