I wanted to share my experience of fixing multi master MySQL replication after a long reboot. I hope it will help others :)

The story

I have a load balanced + high availability server. The two servers are doing master-master replication.

I shutdown one of them for maintenance (for 12h+), when I rebooted I was getting this error when i was doing "start slave" :

Error "ERROR 1201 (HY000): Could not initialize master info structure;

Then I did (no detailed explanation):

cat /var/log/syslog | grep relay 

Which told me something like :

Jan  4 12:27:10 portafixeweb2 mysqld[17754]: 090104 12:27:10 [ERROR] Failed to open the relay log '/var/lib/mysql/slave-relay.000341' (relay_log_pos 237)

Indeed /var/lib/mysql/slave-relay.000341 did not exist anymore.

So I checked in ls /var/lib/mysql/ to see what was the latest slave-relay.XXXXXX file

and replaced slave-relay.000341 with that latest file name into relay-log.info

Then I could start slave;

To sync data, I did :

LOAD DATA FROM MASTER;

Note for LOAD DATA FROM MASTER ; make sure you grant SUPER, REPLICATION CLIENT to the replication user and reload his privileges on the master. Otherwise you will get something like this :

ERROR 1219 (HY000): Error running query on master: Access denied; you need the RELOAD privilege for this operation