记录分享工作的点点滴滴

0%

MySQL8.0 MGR部署

发表于 分类于

单主模式下部署MGR集群

我们通过在3台服务器上部署3个MySQL Server 实例来创建组复制，组复制提供了内部数据复制和内部故障转移的机制。在单主模式下，primary节点故障，会有一个secondary节点自动晋升我primary。

MGR 架构图如下：

创建实例

版本信息

https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz

安装

cd /opt/soft/
tar -xf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
ln -s mysql-8.0.18-linux-glibc2.12-x86_64 mysql80

目录结构

实例信息

Role HostIp Hostname Inst_port LocalAddr Localaddr_port
Primary 192.168.184.40 bjm6-184-40.cdn.org 3008 192.168.184.40 33061
Secondary 192.168.196.59 bjm6-196-59.cdn.org 3009 192.168.196.59 33061
Secondary 192.168.196.85 bjm6-196-85.cdn.org 3010 192.168.196.85 33061

实例目录

创建3个实例基础目录如下:

1
2
3
4
5
6
/work/mysql3008
├── etc
│   └── my3008.cnf
├── log
├── tmp
└── var

编辑配置文件

cat /work/mysql3008/etc/my3008.cnf

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
[client]
port = 3008
socket = /work/mysql3008/tmp/mysql3008.sock
[mysqld]
# 组复制，数据必须存储在innodb事务引擎中
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
port = 3008
socket = /work/mysql3008/tmp/mysql3008.sock
basedir = /opt/soft/mysql80
datadir = /work/mysql3008/var/
pid-file = /work/mysql3008/tmp/mysql3008.pid
plugin-load-add=mysql_clone.so
plugin_dir=/opt/soft/mysql80/lib/plugin

server_id=10300801
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
plugin_load_add='group_replication.so'
# 创建组的命令方式：通过select uuid()来随机获取
group_replication_group_name="291e41a2-6f40-11ea-a9da-74867aea62bc"
# 组复制启动默认是关闭的，方便在启动组复制前，进行相关的配置
group_replication_start_on_boot=off
# 组内成员通信的网络地址和端口
# 内部端口一般设置: 实例端口 * 10 + 1；不同服务器端口可以一致   
group_replication_local_address= "192.168.184.40:33061"
group_replication_group_seeds= "192.168.184.40:33061,192.168.196.85:33061,192.168.196.59:33061"
# 只有启动引导节点时，才会开启，并且在一个实例上，只开启一次 
group_replication_bootstrap_group=off

cat /work/mysql3008/etc/my3009.cnf 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[client]
port = 3009
socket = /work/mysql3009/tmp/mysql3009.sock
[mysqld]
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
port = 3009
socket = /work/mysql3009/tmp/mysql3009.sock
basedir = /opt/soft/mysql80/
datadir = /work/mysql3009/var/
pid-file = /work/mysql3009/tmp/mysql3009.pid
plugin-load-add=mysql_clone.so
plugin_dir=/opt/soft/mysql80/lib/plugin

server_id=10300902
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
plugin_load_add='group_replication.so'
group_replication_group_name="291e41a2-6f40-11ea-a9da-74867aea62bc"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.196.59:33061"
group_replication_group_seeds= "192.168.184.40:33061,192.168.196.59:33061,192.168.196.85:33061"
group_replication_bootstrap_group=off

cat /work/mysql3008/etc/my3010.cnf 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[client]
port = 3010
socket = /work/mysql3010/tmp/mysql3010.sock
[mysqld]
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
port = 3010
socket = /work/mysql3010/tmp/mysql3010.sock
basedir = /opt/soft/mysql80/
datadir = /work/mysql3010/var/
pid-file = /work/mysql3010/tmp/mysql3010.pid
plugin-load-add=mysql_clone.so
plugin_dir=/opt/soft/mysql80/lib/plugin

server_id=10301003
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
plugin_load_add='group_replication.so'
group_replication_group_name="291e41a2-6f40-11ea-a9da-74867aea62bc"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.196.85:33061"
group_replication_group_seeds= "192.168.184.40:33061,192.168.196.85:33061,192.168.196.59:33061"
group_replication_bootstrap_group=off

目录赋权并启动

初始化

1
2
3
4
cd /opt/soft/mysql80  
bin/mysqld --initialize --user=mysql --basedir=/opt/soft/mysql80 --datadir=/work/mysql3008/var/  
# 初始化后 会打印出 root@localhost 默认密码 
bin/mysql_ssl_rsa_setup --datadir=/work/mysql3008/var/

启动实例

1
2
# 加载配置文件启动  
bin/mysqld_safe --defaults-file=/work/mysql3008/etc/my3008.cnf --user=mysql &

登录客户端

初次登录

1
2
3
# 初次登录使用初始化时打印的密码
# 初次登录后，需要更改root@localhost用户的默认密码  
/opt/soft/mysql80/bin/mysql -uroot -p -S /work/mysql3008/tmp/mysql3008.sock

更改默认密码

1
2
3
4
# 为了避免后续启动secondary节点时，事务ID不一致 3个实例，建议更改密码语句均不写binlog（我这里为了验证问题，主节点写binlog了，其它节点都不写binlog）
mysql> SET SQL_LOG_BIN=0;
mysql> alter user root@'localhost' identified by '123456';
mysql> SET SQL_LOG_BIN=1;

启动引导节点

创建用户

创建用于分布式恢复的复制账号：

1
2
3
4
5
6
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED with mysql_native_password BY '123123';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

备注：
1.用于分布式恢复，需要：REPLICATION SLAVE 权限
2.支持克隆插件并且作为donnor 节点需要：BACKUP_ADMIN 权限

设置白名单

1
2
3
4
5
# 指定允许哪些主机连接到组
# default：AUTOMATIC  它允许来自主机上活动的专用子网的连接  
# 不同网段地址 不会自动连接 必须设置白名单 
# 白名单IP为组复制中配置的localAddress地址  
SET GLOBAL group_replication_ip_whitelist="192.168.184.40,192.168.196.85,192.168.196.59";

启动引导节点

1
2
3
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

检验节点状态

检查是否创建组，并已有一个成员： 

1
2
3
4
5
6
7
mysql> 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 | 2c0ce582-5fce-11ea-b050-74867aea62bc | bjm6-184-40.cdn.org |        3008 | ONLINE       | PRIMARY     | 8.0.18         |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

添加测试数据

此时说明服务器上确实有一个组，并能处理负载，添加一些测试数据：

1
2
3
4
mysql> CREATE DATABASE dbtestx;
mysql> USE dbtestx;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');

检查表t1和二进制日志的内容 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show binlog events in 'binlog.000001';
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                           |
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000001 |    4 | Format_desc    |         1 |         124 | Server ver: 8.0.18, Binlog ver: 4                                                                                                                              |
| binlog.000001 |  124 | Previous_gtids |         1 |         151 |                                                                                                                                                                |
| binlog.000001 |  151 | Gtid           |         1 |         226 | SET @@SESSION.GTID_NEXT= '1036b565-6f41-11ea-a8e8-74867aea62bc:1'                                                                                              |
| binlog.000001 |  226 | Query          |         1 |         463 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$R;}HM4
                                                                           J*Yc>G?\\AN+jpFgvJDwT5MTaZ4BqNGyrjjjkaLrUEDDG1LbDxUMmOC' /* xid=4 */ |
| binlog.000001 |  463 | Gtid           |         1 |         545 | SET @@SESSION.GTID_NEXT= '291e41a2-6f40-11ea-a9da-74867aea62bc:1'                                                                                              |
| binlog.000001 |  545 | Query          |         1 |         607 | BEGIN                                                                                                                                                          |
| binlog.000001 |  607 | View_change    |         1 |         746 | view_id=15852139483469316:1                                                                                                                                    |
| binlog.000001 |  746 | Query          |         1 |         814 | COMMIT                                                                                                                                                         |
| binlog.000001 |  814 | Gtid           |         1 |         894 | SET @@SESSION.GTID_NEXT= '291e41a2-6f40-11ea-a9da-74867aea62bc:2'                                                                                              |
| binlog.000001 |  894 | Query          |         1 |         998 | CREATE DATABASE test /* xid=30 */                                                                                                                              |
| binlog.000001 |  998 | Gtid           |         1 |        1078 | SET @@SESSION.GTID_NEXT= '291e41a2-6f40-11ea-a9da-74867aea62bc:3'                                                                                              |
| binlog.000001 | 1078 | Query          |         1 |        1216 | use `test`; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL) /* xid=35 */                                                                                |
| binlog.000001 | 1216 | Gtid           |         1 |        1298 | SET @@SESSION.GTID_NEXT= '291e41a2-6f40-11ea-a9da-74867aea62bc:4'

将实例添加到组

启动登录

启动第二个节点：secondary；更改密码；设置白名单；创建用户；这几步操作同上

添加节点

change master

1
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123123' FOR CHANNEL 'group_replication_recovery';

启动组复制

1
mysql> START GROUP_REPLICATION;

查看节点状态

1
2
3
4
5
6
7
8
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 | 1036b565-6f41-11ea-a8e8-74867aea62bc | bjm6-184-40.cdn.org |        3008 | ONLINE       | PRIMARY     | 8.0.18         |
| group_replication_applier | 192618d3-6f43-11ea-8c7c-782bcb655966 | bjm6-196-59.cdn.org |        3009 | ONLINE       | SECONDARY   | 8.0.18         |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.02 sec)

继续添加节点

操作类似同上

单主切换多主

单主–>多主

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
# 在任意组成员实例上执行切换  
mysql> SELECT group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully.     |
+--------------------------------------------------+
1 row in set (1.01 sec)

# 组成员角色全部变为 Primary 
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 | 1036b565-6f41-11ea-a8e8-74867aea62bc | bjm6-184-40.cdn.org |        3008 | ONLINE       | PRIMARY     | 8.0.18         |
| group_replication_applier | 192618d3-6f43-11ea-8c7c-782bcb655966 | bjm6-196-59.cdn.org |        3009 | ONLINE       | PRIMARY     | 8.0.18         |
| group_replication_applier | cef4600e-6f43-11ea-9805-ecf4bbc36518 | bjm6-196-85.cdn.org |        3010 | ONLINE       | PRIMARY     | 8.0.18         |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)


# 查看发现 旧的secondary 变为 primary；原来的只读状态 自动变为关闭 
mysql> show variables like 'super_read_only';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| super_read_only | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

主动切换主节点

选择第二个节点：secondary 登录：执行切换命令

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
# 指定 第二节点的：MEMBER_ID
mysql> SELECT group_replication_set_as_primary('192618d3-6f43-11ea-8c7c-782bcb655966');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('192618d3-6f43-11ea-8c7c-782bcb655966') |
+--------------------------------------------------------------------------+
| Primary server switched to: 192618d3-6f43-11ea-8c7c-782bcb655966         |
+--------------------------------------------------------------------------+
1 row in set (0.02 sec)

# 查看状态 第二个节点 由 secondary 变为 Primary 
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 | 1036b565-6f41-11ea-a8e8-74867aea62bc | bjm6-184-40.cdn.org |        3008 | ONLINE       | SECONDARY   | 8.0.18         |
| group_replication_applier | 192618d3-6f43-11ea-8c7c-782bcb655966 | bjm6-196-59.cdn.org |        3009 | ONLINE       | PRIMARY     | 8.0.18         |
| group_replication_applier | cef4600e-6f43-11ea-9805-ecf4bbc36518 | bjm6-196-85.cdn.org |        3010 | ONLINE       | SECONDARY   | 8.0.18         |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+

mysql> show variables like 'super_read_only';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| super_read_only | ON    |
+-----------------+-------+