MySQL5.7 MGR集群搭建

最新推荐文章于 2024-03-15 18:30:00 发布

utopiaZH7

最新推荐文章于 2024-03-15 18:30:00 发布

阅读量550

点赞数

CC 4.0 BY-SA版权

分类专栏： Linux 文章标签： mysql linux

本文链接：https://blog.youkuaiyun.com/utopiaZH7/article/details/106077771

Linux 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了如何在三节点环境下部署MySQL Group Replication(MGR)，包括配置文件设置、用户权限分配、组复制启动步骤及常见错误处理。同时，提供了查看组成员状态和主节点信息的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

###节点规划
node1:192.168.10.5:3306
node2:192.168.10.5:3307
node3:192.168.10.5:3308

###################node1配置文件#########################

[root@localhost 3306data]# cat my3306.cnf 
[mysqld]
user=mysql
basedir=/home/mysql
datadir=/home/mysql/3306data/data
socket=/tmp/3306mysql.sock
port=3306
server_id=6
#事务手动提交
autocommit=0
#配置独立存储
innodb_file_per_table=1
#开启并配置binlog位置
log_bin=/home/mysql/3306data/mysql_bin
#binlog记录模式为行模式
binlog_format=row
#最大binlog数量
innodb_log_files_in_group=3
#设置binlog刷写动作为commit
innodb_flush_log_at_trx_commit=1
#数据直接刷写至磁盘
innodb_flush_method=O_DIRECT
#开启gtid
enforce_gtid_consistency=true
gtid_mode=on
log-slave-updates=1
log_error=/home/mysql/3306data/3306_error.log
#group特殊配置
#关闭其余引擎
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
############group配置##################
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
#mysql> select UUID();
group_replication_group_name="0ab806e9-91c4-11ea-80b4-000c29c83a75"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.10.5:33031"
group_replication_group_seeds= "192.168.10.5:33031,192.168.10.5:33032,192.168.10.5:33033"
group_replication_bootstrap_group=off
[mysql]
socket=/tmp/3306mysql.sock

###################node2配置文件#########################

[root@localhost 3307data]# cat my3307.cnf 
[mysqld]
user=mysql
basedir=/home/mysql
datadir=/home/mysql/3307data/data
socket=/tmp/3307mysql.sock
port=3307
server_id=7
#事务手动提交
autocommit=0
#配置独立存储
innodb_file_per_table=1
#开启并配置binlog位置
log_bin=/home/mysql/3307data/mysql_bin
#binlog记录模式为行模式
binlog_format=row
#最大binlog数量
innodb_log_files_in_group=3
#设置binlog刷写动作为commit
innodb_flush_log_at_trx_commit=1
#数据直接刷写至磁盘
innodb_flush_method=O_DIRECT
#开启gtid
enforce_gtid_consistency=true
gtid_mode=on
log-slave-updates=1
log_error=/home/mysql/3307data/3307_error.log
#group特殊配置
#关闭其余引擎
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
############group配置##################
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="0ab806e9-91c4-11ea-80b4-000c29c83a75"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.10.5:33032"
group_replication_group_seeds= "192.168.10.5:33031,192.168.10.5:33032,192.168.10.5:33033"
group_replication_bootstrap_group=off
[mysql]
socket=/tmp/3307mysql.sock

###################node3配置文件#########################

[root@localhost 3308data]# cat my3308.cnf 
[mysqld]
user=mysql
basedir=/home/mysql
datadir=/home/mysql/3308data/data
socket=/tmp/3308mysql.sock
port=3308
server_id=8
#事务手动提交
autocommit=0
#配置独立存储
innodb_file_per_table=1
#开启并配置binlog位置
log_bin=/home/mysql/3308data/mysql_bin
#binlog记录模式为行模式
binlog_format=row
#最大binlog数量
innodb_log_files_in_group=3
#设置binlog刷写动作为commit
innodb_flush_log_at_trx_commit=1
#数据直接刷写至磁盘
innodb_flush_method=O_DIRECT
#开启gtid
enforce_gtid_consistency=true
gtid_mode=on
log-slave-updates=1
log_error=/home/mysql/3308data/3308_error.log
#group特殊配置
#关闭其余引擎
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
############group配置##################
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="0ab806e9-91c4-11ea-80b4-000c29c83a75"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.10.5:33033"
group_replication_group_seeds= "192.168.10.5:33031,192.168.10.5:33032,192.168.10.5:33033"
group_replication_bootstrap_group=off
[mysql]
socket=/tmp/3308mysql.sock

步骤：
1、node1操作：

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER group_rpl@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO group_rpl@'%';
mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_USER='group_rpl', MASTER_PASSWORD='123456'  FOR CHANNEL 'group_replication_recovery';
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SET SQL_LOG_BIN=1;

2、node2操作：

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER group_rpl@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO group_rpl@'%';
mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_USER='group_rpl', MASTER_PASSWORD='123456'  FOR CHANNEL 'group_replication_recovery';
mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
mysql> START GROUP_REPLICATION;
mysql> SET SQL_LOG_BIN=1;

3、node3操作：

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER group_rpl@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO group_rpl@'%';
mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_USER='group_rpl', MASTER_PASSWORD='123456'  FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SET SQL_LOG_BIN=1;

4、查看状态

mysql> SELECT * FROM performance_schema.replication_group_members;

5、所遇报错
（1）报错1：

2020-05-08T12:54:19.359110Z 0 [ERROR] Plugin group_replication reported: 'Unable to announce tcp port 3306. Port already in use?'
2020-05-08T12:54:19.359547Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error joining the group while waiting for the network layer to become ready.'
2020-05-08T12:54:19.359606Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3306'
2020-05-08T12:55:19.358740Z 3 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'

配置此参数解决：

mysql> set global group_replication_allow_local_disjoint_gtids_join=on;

或者通过各节点reset master使各节点的GTID号保持一致解决。

（2）报错2：

2020-05-09T02:04:24.171758Z 0  [Warning] Plugin group_replication reported: 'read failed'
2020-05-09T02:04:24.171873Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33071'

所有配置文件的group_replication_group_name参数必须与node1的一致。

关于MGR相关参数说明

transaction_write_set_extraction #记录事务的算法
group_replication_start_on_boot #是否随服务器启动而自动启动组复制
group_replication_bootstrap_group #引导组成员的组，这个用于第一次搭建MGR跟重新搭建MGR的时候使用
group_replication_group_name  #此GROUP的名字，必须是一个有效的UUID，以此来区分整个内网里边的各个不的GROUP
group_replication_local_address #本地的IP地址字符串，host:port
group_replication_group_seeds  #需要接受本实例的信息服务器IP地址字符串
group_replication_single_primary_mode #是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读
group_replication_enforce_update_everywhere_checks #多主模式下，强制检查每一个实例是否允许该操作

查看组成员信息

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST           | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 76ab24d7-91bf-11ea-a514-000c29c83a75 | localhost.localdomain |        3306 | ONLINE       |
| group_replication_applier | 7df5b5f8-91bf-11ea-a6d5-000c29c83a75 | localhost.localdomain |        3307 | ONLINE       |
| group_replication_applier | 829e3ecb-91bf-11ea-a7d8-000c29c83a75 | localhost.localdomain |        3308 | ONLINE       |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)

#查看哪个节点是主库

mysql> SHOW STATUS LIKE 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 76ab24d7-91bf-11ea-a514-000c29c83a75 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

#查看当前库的组信息

	mysql> SELECT * FROM performance_schema.replication_group_member_stats;
	+---------------------------+---------------------+--------------------------------------+-----------------------------+----------------------------+--------------------------+------------------------------------+-------------------------------------------+-----------------------------------------+
	| CHANNEL_NAME              | VIEW_ID             | MEMBER_ID                            | COUNT_TRANSACTIONS_IN_QUEUE | COUNT_TRANSACTIONS_CHECKED | COUNT_CONFLICTS_DETECTED | COUNT_TRANSACTIONS_ROWS_VALIDATING | TRANSACTIONS_COMMITTED_ALL_MEMBERS        | LAST_CONFLICT_FREE_TRANSACTION          |
	+---------------------------+---------------------+--------------------------------------+-----------------------------+----------------------------+--------------------------+------------------------------------+-------------------------------------------+-----------------------------------------+
	| group_replication_applier | 15890085766642228:7 | 76ab24d7-91bf-11ea-a514-000c29c83a75 |                           0 |                          8 |                        0 |                                  4 | 0ab806e9-91c4-11ea-80b4-000c29c83a75:1-13 | 0ab806e9-91c4-11ea-80b4-000c29c83a75:13 |
	+---------------------------+---------------------+--------------------------------------+-----------------------------+----------------------------+--------------------------+------------------------------------+-------------------------------------------+-----------------------------------------+
	1 row in set (0.00 sec)
#

官方文档