EmergencyEMERGENCY? Get 24/7 Help Now!

Docker MySQL Replication 101

 | March 30, 2016 |  Posted In: Cloud and MySQL, Docker, High-availability, MySQL, Percona Server, Replication

PREVIOUS POST
NEXT POST

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’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):

Now create locally persisted directories for the:

  1. Instance configuration
  2. Data files

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:

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:

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

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.

PREVIOUS POST
NEXT POST
Nik Vyzas

Nik Vyzas joined the Percona Remote DBA Team in 2015 and is based out of Athens, Greece. Nik has 10+ years experience working with various SQL, NoSQL and Open-source databases. He has worked at a number of companies including Accenture and Pythian. When he is not working, Nik enjoys coding, gaming and cycling.

6 Comments

    • Thanks for your comment, the articles you’ve shared are wonderful – it is a fantastic series!

      The goal of this blog is to provide a simple guide on getting setup with Docker and using MySQL Replication together, hence the title 101.

      The my.cnf files defined are less than optimal and using links rather than networks to communicate between Docker containers.

  • Thanks for this informative article. And the comment by datacharmer was very useful.

    There is a typo. The second parameter should be “slavedb” instead of “masterdb”.

    # Create local my.cnf directories
    mkdir -p /opt/Docker/masterdb/cnf /opt/Docker/masterdb/cnf

    Can it be used for multi-master slave? I can have multiple slave containers pointing to the same data directory. My master DB names are different. For e.g. can I replicate db “XYZ” from master 1 and db “PQR” from master 2 on a single slave?

    • Thanks updated. You can use Docker for multi-source replication, in that configuration you should rather use a network which the containers are linked to (see https://docs.docker.com/engine/userguide/networking/dockernetworks/#user-defined-networks) – we will explore this in another post related to container failover.

      A note about the data directories for slaves – each slave container must have its own unique directory e.g. /opt/Docker/slavedb1, /opt/Docker/slavedb2.

  • When I tried the same commands using percona:5.5 version, it worked with default log file size. But If I increase it to more than 500 MB then mysql does not start. Is it because I am using older hardware?

    • This was because mysql waits for 30 seconds (only!) before exiting. Since creating larger log files takes time, we need to increase the check from 30 seconds to 180 seconds in the file docker-entrypoint.sh. More info here…

      http://stackoverflow.com/questions/36425888/mysql-does-not-start-with-large-log-files

      I will suggest that this change should be accepted by percona.

Leave a Reply