Mysql Master-Master Replication setup on Docker

Ever wondered why Docker is so much popular. Here it is why?  Docker has made it so much easier to spin up nodes as required and then map them all in quick easy way .

In this tutorial I am going to setup a Master-Master Replication between 2 Mysql Nodes , and of course both of them running on Docker based on Ubuntu 16 LTS.

Requirements: –

  • docker setup . Refer to Docker article for installing docker
  • docker pull mysql:latest from docker hub repo .

Lets crack on

Step 1  – Prepare the configurations / data folders 

Best thing of this docker mysql image is that you can setup your own Data, Log, config, passwords based on your requirements.  So first of all we would create directory structure as below for each node we want to spin up.

~/server#/backup  – This would contain any

~/server#/data – This would be starting point from where the data would be mounted / created. again the data would be persisted during restarts as it is going to be a host mounted volume.

~/server#/log – For storing any log files & persisting them

~/server#/conf.d – mounting special configuration files.

Make sure that the owner of the above folders / files is set to 999:999 Lets create the 2 configuration files for both nodes . The content would be like below

~/server1/conf.d/server1.cnf

[code language=”perl”]
[mysqld]
server-id = 101
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydata
bind-address = 0.0.0.0 # make sure to bind it to all IPs, else mysql listens on 127.0.0.1
character_set_server = utf8
collation_server = utf8_general_ci

[mysql]
default_character_set = utf8[/code]
~/server1/backup/initdb.sql

[code language=”sql”]use mysql;
create user ‘replicator’@’%’ identified by ‘repl1234or’;
grant replication slave on *.* to ‘replicator’@’%’;
# do note that the replicator permission cannot be granted on single database.
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
SHOW VARIABLES LIKE ‘server_id’;[/code]
~/server2/conf.d/server2.cnf

[code language=”perl”][mysqld]
server-id = 102 # Remember this is only Integer per official documentation
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydata
bind-address = 0.0.0.0 # make sure to bind it to all IPs, else mysql listens on 127.0.0.1
character_set_server = utf8
collation_server = utf8_general_ci
[mysql]
default_character_set = utf8[/code]
~/server2/backup/initdb.sql

[code language=”sql”]use mysql;
create user ‘replicator’@’%’ identified by ‘repl1234or’;
grant replication slave on *.* to ‘replicator’@’%’;
# do note that the replicator permission cannot be granted on single database.
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
SHOW VARIABLES LIKE ‘server_id’;[/code]

Step 2 – Launch the Nodes with the configurations

With the above files created, now we are good to create the Containers with the above configurations / Data folders.

# Launch node1

[code language=”bash”]docker run –name mysql1 -e MYSQL_ROOT_PASSWORD=mysql1pass -e MYSQL_DATABASE=mydata -dit -p 33061:3306 -v /opt2/mysql/server1/conf.d:/etc/mysql/mysql.conf.d/   -v /opt2/mysql/server1/data:/var/lib/mysql -v /opt2/mysql/server1/log:/var/log/mysql -v /opt2/mysql/server1/backup:/backup -h  mysql1 mysql[/code]
# Launch node2

[code language=”bash”]docker run –name mysql2  <strong>–link mysql1</strong> -e MYSQL_ROOT_PASSWORD=mysql2pass -e MYSQL_DATABASE=mydata -dit -p 33062:3306 -v /opt2/mysql/server2/conf.d:/etc/mysql/mysql.conf.d/   -v /opt2/mysql/server2/data:/var/lib/mysql -v /opt2/mysql/server2/log:/var/log/mysql -v /opt2/mysql/server2/backup:/backup -h  mysql2 mysql[/code]
Give the nodes some time to boot up and make the services available.  Also note that we have linked the mysql2 node with mysql1 node during the “docker run” time itself.

Step 3 – Link Node1 with node2 (unofficial way)

The link other way around is not possible officially as I read in some articles / stackoverflow, but I have found a workaround to link mysql1 with mysql2 inside docker0 interface .  Key thing is that docker just creates  host entry to the linked container, and this can be achieved if we modify the host file within running container. Beware this IP can be changed by docker if your container restarts.

So we find out the runtime IP of the mysql2 node and then create a host entry within mysql1 node to point to correct IP of mysql2. Here are steps

[code language=”bash”]# find out IP Address of mysql2

mysql2ip=$(docker inspect –format ‘{{ .NetworkSettings.IPAddress }}’ mysql2)

#Append the new IP as new host entry in mysql1’s host file.

docker exec -i mysql1 sh -c "echo ‘$mysql2ip mysql2 mysql2’ >> /etc/hosts"

# Check if the above command worked

docker exec -i mysql1 sh -c "cat /etc/hosts"[/code]

Here are steps to verify connectivity both ways.

[code language=”bash”]docker exec -ti mysql2 sh -c "ping mysql1"
docker exec -ti mysql1 sh -c "ping mysql2"[/code]
ping-verify

Now the nodes are up, time to setup the replication.

Step 4 – Initialize the Nodes to create replication users as well as check Master Log / position and verify server_id

Node1 

Connect to node1  and run the /backup/initdb.sql

[code language=”bash”]/opt2/mysql$ docker exec -ti mysql1 sh -c "mysql -uroot -p"
Enter password:
mysql> source /backup/initdb.sql
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00<span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span> sec)
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000003 | 154 | mydata | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id | 101 |
+—————+——-+
1 row in set (0.01 sec)

[/code]

Node2

Connect to node2  and run the /backup/initdb.sql

[code language=”bash”]/opt2/mysql$ docker exec -ti mysql2 sh -c "mysql -uroot -p"
Enter password:
mysql> source /backup/initdb.sql
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000003 | 154 | mydata | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)
+—————+——-+
| Variable_name | Value |
+—————+——-+
| server_id | 102 |
+—————+——-+
1 row in set (0.01 sec)[/code]

Now, both the Nodes are showing up very much similar File name and position .  Also note that the server-id displayed should be unique , which is why the server1.cnf and server2.cnf had different server-id variables.

Step 5 – Setup the Replication source for both nodes. 

Node2 .

[code language=”bash”]/opt2/mysql$ docker exec -ti mysql2 sh -c "mysql -uroot -p"
Enter password:
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST = ‘mysql1’, MASTER_USER = ‘replicator’,
-> MASTER_PASSWORD = ‘repl1234or’, MASTER_LOG_FILE = ‘mysql-bin.000003’,
-> MASTER_LOG_POS = 154;
mysql> start slave;
mysql> show slave status\g[/code]
Node1 .

[code language=”bash”]/opt2/mysql$ docker exec -ti mysql1 sh -c "mysql -uroot -p"
Enter password:
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST = ‘mysql2’, MASTER_USER = ‘replicator’,
-> MASTER_PASSWORD = ‘repl1234or’, MASTER_LOG_FILE = ‘mysql-bin.000003’,
-> MASTER_LOG_POS = 154;
mysql> start slave;
mysql> show slave status\g[/code]

Step 6 – Testing Master-Master Replication

We are going to test the replication. To do this, we will create a table in our mydata database on Node 1 and check on Node 2 to see if gets reflected. Then, we will remove the table in from Node2 and ideally the Node1 should no longer show up on Node1.

Lets create a table

[code language=”sql”]use mydata;
create table students (‘id’ int, ‘name’ varchar(20));[/code]
We now are going to check Node2 to see if our table exists.

[code language=”sql”]show tables in mydata;[/code]
We should see output similiar to the following:

[code language=”sql”]+——————-+
| Tables_in_mydata |
+——————-+
| students |
+——————-+
1 row in set (0.00 sec)[/code]
The last test to do is to delete our table from node2. It should also be deleted from Node1. We can do this by entering the following on node2 mysql prompt:

[code language=”sql”]DROP TABLE students;[/code]
To confirm this, running the “show tables” command on node1 will show no tables:

[code language=”sql”]Empty set (0.00 sec)[/code]
Thats it! A completely working mysql master-master replication setup on Docker.

Happy Reading !  Enjoy !

Some References

https://github.com/besnik/tutorials/tree/master/docker-mysql

http://stackoverflow.com/questions/17157721/getting-a-docker-containers-ip-address-from-the-host

Source Code / Configurations i used   – https://github.com/vnextcoder/docker/tree/master/mysql