Wednesday, March 30, 2016

Docker MySQL Replication 101 [feedly]

Docker MySQL Replication 101

-- via my feedly newsfeed


Precona Server DockerIn 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 use the operating system repository packages, for the latest version use the official

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  apt-get install  # Docker install for Red Hat / CentOS (requires EPEL repo)  yum install epel-release # If not installed already  yum install docker-io  # Pull docker repos  docker pull percona

Now create locally persisted directories for the:

  1. Instance configuration
  2. Data files

# 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/masterdb/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  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:


  • 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


  • 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  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  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/                      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.