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, to fix it up and get back in working shape.

First of all, some useful links:

• http://www.howtoforge.com/mysql_master_master_replication
• http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html
• http://www.howtoforge.com/mysql5_master_master_replication_debian_etch
• http://dev.mysql.com/doc/refman/5.1/en/replication-sql.html

There was no existing MySQL replication, what had been was gone, and a combination of reseting 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.