转自 GreatSQL 社区
关于 GreatSQL
GreatSQL是源于Percona Server的分支版本，除了Percona Server已有的稳定可靠、高效、管理更方便等优势外，特别是进一步提升了MGR（MySQL Group Replication）的性能及可靠性，以及众多bug修复。
GreatSQL可以作为MySQL或Percona Server的可选替代方案，用于线上生产环境。
GreatSQL完全免费并兼容MySQL或Percona Server。
拉取GreatSQL镜像，并创建容器
拉取GreatSQL官方镜像
1
2
3
4
5
6
7
8
|
> docker pull greatsql/greatsql
Using default tag: latest
latest: Pulling from greatsql/greatsql
2d473b07cdd5: Downloading
Digest: sha256:cfe664d431d718b7bdd8eb4789d013720b4a6482e4c4863a01970569c4776dcc
Status: Downloaded newer image for greatsql/greatsql:latest
docker.io/greatsql/greatsql:latest
|
查看镜像
1
2
3
|
> docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
greatsql/greatsql latest 6da70ea0c11d 7 days ago 880MB
|
创建一个新的容器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
> 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
cd678bf1d3d5677bc1bd027175e3a8c3c9de71590f5da89c52efe80885cb21af
> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cd678bf1d3d5 greatsql/greatsql "/docker-entrypoint.…" 9 seconds ago Up 7 seconds 0.0.0.0:3306->3306/tcp, 0.0.0.0:33060-33061->33060-33061/tcp mgr1
|
进入容器查看
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
> docker exec -it mgr1 /bin/bash
[root@mgr1 /]# mysqladmin ver
mysqladmin Ver 8.0.25-15 for Linux on x86_64 (GreatSQL, Release 15, Revision 80bbf22abbd)
Copyright (c) 2018-2021 GreatOpenSource and/or its affiliates
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 8.0.25-15
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /data/GreatSQL/mysql.sock
Uptime: 1 min 4 sec
Threads: 2 Questions: 2 Slow queries: 0 Opens: 119 Flush tables: 3 Open tables: 36 Queries per second avg: 0.031
|
进入容器, 直接无密码登录, 查看MGR账号及相应复制通道
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
[root@mgr1 /]# mysql -h127.0.0.1 -P3306 -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.25-15 GreatSQL, Release 15, Revision 80bbf22abbd
Copyright (c) 2018-2021 GreatOpenSource and/or its affiliates
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(Sun Jul 25 03:34:56 2021)[root@GreatSQL][(none)]>show grants for repl;
+----------------------------------------------+
| Grants for repl@% |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `repl`@`%` |
| GRANT BACKUP_ADMIN ON *.* TO `repl`@`%` |
+----------------------------------------------+
2 rows in set (0.00 sec)
(Sun Jul 25 03:35:14 2021)[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 | | |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)
|
容器销毁
1
2
3
4
5
6
7
8
9
10
11
12
|
停止容器
> docker stop mgr1
删除容器
> docker rm mgr1
删除镜像
> docker rmi greatsql/greatsql
销毁容器、镜像之后，清除相应的数据目录
> docker volume prune
|
构建MGR集群
创建专用子网
1
2
3
4
5
6
7
8
|
> docker network create mgr-net
c0a4a4628549d87adb6c1c885f0291a0180534f43ab2f4ff131dbee964b0e4f5
> docker network ls
NETWORK ID NAME DRIVER SCOPE
ee131bf830aa bridge bridge local
87d1d0470158 host host local
c0a4a4628549 mgr-net bridge local
3a3a51ad2639 none null local
|
查看子网配置信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
> docker inspect mgr-net
[
{
"Name": "mgr-net",
"Id": "c0a4a4628549d87adb6c1c885f0291a0180534f43ab2f4ff131dbee964b0e4f5",
"Created": "2021-07-25T06:58:01.456450007Z",
"Scope": "local",
"Driver": "bridge",
"EnableIPv6": false,
"IPAM": {
"Driver": "default",
"Options": {},
"Config": [
{
"Subnet": "172.18.0.0/16",
"Gateway": "172.18.0.1"
}
]
},
"Internal": false,
"Attachable": false,
"Ingress": false,
"ConfigFrom": {
"Network": ""
},
"ConfigOnly": false,
"Containers": {},
"Options": {},
"Labels": {}
}
]
|
创建3个新容器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
> 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
afbaf07b4fcb2f79af6a47c45a7e3bdd7e21bda5478660f5825997652ee7d646
> docker run -d \
--name mgr2 --hostname=mgr2 --net=mgr-net \
-e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
-e MYSQL_MGR_LOCAL='172.18.0.3: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
4d39b4a1aa99037b0a6ce5b8b773ca7a6b885394fe40f4fdde48d65a136ec516
> docker run -d \
--name mgr3 --hostname=mgr3 --net=mgr-net \
-e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
-e MYSQL_MGR_LOCAL='172.18.0.4: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
42395828d1a697a182f1f147c3c053b5de9e32196eb328d9dbb6bd673d971d0a
|
查看创建的容器运行状态
1
2
3
4
5
|
> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
42395828d1a6 greatsql/greatsql "/docker-entrypoint.…" 6 seconds ago Up 5 seconds 3306/tcp, 33060-33061/tcp mgr3
4d39b4a1aa99 greatsql/greatsql "/docker-entrypoint.…" 51 seconds ago Up 50 seconds 3306/tcp, 33060-33061/tcp mgr2
afbaf07b4fcb greatsql/greatsql "/docker-entrypoint.…" 2 minutes ago Up 2 minutes 3306/tcp, 33060-33061/tcp mgr1
|
查看3个容器的IP地址
1
2
3
4
5
6
7
8
9
10
11
12
|
> docker inspect mgr1 | grep IPAddress
"SecondaryIPAddresses": null,
"IPAddress": "",
"IPAddress": "172.18.0.2",
> docker inspect mgr2 | grep IPAddress
"SecondaryIPAddresses": null,
"IPAddress": "",
"IPAddress": "172.18.0.3",
> docker inspect mgr3 | grep IPAddress
"SecondaryIPAddresses": null,
"IPAddress": "",
"IPAddress": "172.18.0.4",
|
初始化MGR集群
创建容器时设置-e MYSQL_INIT_MGR=1选项，自动完成创建账户及授权等操作
未设置时需要手动创建账户及授权，并创建MGR复制通道，操作如下：
1
2
3
4
|
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';
|
容器 mgr1 作为 PRIMARY节点
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
> docker exec -it mgr1 /bin/bash
[root@mgr1 /]# mysql -S/data/GreatSQL/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25-15 GreatSQL, Release 15, Revision 80bbf22abbd
Copyright (c) 2018-2021 GreatOpenSource and/or its affiliates
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 设置本节点为MGR引导启动节点，【注意】其他节点无需执行本操作
(Sun Jul 25 07:08:53 2021)[root@GreatSQL][(none)]>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
# 启动MGR服务
(Sun Jul 25 07:09:07 2021)[root@GreatSQL][(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.47 sec)
# 启动完MGR服务后，关闭引导参数
(Sun Jul 25 07:09:17 2021)[root@GreatSQL][(none)]>SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
|
容器 mgr2，mgr3 启动MGR服务
在非PRIMARY节点不要设置 group_replication_bootstrap_group=ON，直接启动 MGR服务即可。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
> docker exec -it mgr2 /bin/bash
[root@mgr2 /]# mysql -S/data/GreatSQL/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25-15 GreatSQL, Release 15, Revision 80bbf22abbd
Copyright (c) 2018-2021 GreatOpenSource and/or its affiliates
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(Sun Jul 25 07:15:48 2021)[root@GreatSQL][(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.99 sec)
> docker exec -it mgr3 /bin/bash
[root@mgr3 /]# mysql -S/data/GreatSQL/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25-15 GreatSQL, Release 15, Revision 80bbf22abbd
Copyright (c) 2018-2021 GreatOpenSource and/or its affiliates
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(Sun Jul 25 07:16:35 2021)[root@GreatSQL][(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.99 sec)
|
查看所有节点都启动后的MGR服务状态
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
> docker exec -it mgr1 /bin/bash
[root@mgr1 /]# mysql -S/data/GreatSQL/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 89
Server version: 8.0.25-15 GreatSQL, Release 15, Revision 80bbf22abbd
Copyright (c) 2018-2021 GreatOpenSource and/or its affiliates
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(Sun Jul 25 07:17:26 2021)[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 | 108ef102-ed16-11eb-84f5-0242ac120002 | mgr1 | 3306 | ONLINE | PRIMARY | 8.0.25 |
| group_replication_applier | 4103221e-ed16-11eb-b0bf-0242ac120003 | mgr2 | 3306 | ONLINE | SECONDARY | 8.0.25 |
| group_replication_applier | 5bd348f8-ed16-11eb-948b-0242ac120004 | mgr3 | 3306 | ONLINE | SECONDARY | 8.0.25 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)
|
写入测试数据
尝试在 PRIMARY节点 中创建库表并写入测试数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
> docker exec -it mgr1 /bin/bash
[root@mgr1 /]# mysql -S/data/GreatSQL/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 89
Server version: 8.0.25-15 GreatSQL, Release 15, Revision 80bbf22abbd
#提醒：从这里开始要重新启动binlog记录
(Sun Jul 25 07:21:44 2021)[root@GreatSQL][(none)]>SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.01 sec)
(Sun Jul 25 07:22:28 2021)[root@GreatSQL][(none)]>create database mymgr;
Query OK, 1 row affected (0.03 sec)
(Sun Jul 25 07:22:39 2021)[root@GreatSQL][(none)]>create table mymgr.t1(id int primary key);
Query OK, 0 rows affected (0.04 sec)
(Sun Jul 25 07:23:14 2021)[root@GreatSQL][(none)]>insert into mymgr.t1 select rand()*10240;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
(Sun Jul 25 07:23:39 2021)[root@GreatSQL][(none)]>select * from mymgr.t1;
+------+
| id |
+------+
| 2862 |
+------+
1 row in set (0.00 sec)
|
登录mgr2/mgr3节点查看写入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
> docker exec -it mgr2 /bin/bash
[root@mgr2 /]# mysql -S/data/GreatSQL/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 151
Server version: 8.0.25-15 GreatSQL, Release 15, Revision 80bbf22abbd
Copyright (c) 2018-2021 GreatOpenSource and/or its affiliates
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(Sun Jul 25 07:26:58 2021)[root@GreatSQL][mymgr]>show create table mymgr.t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
|
重启容器mgr3节点后，查看数据同步情况
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
> docker stop mgr3
# 容器停止期间PRIMARY节点写入数据
> docker start mgr3
> docker exec -it mgr3 /bin/bash
[root@mgr3 /]# mysql -S/data/GreatSQL/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25-15 GreatSQL, Release 15, Revision 80bbf22abbd
(Sun Jul 25 07:30:48 2021)[root@GreatSQL][(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.75 sec)
(Sun Jul 25 07:30:56 2021)[root@GreatSQL][(none)]>select * from mymgr.t1;
+------+
| id |
+------+
| 163 |
| 624 |
| 663 |
| 1614 |
| 2469 |
| 2862 |
| 5885 |
| 7695 |
| 8715 |
+------+
9 rows in set (0.00 sec)
|
参考