Setting up MySQL Group Replication with MySQL Docker images
source link: https://www.tuicool.com/articles/hit/buQJNnN
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
MySQL Group Replication (GR) is a MySQL Server plugin that enables you to create elastic, highly-available, fault-tolerant replication topologies. Groups can operate in a
single-primary mode with automatic primary election, where only one server accepts updates at a time. Alternatively, groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently.
Docker is an open source framework that automates deployment and provisioning, and simplifies distribution of applications in lightweight and portable containers. These containers can be executed in a wide variety of places including your laptop, the cloud, or a virtual machine (VM).
In this blog post, we show how to combine these two technologies by setting up GR using Docker containers running a MySQL image. We will also demonstrate how to use Docker commands to emulate GR fault scenarios like disconnecting a group member from the network and crashing one of the group members.
Overview
We start by downloading the MySQL 8 image from Docker Hub ( hub.docker.com/r/mysql/mysql-server/ ), then we are going to create a Docker network named groupnet and finally setup a Multi-Primary GR topology with 3 group members in different containers. The steps that follow assume that you have Docker installed on your system and know its basic commands.
Pulling MySQL Sever Image
In this example, we are going to use mysql/mysql-server:8.0:
$ docker pull mysql/mysql-server:8.0
If you prefer to use another version, the general command to download the MySQL Community Edition image is:
$ docker pull mysql/mysql-server:<tag>
where <tag> is the server version you’re targeting. If :<tag> is omitted, the latest tag is used, and the image for the latest GA version of MySQL Server is downloaded.
Examples:
$ docker pull mysql/mysql-server
$ docker pull mysql/mysql-server:5.7
You can check whether the image was downloaded by running:
$ docker images
myuser@computer:~$ docker images REPOSITORY TAG IMAGE ID CREATED SIZE mysql/mysql-server 8.0 ee4df5cdfffd 5 weeks ago 309MB
Creating a Docker network
We start off by creating a Docker network named groupnet that we will have our containers connected to:
$ docker network create groupnet
Just need to create it once, unless you remove it from Docker network catalog.
To list all existing Docker networks:
$ docker network ls
myuser@computer:~$ docker network ls NETWORK ID NAME DRIVER SCOPE 4bc7f82a2e65 bridge bridge local 9594e9de9fd5 groupnet bridge local 816c7885e725 host host local 6fe343e9fb17 none null local
Running 3 Docker MySQL containers
You need to decide whether to configure a single-primary or multi-primary mode. In a single primary configuration, MySQL always designates the first group member as the single primary server which will handle all write operations. A multi-primary mode allows writes to any of the group members.
Run the command below in a terminal for creating three MySQL 8 containers:
Note: If you wish to configure a single-primary mode (which is the MySQL GR’s default mode), change the loose-group-replication-single-primary-mode and loose-group-replication-enforce-update-everywhere-checks values to ‘ON’ and ‘OFF’ respectively. For a multi-primary mode, just leave as it is.
for N in 1 2 3 do docker run -d --name=node$N --net=groupnet --hostname=node$N \ -v $PWD/d$N:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mypass \ mysql/mysql-server:8.0 \ --server-id=$N \ --log-bin='mysql-bin-1.log' \ --enforce-gtid-consistency='ON' \ --log-slave-updates='ON' \ --gtid-mode='ON' \ --transaction-write-set-extraction='XXHASH64' \ --binlog-checksum='NONE' \ --master-info-repository='TABLE' \ --relay-log-info-repository='TABLE' \ --plugin-load='group_replication.so' \ --relay-log-recovery='ON' \ --group-replication-start-on-boot='OFF' \ --group-replication-group-name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee' \ --group-replication-local-address="node$N:33061" \ --group-replication-group-seeds='node1:33061,node2:33061,node3:33061' \ --loose-group-replication-single-primary-mode='OFF' \ --loose-group-replication-enforce-update-everywhere-checks='ON' done
You should have noticed this strange flag -v $PWD/d$N:/var/lib/mysql in the command above. It sets up a mount volume that links the /var/lib/mysql directory from inside the node container to the $PWD/d$N directory on the host machine. Docker uses a : to split the host’s path from the container path, and the host path always comes first. Since those volumes don’t exist, Docker will create 3 new folders (‘d1’, ‘d2’ and ‘d3’) in the current location (PWD) from where the command is launched.
This script will bootstrap 3 Docker containers named ‘node1’, ‘node2’ and ‘node3’.
It’s possible to see whether the containers are started by running:
$ docker ps -a
myuser@computer:~$ docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 182edcdabfab mysql/mysql-server:8.0 "/entrypoint.sh --se…" 8 seconds ago Up 6 seconds (health: starting) 3306/tcp, 33060/tcp node3 87abb0f5ce65 mysql/mysql-server:8.0 "/entrypoint.sh --se…" 9 seconds ago Up 7 seconds (health: starting) 3306/tcp, 33060/tcp node2 1c51f1d33534 mysql/mysql-server:8.0 "/entrypoint.sh --se…" 10 seconds ago Up 9 seconds (health: starting) 3306/tcp, 33060/tcp node1
After some seconds, all three containers are up and running (healthy).
myuser@computer:~$ docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 182edcdabfab mysql/mysql-server:8.0 "/entrypoint.sh --se…" About a minute ago Up 58 seconds (healthy) 3306/tcp, 33060/tcp node3 87abb0f5ce65 mysql/mysql-server:8.0 "/entrypoint.sh --se…" About a minute ago Up 59 seconds (healthy) 3306/tcp, 33060/tcp node2 1c51f1d33534 mysql/mysql-server:8.0 "/entrypoint.sh --se…" About a minute ago Up About a minute (healthy) 3306/tcp, 33060/tcp node1
If some problem happens and one or more containers are not started, we can check the MySQL logs running (for instance in node1):
$ docker logs node1
P.S. You must always remember to remove a stopped container (for instance running: “ docker rm node1 “) and delete the MySQL data directory which was created, before running a new container with the same name.
Setting up and starting GR in the containers
From now on, we are going to run MySQL GR setup commands from outside the containers using the flag “-it”.
Execute these commands on node1 which will bootstrap the group:
docker exec -it node1 mysql -uroot -pmypass \ -e "SET @@GLOBAL.group_replication_bootstrap_group=1;" \ -e "create user 'repl'@'%';" \ -e "GRANT REPLICATION SLAVE ON *.* TO repl@'%';" \ -e "flush privileges;" \ -e "change master to master_user='repl' for channel 'group_replication_recovery';" \ -e "START GROUP_REPLICATION;" \ -e "SET @@GLOBAL.group_replication_bootstrap_group=0;" \ -e "SELECT * FROM performance_schema.replication_group_members;"
For node2 and node3, execute the below command:
for N in 2 3 do docker exec -it node$N mysql -uroot -pmypass \ -e "change master to master_user='repl' for channel 'group_replication_recovery';" \ -e "START GROUP_REPLICATION;" done
Use the Performance Schema tables to monitor GR:
docker exec -it node1 mysql -uroot -pmypass \ -e "SELECT * FROM performance_schema.replication_group_members;"
By now, you should see:
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 8c067369-bdba-11e8-8ba1-0242ac120002 | node1 | 3306 | ONLINE | PRIMARY | 8.0.12 | | group_replication_applier | 8cb18698-bdba-11e8-9f3d-0242ac120003 | node2 | 3306 | ONLINE | PRIMARY | 8.0.12 | | group_replication_applier | 8d4feb8e-bdba-11e8-b0de-0242ac120004 | node3 | 3306 | ONLINE | PRIMARY | 8.0.12 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
Adding some data
We have just created a multi-primary group. Let’s add some data.
docker exec -it node1 mysql -uroot -pmypass \ -e "create database TEST; use TEST; CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; show tables;"
The output is:
mysql: [Warning] Using a password on the command line interface can be insecure. +----------------+ | Tables_in_TEST | +----------------+ | t1 | +----------------+
Let’s add some data by connecting to the other group members:
for N in 2 3 do docker exec -it node$N mysql -uroot -pmypass \ -e "INSERT INTO TEST.t1 VALUES($N);" done
Let’s see whether the data was inserted:
for N in 1 2 3 do docker exec -it node$N mysql -uroot -pmypass \ -e "SHOW VARIABLES WHERE Variable_name = 'hostname';" \ -e "SELECT * FROM TEST.t1;" done
The output is:
mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | hostname | node1 | +---------------+-------+ +----+ | id | +----+ | 2 | | 3 | +----+ mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | hostname | node2 | +---------------+-------+ +----+ | id | +----+ | 2 | | 3 | +----+ mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | hostname | node3 | +---------------+-------+ +----+ | id | +----+ | 2 | | 3 | +----+
GR fault tolerance scenarios
Let’s start by creating and analysing GR behaviour if one of the nodes looses connectivity.
First, let’s set the option group_replication_exit_state_action to READ_ONLY , so node3 will not be killed when it goes to ERROR state.
docker exec -it node3 mysql -uroot -pmypass \ -e "set @@global.group_replication_exit_state_action=READ_ONLY;"
Since Docker allows us to disconnect a container from a network by just running one command, we can disconnect now node3 from the groupnet network by running:
$ docker network disconnect groupnet node3
Checking the group members:
for N in 1 3 do docker exec -it node$N mysql -uroot -pmypass \ -e "SHOW VARIABLES WHERE Variable_name = 'hostname';" \ -e "SELECT * FROM performance_schema.replication_group_members;" done
node3 was expelled from the group and it sees the other group members as UNREACHABLE:
mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | hostname | node1 | +---------------+-------+ +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 8c067369-bdba-11e8-8ba1-0242ac120002 | node1 | 3306 | ONLINE | PRIMARY | 8.0.12 | | group_replication_applier | 8cb18698-bdba-11e8-9f3d-0242ac120003 | node2 | 3306 | ONLINE | PRIMARY | 8.0.12 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | hostname | node3 | +---------------+-------+ +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 8c067369-bdba-11e8-8ba1-0242ac120002 | node1 | 3306 | UNREACHABLE | PRIMARY | 8.0.12 | | group_replication_applier | 8cb18698-bdba-11e8-9f3d-0242ac120003 | node2 | 3306 | UNREACHABLE | PRIMARY | 8.0.12 | | group_replication_applier | 8d4feb8e-bdba-11e8-b0de-0242ac120004 | node3 | 3306 | ONLINE | PRIMARY | 8.0.12 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
Let’s reestablish the network connection in node3 and rejoin the node:
$ docker network connect groupnet node3
Rejoining node3:
docker exec -it node3 mysql -uroot -pmypass \ -e "STOP GROUP_REPLICATION; START GROUP_REPLICATION;"
Checking the group members:
for N in 1 3 do docker exec -it node$N mysql -uroot -pmypass \ -e "SHOW VARIABLES WHERE Variable_name = 'hostname';" \ -e "SELECT * FROM performance_schema.replication_group_members;" done
Group has 3 nodes again:
mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | hostname | node1 | +---------------+-------+ +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 8c067369-bdba-11e8-8ba1-0242ac120002 | node1 | 3306 | ONLINE | PRIMARY | 8.0.12 | | group_replication_applier | 8cb18698-bdba-11e8-9f3d-0242ac120003 | node2 | 3306 | ONLINE | PRIMARY | 8.0.12 | | group_replication_applier | 8d4feb8e-bdba-11e8-b0de-0242ac120004 | node3 | 3306 | ONLINE | PRIMARY | 8.0.12 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | hostname | node3 | +---------------+-------+ +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 8c067369-bdba-11e8-8ba1-0242ac120002 | node1 | 3306 | ONLINE | PRIMARY | 8.0.12 | | group_replication_applier | 8cb18698-bdba-11e8-9f3d-0242ac120003 | node2 | 3306 | ONLINE | PRIMARY | 8.0.12 | | group_replication_applier | 8d4feb8e-bdba-11e8-b0de-0242ac120004 | node3 | 3306 | ONLINE | PRIMARY | 8.0.12 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
We can also stop or kill a node. Let’s kill node3:
$ docker kill node3
Run the command below to check the group members again:
docker exec -it node1 mysql -uroot -pmypass \ -e "SELECT * FROM performance_schema.replication_group_members;"
node3 was expelled from the group which has 2 members now:
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 8c067369-bdba-11e8-8ba1-0242ac120002 | node1 | 3306 | ONLINE | PRIMARY | 8.0.12 | | group_replication_applier | 8cb18698-bdba-11e8-9f3d-0242ac120003 | node2 | 3306 | ONLINE | PRIMARY | 8.0.12 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
Cleaning up: stopping containers, removing created network and image
To stop the running container(s):
$ docker stop node1 node2 node3
To remove the stopped container(s):
$ docker rm node1 node2 node3
To remove the data directories created (they are located in the folder where the containers were started from):
$ sudo rm -rf d1 d2 d3
To remove the created network:
$ docker network rm groupnet
To remove the MySQL 8 image:
$ docker rmi mysql/mysql-server:8.0
Summary
This blog post details a simple way to set up MySQL Group Replication using MySQL Docker images. For that it covers:
– How to pull the MySQL image and use it to run many containers;
– How to create a Docker network;
– How to setup GR in the running containers;
– How to use Docker commands to emulate fault scenarios in GR for testing;
– In end, how to remove the old containers, network, images and data files for the new setup;
If you don’t have Docker installed, you can find instructions here to install. We invite you to try out this setup and leave comments regarding your experience and doubts.
MySQL Group Replication and Docker are most powerful when you understand the concepts and ideas behind them. To get that understanding, you can read the
official documentations here and here , respectively.
500 total views, 440 views today
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK