MySQL Forums
Forum List  »  Other Migration

Migrating MySQL database
Posted by: Chris Kimberley
Date: February 23, 2012 11:30AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Migrating MySQL database
3767
February 23, 2012 11:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.