MySQL master-master replication

MANY articles about it is on the Net, but I try to show you, how quickly check what and why doesn't work in MySQL replication.

Initialization

In case of master-master replication, lets assume that we have server A, the origin of data, and server B, which is now a slave for A, but after transferring data on B we make it a master for A. That's why you:

1. hide server B (that one which you pass the data on), to avoid lost of data during initialization. Simply bind MySQL in my.cnf to 127.0.0.1 instead of 0.0.0.0


2. on server A list all databases you want to not replicate in my.cnf in ignore-bin-db directive


3. create a dump on server A using mysqldump with option --master-data. You can add --extended-insert or even --add-drop-database, but master-data is the most important option here. man mysqldump :-) My dump command is below


4. transfer the dump file on server B and stop slave (echo "STOP SLAVE" | mysql -p). You can RESET SLAVE if you wish and then import all to MySQL instance:
   mysql -p < ./mysql_dump.sql


5. While keeping the server B hidden on 127.0.0.1 start slave and watch: SHOW SLAVE STATUS\G. In case of problems check the error message and act accordingly. Check what databases you dumped, which of them are on destination machine (especially if it is not the first transfer of data) and so on


6. Because nobody make 'write' statements on hidden slave (server B), you can easily make it master for the server you took data off (server A).
   
   
   
   1. We want to make the new slave (server B) a master for server A. We start executing in mysql prompt SHOW MASTER STATUS and...
   
   
   2. ...writing that parameters on the server A in CHANGE MASTER TO command. This syntax is always confusing me, that's why I have to point you to reference site.
   
   
   3. When you entered it you can start slave on server A, but you can be sure that nothing will change (SHOW SLAVE STATUS\G). Until you have server B hidden, there no statement to transfer thru replication mechanism to server A.
      
   
   


7. Bind the hidden server B to 0.0.0.0 (edit my.cnf and restart)


8. Grab a piece of cake. You deserved ;-)

Dump on server A:

DATABASES=`echo "SHOW DATABASES" | \
  mysql -p | \
  grep -E '(db1|db2|db4)' | \
  grep -v db4_backup | xargs`
nice mysqldump -p --add-drop-database --extended-insert \
  --master-data --databases $DATABASES | \
  gzip -9 > /var/tmp/mysql_transfer.sql; \
  scp /var/tmp/mysql_transfer.sql.gz slaveserver:/var/tmp
rm /var/tmp/mysql_transfer.sql.gz

I write it as one long command with additional semicolons and slashes.
The dump is compressed because I had slow connection between servers. Usually you have pretty good link between them :-) In such case you can transfer data between servers without leaving the destination server (the server B):

DATABASES=`echo "SHOW DATABASES" | \
  mysql --host=serverA -p | \
  grep -E '(db1|db2|db4)' | \
  grep -v db4_backup | xargs`
nice mysqldump --host=serverA -p --add-drop-database --extended-insert \
  --master-data --databases $DATABASES | \
  gzip -9 > /var/tmp/mysql_transfer.sql

Note: if you have many instances, remember to which one you are connecting to. Add --host=www114.ibm.com --port=3305 (for example) to all mysql... commands.

Troubleshooting

Doesn't work? Ha! That's what tigers like most!
First of all check show slave status. What's the error?
If duplicating unique key, you're home. Simply forget --master-data, which blocks tables and adds two statements to dump: MASTER_LOG and MASTER_POS. Please imagine - mysqldump took care of freezing database state, built dump file including position on which it was frozen. Wow!
Another possibility to make similar mistake is when you prepare master-master replication and after start the first slave you allow to change data on both servers before you start the second one. I didn't try to freeze server B and I simply bind it to 127.0.0.1 making it invisible. But probably there is a simpler method.

Another problem is when you have functions in replicated databases, because they can try to write to databases absent on slaves. It's a problem. I solve it building dump with those databases, I add CREATE FUNCTION to the end of dump, but on master the databases are in ignore-bin-db directive in my.cnf and actually aren't not transferred on the network. How to retrieve code of functions creation? SHOW FUNCTION STATUS\G and then SHOW CREATE FUNCTION database_with_functions.YourFunctionName\G;

In case of deeper investigation you can use mysqlbinlog tool. It takes a binlog file (provided as an argument) and translate it to text for you. You can find whats going on in replication tracking the binary log.