Migrating MySQL database

[Chris Kimberley]

Hi all,

I've been doing a lot of reading lately on ways to migrate MySQL. The traditional way I've migrated a database is by using the mysqldump tool, but I'd like to explore some other options.

In my test environment I have two mysql servers live and running on two different machines (all linux based). I am using many InnoDB tables. Before I perform a method of grabbing the contents in the MySQL data directory I will be issuing a flush table with read lock command.

Because i'm using InnoDB, RSync is not an option because reads can still place while the RSync is happening. From what I've read this can cause issues with the table space and log files as they may copy over in a corrupted state even if all writes have been locked by the flush tables.

I'm wonding if using tar and gunzip will have similar issues as an RSync with InnoDB. The zip will lock a tablespace file or log file while it's performing the compression, but the other related files to InnoDB will still be able to perform reads. If a disk flush happen can this lead to issues ? Or does gunzip/tar lock all the files at once ? Does any of this even matter when the files get copied over to the other server and MySQL is started ? Does MySQL sort everything out when it starts ?

I'm particularly interested in any answers to the tar/gunzip method. Any other suggestions for migrating the DB are welcome.

Thanks,
Chris