MySQL · 2016-12-21 2

MySQL Group Replication 9节点快速部署

1.概述


本篇讲解group replication的安装部署。由于group replication最大支持9个节点,本文将大家在一台机器上快速部署9节点的group replication,多机器部署是一样的,没有任何区别。

1

2.安装规划


group_replication_group_name=”aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa”

节点名	Internal port	Db port	datadir	conf	mode
s1	5701	9001	/data/for_gr/s1	s1.cnf	read/write
s2	5702	9002	/data/for_gr/s2	S2.cnf	read/write
s3	5703	9003	/data/for_gr/s3	S3.cnf	read/write
s4	5704	9004	/data/for_gr/s4	S4.cnf	read/write
s5	5705	9005	/data/for_gr/s5	S5.cnf	read/write
s6	5706	9006	/data/for_gr/s6	S6.cnf	read/write
s7	5707	9007	/data/for_gr/s7	S7.cnf	read/write
s8	5708	9008	/data/for_gr/s8	S8.cnf	read/write
s9	5709	9009	/data/for_gr/s9	S9.cnf	read/write

3.目录和二进制文件准备


3.1二进制安装包准备

直接在官网下载5.7.17,解压放到/opt下

[mysql@10-25-126-227 mysql-5.7.17]$ pwd
/opt/mysql-5.7.17
[mysql@10-25-126-227 mysql-5.7.17]$ ls -l
total 52
drwxrwxr-x  2 mysql mysql  4096 Dec 13 11:44 bin
-rw-r--r--  1 mysql mysql 17987 Nov 28 21:32 COPYING
drwxrwxr-x  2 mysql mysql  4096 Dec 13 11:44 docs
drwxrwxr-x  3 mysql mysql  4096 Dec 13 11:44 include
drwxrwxr-x  5 mysql mysql  4096 Dec 13 11:44 lib
drwxrwxr-x  4 mysql mysql  4096 Dec 13 11:44 man
-rw-r--r--  1 mysql mysql  2478 Nov 28 21:32 README
drwxrwxr-x 28 mysql mysql  4096 Dec 13 11:44 share
drwxrwxr-x  2 mysql mysql  4096 Dec 13 11:44 support-files

3.2数据目录准备

mkdir -p /data/for_gr/{s1,s2,s3,s4,s5,s6,s7,s8,s9}

3.3配置文件准备

这里以s1配置为例,其他配置文件修改数据目录和端口即可。

[mysqld]
# server configuration
datadir=/data/for_gr/s1    #不同节点,数据目录修改即可
basedir=/opt/mysql-5.7.17
port=9001                   #修改为响应节点的端口,用户客户端连接
socket=/data/for_gr/s1/s1.sock  #socket也改到响应数据目录

server_id=1                       #server-id别忘记改,就1-9吧
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
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=1G
innodb_flush_log_at_trx_commit=2
sync_binlog=0
#for parallel apply binlog
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=on
#for group replication
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:5701" #改一下端口就可以了
loose-group_replication_group_seeds= "127.0.0.1:5701,127.0.0.1:5702,127.0.0.1:5703,127.0.0.1:5704,127.0.0.1:5705,127.0.0.1:5706,127.0.0.1:5707,127.0.0.1:5708,127.0.0.1:5709"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=false

其他节点,改一下上面注释部分就可以了。
复制配置文件:

for i in {2..9};do cp s1.cnf s$i.cnf;done
修改相关配置:
for i in {2..9};do sed -i "s/s1/s$i/g" s$i.cnf;done
for i in {2..9};do sed -i "s/server_id=1/server_id=$i/g" s$i.cnf;done
for i in {2..9};do sed -i "s/port=9001/port=900$i/g" s$i.cnf;done
for i in {2..9};do sed -i "s/loose-group_replication_local_address= \"127.0.0.1:5701\"/loose-group_replication_local_address= \"127.0.0.1:570$i\"/g" s$i.cnf;done
确认准备好的目录和配置文件:
[mysql@10-25-126-227 for_gr]$ tree /data/conf/for_gr/
/data/conf/for_gr/
├── s1.cnf
├── s2.cnf
├── s3.cnf
├── s4.cnf
├── s5.cnf
├── s6.cnf
├── s7.cnf
├── s8.cnf
└── s9.cnf

0 directories, 9 files
[mysql@10-25-126-227 for_gr]$ tree /data/for_gr/
/data/for_gr/
├── s1
├── s2
├── s3
├── s4
├── s5
├── s6
├── s7
├── s8
└── s9


9 directories, 0 files

4.初始化数据库


for i in {1..9};do /opt/mysql-5.7.17/bin/mysqld --initialize-insecure --basedir=/opt/mysql-5.7.17 --datadir=/data/for_gr/s$i ;done;

5.启动数据库


[mysql@10-25-126-227 mysql-5.7.17]$ cd /opt/mysql-5.7.17/
[mysql@10-25-126-227 mysql-5.7.17]$ pwd
/opt/mysql-5.7.17
[mysql@10-25-126-227 mysql-5.7.17]$ for i in {1..9}; do ./bin/mysqld_safe --defaults-file=/data/conf/for_gr/s$i.cnf &  done
[mysql@10-25-126-227 mysql-5.7.17]$ pidof mysqld
5615 5608 5607 5606 5604 5600 5592 5591 5585

6.添加复制用户


由于group replication只支持innodb引擎,用户放在mysql.user里面,是myisam引擎,所以我们先把每个节点的用户准备好,分节点创建。这里我们创建两个用户,一个用于group replication内部使用,另外一个用户管理整个group replication。

注意:添加用户不要写到binlog去。

6.1准备好用户创建脚本

[mysql@10-25-126-227 ~]$ cat ~/user.sql 
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
CREATE USER dbmon@'%';
GRANT ALL PRIVILEGES ON *.* TO dbmon@'%' IDENTIFIED BY 'dbmon';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

6.2所有节点都创建一遍

cd /opt/mysql-5.7.17
for i in {1..9};do ./bin/mysql -S /data/for_gr/s$i/s$i.sock -e "source /home/mysql/user.sql";done

7.初始化group replication


注意创建一个group replication,需要在一个节点初始化,也只需要在一个节点初始化,不可在多个节点都执行。这里我们选择第一个。

[mysql@10-25-126-227 mysql-5.7.17]$ ./bin/mysql -S /data/for_gr/s1/s1.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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.

root@localhost [(none)] 03:22:30>>>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] 03:22:54>>>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] 03:22:54>>>START GROUP_REPLICATION;
Query OK, 0 rows affected (1.05 sec)

root@localhost [(none)] 03:22:55>>>SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] 03:23:04>>>SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: e0c9963b-c4ee-11e6-99a1-525400e71e7c
 MEMBER_HOST: 10-25-126-227
 MEMBER_PORT: 9001
MEMBER_STATE: ONLINE
1 row in set (0.00 sec)

由以上查询可以看到,我们整个group replication已经有一个节点在线了。

8.添加其他节点加入group replication


for i in {2..9};do ./bin/mysql -S /data/for_gr/s$i/s$i.sock -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so';START GROUP_REPLICATION;";done

注意:这次只添加了2-9,1号节点已经在线了。

9.在线节点状态查询


2

九个节点都是Online状态。

10.关闭group replication


for i in {1..9};do ./bin/mysql -S /data/for_gr/s$i/s$i.sock -e "STOP GROUP_REPLICATION;";done

然后关闭所有数据库就行了。

11.启动group replication


首先按照正常流程启动数据库。

11.1启动第一个节点

启动节点首先需要在某个节点执行初始化动作,这个节点最好是最后关闭的那个节点,这里我们用S9:

root@localhost [(none)] 03:30:25>>>show variables like '%server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 9     |
+---------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)] 03:30:29>>>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] 03:30:38>>>START GROUP_REPLICATION;
Query OK, 0 rows affected (1.01 sec)

root@localhost [(none)] 03:30:39>>>SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] 03:30:55>>>SELECT MEMBER_HOST,MEMBER_PORT,MEMBER_STATE FROM performance_schema.replication_group_members;
+---------------+-------------+--------------+
| MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE |
+---------------+-------------+--------------+
| 10-25-126-227 |        9009 | ONLINE       |
+---------------+-------------+--------------+
1 row in set (0.00 sec)

11.2启动添加剩下8个节点

for i in {1..8};do ./bin/mysql -S /data/for_gr/s$i/s$i.sock -e "START GROUP_REPLICATION;";done

3