MySQL - MGR多主部署教程

官方文档，遇到问题多看看官方文档...

准备工作

系统：Centos7.6 / 64 位
MySql：v5.7.34
Keepalived：v1.2.24
Rpm：http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
二进制：https://downloads.mysql.com/archives/community/

MySql部署规划（双主互备）

实例	IP	端口	备注
MySQL	10.88.40.17	3306	主
MySQL	10.88.40.47	3306	主
MySQL	10.88.40.60	3306	主

首先每台机器上安装MySQL，并设置密码

https://www.jianshu.com/p/23ba05b80cd4

修改每个节点的配置文件my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

relay-log-recovery=1
server_id=1 #每个节点要不一样
log-bin=mysql-bin
log-slave-updates
binlog-format=row
binlog-checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction-write-set-extraction=XXHASH64
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4
slave_preserve_commit_order=1

transaction_write_set_extraction = XXHASH64  #以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212889f856' #组的名字可以随便起,但不能用主机的GTID，select uuid()可随机获取一个UUID
loose-group_replication_start_on_boot = off  #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_bootstrap_group = off #同上
loose-group_replication_local_address = '10.88.40.17:33061' #写自己主机所在IP
loose-group_replication_group_seeds ='10.88.40.17:33061,10.88.40.47:33061,10.88.40.60:33061'
loose-group_replication_single_primary_mode = off  #关闭单主模式的参数
loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数

plugin_load_add='group_replication.so'

设置好后重启MySQL，使用命令service mysqld restart

• 使用命令行登录 mysql -uroot -pxxxx 自己设置的密码
• 在3台服务器上执行

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

• 在17服务器上执行

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members;

• 在47、60服务器上执行

set global group_replication_single_primary_mode =OFF;
CHANGE MASTER TO MASTER_USER='xxxxxx',MASTER_PASSWORD='xxxx' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;

• 错误锦集

[https://blog.csdn.net/lijingkuan/article/details/80827779](https://blog.csdn.net/lijingkuan/article/details/80827779)
[https://www.cnblogs.com/naocanzhishen/p/13540861.html](https://www.cnblogs.com/naocanzhishen/p/13540861.html)

• 参考

[https://www.cnblogs.com/nanxiang/p/13982906.html](https://www.cnblogs.com/nanxiang/p/13982906.html)
[https://www.cnblogs.com/manger/p/7211932.html](https://www.cnblogs.com/manger/p/7211932.html)
[https://www.cnblogs.com/sallyluo/p/11760304.html](https://www.cnblogs.com/sallyluo/p/11760304.html)

测试多主 MGR

• 17节点

mysql> create database ceshi;
Query OK, 1 row affected (10.01 sec)

• 47节点

mysql> create table t1(id int primary key);
Query OK, 0 rows affected (0.02 sec)

• 60节点

mysql> insert into t1 select 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

• 17节点

mysql> select * from ceshi.t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)