DaveQB's Rambles

It turns out that multi-master replication is nothing more than a "criss-cross" master-slave replication setup. To explain that better with an example, server A is the master for server B and server B is the master of server A. This obviously mean any changes on either is replicated to the other. So you simply get master-slave replication working in one way, then mimic that on the other side once satisfied.

The first step in getting this working is to set up the config files. See here:

[mysqld]
# Needed for masters and slaves
server-id       = 3
log_bin         = /var/log/mysql/mysql-bin.log
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=true

# Needed for the slaves
# Better to filter here than using binlog_do_db on the master etc
# http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/
# more here: http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_binlog-do-db
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%

relay_log         = /var/log/mysql/mysql-relay-bin.log
# http://jonathonhill.net/2011-09-30/mysql-replication-that-hurts-less/
auto-increment-offset = 1
auto-increment-increment = 4
read_only=OFF

I simply add this to /etc/mysql/conf.d/ ensuring the file name ends in .cnf. Restart mysql and that part is done. Next we connect to one of the servers, let's call this one A. Step 1 is to create a dump or a snapshot of your data.

 mysqldump -u root -p --all-databases --flush-privileges --single-transaction --master-data=2 --flush-logs --triggers --routines --events --hex-blob  |bzip2 > $HOSTNAME-$(date +%F).sql.bz2

Next step is to add the replication user on server A. You could use root, but best not to as you will be setting up the user (password and all) on another server.

 GRANT REPLICATION SLAVE ON *.* TO 'rep'@'slave_ip' IDENTIFIED BY 'some_secret';

Now on server B, import the dump from A.

 bzcat $HOSTNAME-$(date +%F).sql.bz | mysql -u root -p

Now we tell server B where the master server is and the user to use (the one we just setup).

 change master to master_host='master_ip", master_port=3306, master_user='rep', master_password='some_secret', master_auto_position=1; 
start slave; 

We should be up and running. Check with the "show slave status\G" command on the slave. Now we simply repeat this but the other way around (skipping the dump and restore) in order to set up multi-master. Setup a replication user on B, then run the "change master to" command on server A so it is now a slave of B. All should be done now. See the references for troubleshooting. I actually use stunnel to connect my MySQL servers over the internet. Also, you can create a ~/.my.cnf file with your login info to save having to pass that to the mysql command every time. Contents would like so:

[client]
user=root
password=some_secret

Ref: http://fromdual.com/gtid_in_action?_ga=1.204815570.317472044.1416546765 http://fromdual.com/replication-troubleshooting-classic-vs-gtid https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication http://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/