Mysql master to master Replication with GTID

I have written about Mysql Replication before. That article is now outdated as, for quite some time now, mysql comes with a better replication paradigm.

This article will works with mysql 5.7.x, the same commands and settings should work with 5.6 as well. I’m not sure if anything should be different with a mysql 8 installation.

The idea here is to have 2 separate mysql servers holding the exact same information and (almost) instantly having updates coming form one written on the other, all without creating conflicts.

We will have an already working and live mysql server installation that we will transform into a 2 servers installation, I will not get into mysql installation, just into the configurations details needed for this setup.

Server 1 (the currently live one) will be pyxis and have IP 10.10.0.50 , Server 2 will be gemini and have IP 10.10.1.242.

Just to give an outline, we will enable GTID (if it wasn’t already) on pyxis; make a mysqldump of its content; enable GTID for gemini; replace gemini content with that of pyxis; enable replication from pyxis to gemini; finally enable replication from gemini to pyxis.

Step 1 – Edit Pyxis my.cnf file

We need to make sure that the following information is part of my.cnf for pyxis, these must be under the [mysqld] section of the file. There will likely be many other configurations which we don’t need to change.

server-id                     = 1
log-bin                       = mysql-bin
binlog_format                 = row
gtid-mode                     = ON
enforce-gtid-consistency      = 1
log-slave-updates             = ON
replicate-same-server-id      = 0

once these configurations changes are made we will restart the server.

Step 2 – Configure gemini my.cnf file

For gemini we want pretty much the exact same settings, the only thing is that server-id must be different.

server-id                     = 2
log-bin                       = mysql-bin
binlog_format                 = row
gtid-mode                     = ON
enforce-gtid-consistency      = 1
log-slave-updates             = ON
replicate-same-server-id      = 0

Step 3 – Create replication users

We should now create the replication users (replication works with a connection authenticated by a special user with replication permissions). We will create 2 users, one to allow replication from pyxis to gemini and one vice versa. We will do it before making the backup so that the users are available on both servers (pyxis will have this user because we create it there, gemini will have thanks to the mysqldump we will do next).

CREATE USER 'replica'@'10.10.0.50' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'10.10.0.50';

CREATE USER 'replica'@'10.10.1.242' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'10.10.1.242';

I like to use ‘replica’ as the username, but, of course, you can use whatever you like. ‘yourpassword’ instead is not what i would use for a password 🙂

Step 4 – Create a full mysqldump of Pyxis

Before starting the mysqldump run this command on pyxis

SET @@GLOBAL.read_only = ON;

Then run mysqldump:

mysqldump --all-databases --flush-privileges --single-transaction --flush-logs --triggers --routines 
--events --hex-blob --user=root -p > pyxis.sql

It will ask you to enter the root password for pyxis mysql server and will then create the backup. Depending on the size of you database it might take a few seconds or many minutes.

After the dump is done run this command on pyxis

SET @@GLOBAL.read_only = OFF;

Step 5 – import pyxis backup into gemini

Once the mysqldump is done you will need to move the resulting file to gemini. I’ll leave that to you.

I am assuming the gemini was a just created installation of mysql, nothing modified other than setting the root password, and i will use the same root password used by pyxis.

Then i will set:

SET @@GLOBAL.read_only = ON;

And right after, still within mysql:

source /location/of/mysqldump/moved/pyxis.sql

This will copy all the content of the mysqldump into gemini.

As soon as it is done i will run:

SET @@GLOBAL.read_only = OFF;

Step 6 – start replication from pyxis to gemini

At the beginning of the mysqldump file you will find a line looking like this:

SET @@GLOBAL.GTID_PURGED='xxxxx:xxxx'; 

Instead than ‘xxxxx:xxxx’ there will be letters dashes and number …

This line effectively tells gemini where it should start to catch up with whatever changes have happen on pyxis since the mysqdump was done. It should be already set, but just on case (sometimes it doesn’t get set properly) i will make sure to set it.

The following two statements should not be needed but might become useful when for some reason something didn’t work properly.

RESET MASTER;
SET @@GLOBAL.GTID_PURGED='xxxxx:xxxx'; 

Here is how we start the replication:

CHANGE MASTER TO
MASTER_HOST = '10.10.0.50',
MASTER_PORT = 3306,
MASTER_USER = 'replica',
MASTER_PASSWORD = 'thepassword',
MASTER_AUTO_POSITION = 1;

START SLAVE;

This replication will only do one direction, from pyxis to gemini.

Step 7 – start replication from gemini to pyxis

If everything works well we are ready to create the second part of the replication.

CHANGE MASTER TO 
MASTER_HOST = '10.10.1.242', 
MASTER_PORT = 3306, 
MASTER_USER = 'replica', 
MASTER_PASSWORD = 'thepassword', 
MASTER_AUTO_POSITION = 1; 

START SLAVE;

At this point whatever changes happen in either mysql servers will be replicated over to the other.