How can I rebuilt MySQL replication without dumping the master?

Our MySQL database has grown fairly large, and I am running with master replication. Replication is broken. I am trying to rebuild the second master without freezing up the first master in doing a mysqldump. The database is big enough that the dump is taking a few hours, and I cannot have the database offline that long.

How can I rebuilt MySQL replication without dumping the master?

Solution 1:

Take a look at the Percona Toolkit. pt-table-checksum can help you find out the tables out of sync and you can use pt-table-sync afterward.

Solution 2:

Adding to @quanta's answer, if you had no luck trying to compare-and-repair the replication you can use the innobackupex tool that is shipped with percona xtra-backup tool to make a fast-copy of your master, which you can restore to the slave. (takes a while to run, but locks tables for much less time than a mysqldump)

Basically innobackupex works with both innodb and myisam tables, and takes advantage of the underlying implementations to massively improve the export times to produce consistent backups with only minimal locking time.

There is a walk-through example of creating a slave from the master on the site; http://www.percona.com/doc/percona-xtrabackup/howtos/setting_up_replication.html

Solution 3:

Tom H's suggestion is great as well. If you can get Xtra back up working it is very nice. I am not sure whats best in this case, it would help to know:

Version of MySQL All InnoDB? All MyISAM ? Or mixed? If InnoDB, are you using file per table? How large is large? (50 GB vs. 500 GB?)

If for some reason you cannot get Xtrabackup working, or @quanta's advice with the pt tools, you may have to go old school and stop mysql and copy the data dir to the slave, adjust accordingly and start repl back up. If the you are dealing with 100's of GB's and you need to do the copy over network, AND you have multi core / CPU's you may want to look at using pigz for your copy http://zlib.net/pigz/ This helped us where it was taking 4.5 hours to copy the dump file, OR the directory, by reducing to 2 hours.

Good luck I know how stressful this can get.

Solution 4:

If the mysql partition is on a LVM then us you can mylvmbackup. It will take a snapshot and will also save the co-ordinates. The fastest way to rebuild mysql is to use the backup of the data directory. You can then run an optimize to optimize.

If you don't have LVM and can shutdown the mysql server.
1. Do a read lock on the master and note down the position.
2. Shutdown mysql
3. Copy the data directory to the target machine. You can create a local copy of the data directory, which is usually faster than network transfer to minimize the mysql downtime.
4. Start mysql
5. On the target machine make sure that the ib_logfile settings are same as that of master else mysql will complain.

Rarely the data copy doesn't happen cleanly so might want to try it again.