MySQL Docker Containers: Quick Async Replication Test Setup

This blog discusses a few concepts about Docker and how we can use it to run a MySQL async replication environment. Docker is a tool designed to make it easier for developers and sysadmins to create/develop, configure, and run applications with containers. The container allows us to package all parts of the application it needs, such as libraries, dependencies like code, configurations, and runtime engine. Docker runtime containers are platform-independent so the package created can be shipped one platform to another platform.

Dockerhub is the repository where you can find containerized docker images for applications like MySQL, Percona Server for MySQL , and MariaDB. Using the example below, I will show you how to set up a docker container from the Pecona Server for MySQL docker image that I download from the repository.

Custom Network Instead of the Default

First, I will create a network that my docker instances will use. I will be using a user-defined network instead of the default one. It is recommended to use the user-defined bridge networks to control which containers can communicate with each other. Docket daemon automatically takes care of DNS name resolution. By creating your own network, every single container using that network will have DNS resolution automagically.

MacBook-Pro: $ docker network create --driver bridge isolated_nw
f8cd8f09b4042b39b04a6a43fd9dc71507af22dfd6ee2817fa80360577b24d6f
MacBook-Pro: $

Storage for Persisting the Data

The second step is to provision the storage which my docker instances will be using. In docker, storage can be provisioned in two ways, by using a bind mount or by using a docker volume. Bind mounts are dependent on the directory structure of the host machine while docker volumes are completely managed by Docker. In my example, I am using bind mounts for the primary instance and docker volumes for the replica to illustrate how either of these options can be used.

MacBook-Pro: $ mkdir /Users/myuser/mysql_primary_data

Configuration File for the Primary Instance

I will proceed with the creation of the configuration file which my primary instance will be using.

MacBook-Pro: $ cat /Users/myuser/primaryconf/my-custom.cnf
[mysqld]
server-id = 100
log_bin

Provisioning the Primary Instance

In the fourth step, we will provision the primary instance. The docker run command will download the latest percona server image if the image does not already exist in the local repository. In this example, we already have the downloaded image so the docker run does not need to download it again.

MacBook-Pro: $ docker run --network=isolated_nw -v /Users/myuser/mysql_primary_data:/var/lib/mysql -v /Users/myuser/primaryconf:/etc/percona-server.conf.d/ -p 3308:3306 -p 33061:33060 --name percona_primary -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD=MysqlTestInstance -d percona --default-authentication-plugin=mysql_native_password
deb40d6941db74d845cbc5ec550572d37d4763740e3a72c015ada0c7520a0fd7
MacBook-Pro: $

I intend to set up an async replication environment, so I will get the binary log details from the primary instance.

MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "show master status;"
Enter password:
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| deb40d6941db-bin.000003 |      154 |              |                  |                   |
+-------------------------+----------+--------------+------------------+-------------------+
MacBook-Pro: $

For setting the replication we need a user and the below command connects the primary instance and grants the privilege.

MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'replpass';"
Enter password:
MacBook-Pro: $

Docker Volume for Replication Storage

Before I create my replica instance, I will provision the docker volume which my replica instance will use for storage.

MacBook-Pro: $ docker volume create mysqlreplicavol
mysqlreplicavol
MacBook-Pro: $

Configuration File for the Replica Instance

Create a custom MySQL configuration file for the replica instance.

MacBook-Pro: $ cat /Users/myuser/replicaconf/my-custom.cnf
[mysqld]
server-id = 101

Provisioning the Replica Instance

The next step is to set up a replica instance using the docker volume that I created above.

MacBook-Pro: $ docker run --network=isolated_nw -v mysqlreplicavol:/var/lib/mysql -v /Users/myuser/replicaconf:/etc/percona-server.conf.d/ -p 3309:3306 -p 33062:33060 --name percona_replica -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD=MysqlTestInstance -d percona --default-authentication-plugin=mysql_native_password
98c109998a522a51c4ceca7d830a1f9af2abdd408c5e1d6d12cfd55af13d170d
MacBook-Pro: $

To set up the replication, apply the change master command in the replica instance and start the replica.

MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "CHANGE MASTER TO MASTER_HOST='percona_primary',MASTER_USER='repl_user', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='deb40d6941db-bin.000003', MASTER_LOG_POS=154;"
Enter password:
MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "start slave";
Enter password:
MacBook-Pro: $

Verify the Replication Status

MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "show slave status \G" |egrep 'Running|Master' | egrep -v 'SSL|TLS'
Enter password:
                  Master_Host: percona_primary
                  Master_User: repl_user
                  Master_Port: 3306
              Master_Log_File: deb40d6941db-bin.000003
          Read_Master_Log_Pos: 434
        Relay_Master_Log_File: deb40d6941db-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 434
        Seconds_Behind_Master: 0
             Master_Server_Id: 100
                  Master_UUID: 49451bba-2627-11ea-be09-0242ac120002
             Master_Info_File: /var/lib/mysql/master.info
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
MacBook-Pro: $

Create a test DB to confirm it is replicating to the replica instance.

MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "create database mytestdb;"
Enter password:
MacBook-Pro: $
 
MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "show databases like 'mytestdb';"
Enter password:
+---------------------+
| Database (mytestdb) |
+---------------------+
| mytestdb            |
+---------------------+
MacBook-Pro: $

To check the logs we can use the docker logs command, for example :

MacBook-Pro: $ docker logs percona_primary
Initializing database
2019-12-24T08:27:54.395857Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
...
....
2019-12-24T08:28:19.792475Z 0 [Note] mysqld: ready for connections.
Version: '5.7.26-29-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 29, Revision 11ad961
2019-12-24T08:45:04.071744Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(101), pos(deb40d6941db-bin.000003, 154)
MacBook-Pro: $

The setup that I have shown above can be used as a quick setup of an async replication configuration to run demos or test experiments. Such a configuration is not suitable for production and will not be supported for production use by Docker.

Note: Percona Monitoring and Management (PMM) is also distributed as an appliance in the form of a docker image.