Home / server / Questions / 183768
In Process
Dave Rix
Asked: 2010-09-23 12:49:11 +0800 CST

Duplicating a database in a master slave environment

  • 772

I have a number of large databases, 350Gb+ each with over 100 tables in each. They are mainly MyISAM tables, with a few InnoDB tables thrown in for good measure.

Platform :- Linux, MySQL 5.1, MyISAM + InnoDB tables (with file_per_table option turned on)

I need to duplicate these database on the master and have the duplication correctly reflected on the slave server, and am running into some issues.

Doing a mysqldump and then a restore is successful, but extremely slow, and takes a long time for the slave to catch up.

Doing a straight disk copy of the data files does not seem to work as the information_schema does not accurately reflect the copied database - in some instances the tables don't even seem to exist in the target database, even though myisamchk is able to check and repair the indexes successfully.

Is there a way of performing the disk copy of the data files and having the information_schema accurately reflect the copied database, or am I just going to have to lump it and persevere with the dump/restore option? And if I can duplicate the databases in this way, can I perform the same steps on the slave without breaking the replication status?

Thanks in advance, Dave

linux mysql

2 Answers

  1. The most fail-safe method is to take down the master database, and copy the binary datbase files from the master to the slave, bring them both up and start the replication. With 350Gb of tables, I can understand this could be a problematic solution. You can gain some time by making the copy the fastest you can, which usually means copying to an entirely other set of disks on the master server. Then you can bring the master back up and you can take your time moving the copy over a slower network link.

    Copying MyISAM files out from underneath MySQL will work, or at least I know it did in the days of MySQL v3 and v4. (Make sure you have the slave shutdown when you run myisamchk.) However, this doesn't work for InnoDB files.

    There is a tool called "MySqL Hot Backup" or some such will is also capable of doing all this in a cleverer way. The catch is that you may have to pay for it, at least for InnoDB support.

    • 1

  2. Have you LVM enabled? If yes, you could use it to create snapshot of partition without shutting mysql and then you could mount snapshot and copy files from it. Here is article describing the method.

    If you could shutdown database, you could copy raw data files. Just remember to correct record your master information.

    • 1