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.
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.
|
1 |
MacBook-Pro: $ docker network create --driver bridge isolated_nw<br>f8cd8f09b4042b39b04a6a43fd9dc71507af22dfd6ee2817fa80360577b24d6f<br>MacBook-Pro: $ |
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.
|
1 |
MacBook-Pro: $ mkdir /Users/myuser/mysql_primary_data |
I will proceed with the creation of the configuration file which my primary instance will be using.
|
1 |
MacBook-Pro: $ cat /Users/myuser/primaryconf/my-custom.cnf<br>[mysqld]<br>server-id = 100<br>log_bin |
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.
|
1 |
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<br>deb40d6941db74d845cbc5ec550572d37d4763740e3a72c015ada0c7520a0fd7<br>MacBook-Pro: $ |
I intend to set up an async replication environment, so I will get the binary log details from the primary instance.
|
1 |
MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "show master status;"<br>Enter password:<br>+-------------------------+----------+--------------+------------------+-------------------+<br>| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |<br>+-------------------------+----------+--------------+------------------+-------------------+<br>| deb40d6941db-bin.000003 | 154 | | | |<br>+-------------------------+----------+--------------+------------------+-------------------+<br>MacBook-Pro: $<br> |
For setting the replication we need a user and the below command connects the primary instance and grants the privilege.
|
1 |
MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'replpass';"<br>Enter password:<br>MacBook-Pro: $ |
Before I create my replica instance, I will provision the docker volume which my replica instance will use for storage.
|
1 |
MacBook-Pro: $ docker volume create mysqlreplicavol<br>mysqlreplicavol<br>MacBook-Pro: $ |
Create a custom MySQL configuration file for the replica instance.
|
1 |
MacBook-Pro: $ cat /Users/myuser/replicaconf/my-custom.cnf<br>[mysqld]<br>server-id = 101 |
The next step is to set up a replica instance using the docker volume that I created above.
|
1 |
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<br>98c109998a522a51c4ceca7d830a1f9af2abdd408c5e1d6d12cfd55af13d170d<br>MacBook-Pro: $ |
To set up the replication, apply the change master command in the replica instance and start the replica.
|
1 |
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;"<br>Enter password:<br>MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "start slave";<br>Enter password:<br>MacBook-Pro: $ |
|
1 |
MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "show slave status G" |egrep 'Running|Master' | egrep -v 'SSL|TLS'<br>Enter password:<br> Master_Host: percona_primary<br> Master_User: repl_user<br> Master_Port: 3306<br> Master_Log_File: deb40d6941db-bin.000003<br> Read_Master_Log_Pos: 434<br> Relay_Master_Log_File: deb40d6941db-bin.000003<br> Slave_IO_Running: Yes<br> Slave_SQL_Running: Yes<br> Exec_Master_Log_Pos: 434<br> Seconds_Behind_Master: 0<br> Master_Server_Id: 100<br> Master_UUID: 49451bba-2627-11ea-be09-0242ac120002<br> Master_Info_File: /var/lib/mysql/master.info<br> Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates<br> Master_Retry_Count: 86400<br> Master_Bind:<br>MacBook-Pro: $ |
Create a test DB to confirm it is replicating to the replica instance.
|
1 |
MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3308 -e "create database mytestdb;"<br>Enter password:<br>MacBook-Pro: $<br><br>MacBook-Pro: $ mysql -h127.0.0.1 -uroot -p -P3309 -e "show databases like 'mytestdb';"<br>Enter password:<br>+---------------------+<br>| Database (mytestdb) |<br>+---------------------+<br>| mytestdb |<br>+---------------------+<br>MacBook-Pro: $<br> |
To check the logs we can use the docker logs command, for example :
|
1 |
MacBook-Pro: $ docker logs percona_primary<br>Initializing database<br>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).<br>...<br>....<br>2019-12-24T08:28:19.792475Z 0 [Note] mysqld: ready for connections.<br>Version: '5.7.26-29-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 29, Revision 11ad961<br>2019-12-24T08:45:04.071744Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(101), pos(deb40d6941db-bin.000003, 154)<br>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 a typical production install and you need to test it well before any other use.
Note: Percona Monitoring and Management (PMM) is also distributed as an appliance in the form of a docker image.
Resources
RELATED POSTS
