Docker MySQL Replication 101

March 30, 2016

Author

Nik Vyzas

Cloud

Insight for DBAs

MySQL

Share this Post:

In this blog post, we’ll discuss some of the basics regarding Docker MySQL replication. Docker has gained widespread popularity in recent years as a lightweight alternative to virtualization. It is ideal for building virtual development and testing environments. The solution is flexible and seamlessly integrates with popular CI tools.

This post walks through the setup of MySQL replication with Docker using Percona Server 5.6 images. To keep things simple we’ll configure a pair of instances and override only the most important variables for replication. You can add whatever other variables you want to override in the configuration files for each instance.

Note: the configuration described here is suitable for development or testing. We’ve also used the operating system repository packages; for the latest version use the official Docker images. The steps described can be used to setup more slaves if required, as long as each slave has a different server-id.

First, install Docker and pull the Percona images (this will take some time and is only executed once):

# Docker install for Debian / Ubuntu<br>apt-get install docker.io<br><br># Docker install for Red Hat / CentOS (requires EPEL repo)<br>yum install epel-release # If not installed already<br>yum install docker-io<br><br># Pull docker repos<br>docker pull percona<br>

1	# Docker install for Debian / Ubuntu<br>apt-get install docker.io<br><br># Docker install for Red Hat / CentOS (requires EPEL repo)<br>yum install epel-release # If not installed already<br>yum install docker-io<br><br># Pull docker repos<br>docker pull percona<br>

Now create locally persisted directories for the:

Instance configuration

Data files

# Create local data directories<br>mkdir -p /opt/Docker/masterdb/data /opt/Docker/slavedb/data<br><br># Create local my.cnf directories<br>mkdir -p /opt/Docker/masterdb/cnf /opt/Docker/slavedb/cnf<br><br>### Create configuration files for master and slave<br><br>vi /opt/Docker/masterdb/cnf/config-file.cnf<br># Config Settings:<br>[mysqld]<br>server-id=1<br>binlog_format=ROW<br>log-bin<br><br>vi /opt/Docker/slavedb/cnf/config-file.cnf<br># Config Settings:<br>[mysqld]<br>server-id=2<br><br>

# Create local data directories mkdir -p /opt/Docker/masterdb/data /opt/Docker/slavedb/data # Create local my.cnf directories mkdir -p /opt/Docker/masterdb/cnf /opt/Docker/slavedb/cnf ### Create configuration files for master and slave vi /opt/Docker/masterdb/cnf/config-file.cnf # Config Settings: [mysqld] server-id=1 binlog_format=ROW log-bin vi /opt/Docker/slavedb/cnf/config-file.cnf # Config Settings: [mysqld] server-id=2

Great, now we’re ready start our instances and configure replication. Launch the master node, configure the replication user and get the initial replication co-ordinates:

# Launch master instance<br>docker run --name masterdb -v /opt/Docker/masterdb/cnf:/etc/mysql/conf.d -v /opt/Docker/masterdb/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6<br><br>00a0231fb689d27afad2753e4350192bebc19ab4ff733c07da9c20ca4169759e<br><br># Create replication user<br>docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -vvv -e"GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'slavepass'G"<br><br>mysql: [Warning] Using a password on the command line interface can be insecure.<br>--------------<br>GRANT REPLICATION SLAVE ON *.* TO repl@"%"<br>--------------<br><br>Query OK, 0 rows affected (0.02 sec)<br><br>Bye<br><br>### Get master status<br>docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -e"SHOW MASTER STATUSG"<br><br>mysql: [Warning] Using a password on the command line interface can be insecure.<br>*************************** 1. row ***************************<br>             File: mysqld-bin.000004<br>         Position: 310<br>     Binlog_Do_DB: <br> Binlog_Ignore_DB: <br>Executed_Gtid_Set:<br><br>

# Launch master instance docker run --name masterdb -v /opt/Docker/masterdb/cnf:/etc/mysql/conf.d -v /opt/Docker/masterdb/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6 00a0231fb689d27afad2753e4350192bebc19ab4ff733c07da9c20ca4169759e # Create replication user docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -vvv -e"GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'slavepass'G" mysql: [Warning] Using a password on the command line interface can be insecure. -------------- GRANT REPLICATION SLAVE ON *.* TO repl@"%" -------------- Query OK, 0 rows affected (0.02 sec) Bye ### Get master status docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -e"SHOW MASTER STATUSG" mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row *************************** File: mysqld-bin.000004 Position: 310 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set:

If you look carefully at the “docker run” command for masterdb, you’ll notice we’ve defined two paths to share from local storage:

/opt/Docker/masterdb/data:/var/lib/mysql<br>

1	/opt/Docker/masterdb/data:/var/lib/mysql<br>

This maps the local “/opt/Docker/masterdb/data” to the masterdb’s container’s “/var/lib/mysql path”

All files within the datadir “/var/lib/mysql” persist locally on the host running docker rather than in the container

/opt/Docker/masterdb/cnf:/etc/mysql/conf.d<br>

1	/opt/Docker/masterdb/cnf:/etc/mysql/conf.d<br>

This maps the local “/opt/Docker/masterdb/cnf” directory to the container’s “/etc/mysql/conf.d” path

The configuration files for the masterdb instance persist locally as well

Remember these files augment or override the file in “/etc/mysql/my.cnf” within the container (i.e., defaults will be used for all other variables)

We’re done setting up the master, so let’s continue with the slave instance. For this instance the “docker run” command also includes the “–link masterdb:mysql” command, which links the slave instance to the master instance for replication.

After starting the instance, set the replication co-ordinates captured in the previous step:

docker run --name slavedb -d -v /opt/Docker/slavedb/cnf:/etc/mysql/conf.d -v /opt/Docker/slavedb/data:/var/lib/mysql --link masterdb:mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6<br><br>eb7141121300c104ccee0b2df018e33d4f7f10bf5d98445ed4a54e1316f41891<br><br>docker exec -ti slavedb 'mysql' -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<br><br>mysql: [Warning] Using a password on the command line interface can be insecure.<br>--------------<br>change master to master_host="mysql",master_user="repl",master_password="slavepass",master_log_file="mysqld-bin.000004",master_log_pos=310<br>--------------<br><br>Query OK, 0 rows affected, 2 warnings (0.23 sec)<br><br>Bye<br>

docker run --name slavedb -d -v /opt/Docker/slavedb/cnf:/etc/mysql/conf.d -v /opt/Docker/slavedb/data:/var/lib/mysql --link masterdb:mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6 eb7141121300c104ccee0b2df018e33d4f7f10bf5d98445ed4a54e1316f41891 docker exec -ti slavedb 'mysql' -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 mysql: [Warning] Using a password on the command line interface can be insecure. -------------- change master to master_host="mysql",master_user="repl",master_password="slavepass",master_log_file="mysqld-bin.000004",master_log_pos=310 -------------- Query OK, 0 rows affected, 2 warnings (0.23 sec) Bye

Almost ready to go! The last step is to start replication and verify that replication running:

# Start replication<br>docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"START SLAVE;" -vvv <br><br>mysql: [Warning] Using a password on the command line interface can be insecure.<br>--------------<br>START SLAVE<br>--------------<br><br>Query OK, 0 rows affected, 1 warning (0.00 sec)<br><br>Bye<br><br># Verify replication is running OK<br>docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"SHOW SLAVE STATUSG" -vvv<br><br>mysql: [Warning] Using a password on the command line interface can be insecure.<br>--------------<br>SHOW SLAVE STATUS<br>--------------<br><br>*************************** 1. row ***************************<br>               Slave_IO_State: Waiting for master to send event<br>                  Master_Host: mysql<br>                  Master_User: repl<br>                  Master_Port: 3306<br>                Connect_Retry: 60<br>              Master_Log_File: mysqld-bin.000004<br>          Read_Master_Log_Pos: 310<br>               Relay_Log_File: mysqld-relay-bin.000002<br>                Relay_Log_Pos: 284<br>        Relay_Master_Log_File: mysqld-bin.000004<br>             <strong>Slave_IO_Running: Yes<br>            Slave_SQL_Running: Yes</strong><br>              Replicate_Do_DB: <br>          Replicate_Ignore_DB: <br>           Replicate_Do_Table: <br>       Replicate_Ignore_Table: <br>      Replicate_Wild_Do_Table: <br>  Replicate_Wild_Ignore_Table: <br>                   Last_Errno: 0<br>                   Last_Error: <br>                 Skip_Counter: 0<br>          Exec_Master_Log_Pos: 310<br>              Relay_Log_Space: 458<br>              Until_Condition: None<br>               Until_Log_File: <br>                Until_Log_Pos: 0<br>           Master_SSL_Allowed: No<br>           Master_SSL_CA_File: <br>           Master_SSL_CA_Path: <br>              Master_SSL_Cert: <br>            Master_SSL_Cipher: <br>               Master_SSL_Key: <br>        <strong>Seconds_Behind_Master: 0</strong><br>Master_SSL_Verify_Server_Cert: No<br>                Last_IO_Errno: 0<br>                Last_IO_Error: <br>               Last_SQL_Errno: 0<br>               Last_SQL_Error: <br>  Replicate_Ignore_Server_Ids: <br>             Master_Server_Id: 1<br>                  Master_UUID: 230d005a-f1a6-11e5-b546-0242ac110004<br>             Master_Info_File: /var/lib/mysql/master.info<br>                    SQL_Delay: 0<br>          SQL_Remaining_Delay: NULL<br>      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it<br>           Master_Retry_Count: 86400<br>                  Master_Bind: <br>      Last_IO_Error_Timestamp: <br>     Last_SQL_Error_Timestamp: <br>               Master_SSL_Crl: <br>           Master_SSL_Crlpath: <br>           Retrieved_Gtid_Set: <br>            Executed_Gtid_Set: <br>                Auto_Position: 0<br>1 row in set (0.00 sec)<br><br>Bye<br>

# Start replication docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"START SLAVE;" -vvv mysql: [Warning] Using a password on the command line interface can be insecure. -------------- START SLAVE -------------- Query OK, 0 rows affected, 1 warning (0.00 sec) Bye # Verify replication is running OK docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"SHOW SLAVE STATUSG" -vvv mysql: [Warning] Using a password on the command line interface can be insecure. -------------- SHOW SLAVE STATUS -------------- *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqld-bin.000004 Read_Master_Log_Pos: 310 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: mysqld-bin.000004 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: 310 Relay_Log_Space: 458 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: 230d005a-f1a6-11e5-b546-0242ac110004 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 the slave I/O thread to update it 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 1 row in set (0.00 sec) Bye

Finally, we have a pair of dockerized Percona Server 5.6 master-slave servers replicating!

As mentioned before, this is suitable for a development or testing environment. Before going into production with this configuration, think carefully about the tuning of the “my.cnf” variables and the choice of disks used for the data/binlog directories. It is important to remember that newer versions of Docker recommend using “networks” rather than “linking” for communication between containers.