2

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

Dave Rix
  • 285
  • 3
  • 7
  • For those reading this, I'm still having issues with re-building the information_schema data, as this still occasionally does not accurately reflect what the data files are showing in terms of tables present in databases. – Dave Rix Oct 03 '10 at 11:58
  • 1
    http://serverfault.com/questions/332339/way-to-avoid-server-downtime-when-creating-master-slave-relationship <-- good answers. Works with plain MySQL. – Prof. Falken Nov 21 '11 at 12:38
  • +1 great answer and thanks for pointing it out. Fortunately(?!), I'm not in that job any more, so don't need to sort this out any more! I will however remember about Xtraback for the future (did know of it, but not thought of it in this scenario). – Dave Rix Nov 21 '11 at 16:05

2 Answers2

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.

staticsan
  • 1,529
  • 1
  • 11
  • 14
  • If you precopy the database files, you may be able to reduce the down time using rsync to copy any missed changes over to the pre-copied tables. You will likely want to use the --inplace option to avoid copying updated files on the target system. – BillThor Sep 23 '10 at 03:45
  • It's a shame there isn't a utility that can export and then import using a "raw" MySQL format without having to go through the pain of many millions of inserts and the index rebuilds :( I will certainly investigate the Hot Backup option though – Dave Rix Sep 23 '10 at 09:26
  • I also agree about copying the MyISAM files - I've done that before using MySQL v4, but in v5 it seems to have issues... – Dave Rix Sep 23 '10 at 09:27
  • Looks like the "Hot Backup" option is the way to go. We have finally managed to dump and restore all the databases onto the new master. Not the most efficient way to do it, but it worked. – Dave Rix Oct 03 '10 at 11:56
1

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.

sumar
  • 2,086
  • 12
  • 12
  • Unfortunately, we don't have LVM enabled and I dont have enough free storage to make a snapshot anyway! Things are really tight here. – Dave Rix Oct 03 '10 at 11:54
  • For MyISAM you could copy raw data files. When MySQL is running, you need enter in mysql cli seesion FLUSH TABLES WITH READ LOCK. Don't close session, copy mysiam files, and then type UNLOCK TABLES. – sumar Oct 15 '10 at 06:15
  • 1
    Here is article about copying innodb tables: http://www.mysqlperformanceblog.com/2009/07/31/copying-innodb-tables-between-servers/ and there is explained how: http://www.percona.com/files/white-papers/percona-server-saas-white-paper.pdf. The destination server must run XtraDB. – sumar Oct 15 '10 at 06:18