1. mysql高可用之组复制
1.1 概念
a.mysql组复制是基于GTID和Paxos类一致性协议的高可用方案，多节点组成复制组
b.读写事务需经过组内超过半数节点同意才提交，确保数据一致性，只读事务本地直接提交
c.支持单主/多主模式，节点故障时自动选择新主，能够自动处理，保障集群高可用与数据可靠性
1.2 组复制流程
复制组中，读写事务需经组内超过半数(N/2+1)节点同意才能提交，只读事务可直接提交,如下图所示:
1.3 组复制单主和多主模式
1.3.1 单写模式/单主模式
单写模式group内有一台节点可写可读，其他节点只可以读，当主服务器失败时，会自动选择新的主服务器,如下图所示
1.3.2 多写/多主模式
组内所有机器都是primary节点，同时可以进行读写操作，并且数据是最终一致的，如下图所示:
2. 实现mysql组复制-单主模式
2.1 配置环境
| 主机名 | ip | 操作系统版本 | mysql版本 |
| mysql-master171 | 192.168.75.171/24 | rhel7.9 | mysql8.0.40 |
| mysql-master172 | 192.168.75.172/24 | rhel7.9 | mysql8.0.40 |
| mysql-master173 | 192.168.75.173/24 | rhel7.9 | mysql8.0.40 |
2.2 环境配置
2.2.1 编写jeams脚本
[root@mysql-master171 ~]# vim jeams.sh
#!/bin/bash
#移除系统的maraidb
yum remove mariadb* -y
#把编译好的mysql上传并解压缩
tar -xvf /root/mysql.tar.gz -C /
#添加环境变量
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
#立即加载并立即生效
source /etc/profile
#创建mysql系统用户
useradd -M -s /sbin/nologin mysql
#将 MySQL 安装目录下的服务启动脚本，复制到系统默认的服务管理目录，便于系统命令管理mysql服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
#创建存储mysql数据文件的目录
mkdir -p /data/mysql
#/data/设置所属者:所属组为mysql
chown -R mysql:mysql /data/
#初始化
mysqld --initialize --user=mysql
#启动mysql
/etc/init.d/mysqld start
#执行完这脚本，会弹出一个初始密码，然后执行以下命令即可完成mysql的部署
#修改数据库密码，新密码为123
[root@mysql-master171 ~]# mysqladmin -uroot -p password '123'
#使用新密码登录数据库
[root@mysql-master171 ~]# mysql -uroot -p123
mysql>2.2.2 将编译好的mysql和执行脚本传输给两个从库
a. 传输编译好的mysql给两台从库
[root@mysql-master171 ~]# scp mysql.tar.gz root@192.168.75.172:/root
[root@mysql-master171 ~]# scp mysql.tar.gz root@192.168.75.173:/root
b. 传输脚本给两台从库
[root@mysql-master171 ~]# scp jeams.sh root@192.168.75.172:/root
[root@mysql-master171 ~]# scp jeams.sh root@192.168.75.173:/rootc. 两个从库要设置权限给脚本
[root@mysql-master172 ~]# chmod +x jeams.sh
[root@mysql-master173 ~]# chmod +x jeams.shd. 立即执行当前jeams.sh脚本
[root@mysql-master172 ~]# source ./jeams.she. 三台主机均需要配置hosts文件的解析
mysql-master171:
[root@mysql-master171 ~]# vim /etc/hosts
192.168.75.171 mysql-master171
192.168.75.172 mysql-master172
192.168.75.173 mysql-master173
mysql-master172:
[root@mysql-master172 ~]# vim /etc/hosts
192.168.75.171 mysql-master171
192.168.75.172 mysql-master172
192.168.75.173 mysql-master173
mysql-master173:
[root@mysql-master173 ~]# vim /etc/hosts
192.168.75.171 mysql-master171
192.168.75.172 mysql-master172
192.168.75.173 mysql-master173
3. mysql组复制-单主模式
3.1 mysql-mater171的配置
3.1.1 修改配置文件
[root@mysql-master171 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log_timestamps=SYSTEM #设置日志时间和本地时间保持一致
server_id=171
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #组复制，数据必须存储在 InnoDB 事务存储引擎中
gtid_mode=ON #组复制要开启gtid
enforce_gtid_consistency=ON
log_bin=binlog #默认开启
log_slave_updates=ON #默认开启
binlog_format=ROW #默认开启
transaction_write_set_extraction=XXHASH64 #默认开启，组复制使用此信息在所有组成员上进行冲突检测
plugin_load_add='group_replication.so' #将组复制插件添加到服务器启动时加载的插件列表中
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #告诉插件它正在加入或创建的组名为“aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa”
group_replication_start_on_boot=off #插件在服务器启动时不自动启动操作，使用手动启动插件
group_replication_local_address= "192.168.75.171:33061" #与其它主机通信时使用的网络地址和端口
group_replication_group_seeds= "192.168.75.171:33061,192.168.75.172:33061,192.168.75.173:33061" #设置组成员的主机名和端口
group_replication_bootstrap_group=off #指示插件是否启动该组，在首次引导组时在一个服务器上启用
group_replication_ip_whitelist="192.168.75.0/24,127.0.0.1/8" #仅允许白名单内的 IP 加入复制组
group_replication_recovery_use_ssl=on #caching_sha2_password插件要求安全传输密码，开启主从之间的连接使用SSL/TLS 3.1.2 重启mysql
[root@mysql-master171 ~]# /etc/init.d/mysqld restart3.1.3 主库中使用sql语句添加复制账号并授予权限，账号需要设置一致
#临时关闭当前会话二进制日志记录功能
mysql> SET SQL_LOG_BIN=0;
#创建名为rp的mysql用户，允许任何主机连接，密码设为123
mysql> CREATE USER rp@'%' IDENTIFIED BY '123';
#给rp用户授予复制从库的权限
mysql> GRANT REPLICATION SLAVE ON *.* TO rp@'%';
#给rp用户授予连接管理的权限
mysql> GRANT CONNECTION_ADMIN ON *.* TO rp@'%';
#给rp用户授予备份管理的权限
mysql> GRANT BACKUP_ADMIN ON *.* TO rp@'%';
#给rp用户授予组复制的权限
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rp@'%';
#刷新mysql的权限缓存
mysql> FLUSH PRIVILEGES;
#重新开启当前会话的二进制日志记录功能
mysql> SET SQL_LOG_BIN=1;
#给组复制里负责数据恢复的专用通道，并指定该通道使用rp用户和密码123去连接其他节点
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rp', SOURCE_PASSWORD='123'FOR CHANNEL 'group_replication_recovery';3.1.4 查看是否有group_replication插件
mysql> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
3.1.5 启动MGR集群
a. 使用当前服务器作为引导服务器启动一个新的群组复制过程
#开启组复制初始化模式，告诉当前节点 “你是第一个启动的，要负责创建复制组”
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
#用之前创建的 rp 账号和密码 123，启动当前节点的组复制功能
mysql> START GROUP_REPLICATION USER='rp',PASSWORD='123';
#关闭组复制初始化模式，收回“组长权限”
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;b. 在主库上查看组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 8897d825-899f-11f0-ac65-000c29301e50 | mysql-master171 | 3306 | ONLINE | PRIMARY | 8.0.40 | XCom |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.01 sec)
#CHANNEL_NAME：通道名称。组复制插件创建两个复制通道：
#group_replication_recovery：用于与分布式恢复阶段相关的复制更改。
#group_replication_applier：用于来自组传入的更改，是应用直接来自组的事务的通道。
#MEMBER_ID：组成员实例的server_uuid。
#MEMBER_HOST：组成员主机名。如果配置了report_host参数，这里显示IP地址。
#MEMBER_ROLE：成员角色，主为PRIMARY，从为SECONDARY。
#MEMBER_VERSION：成员数据库实例版本。
#MEMBER_STATE：成员状态，取值和含义如下所示：
#ONLINE 表示该成员可正常提供服务
#RECOVERING 表示当前成员正在从其它节点恢复数据
#OFFLINE 表示组复制插件已经加载，但是该成员不属于任何一个复制组
#ERROR 表示成员在recovery阶段出现错误或者从其它节点同步状态中出现错误
#UNREACHABLE 成员处于不可达状态，无法与之进行网络通讯
3.2 mysql-master172上的配置
3.2.1 修改配置文件
[root@mysql-master172 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log_timestamps=SYSTEM
server_id=172
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="192.168.75.172:33061"
group_replication_group_seeds="192.168.75.171:33061,192.168.75.172:33061,192.168.75.173:33061"
group_replication_ip_whitelist="192.168.75.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_recovery_use_ssl=on 3.2.2 重启mysql
[root@mysql-master172 ~]# /etc/init.d/mysqld restart3.2.3 在数据库中使用sql语句添加复制账号并授予权限，要设置一致的账号
#临时关闭当前会话二进制日志记录功能
mysql> SET SQL_LOG_BIN=0;
#创建名为rp的mysql用户，允许任何主机连接，密码设为123
mysql> CREATE USER rp@'%' IDENTIFIED BY '123';
#给rp用户授予复制从库的权限
mysql> GRANT REPLICATION SLAVE ON *.* TO rp@'%';
#给rp用户授予连接管理的权限
mysql> GRANT CONNECTION_ADMIN ON *.* TO rp@'%';
#给rp用户授予备份管理的权限
mysql> GRANT BACKUP_ADMIN ON *.* TO rp@'%';
#给rp用户授予组复制的权限
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rp@'%';
#刷新mysql的权限缓存
mysql> FLUSH PRIVILEGES;
#重新开启当前会话的二进制日志记录功能
mysql> SET SQL_LOG_BIN=1;
#给组复制里负责数据恢复的专用通道，并指定该通道使用rp用户和密码123去连接其他节点
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rp', SOURCE_PASSWORD='123'FOR CHANNEL 'group_replication_recovery';3.2.4 开启组复制
mysql> START GROUP_REPLICATION USER='rp', PASSWORD='123';3.2.5 查看组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 8897d825-899f-11f0-ac65-000c29301e50 | mysql-master171 | 3306 | ONLINE | PRIMARY | 8.0.40 | XCom |
| group_replication_applier | bd0796d7-899f-11f0-ab0f-000c291d967f | mysql-master172 | 3306 | ONLINE | SECONDARY | 8.0.40 | XCom |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)
3.3 mysql-master173上的配置
3.3.1 修改配置文件
[root@mysql-master173 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log_timestamps=SYSTEM
server_id=173
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="192.168.75.173:33061"
group_replication_group_seeds="192.168.75.171:33061,192.168.75.172:33061,192.168.75.173:33061"
group_replication_ip_whitelist="192.168.75.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_recovery_use_ssl=on 3.3.2 重启mysql
[root@mysql-master173 ~]# /etc/init.d/mysqld restart3.3.3 在数据库中使用sql语句添加复制账号并授予权限，要设置一致的账号
#临时关闭当前会话二进制日志记录功能
mysql> SET SQL_LOG_BIN=0;
#创建名为rp的mysql用户，允许任何主机连接，密码设为123
mysql> CREATE USER rp@'%' IDENTIFIED BY '123';
#给rp用户授予复制从库的权限
mysql> GRANT REPLICATION SLAVE ON *.* TO rp@'%';
#给rp用户授予连接管理的权限
mysql> GRANT CONNECTION_ADMIN ON *.* TO rp@'%';
#给rp用户授予备份管理的权限
mysql> GRANT BACKUP_ADMIN ON *.* TO rp@'%';
#给rp用户授予组复制的权限
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO rp@'%';
#刷新mysql的权限缓存
mysql> FLUSH PRIVILEGES;
#重新开启当前会话的二进制日志记录功能
mysql> SET SQL_LOG_BIN=1;
#给组复制里负责数据恢复的专用通道，并指定该通道使用rp用户和密码123去连接其他节点
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='rp', SOURCE_PASSWORD='123'FOR CHANNEL 'group_replication_recovery';3.3.4 开启组复制
mysql> START GROUP_REPLICATION USER='rp', PASSWORD='123';3.3.5 查看组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 60b68ec8-89a1-11f0-91a1-000c29cc483f | mysql-master173 | 3306 | ONLINE | SECONDARY | 8.0.40 | XCom |
| group_replication_applier | 8897d825-899f-11f0-ac65-000c29301e50 | mysql-master171 | 3306 | ONLINE | PRIMARY | 8.0.40 | XCom |
| group_replication_applier | bd0796d7-899f-11f0-ab0f-000c291d967f | mysql-master172 | 3306 | ONLINE | SECONDARY | 8.0.40 | XCom |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
3.4 测试
3.4.1 在主库上进行测试
mysql> create database jeams;
mysql> use jeams
#必须设置主键，不然无法插入数据
mysql> create table t1(id int primary key,name char(30));
mysql> insert into t1 values (1,'xiaoming');
#在三个节点均可以查看到新增信息
mysql> select * from jeams.t1;
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoming |
+----+----------+
1 row in set (0.00 sec)3.4.2 在从库上进行测试
#验证不支持写入操作
mysql> insert into jeams.t1 values (2,'xiaohong');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement3.4.3 测试主库挂机
#测试主节点宕机
mysql> stop GROUP_REPLICATION;
3.4.4 查看是否有从库变为主库
#从库节点查看，发现仅剩2个节点，并且自动选举出主节点
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 60b68ec8-89a1-11f0-91a1-000c29cc483f | mysql-master173 | 3306 | ONLINE | PRIMARY | 8.0.40 | XCom |
| group_replication_applier | bd0796d7-899f-11f0-ab0f-000c291d967f | mysql-master172 | 3306 | ONLINE | SECONDARY | 8.0.40 | XCom |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)
#在重新选举出来的mysql-master173上测试插入数据
mysql> insert into jeams.t1 values (3,'xiaohei');
mysql> select * from jeams.t1;
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoming |
| 3 | xiaohei |
+----+----------+
2 rows in set (0.00 sec)
3.4.5 重新启动前面的主库，可以看到前面的主库已经变成现在的从库了
#启动组复制命令
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (10.73 sec)
#查看当前复制组里所有节点的详细状态
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 60b68ec8-89a1-11f0-91a1-000c29cc483f | mysql-master173 | 3306 | ONLINE | PRIMARY | 8.0.40 | XCom |
| group_replication_applier | 8897d825-899f-11f0-ac65-000c29301e50 | mysql-master171 | 3306 | ONLINE | SECONDARY | 8.0.40 | XCom |
| group_replication_applier | bd0796d7-899f-11f0-ab0f-000c291d967f | mysql-master172 | 3306 | ONLINE | SECONDARY | 8.0.40 | XCom |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
4. 实现mysql组复制-多主模式
| 主机 | ip | 操作系统模式 | mysql版本 |
| mysql-master171 | 192.168.75.171/24 | rhel7.9 | mysql8.0.40 |
| mysql-master172 | 192.168.75.172/24 | rhel7.9 | mysql8.0.40 |
| mysql-master173 | 192.168.75.173/24 | rhel7.9 | mysql8.0.40 |
4.1 修改所有的my.cnf的配置文件
所有主机在上一个实验的后面添加两条命令
[root@mysql-master171 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log_timestamps=SYSTEM
server_id=171
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="192.168.75.171:33061"
group_replication_group_seeds="192.168.75.171:33061,192.168.75.172:33061,192.168.75.173:33061"
group_replication_ip_whitelist="192.168.75.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_recovery_use_ssl=on
#要添加的东西
#关闭单master模式
loose-group_replication_single_primary_mode=off
#多主一致性检查
loose-group_replication_enforce_update_everywhere_checks=ON4.2 在mysql-master171主机里面执行以下命令
4.2.1 给当前节点开启组复制初始化权限，告诉它"你是第一个节点，负责创建复制组"
mysql> SET GLOBAL group_replication_bootstrap_group=ON;4.2.2启动当前节点的组复制功能
mysql> START GROUP_REPLICATION;4.2.3关掉组复制初始化权限，收回建组资格
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;4.3 在myql-master172和mysql-master173主机里面执行以下命令
4.3.1 启动当前mysql节点的组复制功能
mysql> START GROUP_REPLICATION;4.3.2 查询当前复制组里所有节点的状态信息
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 60b68ec8-89a1-11f0-91a1-000c29cc483f | mysql-master173 | 3306 | ONLINE | PRIMARY | 8.0.40 | XCom |
| group_replication_applier | 8897d825-899f-11f0-ac65-000c29301e50 | mysql-master171 | 3306 | ONLINE | PRIMARY | 8.0.40 | XCom |
| group_replication_applier | bd0796d7-899f-11f0-ab0f-000c291d967f | mysql-master172 | 3306 | ONLINE | PRIMARY | 8.0.40 | XCom |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
4.4 测试
在三个主机上面分别执行三条插入语句测试是否可以执行写入操作
4.4.1 mysql-master171
mysql> insert jeams.t1 values (4,'xiaolan');
Query OK, 1 row affected (0.01 sec)
4.4.2 mysql-master172
mysql> insert jeams.t1 values ('5','xiaozi');
Query OK, 1 row affected (0.01 sec)
4.4.3 mysql-master173
mysql> insert jeams.t1 values (6,'xiaohei');
Query OK, 1 row affected (0.00 sec)
4.4.4 查看结果
mysql> select * from jeams.t1;
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoming |
| 3 | xiaohei |
| 4 | xiaolan |
| 5 | xiaozi |
| 6 | xiaohei |
+----+----------+
5 rows in set (0.00 sec)