Docker MySQL Replication 101

Docker MySQL Replication 101

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

Share this post

Comments (7)

  • datacharmer Reply

    Your method of using passwords in the open is less than optimal.
    I have described how to use password files and GTID in
    MySQL-Docker operations. – Part 2: Customizing MySQL in Docker.

    I have also shown a semi-automated method for setting replication:
    MySQL-Docker operations. – Part 3: MySQL replication in Docker.

    March 31, 2016 at 10:22 am
    • Nik Vyzas Reply

      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.

      March 31, 2016 at 10:36 am
  • shantanu oak Reply

    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?

    April 3, 2016 at 10:07 am
  • shantanu oak Reply

    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?

    April 5, 2016 at 8:16 am
  • Nk Reply

    Thanks for article; Is there a way to give auto fill the values of master_log_file etc.. in this query “change master to master_host=”mysql”,master_user=”repl”,master_password=”slavepass”,master_log_file=”mysqld-bin.000004″,master_log_pos=310;” ?

    I am trying to prepare docker compose file for master and slave. I want to slave to use run this query while bringing up the mysql server so that these would be automated instead of manual run. Don’t whether I can pass this as environment variable in DockerFile as a hardcoded values ??

    January 8, 2018 at 12:30 pm

Leave a Reply