Docker master slave MySQL replication
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!
Notice: this post was originally published on my personal blog.
Get the mysql image:
docker pull percona:5.7
- Two separate folders for master and slave.
- The cnf directory has the configuration files for mysql
- The data directory has the volume of mysql structural data.
- The folders called mysql, performance schema and sys should NOT be created. those are done by MySQL server on start.
Check the first image in the post to understand this points.
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
#
# 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:
-
- At this point I also created and empty DB on master so you see he difference once replication starts:
- At this point I also created and empty DB on master so you see he difference once replication starts:
Setup replication according to master's output:
Can be done from the host outside the master 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;
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:
-
- 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.
- 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:
- 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.
mysql> create database replicate_this;
-
- If the replication is working as expected this DB should be created on slave as follows:
- If the replication is working as expected this DB should be created on slave as follows:
Conclusion:
-
- We managed to create a simple example of replication of Percona MySQL 5.7.
- All was done using docker containers.
- This is a simplified version and should not be used on production environment.