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.
1 2 3 | 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.
1 | 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.
1 2 3 4 | 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.
1 2 3 | 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.
1 2 3 4 5 6 7 8 | 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.
1 2 3 | 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.
1 2 3 | 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.
1 2 3 | 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.
1 2 3 | 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.
1 2 3 4 5 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 | 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 :
1 2 3 4 5 6 7 8 9 | 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 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.
Thanks for that, Arunjith. I’m sure many will appreciate the succinct steps.
That said, I think many others would benefit if you could elaborate on the last sentence. First, what makes this configuration “not suitable for production”? Is that a licensing point, or some other legal or technical point?
If you may be implying merely that this one image (without orchestration) might not stand up to load, that could still “work” as production for some implementations, who without docker may also be only one instance. Heck, just adding replication will be a step up for some.
Of course, I appreciate the real value such a docker-based configuration adds for development and testing. I just really seek your clarification on what does make this NOT suitable for production.
Finally, still more curious, you add that it would “not be supported for production use by Docker”? That too is a point I’d think many would benefit hearing elaborated. Thanks.
Hi Charlie, Thanks for the response. The aforesaid details are the basics to get a test instance running quickly and easily including the async replication and not for a production setup. It is clearly a technical point, it does not match a typical production install and you need to test it well before any other use.
Ok, so it’s not really that it “it IS not suitable to production”, but rather that it “may not be”. I gather you won’t be changing it, but I offer this for the sake of other readers, barring any additional clarification from you or others. (Your concluding point about testing is understood and doesn’t counter what I’d said).
Finally, you still haven’t explained your last point, that this “will not be supported for production use by Docker”. Can you please elaborate?
I’m sorry if it seems I’m “looking a gift horse in the mouth”, in pressing these points. But they seem reasonable ones, given how the article kind of turned on a dime with that last sentence. It’s just not clear that things are as firm as you said, without more clarification.
Changed the text for better clearly, all set! Thanks.
Can you also advice on the steps to get a local mysql install on my MacBook Pro
—-
❯ mysql -h127.0.0.1 -uroot -p -P3308 -e “show master status;”
zsh: command not found: mysql
Got it:
❯ brew install mysql-client
❯ brew link mysql-client
❯ echo ‘export PATH=”/usr/local/opt/mysql-client/bin:$PATH”‘ >> ~/.zshrc
❯ . ~/.zshrc