Docker master slave mysql replication can be a bit intimidating, but the basics are actually quite easy, just a bit of a laborious work. As of this example I will use docker swarm. for that I assume you have docker already installed fresh on the machine. My example uses CentOS 7.6, but should be similar on other systems like ubuntu. Enjoy the post!

Get the mysql image:

docker pull percona:5.7

Create a few dirs to hold the structure:

1. Two separate folders for master and slave.
2. The cnf directory has the configuration files for mysql
3. The data directory has the volume of mysql structural data.
4. The folders called mysql, performance schema and sys should NOT be created. those are done by MySQL server on start.

Create the docker swarm files.

I am using docker swarm, because it’s very easy, but these following few steps are essentially to build the containers and run them. Other Orchestration systems work similarly. If you want to go with Kubernetes (k8s) and a high availability cluster you will need something like this article from My Friend Alex about “How to deploy Kubernetes HA cluster”.

Initialize docker swarm:

docker swarm init --advertise-addr=10.15.0.6

Join swarm cluster:

docker swarm join --token \
SWMTKN-1-3c32m8jq5epyaoo711b1beb2sirqgawhw98xd1jhua1c4jaqq9-1t44577d2t22snqnitujl8taf \
10.15.0.6:2377

Create MySQL config files for the master and the slave:

Here add this file: /opt/scripts/masterdb/cnf/master.cnf

[mysqld]
server-id=1
binlog_format=ROW
log-bin

Then do the same for the slave here: /opt/scripts/slavedb/cnf/slave.cnf

[mysqld]
server-id=2

Create docker swarm yml file:

version: '3.2'

services:

  db_master:

    image: percona

    environment:

      MYSQL_ROOT_PASSWORD: examplei123!

    volumes:

      - /opt/scripts/masterdb/data:/var/lib/mysql

      - /opt/scripts/masterdb/cnf/master.cnf:/etc/my.cnf.d/master.cnf

  db_slave:

    image: percona

    environment:

      MYSQL_ROOT_PASSWORD: examplei123!

    volumes:

      - /opt/scripts/slavedb/data:/var/lib/mysql

      - /opt/scripts/slavedb/cnf/slave.cnf:/etc/my.cnf.d/slave.cnf

Run the stack:

# docker stack deploy -c docker-master-slave.yml dbs

Creating network dbs_default

Creating service dbs_db_master

Creating service dbs_db_slave

#

Make sure the stack is running:

# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

8f5954413422 percona:latest "/docker-entrypoint.…" 11 seconds ago Up 10 seconds 3306/tcp dbs_db_master.1.o95296xfp17d83c7rhb67s334

96157a26d449 percona:latest "/docker-entrypoint.…" 14 seconds ago Up 13 seconds 3306/tcp dbs_db_slave.1.jpqgijiyvxbz3ymts1qwfwmwb

We have our containers running now. Let’s work on replication.

Notice: You don’t need to if it works, but you can stop the stack with:

docker stack rm dbs

Create replication user on master:

Notice: the “\” character is introduced for a line jump you might want to convert all into one single line if the command does not work for you.

[root@srv-docker-db1 scripts]# docker exec -ti 18776ab98a0f 'mysql' -uroot -pd \
-vvv -e"GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'slavepass123'\G"

mysql: [Warning] Using a password on the command line interface can be insecure.

--------------

GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'slavepass123'

--------------

Query OK, 0 rows affected, 1 warning (0.00 sec)

Bye

[root@srv-docker-db1 scripts]#

Check that is working:

# docker exec -it 8f5954413422 sh

sh-4.2$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.24-26 Percona Server (GPL), Release 26, Revision c8fe767

Copyright (c) 2009-2018 Percona LLC and/or its affiliates

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants for repl@'%';

+----------------------------------------------+

| Grants for repl@% |

+----------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' |

+----------------------------------------------+

1 row in set (0.00 sec)

mysql>

**As you see the user exists on master and has replication privileges.

Show binary logs position to replicate to slave:

# docker exec -ti dbs_db_master.1.ndu3zg45si7o73pia77nryw9a 'db_master' -uroot -pexamplei123! -e "SHOW MASTER STATUS\G"

mysql: [Warning] Using a password on the command line interface can be insecure.

*************************** 1. row ***************************

File: 3a34be5b7f9b-bin.000001

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

• 1. At this point I also created and empty DB on master so you see he difference once replication starts:
     

Setup replication coordinates as per master output:

Can be done ether from outside like this:

docker exec -ti slavedb 'db_master' -uroot -pmysecretpass -e'change master \ 
to master_host="mysql",master_user="repl", \
master_password="slavepass",master_log_file="mysqld-bin.000004", \ 
master_log_pos=310;"' -vvv

Or login the slave and execute:

change master to master_host="db_master",master_user="repl", \
master_password="slavepass123",master_log_file="3a34be5b7f9b-bin.000001", \
master_log_pos=154;

Start the slave replication

START SLAVE;

Replication status

If all the steps wet well after this you should be able to see the replication status:

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: db_master

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: 3a34be5b7f9b-bin.000001

Read_Master_Log_Pos: 154

Relay_Log_File: 089eecce4162-relay-bin.000002

Relay_Log_Pos: 327

Relay_Master_Log_File: 3a34be5b7f9b-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 154

Relay_Log_Space: 541

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: c67dabf3-fcea-11e8-8f31-02420a000103

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

mysql>

Make sure all works

Then we are done, but we need to make sure it works, for that we just need to create something on master and is should be replicated to slave:

• 1. First remember on item (Show binary logs position to replicate to slave) we created a dummy db on master. If you check now it did not replicate to slave. This behaviour is expected.
     
  2. So let’s try to create another DB again now on the master, lets call it “replicate_this”. To do that execute this on master:

mysql> create database replicate_this;

• 1. If the replication is working as expected this DB should be created on slave as follows:
     

Conclusion:

• 1. We managed to create a simple example of replication of Percona MySQL 5.7.
  2. All was done using docker containers.
  3. This is a simplified version and should not be used on production environment.