MySQL8.0 MGR多主集群

HA 评论

多主集群实验环境

ip server-id db-port mgr-port node
172.10.0.2 100 3306 3308 master1
172.10.0.3 101 3306 3308 master2

1.集群环境配置

1
2
3
4
5
6
7
8
9
10
11
# 拉取MySQL镜像
docker pull ubuntu/mysql:latest

# 创建docker网络
docker network create --subnet=172.10.0.0/24 mysql-network

# 启动master1节点
docker run -d --name mysql-master1 -e TZ=UTC -p 13306:3306 -e MYSQL_ROOT_PASSWORD=root --net=mysql-network ubuntu/mysql:latest

# 启动master2节点
docker run -d --name mysql-master2 -e TZ=UTC -p 13307:3306 -e MYSQL_ROOT_PASSWORD=root --net=mysql-network ubuntu/mysql:latest

2.MGR配置

1
2
3
4
5
# 进入容器master1
docker exec -it mysql-master1 /bin/bash

# 连接MySQL数据库
mysql -uroot -p

16296470519535

1
2
3
4
# 安装mgr插件
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
# 查看插件是否安装成功
mysql> show plugins;

16296472489733

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
# 修改master1 MySQL配置文件
apt update && apt install vim -y && vim /etc/mysql/my.cnf

# 修改如下
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
gtid_mode=on
enforce-gtid-consistency=on
binlog_format=row
# 唯一值，每个节点应设置不同的值
server-id = 100
transaction_isolation = READ-COMMITTED
log-slave-updates=1
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction = XXHASH64
# 当前组名称，Linux可以通过uuidgen生成。组内所有成员必须填写同一个值
loose-group_replication_group_name = '1080E4E6-6245-410E-9D91-B1F07E289D0D'
# ip白名单,默认只添加127.0.0.1,不会允许来自外部主机的连接,按需安全设置
loose-group_replication_ip_whitelist = '172.10.0.0/24'
# 是否随服务器启动而自动启动组复制
loose-group_replication_start_on_boot = OFF
# 本机地址:mgr端口
loose-group_replication_local_address = '172.10.0.2:3308'
# 组内成员地址:mgr端口
loose-group_replication_group_seeds = '172.10.0.2:3308,172.10.0.3:3308'
# 引导模式，首次搭建MGR或重建MGR时使用，组内只需要一台开启
loose-group_replication_bootstrap_group = OFF
# on为单主模式，off为多主模式
loose-group_replication_single_primary_mode = off
# 多主需开启强制检查
loose-group_replication_enforce_update_everywhere_checks = on
# Custom config should go here
!includedir /etc/mysql/conf.d/
1
2
3
4
5
# 进入容器master2
docker exec -it mysql-master2 /bin/bash

# 连接MySQL数据库
mysql -uroot -p

16296487514717

安装MGR插件略，和上述master1节点安装方式一致

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
# 修改master2 MySQL配置文件
vim /etc/mysql/my.cnf

# 修改如下
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
gtid_mode=on
enforce-gtid-consistency=on
binlog_format=row
# 唯一值，每个节点应设置不同的值
server-id = 101
transaction_isolation = READ-COMMITTED
log-slave-updates=1
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = '1080E4E6-6245-410E-9D91-B1F07E289D0D'
loose-group_replication_ip_whitelist = '172.10.0.0/24'
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = '172.10.0.3:3308'
loose-group_replication_group_seeds = '172.10.0.2:3308,172.10.0.3:3308'
loose-group_replication_bootstrap_group = OFF
loose-group_replication_single_primary_mode = off
loose-group_replication_enforce_update_everywhere_checks = on
# Custom config should go here
!includedir /etc/mysql/conf.d/

3.启动MGR集群

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# master1节点执行下述命令
# 临时设置为引导模式，初始化集群，其他节点忽略这一步
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
# 创建用于同步数据的用户，集群中每一个节点都要做
mysql> CREATE USER 'sroot'@'%' IDENTIFIED WITH sha256_password BY '123123';
# 用户授权
mysql> grant REPLICATION SLAVE on *.* to 'sroot'@'%' with grant option;
#清空所有旧的GTID信息,避免冲突
mysql> reset master;
#创建同步规则认证信息,就是刚才授权的那个用户,和一般的主从规则写法不太一样
mysql> CHANGE MASTER TO MASTER_USER='sroot', MASTER_PASSWORD='123123' FOR CHANNEL 'group_replication_recovery';
#启动MGR
mysql> start group_replication;
#查看是否启动成功,看到online就是成功了
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 6eada4c6-034b-11ec-ad7e-0242ac0a0002 | 34ed0726e911 |        3306 | ONLINE       | PRIMARY     | 8.0.26         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)
# 成功后关闭引导
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# master2节点执行下述命令
mysql> CREATE USER 'sroot'@'%' IDENTIFIED WITH sha256_password BY '123123';
mysql> grant REPLICATION SLAVE on *.* to 'sroot'@'%' with grant option;
mysql> reset master;
mysql> CHANGE MASTER TO MASTER_USER='sroot', MASTER_PASSWORD='123123' FOR CHANNEL 'group_replication_recovery';
mysql> start group_replication;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 3cdb4148-0368-11ec-a98c-0242ac0a0002 | ac552a48ba6a |        3306 | ONLINE       | PRIMARY     | 8.0.26         |
| group_replication_applier | 42d5b286-0368-11ec-be8c-0242ac0a0003 | 678d06c71f3b |        3306 | ONLINE       | PRIMARY     | 8.0.26         |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
2 rows in set (0.01 sec)

4.测试验证

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# master1中创建数据库
mysql> create database test;
# master2中查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)
# 模拟宕机，停止容器master1
docker stop mysql-master1
# master2中建表并写入数据
mysql> use test;
Database changed
mysql> create table student
    -> (
    -> id int primary key,
    -> name varchar(20),
    -> gender int,
    -> age int
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into student values(1,'tom',1,23);
Query OK, 1 row affected (0.01 sec)
# 启动master1并进入容器
docker start mysql-master1 && docker exec -it mysql-master1 /bin/bash
# 由于没有打开group_replication_start_on_boot，需手动启动组复制
mysql> start group_replication;
mysql> use test;
Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student        |
+----------------+
1 row in set (0.01 sec)

mysql> select * from student;
+----+------+--------+------+
| id | name | gender | age  |
+----+------+--------+------+
|  1 | tom  |      1 |   23 |
+----+------+--------+------+
1 row in set (0.00 sec)

Aurora随手记的，爱看不看

这里介绍不了我。