In my prior article, “Running a relational database with Docker”, I showed how to spin up a Docker container with an instance of MySQL running in it. I also showed how you can persist data even when you blow away the container and recreate it by connecting to a Volume or Bind Mount.

In this article I am going to extend this to show you how to use Replication with MySQL instances in Docker Containers.

Replication Basics

For a relational database like MySQL, you can achieve high availability even when using it inside of Docker containers. While this is mostly common in production environments like standalone servers and virtual machines, you can do the same with Docker, however with a different setup.

For standalone servers or virtual machines you will typically set MySQL replication by manually configuring the my.cnf file; here’s my article on the SimpleTalk blog that describes this in detail. But MySQL replication for Docker is done using environment variables, Docker networks and volumes to persist data.

Replication involves syncing data between a primary database which is also referred to as master and one or more secondary databases (replicas) also referred to as slaves. For the master and slave(s) to communicate, the master logs all changes to the binary log and the slave(s) reads and applies those changes.

This means that read operations are distributed across replicas while only the master handles all write operations. Each MySQL instance will have a unique identifier, server-id=1 and server-id=2, server-id=3 and the master must have binary logging enabled to allow all records or changes to the database in a binary format so that slaves read from these logs and apply the changes.

In our use case we will spin up the master MySQL container first and then configure it for replication. After that, we’ll start a slave MySQL container that will sync data from the master. Keep in mind that, the master and its replicas should communicate over the same Docker network and then use a shared replication user for authentication and secure data transfer.

Configuring Replication in Containers

To begin, remove your already existing MySQL container:

We already have a Docker network mysql-net created; use it to start the MySQL master container:

From the command above we are running the master container with the following configuration settings:

And then, pass several MySQL specific configuration options for replication:

Next, confirm that the MySQL container is running;

Once it is running, we must create a replication user with REPLICATION SLAVE privileges that the slave containers will use to connect and sync data from the master’s binary logs.

Connect to the MySQL shell inside the master container and sign in as root:

Execute the following SQL commands one by one to create a replication user:

From the above command, the replication user is jane, and ‘%’ allows connections from any host/IP address, unlike a very restrictive user definition jane’@’%.example.com which restricts access to hosts within the example.com domain. The follow up command - IDENTIFIED BY 'jane123'; means jane123 is the assigned password for the user jane.

For the other statement – GRANT REPLICATION SLAVE ON *.* TO 'jane'@'%'; we are simply telling MySQL to grant the REPLICATION SLAVE privilege to the user jane; which applies immediately.

Display the status of the MySQL binary log with the below command:

You should see the following output:

From the output above the position is 3172 and the Binary_Do_DB is set to testdb which is what we want. The position 3172 is a binary position within the binary log where the next event should be read by the slave(s). This implies that the slave containers should start reading the binary log file mysql-bin.000003 from position 3172.

The other parameters Binlog_Ignore_DB and Executed_Gtid_Set specifies which database should not be logged in the binary log and tracks the GTIDs of executed transactions respectively. Since these parameters were not set, they are empty, no databases are explicitly ignored, and no GTID transactions have been recorded.

Now start a MySQL slave container:

This will do the following:

Confirm the slave container is running:

Open a shell in the slave container as the root user:

Configure the replica with the following command:

Here’s what each part of the statement above does:

Next, initiate the replication process by allowing the slave (replica) container to start reading and applying changes from the master’s binary logs:

Verify that the replication is done successfully and that it is working correctly:

If you have an output similar to the one below, the replication is set:

From the above output:

Testing the Replication Configuration

We have successfully set replication to apply changes from the master MySQL container. To test out our setup, we’ll need to insert a test record into the testdb database on the mysql-master container. We are using the testdb database because it is the one explicitly included in the master’s binary logs via the binlog-do-db setting.

Access the MySQL shell in the master container:

Switch to the testdb database:

Create a table users and insert a record:

Now confirm that the data has been replicated on the mysql-slave container:

If the replication is working correctly you should have the new row outputted to you like so:

Conclusion

Through this article, you now have an understanding of how you can configure MySQL replication between Docker containers. Whether you want to run MySQL using Docker for testing purposes or preparing for production you now have all the necessary configuration steps at your fingertips.