Category Image Resyncing Broken MySQL Replication


My replication stopped working after we lost power due to faulty Uninterruptable Power Supply on the slave while it was replicating a massive 70 million record LOAD DATA INFILE from the Master. I decided the simplest strategy here was to reset the replication process and copy the master databases to the slave and start replication again. The whole process took about 15 minutes, 10 of which the master was down while I copied the files across the gigabit network to the slave.

These instructions apply to simple case of one slave replicating from one master. These instructions are brief since I was busily resyncing while typing them, so excuse the brevity! Here is the steps involved
(By the way, both master and slave were running OS X 10.3.5 Server, MySQL 4.0.20)
[Revised 12/17/04]

1) Prepare the slave

2) Prepare the master, copy databases and restart master

3) Restart Replication Slave


1) Prepare the slave
SSH into the slave server.
Log into MySQL on the slave as root
mysql>STOP SLAVE;
mysql>RESET SLAVE;
mysql> exit;

Now exit and shutdown slave
$ mysqladmin shutdown -u root -p

Delete the databases on slave (DANGER ... this deletes ALL databases on slave)
$ cd /usr/local/mysql/
$ sudo rm -r data

2) Prepare the master, copy databases and restart master
SSH into the master as root
log into mysql as root

Check users and kick them all off if more than yourself is connected
mysql> show processlist;

Reset the Master
mysql> RESET MASTER;

exit and shutdown immediately
$ mysqladmin shutdown -p

Now copy all the data from this master to the slave (may take a while)
# cd /usr/local/mysql
# scp -r data root@slavehost:/usr/local/mysql

Note: If you have a really huge amount of data and you have the drive space, it may be faster to make a local copy of the data directory on the master which can be then copied to the slave after you have restarted the master.

When copying is done, restart the master
$ sudo echo
$ sudo mysqld_safe &

Log into master and make sure it is logging.
mysql> show master status\G
*************************** 1. row ***************************
File: binary-log.002
Position: 280
Binlog_do_db:
Binlog_ignore_db:
1 row in set (0.00 sec)

Make sure Position is increasing if insert and update activity is taking place on master.

Now you can breathe for a while!

3) Restart Replication Slave
Log into slave server and fix privileges on the data folder
$ cd /usr/local/mysql
$ sudo chown -R mysql:wheel data

Restart server
$ sudo echo
$ sudo mysqld_safe &

Log into mysql as root
mysql> SHOW SLAVE STATUS\G;

Verify that it is replicating and you are done.


Posted: Monday - November 01, 2004 at 05:02 PM        

Published by