- The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.
In order to experience GreatSQL for community users, we also provide Docker images. This article describes in detail how to deploy GreatSQL in Docker and build an MGR cluster.
The operating environment involved in this paper is as follows:
[root@greatsql]# cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core) [root@greatsql]# uname -a Linux GreatSQL 3.10.0-1160.11.1.el7.x86_64 #1 SMP Fri Dec 18 16:34:56 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
1. Install Docker
Install docker directly using yum, which is very easy.
[root@greatsql]# yum install -y docker
Then start the docker service and set the startup self startup.
[root@greatsql]# systemctl enable docker [root@greatsql]# systemctl start docker
2. Pull the GreatSQL image and create a container
2.1 pulling image
Pull the official image of GreatSQL:
[root@greatsql]# docker pull greatsql/greatsql docker pull greatsql/greatsql Using default tag: latest Trying to pull repository docker.io/greatsql/greatsql ... latest: Pulling from docker.io/greatsql/greatsql ... Digest: sha256:63eff1b099a75bb4e96b2c5bc7144889f6b3634a6163b56642a71a189183966c Status: Downloaded newer image for docker.io/greatsql/greatsql:latest
Check for success:
[root@greatsql]# docker images REPOSITORY TAG IMAGE ID CREATED SIZE docker.io/greatsql/greatsql latest d1963ef0c403 3 days ago 582 MB
2.2 creating a new container
After that, you can directly create a new container, using the normal method first.
[root@greatsql]# docker run -d \ --name mgr1 --hostname=mgr1 \ -p 3306:3306 -p 33060:33060 -p 33061:33061 \ -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \ -e MYSQL_IBP=1G \ -e MYSQL_MGR_NAME='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1' \ -e MYSQL_MGR_LOCAL='172.17.0.2:33061' \ -e MYSQL_MGR_SEEDS='172.17.0.2:33061,172.17.0.3:33061,172.17.0.4:33061' \ -e MYSQL_INIT_MGR=1 \ -e MYSQL_MGR_USER='repl' \ -e MYSQL_MGR_USER_PWD='repl' \ greatsql/greatsql
Several parameters are explained as follows:
greatsql/greatsql is the name of the image and can also be specified as the ID of the image, for example, d1963ef0c403.
If you don't want the root account to use an empty password, you can use mysql_ ALLOW_ EMPTY_ Replace the password = 1 parameter with something like MYSQL_ROOT_PASSWORD='GreatSQL3#) ^ 'or specify the random password MYSQL_RANDOM_ROOT_PASSWORD=1.
When the option MySQL is enabled_ INIT_ When MGR = 1, the account required by MGR will be automatically created and CHANGE MASTER TO specify the MGR replication channel.
If MySQL is not specified at the same time_ MGR_ User or MYSQL_MGR_USER_ In case of PWD, Mgr accounts are created with their respective default values.
This will successfully create a new container and automatically complete the initialization and startup of GreatSQL.
2.3 container management
First confirm the status of the container:
[root@greatsql]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 2e277c852f52 d1963ef0c403 "/docker-entrypoin..." 4 minutes ago Up 12 minutes 3306/tcp, 33060-33061/tcp mgr1
Seeing that the container status is Up indicates that it has been started normally.
Then enter the container to view:
[root@greatsql]# docker exec -it mgr1 /bin/bash [root@mgr1 ~]# mysqladmin ver mysqladmin Ver 8.0.23-14 for Linux on x86_64 (GreatSQL (GPL), Release 14, Revision) ... Server version 8.0.23-14 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /data/GreatSQL/mysql.sock Uptime: 11 min 19 sec Threads: 2 Questions: 2 Slow queries: 0 Opens: 120 Flush tables: 3 Open tables: 36 Queries per second avg: 0.002
See that the container has completed initialization and can log in directly without password.
View MGR account and corresponding replication channel:
[root@GreatSQL][(none)]> show grants for repl; +----------------------------------------------+ | Grants for repl@% | +----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO `repl`@`%` | | GRANT BACKUP_ADMIN ON *.* TO `repl`@`%` | +----------------------------------------------+ [root@GreatSQL][none]> select * from performance_schema.replication_group_members; +---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | | | NULL | OFFLINE | | | +---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+
You can also use this container as a service to close, start, restart and suspend it:
kill Kill one or more running containers pause Pause all processes within one or more containers ps List containers restart Restart one or more containers rm Remove one or more containers start Start one or more stopped containers stop Stop one or more running containers unpause Unpause all processes within one or more containers update Update configuration of one or more containers wait Block until one or more containers stop, then print their exit codes
You can try one by one.
2.4 container destruction
If you want to destroy the container, you need to stop the container first, and then execute the docker rm [container ID | container name] Command:
[root@greatsql]# docker stop mgr1
[root@greatsql]# docker rm mgr1
If you want to destroy an image, you can run the docker rmi [image ID | image name] command. However, you must ensure that the image is not currently used by other containers before deleting it:
[root@greatsql]# docker rmi greatsql/greatsql
After destroying the container and image, the corresponding data directory is no longer needed. You can clear it by executing the following command:
[root@greatsql]# docker volume prune WARNING! This will remove all volumes not used by at least one container. Are you sure you want to continue? [y/N] y Deleted Volumes: 5a68a6286361f92430dbc4c1a2d1bd65a2db707274bd4d3dd9d53cdf58a5ac5f 3ae2211c61187371c312a606e36bc361e549e830ab5896356e7a920266574666 cbbfa248d2fc827d92ceac231b52b61ab7cfe92479f2e84969bd516dd211416f 1c95278033575062b7f15e3e3f1319290d8dcfc7caba1c50b47592f665ba5456 Total reclaimed space: 13.08 GB
3. Build MGR cluster
The network communication of docker containers between cross host hosts is relatively troublesome. For simplicity, this time, an MGR cluster composed of three docker containers is built in a single machine environment.
3.1 create a private subnet
First, create a network for the MGR cluster:
[root@greatsql]# docker network create mgr-net [root@greatsql]# docker network ls NETWORK ID NAME DRIVER SCOPE 70c3ac08c7a9 bridge bridge local 3a480a3ec570 host host local 191d6d902b26 mgr-net bridge local 1e3e6267dcda none null local
View the configuration information of this subnet:
[root@greatsql]# docker inspect mgr-net
[
{
"Name": "mgr-net",
...
"Subnet": "172.18.0.0/16", <-- Network segment
"Gateway": "172.18.0.1" <-- gateway
...3.2 create 3 new containers
Start three docker containers respectively:
[root@greatsql]# docker run -d \ --name mgr1 --hostname=mgr1 --net=mgr-net \ -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \ -e MYSQL_MGR_LOCAL='172.18.0.2:33061' \ -e MYSQL_MGR_SEEDS='172.18.0.2:33061,172.18.0.3:33061,172.18.0.4:33061' \ -e MYSQL_INIT_MGR=1 \ greatsql/greatsql
In the following two examples, only mgr1 in the -- name and -- hostname parameters is changed to mgr2 and mgr3, and - e MySQL is changed_ MGR_ The IP address of the local ='172.18.0.2:33061 'parameter is incremented, for example -e MYSQL_MGR_LOCAL='172.18.0.3:33061'.
To view the running status of the container:
[root@greatsql]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1bcd23c6f378 d1963ef0c403 "/docker-entrypoin..." 2 minutes ago Up 2 minutes 3306/tcp, 33060-33061/tcp mgr3 9d12ab273d81 d1963ef0c403 "/docker-entrypoin..." 2 minutes ago Up 2 minutes 3306/tcp, 33060-33061/tcp mgr2 56fd564a1789 d1963ef0c403 "/docker-entrypoin..." 4 minutes ago Up 4 minutes 3306/tcp, 33060-33061/tcp mgr1
View the IP addresses of the three containers:
[root@greatsql]# docker inspect mgr1 | grep IPAddress
"SecondaryIPAddresses": null,
"IPAddress": "172.18.0.2",
"IPAddress": "172.18.0.2",The IP address of the first container is 172.18.0.2, and the other two containers are 172.18.0.3 and 172.18.0.4 respectively (incremental relationship).
Because the newly created network Mgr net specified when I started the container, it is 172.18.0.0/24 network segment. If the newly created network is not specified, the default should be 172.17.0.0/24 network segment. Pay attention to the differences.
Edit the / etc/hosts file under the three containers and add the hostname configuration of all nodes:
172.18.0.2 mgr1 172.18.0.3 mgr2 172.18.0.4 mgr3
Reminder: after the docker container is restarted, the contents of the / etc/hosts file in the container will be reset, so it is recommended to hang it by mapping volumes.
Edit a file / data/docker/hosts on the host computer:
[root@greatsql]# cat /data/docker/hosts 127.0.0.1 localhost.localdomain localhost 127.0.0.1 localhost4.localdomain4 localhost4 ::1 localhost.localdomain localhost ::1 localhost6.localdomain6 localhost6 172.18.0.2 mgr1 172.18.0.3 mgr2 172.18.0.4 mgr3
When creating a docker container, map the / etc/hosts file mounted to the container:
[root@greatsql]# docker run -d \ ... -v /data/docker/hosts:/etc/hosts \ ... greatsql/greatsql
You can also directly specify -- add host when creating a container, for example:
[root@greatsql]# docker run -d \ ... --add-host "mgr1:172.18.0.2" --add-host "mgr2:172.18.0.3" --add-host "mgr3:172.18.0.4"\ ... greatsql/greatsql
3.3 initialize MGR cluster
Next, prepare to initialize the MGR cluster.
Select the first container mgr1 as the PRIMARY node, set the boot of the MGR of the container, and then start the MGR service:
[root@greatsql]# docker exec -it mgr1 /bin/bash [root@mgr1 ~]# mysql -S/data/GreatSQL/mysql.sock ... #Set this node as the MGR boot node, [note] other nodes do not need to perform this operation SET GLOBAL group_replication_bootstrap_group=ON; #Start MGR service START GROUP_REPLICATION; #After starting the MGR service, close the boot parameters SET GLOBAL group_replication_bootstrap_group=OFF;
Because the operations such as account creation and authorization have been completed when creating the container, the MGR service can be started directly.
If -e MySQL is not specified when creating the container_ INIT_ If MGR = 1 is selected, you also need to manually execute the following commands to create an account, authorize, and create an MGR replication channel:
SET SQL_LOG_BIN=0; CREATE USER repl IDENTIFIED with mysql_native_password BY 'repl4MGR'; GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO repl; CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl4MGR' FOR CHANNEL 'group_replication_recovery';
3.4 start MGR service
In the other two docker containers, remember not to set group_replication_bootstrap_group=ON, start MGR service directly.
View MGR service status after all nodes are started:
[root@GreatSQL][(none)]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 63b55594-da80-11eb-94bf-0242ac120003 | mgr2 | 3306 | ONLINE | SECONDARY | 8.0.23 | | group_replication_applier | 6d33eb83-da80-11eb-91ed-0242ac120004 | mgr3 | 3306 | ONLINE | SECONDARY | 8.0.23 | | group_replication_applier | 7b1e33b1-da7f-11eb-8157-0242ac120002 | mgr1 | 3306 | ONLINE | PRIMARY | 8.0.23 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
At this stage, the common reasons why MGR service cannot be started are:
- If the hostname of each node is not set correctly in / etc/hosts, it will prompt that the remote host cannot be connected.
- The subnet created by docker exceeds the reserved private network address range defined in RFC 1918 (Class A: 10.0.0.0 ~ 10.255.255, class B: 172.16.0.0 ~ 172.31.255.255, class C: 192.168.0.0 ~ 192.168.255.255).
- Remove the group set on the node selected as PRIMARY_ replication_ bootstrap_ In addition to group = on, it is also set on other nodes, which will cause a new PRIMARY node to be started.
- After each node creates an MGR account, BINLOG will be generated, so set SQL should be executed_ LOG_ Bin = 0 or execute RESET MASTER after creating an account, otherwise it will prompt that the local node has more transactions than the remote node and cannot join the cluster.
I have encountered all the above scenarios, and there may be more. Welcome to add.
3.5 writing test data
This completes the construction. You can try to create a library table in the PRIMARY node and write test data:
#Reminder: restart binlog logging from here [root@GreatSQL][(none)]> SET SQL_LOG_BIN=1; [root@GreatSQL][(none)]> create database mymgr; [root@GreatSQL][(none)]> use mymgr; [root@GreatSQL][(mymgr)]> create table t1(id int primary key); [root@GreatSQL][(mymgr)]> insert into t1 select rand()*10240; [root@GreatSQL][mymgr]>select * from t1; +------+ | id | +------+ | 3786 | +------+
4. Using Docker compose to create Docker container
If you feel that manual management is troublesome, you can also choose docker compose, which can more conveniently manage docker containers.
Install docker compose using yum and confirm the version number:
[root@greatsql]# yum install -y docker-compose [root@greatsql]# docker-compose --version docker-compose version 1.18.0, build 8dd22a9
Editing the docker compose configuration file actually solidifies the command-line parameters for creating the docker container into the configuration file:
[root@greatsql]# mkdir -p /data/docker-compose
[root@greatsql]# vi /data/docker-compose/compose-mgr.yml
version: '3'
services:
mgr1:
image: greatsql/greatsql
container_name: mgr1
hostname: mgr1
network_mode: bridge
restart: unless-stopped
environment:
TZ: Asia/Shanghai
MYSQL_ALLOW_EMPTY_PASSWORD: 1
MYSQL_INIT_MGR: 1
MYSQL_MGR_LOCAL: '172.17.0.2:33061'
MYSQL_MGR_SEEDS: '172.17.0.2:33061,172.17.0.3:33061,172.17.0.4:33061'
extra_hosts:
- "mgr1:172.17.0.2"
- "mgr2:172.17.0.3"
- "mgr3:172.17.0.4"
mgr2:
image: greatsql/greatsql
container_name: mgr2
hostname: mgr2
network_mode: bridge
restart: unless-stopped
depends_on:
- "mgr1"
environment:
TZ: Asia/Shanghai
MYSQL_ALLOW_EMPTY_PASSWORD: 1
MYSQL_INIT_MGR: 1
MYSQL_MGR_LOCAL: '172.17.0.3:33061'
MYSQL_MGR_SEEDS: '172.17.0.2:33061,172.17.0.3:33061,172.17.0.4:33061'
extra_hosts:
- "mgr1:172.17.0.2"
- "mgr2:172.17.0.3"
- "mgr3:172.17.0.4"
mgr3:
image: greatsql/greatsql
container_name: mgr3
hostname: mgr3
network_mode: bridge
restart: unless-stopped
depends_on:
- "mgr2"
environment:
TZ: Asia/Shanghai
MYSQL_ALLOW_EMPTY_PASSWORD: 1
MYSQL_INIT_MGR: 1
MYSQL_MGR_LOCAL: '172.17.0.4:33061'
MYSQL_MGR_SEEDS: '172.17.0.2:33061,172.17.0.3:33061,172.17.0.4:33061'
extra_hosts:
- "mgr1:172.17.0.2"
- "mgr2:172.17.0.3"
- "mgr3:172.17.0.4"Start three instances:
[root@greatsql]# docker-compose -f /data/docker-compose/compose-mgr.yml up -d Name Command State Ports Creating mgr1 ... done Creating mgr2 ... done Creating mgr3 ... done Creating mgr2 ... Creating mgr3 ...
View running status:
[root@greatsql]# docker-compose -f /data/docker-compose/compose-mgr.yml up -d Name Command State Ports ---------------------------------------------------------------------------- mgr1 /docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp, 33061/tcp mgr2 /docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp, 33061/tcp mgr3 /docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp, 33061/tcp
Enter the container mgr1 selected as the PRIMARY node and start the MGR service:
[root@greatsql]# docker exec -it mgr1 bash [root@mgr1 /]# mysql ... [root@GreatSQL][(none)]> set global group_replication_bootstrap_group=ON; [root@GreatSQL][(none)]> start group_replication;
Enter the container of other SECONDARY nodes and directly start the MGR service:
[root@greatsql]# docker exec -it mgr2 bash [root@mgr2 /]# mysql ... [root@GreatSQL][(none)]> start group_replication; Query OK, 0 rows affected (2.76 sec) #View MGR service status [root@GreatSQL][(none)]>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | f0bd73d4-dbcb-11eb-99ba-0242ac110002 | mgr1 | 3306 | ONLINE | PRIMARY | 8.0.23 | | group_replication_applier | f1010499-dbcb-11eb-9194-0242ac110003 | mgr2 | 3306 | ONLINE | SECONDARY | 8.0.23 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
As usual, continue to start the mgr3 node, and a three node MGR cluster is completed.
Documents related to greatsql docker have been published to https://gitee.com/GreatSQL/Gr... , welcome to pay attention.
In addition, the GreatSQL Docker image file has also been published to https://hub.docker.com/r/grea... Welcome to download experience.
The level is limited. Please help readers to see what can be optimized. Thank you.
Finally, I would like to thank many friends who have recently provided help in tossing docker and ansible, including Xie hengzhong, LV Baocheng, Fan(), Coral, Dai xiansen, etc. (in no order).
Enjoy GreatSQL & Docker :)
Article recommendation:
GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6...
Wan Da #12, how can the MGR cluster automatically select the master without manual intervention
https://mp.weixin.qq.com/s/07...
"2021 data technology Carnival · ON LINE": evolution and practice of MySQL high availability architecture
https://mp.weixin.qq.com/s/u7...
Packet capture analysis of where an sql statement is slow
https://mp.weixin.qq.com/s/AY...
Wan Da #15, what are the conditions that may cause the MGR service to fail to start
https://mp.weixin.qq.com/s/in...
Technology sharing | why MGR consistency mode does not recommend AFTER
https://mp.weixin.qq.com/s/rN...
About GreatSQL
GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
Bilibili:
https://space.bilibili.com/13...
Wechat & QQ group:
You can search and add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR communication wechat group
QQ group: 533341697
Wechat assistant: wanlidbc
This article is composed of blog one article multi posting platform OpenWrite release!