MySQL Group Replication搭建[Single-Primary Mode]

这篇文章记录如何部署mysql group replication 单写模式 (single-primary mode)

部署方式大致与多写模式的部署一致,所需要修改的只有my.cnf里面关于group replication的配置。

多写模式的部署参考另外一篇文章:

http://blog.csdn.net/d6619309/article/details/53691790

1. 环境准备

  • CentOS5.6
  • mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

2. 部署

我们的目标是在一台机器上安装3个mysql实例,然后配置mysql group replication,模式为single-primary mode

安装目录说明如下:

1) 实例数据将安装在

  • s1: /dba/mysql/data/s1
  • s2: /dba/mysql/data/s2
  • s3: /dba/mysql/data/s3

2) mysql5.7解压到

1
/dba/mysql/mysql-5.7

2.1 安装多实例(s1、s2、s3)

出于试验和测试的考虑,我们直接用编译好的mysql安装包进行精简安装:

1
2
3
4
5
cd /dba/mysql
mkdir data
mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s1
mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s2
mysql-5.7/bin/mysqld --initialize-insecure --basedir=$PWD/mysql-5.7 --datadir=$PWD/data/s3

2.2 s1实例部署

2.2.1 配置实例

1
2
3
cd $PWD/data/s1
touch s1.cnf
vi s1.cnf
  • 加入主配置
1
2
3
4
5
6
7
8
[mysqld]
# server configuration
datadir=/dba/mysql/data/s1
basedir=/dba/mysql/mysql-5.7/
port=24801
socket=/dba/mysql/data/s1/s1.sock
  • 加入replication配置
1
2
3
4
5
6
7
8
9
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

其中大部分配置是为了打开group replication必须配置的,详见:

http://mysqlhighavailability.com/mysqlha/gr/doc/limitations.html

  • 加入group replication配置
1
2
3
4
5
6
7
8
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "127.0.0.1:24901"
loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=true
loose-group_replication_enforce_update_everywhere_checks=false

配置解释见:

http://mysqlhighavailability.com/mysqlha/gr/doc/getting_started.html#group-replication

注意关闭多写模式相关配置:

  • loose-group_replication_single_primary_mode=true
  • loose-group_replication_enforce_update_everywhere_checks=false

2.2.2 启动实例

执行以下命令启动s1实例:

1
nohup mysql-5.7/bin/mysqld --defaults-file=data/s1/s1.cnf >data/s1/nohup.out 2>data/s1/nohup.out &

注意mysql用户必须拥有对data目录下的读写权限

2.2.3 用户授权

成功启动之后,登陆mysql:

1
mysql -uroot -h127.0.0.1 -P24801 --skip-password

登陆之后建议修改root登陆密码,通过以下语句修改:

1
2
3
SET SQL_LOG_BIN=0;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql';
SET SQL_LOG_BIN=1;

修改密码遇到ERROR:

1
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

密码安全策略太高引起,参考http://www.cnblogs.com/ivictor/p/5142809.html

执行:

1
set global validate_password_policy=0;

然后再设置密码即可


mysql命令行下创建group replication所需要的用户:

1
2
3
4
5
6
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

2.2.4 开启组复制

mysql命令行下执行:

1
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
1
2
3
4
5
6
7
8
9
10
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+-------------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+-------------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
(...)
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | PROPRIETARY |
+----------------------------+----------+--------------------+----------------------+-------------+

看到group_replication确保安装插件成功

接下来执行下面的语句,启动group replication:

1
2
3
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

执行下面的语句,验证group replication已经成功启动:

1
2
3
4
5
6
7
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 3c992270-c282-11e6-93bf-fa163ee40410 | ${yourhostname} | 24801 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

接下来创建测试用的库和表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0,00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0,00 sec)
mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0,01 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0,00 sec)
mysql> show binlog events;
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids | 1 | 150 | |
| binlog.000001 | 150 | Gtid | 1 | 211 | SET @@SESSION.GTID_NEXT= '3c992270-c282-11e6-93bf-fa163ee40410:1' |
| binlog.000001 | 211 | Query | 1 | 386 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' |
| binlog.000001 | 386 | Gtid | 1 | 447 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1' |
| binlog.000001 | 447 | Query | 1 | 506 | BEGIN |
| binlog.000001 | 506 | View_change | 1 | 645 | view_id=14817781596395401:1 |
| binlog.000001 | 645 | Query | 1 | 710 | COMMIT |
| binlog.000001 | 710 | Gtid | 1 | 771 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2' |
| binlog.000001 | 771 | Query | 1 | 861 | CREATE DATABASE test |
| binlog.000001 | 861 | Gtid | 1 | 922 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3' |
| binlog.000001 | 922 | Query | 1 | 1046 | use `test`; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL) |
| binlog.000001 | 1046 | Gtid | 1 | 1107 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4' |
| binlog.000001 | 1107 | Query | 1 | 1175 | BEGIN |
| binlog.000001 | 1175 | Table_map | 1 | 1218 | table_id: 219 (test.t1) |
| binlog.000001 | 1218 | Write_rows | 1 | 1260 | table_id: 219 flags: STMT_END_F |
| binlog.000001 | 1260 | Xid | 1 | 1287 | COMMIT /* xid=47 */ |
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)

2.3 增加新成员s2

1
2
3
cd data/s2
touch s2.cnf
vi s2.cnf

与配置s1实例类似,增加s2.cnf并写入如下配置:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[mysqld]
# server configuration
datadir=/dba/mysql/data/s2
basedir=/dba/mysql/mysql-5.7/
port=24802
socket=/dba/mysql/data/s2/s2.sock
#
# Replication configuration parameters
#
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=binlog
binlog_format=ROW
#
# Group Replication configuration
#
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "127.0.0.1:24902"
loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=true
loose-group_replication_enforce_update_everywhere_checks=false

之后启动s2实例:

1
nohup mysql-5.7/bin/mysqld --defaults-file=data/s2/s2.cnf >data/s2/nohup.out 2>data/s2/nohup.out &

修改用户密码:

1
2
3
SET SQL_LOG_BIN=0;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql';
SET SQL_LOG_BIN=1;

之后配置group replication需要的用户:

1
2
3
4
5
6
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

接下来安装group replication插件:

1
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

然后就可以把s2添加到当前group里面:

1
mysql> START GROUP_REPLICATION;

【注意】 前面的用户密码修改和创建用户操作必须设置binlog不记录,执行后再打开,否则会引起START GROUP_REPLICATION执行报错:

报错信息如下所示:

1
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

mysql后台报错信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2016-12-15T07:51:28.317816Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: f16f7f74-c283-11e6-ae37-fa163ee40410:1 > Group transactions: 3c992270-c282-11e6-93bf-fa163ee40410:1,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5'
2016-12-15T07:51:28.317878Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
2016-12-15T07:51:28.317887Z 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'
2016-12-15T07:51:28.317999Z 14 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2016-12-15T07:51:28.318429Z 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2016-12-15T07:51:32.437462Z 0 [Note] Plugin group_replication reported: 'state 4330 action xa_terminate'
2016-12-15T07:51:32.437897Z 0 [Note] Plugin group_replication reported: 'new state x_start'
2016-12-15T07:51:32.437913Z 0 [Note] Plugin group_replication reported: 'state 4257 action xa_exit'
2016-12-15T07:51:32.437981Z 0 [Note] Plugin group_replication reported: 'Exiting xcom thread'
2016-12-15T07:51:32.437993Z 0 [Note] Plugin group_replication reported: 'new state x_start'
2016-12-15T07:51:37.472364Z 14 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2016-12-15T07:51:37.472474Z 14 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2016-12-15T07:51:37.472943Z 19 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2016-12-15T07:51:37.485851Z 16 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'

如果出现上述问题,解决方案是:根据提示打开group_replication_allow_local_disjoint_gtids_join选项,mysql命令行执行:

1
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

然后再执行:

1
2
mysql> start group_replication;
Query OK, 0 rows affected (7.89 sec)

执行成功,查询组成员信息:

1
2
3
4
5
6
7
8
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 3c992270-c282-11e6-93bf-fa163ee40410 | ${yourhostname} | 24801 | ONLINE |
| group_replication_applier | f16f7f74-c283-11e6-ae37-fa163ee40410 | ${yourhostname} | 24802 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

看到实例s2已经加入当前group

查看database发现test数据库和表t1已经被同步创建:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)

表t1的数据也被同步了:

1
2
3
4
5
6
7
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.00 sec

2.4 增加新实例s3

与增加s2实例类似,不赘述,直接看配置:

1
2
3
cd data/s3
touch s3.cnf
vi s3.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[mysqld]
# server configuration
datadir=/dba/mysql/data/s3
basedir=/dba/mysql/mysql-5.7/
port=24803
socket=/dba/mysql/data/s3/s3.sock
#
# Replication configuration parameters
#
server_id=3
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
#
# Group Replication configuration
#
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "127.0.0.1:24903"
loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=true
loose-group_replication_enforce_update_everywhere_checks=false

启动s3实例:

1
nohup mysql-5.7/bin/mysqld --defaults-file=data/s3/s3.cnf >data/s3/nohup.out 2>data/s3/nohup.out &

登陆、修改密码后,执行:

1
2
3
SET SQL_LOG_BIN=0;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql';
SET SQL_LOG_BIN=1;
1
2
3
4
5
6
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

安装插件:

1
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

最后将s3加入group:

1
START GROUP_REPLICATION;

查看同步状态:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 3c992270-c282-11e6-93bf-fa163ee40410 | ${yourhost} | 24801 | ONLINE |
| group_replication_applier | f16f7f74-c283-11e6-ae37-fa163ee40410 | ${yourhost} | 24802 | ONLINE |
| group_replication_applier | f9b49bd8-c283-11e6-afb2-fa163ee40410 | ${yourhost} | 24803 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.00 sec)

这个时候已经完成了一个group replication multi primary mode的部署了。

如何查看group内哪个节点是作为primary节点,官方提供了一个方法:

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

得到的是primary节点的MEMBER_ID,这个id等价于server_uuid

3. 简单测试

  • 在s3上面执行:
1
2
mysql> insert into t1(c1, c2) values(null, 's3');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

执行报错,single-primary mode下只有primary可以写(s1)。

  • 在s2上面执行:
1
2
mysql> insert into t1(c1, c2) values(null, 's2');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

同样报错。

  • 在s1上面执行:
1
2
mysql> insert into t1(c1, c2) values(null, 's1');
Query OK, 1 row affected (0.03 sec)

执行成功。

在s2和s3上查询数据同步情况:

1
2
3
4
5
6
7
8
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
| 8 | s1 |
+----+------+
2 rows in set (0.00 sec)

group replication 自增列的步长默认为7

4. 参考

http://dev.mysql.com/doc/refman/5.7/en/group-replication-getting-started.html

坚持原创技术分享,您的支持将鼓励我继续创作!