Home

Resuming MySQL replication

Sat, 11/24/2007 - 13:56 — Phil

After getting tons of errors with my Master-Master MySQL replication I had turned it off. It needed reworking and at a time that was convenient for me. This morning I set to fix it up and it get back in working shape.
There was no existing MySQL replication, what had been was gone, and a combination of resetting the master/slave status and deleting the log files was in order.

The various commands I used, included:

reset master;
reset slave;
purge master|binary logs now();

I also took the opportunity to amend the mysql configuration file. Entries like the below:

[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log-bin = master-log-bin
log-bin-index = master-log-bin
relay-log = master-relay-log
relay-log-index = master-relay-log
expire_logs_days = 10
max_binlog_size = 500M
...

On the master (and something similar on the slave) ensure that mysql wouldn’t complain about hostname changes affecting the relay-log files names.
After a couple of mysql restart the master server was okay, but the slave not so. “Ah ha!” I thought, I need to:

show master status;

on the master and plug those values into:

change master to MASTER_LOG_FILE=”master-log-bin.000001”, MASTER_LOG_POS=98;

on the slave. So the slave was now happy.
Okay then – last bit, make sure the data is in sync. Yes, I know I should have done that first, but I was dead set in getting rid of the error messages in the mysql log files first.
On the master I did:

use exampledb;
flush tables with read lock;
show master status;

Then I could:

stop slave;

on the slave.
Next I exported the tables from the master and re-imported them on the slave (PHPMyAdmin is a hell of a lot easier than mysqldump!).
Then a quick

change master to MASTER_LOG_FILE=”master-log-bin.000001”, MASTER_LOG_POS=98;

to reset everything, and a

start slave;

to get things going again.

Finally, some useful links: