搭建MGR-3节点，多主模式
OS:CentOS7.2
DB:MySQL5.7.18
一、安装MySQL5.7.18
1、卸载冲突的版本mariadb
rpm -e mariadb-libs-5.5.44-2.el7.centos.x86_64 --nodeps
2、安装依赖
rpm -vih mysql-community-common-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.18-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.18-1.el7.x86_64.rpm
yum -y install net-tools
yum -y install perl
3、安装server
rpm -ivh mysql-community-server-5.7.18-1.el7.x86_64.rpm
4、创建数据目录、日志目录、授权
mkdir -p /data/lib/mysql
mkdir -p /data/log/mysql
chown -R mysql.mysql /data/lib/mysql/
chown -R mysql.mysql /data/log/mysql/
5、修改系统打开文件限制
vim /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
关闭selinux
vim /etc/selinux/config
SELINUX=disabled
6、修改配置文件(仅标示需要注意的地方)
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 = /data/log/mysql/3306/binlog/mysql-bin
log-bin-index = /data/log/mysql/3306/binlog/mysql-bin.index
binlog_format = row
# group replication
transaction_write_set_extraction = XXHASH64 #server必须为每个事物收集写集合，使用XXHASH64哈希算法将其编码为散列
loose-group_replication_group_name = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"
loose-group_replication_start_on_boot = off #插件在server启动时不自动启动组复制
loose-group_replication_local_address = "192.168.99.157:33061" #使用192.168.99.157:33061接受来自组中其他成员的传入连接
loose-group_replication_group_seeds = "192.168.99.156:33061,192.168.99.157:33061,192.168.99.158:33061"
loose-group_replication_bootstrap_group = off #插件不自动引导组
7、启动服务
systemctl start mysqld.service
8、设置开机自启动
systemctl enable mysqld.service
查看是否成功设置开机自启动
systemctl list-unit-files |grep mysqld
9、登录MySQL
到错误日志文件中找到临时密码进行登录，登录后修改临时密码
mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password('test_Pwd868');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> ALTER USER 'root'@'localhost' password expire never;
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
二、搭建MGR,多主模式
1、创建复制账号
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to repl_user@'192.168.99.%' identified by 'abc_Test123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
2、使用change master命令配置server，在下次需要从其他成员恢复其状态时，使用group_replication_recovery复制通道的给定凭据
mysql> change master to master_user='repl',master_password='abc_Test123' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.11 sec)
3、安装复制组插件
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.26 sec)
4、检查插件是否已成功安装，执行show plugins 并检查输出
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
.
.
.
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
5、配置引导组，此引导应仅有单个server独立完成，该server启动组并且只启动一次
mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set global group_replication_single_primary_mode=off;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (1.26 sec)
mysql> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)
6、start group_replication语句返回后，组就已启动了，可以检查组现在是否已创建并且其中已有一个成功
mysql> SELECT * FROM performance_schema.`replication_group_members`;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 7e29f043-2317-11e7-9594-fa163e98778e | host-192-168-99-156 | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
1 row in set (0.00 sec)
7、验证server确实在组中
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test;
Database changed
mysql> create table t1(c1 int primary key,c2 text not null);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t1 values(1,'Lily');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Lily |
+----+------+
1 row in set (0.00 sec)
mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.18-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 150 | |
| mysql-bin.000001 | 150 | Gtid | 1 | 211 | SET @@SESSION.GTID_NEXT= '7e29f043-2317-11e7-9594-fa163e98778e:1' |
| mysql-bin.000001 | 211 | Query | 1 | 278 | BEGIN |
| mysql-bin.000001 | 278 | Table_map | 1 | 329 | table_id: 62 (mysql.plugin) |
| mysql-bin.000001 | 329 | Write_rows | 1 | 401 | table_id: 62 flags: STMT_END_F |
| mysql-bin.000001 | 401 | Xid | 1 | 428 | COMMIT /* xid=3941 */ |
| mysql-bin.000001 | 428 | Stop | 1 | 447 | |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
8 rows in set (0.00 sec)
8、向组中添加实例
(1)修改配置文件
server_id = 2
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 = /data/log/mysql/3306/binlog/mysql-bin
log-bin-index = /data/log/mysql/3306/binlog/mysql-bin.index
binlog_format = row
# group replication
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = "192.168.99.157:33061"
loose-group_replication_group_seeds = "192.168.99.156:33061,192.168.99.157:33061,192.168.99.158:33061"
loose-group_replication_bootstrap_group = off
(2)启动实例
修改用户密码,创建复制账号
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'test_Pwd868';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@'192.168.99.%' IDENTIFIED BY 'abc_Test123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
(3)安装插件，启动复制组
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='abc_Test123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.04 sec)
mysql> set global group_replication_single_primary_mode=off;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
(4)查看现有组成员
mysql> SELECT * FROM performance_schema.`replication_group_members`;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 7e29f043-2317-11e7-9594-fa163e98778e | host-192-168-99-156 | 3306 | ONLINE |
| group_replication_applier | f65f46b2-2341-11e7-9975-fa163e6e90d0 | host-192-168-99-157 | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
2 rows in set (0.00 sec)
(5)查看数据，发现该实例数据已同步
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Lily |
+----+------+
1 row in set (0.00 sec)
9、参照以上方法添加第三个实例
三、异常处理
1、注意:前面的用户密码修改和创建用户操作必须设置binlog不记录，执行后再打开，否则会引起START GROUP_REPLICATION执行报错:
报错信息如下:
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.1
mysql后台报错信息:
2017-04-17T12:06:55.113724+08:00 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 423ccc44-2318-11
e7-96e9-fa163e6e90d0:1-4 > Group transactions: 7e29f043-2317-11e7-9594-fa163e98778e:1-5,
test-testtest-test-testtesttest:1-6'
2017-04-17T12:06:55.113825+08:00 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
2017-04-17T12:06:55.113835+08:00 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'
2017-04-17T12:06:55.113947+08:00 3 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2017-04-17T12:06:55.114493+08:00 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2017-04-17T12:07:00.054225+08:00 0 [Note] Plugin group_replication reported: 'state 4330 action xa_terminate'
2017-04-17T12:07:00.056324+08:00 0 [Note] Plugin group_replication reported: 'new state x_start'
2017-04-17T12:07:00.056349+08:00 0 [Note] Plugin group_replication reported: 'state 4257 action xa_exit'
2017-04-17T12:07:00.057272+08:00 0 [Note] Plugin group_replication reported: 'Exiting xcom thread'
2017-04-17T12:07:00.057288+08:00 0 [Note] Plugin group_replication reported: 'new state x_start'
2017-04-17T12:07:05.069548+08:00 3 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2017-04-17T12:07:05.069644+08:00 3 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2017-04-17T12:07:05.070107+08:00 9 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
解决方案：
根据提示打开group_replication_allow_local_disjoint_gtids_join选项，mysql命令行执行:
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
再次启动组复制
mysql> START GROUP_REPLICATION;
2、连不上master，报错信息如下：
2017-04-17T16:18:14.756191+08:00 25 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-04-17T16:18:14.814193+08:00 25 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master'repl_user@host-192-168-99-156:3306' - retry-time: 60 retries: 1, Error_code: 2005
2017-04-17T16:18:14.814219+08:00 25 [Note] Slave I/O thread for channel 'group_replication_recovery' killed while connecting to master
2017-04-17T16:18:14.814227+08:00 25 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
2017-04-17T16:18:14.814342+08:00 19 [ERROR] Plugin group_replication reported: 'There was an error when connecting to the donor server. Check group replication recovery's connection credentials.'
解决方案：
添加映射
vim /etc/hosts
192.168.99.156 db1 host-192-168-99-156
192.168.99.157 db2 host-192-168-99-157
192.168.99.158 db3 host-192-168-99-158
重启下组复制
mysql> stop group_replication;
Query OK, 0 rows affected (8.76 sec)
mysql> start group_replication;
Query OK, 0 rows affected (2.51 sec)
四、高可用验证
1、验证数据写入
在157写入数据
mysql> insert into t1 values(2,'Tom');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1 values(3,'Bot');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1 values(4,'Cristina');
Query OK, 1 row affected (0.03 sec)
通过156、158查看发现数据已同步
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+----+----------+
| c1 | c2 |
+----+----------+
| 1 | Lily |
| 2 | Tom |
| 3 | Bot |
| 4 | Cristina |
+----+----------+
4 rows in set (0.00 sec)
2、模拟宕机一个节点验证
(1)关闭157server
systemctl stop mysqld.service
(2)在158节点操作
查询组成员，发现157已不在组中
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 165dcc27-2353-11e7-a6a4-fa163edf90fa | host-192-168-99-158 | 3306 | ONLINE |
| group_replication_applier | a1b6be93-2351-11e7-b76b-fa163e98778e | host-192-168-99-156 | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
2 rows in set (0.00 sec)
(3)写入数据
mysql> insert into t1 values(5,'Mr.Li');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1;
+----+----------+
| c1 | c2 |
+----+----------+
| 1 | Lily |
| 2 | Tom |
| 3 | Bot |
| 4 | Cristina |
| 5 | Mr.Li |
+----+----------+
5 rows in set (0.00 sec)
(4)启动157server
修改配置文件，自动启动组复制
loose-group_replication_start_on_boot = on
group_replication_allow_local_disjoint_gtids_join = on
systemctl start mysqld.service
(5)再次查看组成员，发现157已重新加入组
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 165dcc27-2353-11e7-a6a4-fa163edf90fa | host-192-168-99-158 | 3306 | ONLINE |
| group_replication_applier | a1b6be93-2351-11e7-b76b-fa163e98778e | host-192-168-99-156 | 3306 | ONLINE |
| group_replication_applier | b00768d6-2354-11e7-95e0-fa163e6e90d0 | host-192-168-99-157 | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
3 rows in set (0.00 sec)
(6)查看数据，发现数据已同步
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+----+----------+
| c1 | c2 |
+----+----------+
| 1 | Lily |
| 2 | Tom |
| 3 | Bot |
| 4 | Cristina |
| 5 | Mr.Li |
+----+----------+
5 rows in set (0.00 sec)
备注：实际环境中可通过LVS配置VIP挂载三节点供用户应用使用
至此MGR搭建并验证完成
1106
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
