MySQL MGR 集群验证

1. 1. 集群搭建
      1. Mysql安装

本次安装使用的数据库版本是5.7.24，安装采用yum在线安装方式，安装过程如下：

1、安装 wget

yum -y install wget

2、获取mysql源

wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

3、安装mysql源

yum localinstall mysql57-community-release-el7-8.noarch.rpm

4、查看是否安装成功

yum repolist enabled | grep "mysql.*-community.*"

5、安装mysql

yum install mysql-community-server

6、启动MySQL服务

systemctl start mysqld

systemctl restart mysqld

7、查看MySQL的启动状态

systemctl status mysqld

8、设置开机启动

systemctl enable mysqld

systemctl daemon-reload

9、修改root本地登录密码

mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql进行修改：

grep 'temporary password' /var/log/mysqld.log 查看密码

mysql -u root -p 连接mysql

修改密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

修改权限：

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456";

 

    #(a)数据库目录 /var/lib/mysql/ #(b)配置文件 /usr/share /mysql（mysql.server命令及配置文件） #(c)相关命令 /usr/bin（mysqladmin mysqldump等命令） #(d)启动脚本 /etc/rc.d/init.d/（启动脚本文件mysql的目录）

 

默认配置文件路径：

配置文件：/etc/my.cnf 

日志文件：/var/log//var/log/mysqld.log 

服务启动脚本：/usr/lib/systemd/system/mysqld.service 

socket文件：/var/run/mysqld/mysqld.pid

 

如果忘记root密码，则按如下操作恢复：

在[mysqld]的段中加上一句：skip-grant-tables 保存并且退出vi。

mysql  -u root

update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';

flush privileges

1. 1. 1. 集群配置
         1. 准备工作

（1）修改三台机器的名称分别为 mysql-1 mysql-2 mysql-3

hostnamectl set-hostname mysql-1   

修改完成后需要重启。

（2）写入hosts文件映射关系，集群用得到

192.168.1.101    mysql-1

192.168.1.102    mysql-2

192.168.1.103    mysql-3

   vi /etc/hosts 将以上关系加入   

1. 1. 1. 1. 配置过程

（1）配置文件加入/etc/my.cof

 

# Group Replication

server_id = 1

gtid_mode = ON

enforce_gtid_consistency = ON

master_info_repository = TABLE

relay_log_info_repository = TABLE

binlog_checksum = NONE

log_slave_updates = ON

log_bin = binlog

binlog_format= ROW

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = '2612877a-0a08-4c2b-a956-27595d080ffe'

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = '192.168.1.101:33061'

loose-group_replication_group_seeds ='192.168.1.101:33061,192.168.1.102:33062,192.168.1.103:33063'

loose-group_replication_bootstrap_group = off

loose-group_replication_ip_whitelist='192.168.1.101/24,192.168.1.102/24,192.168.1.103/24'

三台机器分别修改配置文件：以上标红的地方需要修改，server_id 不相同即可，

loose-group_replication_local_address 修改为当前机器的ip 对应的地址。

（2）配置完成后重启mysql

（3）连接mysql然后执行下步

（4）开启mgr

 

set sql_log_bin=0;

//创建用户

CREATE USER mgr_user@'%' IDENTIFIED BY '123456';

//用户赋值权限

grant replication slave on *.* to mgr_user@'%' identified by '123456';

flush privileges;

set sql_log_bin=1;

change master to master_user='mgr_user',master_password='123456' for channel 'group_replication_recovery';

//安装mgr插件

install PLUGIN group_replication SONAME 'group_replication.so';

// 主节点 mysql-1 执行

set global group_replication_bootstrap_group=ON;

start group_replication;

set global group_replication_bootstrap_group=OFF;

//其他两个从节点

set global group_replication_allow_local_disjoint_gtids_join=ON;、

start group_replication;

 

（5）执行完成后通过以下sql（任意一个节点）

查看组成员情况

select * from performance_schema.replication_group_members;

以上状态代表 三个节点全部生效

查看主节点：

SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'group_replication_primary_member';

这里可以看到当前是mysql-1 是主节点，此时mgr单主模式配置完成。

或者通过：

SELECT

    MEMBER_ID,

    MEMBER_HOST,

    MEMBER_PORT,

    MEMBER_STATE,

    IF(global_status.VARIABLE_NAME IS NOT NULL,

        'PRIMARY',

        'SECONDARY') AS MEMBER_ROLE

FROM

    performance_schema.replication_group_members

        LEFT JOIN

    performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'

         and     global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;

以上sql 可以直接查询。

 

1. 1. 验证过程
      1. 单主模式
         1. 同步测试

测试过程：

1.在mysql-1主节点创建数据库test_mgr，并在数据库中创建表td_b_test，表中插入数据。

2.查看mysql-2、mysql-3数据库、表以及表数据同步情况

结果：

       mysql-2、mysql-3数据库以及表、表数据都跟mysql-1一致。

 

1. 1. 1. 1. 主节点mysqld进程崩溃或主机宕机（无延迟）

测试过程：

1. 直接杀死mysql-1机器上的mysql进程或者重启myql-1机器（采用杀死进程方式）,然后在mysql-2或者mysql-3中执行1.1.2.2 步骤5中的查询组成员和主节点信息。

        通过查询组成员和主从状态，可以看到mysql-2自动选为主节点。

           3.在mysql2 中 添加一条数据，然后将mysql-1 加入组中，然后查询状态和mysql-1中数据。可以看到mysql-1重新加入组中，并且同步了未加入组时在mysql-2中的数据。

change master to master_user='mgr_user',master_password='123456' for channel 'group_replication_recovery';

set global group_replication_allow_local_disjoint_gtids_join=ON;

start group_replication;

              结果：主节点崩溃后，从节点自动选择住节点，并且节点重新加入后会同步崩溃后新的主节点的数据。

1. 1. 1. 1. 一个从节点mysqld进程崩溃或主机宕机（无延迟）

测试过程：

1.直接杀死mysql-3的进程，然后再查询节点组成员状态。

       2.在mysql-2中修改数据，然后节点3重新加入

      

结果：从节点崩溃后，主节点正常对外提供读写,系统对外没明显异常变化，并且从节点重新加入后会重新加入后会同步崩溃后新的主节点的数据。

1. 1. 1. 1. 两个个从节点mysqld进程崩溃

 

测试过程：

1.直接杀死mysql-3、mysql-1的进程，然后再查询节点组成员状态。

2.主数据库修改数据，查询数据，然后mysql-1，mysql-2 重新加入，查看组成员状态和数据库数据。

 

结果：

                     两个从节点mysql 进程被杀死后，剩余节点可进行读写操作。

 

1. 1. 1. 1. 两个个从节点主机宕机（无延迟）

测试过程：

1.关闭mysql-2、mysql-3机器（这个无法测试，只能通过重启操作）

       2.尝试剩余节点的数据修改、读取操作

结果：

       剩余节点仅能够读，不可写。

1. 1. 1. 1. 主节点mysqld进程崩溃或者主机宕机(复制延迟)

测试过程：

1. 使用mysqlslap压测mysql-1

2. 查询执行事务设置队列长度迫使2个seconady存在较长时间延迟

3.直接杀死mysql-1的mysql进程或者重启mysql-1机器

4.确认集群primary状态

结果：

能正常切换选出新primary节点，并且数据不会出现丢失

 

1. 1. 1. 1. 三个节点在复制存在延迟情况下崩溃或宕机

测试过程：

1.使用mysqlslap压测mysql-1

2.查询执行事务设置队列长度迫使2个seconady存在较长时间延迟

3.重启  mysql-1（primary）; 重启  mysql-2（secondary）; 重启  mysql-3（secondary）

4.启动三个节点，数据最多的主机为主节点

5.确认MGR状态

结果：

       MGR状态2个重启装填转换成在线状态，最终3个在线状态，且数据一致

 

1. 1. 1. 1. 主节点和其他两个节点网络超时

测试过程：

1.使用mysqlslap压测mysql-1

2.在mysql-2和mysql-3模拟网络延迟情况（例如执行iptables -I INPUT -s 192.168.1.101 -j DROP屏蔽主节点服务器的ip）

3.确认mysqlslap是否能继续读写

4.确认primary是否发生迁移

5.去除网络分区从新加入MGR（祛除对主节点服务器ip的屏蔽）

6.确认数据是否一致

结果：

       当发生网络分区情况，少于1/2 members的节点不会对外提供写操作，primary迁移

1. 1. 1. 1. 删除数据，清理binlog下恢复方式

测试过程：

       1．mysql-1purge  binary logs to 'mysql-bin.000021';

2．mysql-2退出mgr删除数据，并reset master

3．mysql-2启动加入mgr命令

结果：

       能自动全库恢复,或者其他方式数据同步基于例如binlog

 

1. 1. 1. 多主模式

参照1.3将单主模式切换成多主模式。

1. 1. 1. 1. 同步测试

测试过程：

1. 在三个数据库中分别创建mgr_mysql1、mgr_mysql2、mgr_mysql3，查看其它两节点数据库是否创建。
2. 在三个数据库中分别创建表td_b_test1、td_b_test2、td_b_test3，查看其它两节点数据表是否创建。
3. 在三个表中分别插入数据，查看其它两节点数据是否同步。

结果：

       在任意节点创建数据库、创建表、添加数据均同步。

1. 1. 1. 1. 三节点同时操作验证

测试过程：

1. 三个应用，每个应用10个线程，同时连接不同节点数据库，操作同一个库，每个应用同一个表插入10000条数据
2. 验证三个节点数据是否始终保持一致，验证数据同步延迟情况。

结果：

       三个节点同时进入数据，并且数据保持一致，数据基本无延迟。

 

1. 1. 1. 1. 任意一个节点mysql崩溃或系统宕机(没有延迟)

测试过程：

1. 停止mysql-3 机器
2. mysql-2、mysql-1 分别进行表数据的修改，查看数据是否同步。
3. mysql-3 重新加入，查看数据是否同步，重新加入需要重新调整模式（mysql宕机后默认启动是单主模式）。

set global group_replication_single_primary_mode=OFF;

set global group_replication_enforce_update_everywhere_checks=ON;

set global group_replication_allow_local_disjoint_gtids_join=ON;

start group_replication;

 

结果：

       一个节点停止后，其他两个节点正常使用，并且数据同步正常，重新增加节点后数修改的数据同步到新的节点。

 

1. 1. 1. 1. 任意两个节点 mysql崩溃或系统宕机(没有延迟)

测试过程：

1. 停止mysql-2、mysql-3机器
2. mysql-1 进行表数据的修改
3. mysql-2、mysql-3 重新加入，查看数据是否同步，重新加入需要重新调整模式（mysql宕机后默认启动是单主模式）。

set global group_replication_single_primary_mode=OFF;

set global group_replication_enforce_update_everywhere_checks=ON;

set global group_replication_allow_local_disjoint_gtids_join=ON;

start group_replication;

结果：

       两个节点停止后，一个节点正常使用，重新增加节点后数修改的数据同步到新的节点。

1. 1. 1. 1. 复制延迟情况下任意一个节点宕机

测试过程：

1.mysqlslap-1 压mysql-1

2.mysqlslap-2 压mysql-2

3.mysqlslap-3 压mysql-3

4.上面三个同时进行压力测试

5.登录各个库比较插入表和复制表是否存在延迟

6.存在延迟后重启其中任意1个主机

7.恢复后从新加入MGR

结果：

       从新加入节点后，数据一致性不受影响。剩余主机读写不受影响

 

1. 1. 1. 1. 复制延迟情况下两个个节点宕机

测试过程：

       1.mysqlslap-1 压mysql-1

2.mysqlslap-2 压mysql-2

3.mysqlslap-3 压mysql-3

4.上面三个同时进行压力测试

5.登录各个库比较插入表和复制表是否存在延迟

6.存在延迟后重启其中任意2个主机

7.恢复后从新加入MGR

结果：

       2个节点重启后，剩下节点任然可写；

2个节点从新启动后任然可以加入到原MGR中恢复数据一致性

 

1. 1. 1. 1. 存在延迟的情况下三个节点都宕机

测试过程：

       1.mysqlslap-1 压mysql-1

2.mysqlslap-2 压mysql-2

3.mysqlslap-3 压mysql-3

4.上面三个同时进行压力测试

5.登录各个库比较插入表和复制表是否存在延迟

6.存在延迟后依次重启 其中3个主机/直接同时重启三个机器

7.恢复后从新加入MGR

结果：

       数据一致，可以正常启动

 

1. 1. 1. 1. 任意一个节点到其他两个节点网络超时

测试过程：

       1.使用mysqlslap压测mysql-1

2.在mysql-2和mysql-3模拟网络延迟情况（例如执行iptables -I INPUT -s 192.168.1.101 -j DROP屏蔽主节点服务器的ip）

3.确认mysqlslap是否能继续读写

4.确认primary是否发生迁移

5.去除网络分区从新加入MGR（祛除对主节点服务器ip的屏蔽）

6.确认数据是否一致

结果：

       当发生网络分区情况，少于1/2 members的分区不会对外提供写操作，数据一致性得到保证

1. 1. 单主多主模式切换
      1. 切换多主模式

# 停止组复制(所有节点执行)：

stop group_replication;

set global group_replication_single_primary_mode=OFF;

set global group_replication_enforce_update_everywhere_checks=ON;

 

loose-group_replication_start_on_boot

 

# 随便选择某个节点执行

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;

# 其他节点执行

set global group_replication_allow_local_disjoint_gtids_join=ON;

start group_replication;

 

或者可以直接修改配置文件 /etc/my.cof

加入或者修改为：

loose-group_replication_single_primary_mode=false
loose-group_replication_enforce_update_everywhere_checks=true 

1. 1. 1. 切换单主模式

# 所有节点执行

stop group_replication;

set global group_replication_enforce_update_everywhere_checks=OFF;

set global group_replication_single_primary_mode=ON;

# 主节点（192.168.1.101）执行

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;

# 从节点（192.168.1.102、192.168.1.103）执行

change master to master_user='mgr_user',master_password='123456' for channel 'group_replication_recovery';

set global group_replication_allow_local_disjoint_gtids_join=ON;

start group_replication;

# 查看MGR组信息

SELECT * FROM performance_schema.replication_group_members;

或者

 

或者可以直接修改配置文件 /etc/my.cof

加入或者修改为：

loose-group_replication_single_primary_mode=true
loose-group_replication_enforce_update_everywhere_checks=false 

 

注意：目前机器配置默认是开启了多主模式

 

1. 1. 测试结论
2. MGR单主和多主模式下，如果由于服务器宕机或者网络原因，导致剩余组内节点数量少于总节点数量的1/2（服务器之间无法进行数据传输），将导致剩余节点仅能够提供读能力，这里是为了保证全局数据的一致性，所以要求一个节点宕机后要及时恢复。
3. MGR中对磁盘的性能要求比较高（需要磁盘同步数据文件）。
4. MGR恢复是基于数据文件的，要对数据文件做好备份。
5. 如果所有节点同时宕机或者网络无法连通或者存在较大的延迟（不是mysql服务停止）情况下，所有节点同时崩溃，会导致数据不可逆无法恢复，且备份时不易备份（因为各个节点都存在延迟）
6. MGR中如果一个节点退出组（stop group_replication）后，数据库服务可用，不过仅是只读模式，在多主模式中要及时加入组；如果是异常退出，mysql进程被杀死或者服务器重启，重新启动mysq服务后，未加入组之前，此节点可以读写数据，重新加入后此节点的数据不会同步到其他节点造成数据不一致（此时如果再操作新加入的节点中其他节点没有的数据可能会造成组失败）,并且无法重新启动。

 

1. 1.     附： MGR官方限制说明:

1、必须binlog-checksum=NONE

2、不支持gap锁，建议使用Read-Commit（这点集成默认推荐使用RC模式不冲突）

3、MGR没有考虑到表锁情况，个人目前感觉这点主要体现在多主情况，或者单主模式下Primary lock table后，主从切换表锁失效问题。

4、不支持 Savepoints

5、不支持事务级别SERIALIZABLE

6、不支持同一对象上的DDL和DML操作（Multi-Master情况下）

7、不支持外键级联约束限制（Multi-Master情况下）

8、避免大事务