本文呈现如何部署MySQL Group Replication的多写模式：Multi-Primary。
在我的环境里有三台服务器：
[root@mysql-manager ~]# cat /etc/hosts #127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 #::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 # Local 127.0.0.1 localhost # Pub 192.168.147.129 mysql-manager 192.168.147.132 mysql-node-1 192.168.147.133 mysql-node-2 [root@mysql-manager ~]#
数据库版本：MySQL 5.7.17
[root@mysql-manager ~]# cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# MySQL 5.7.17
export MYSQL_HOME=/opt/mysql/5.7.17
export PATH=$MYSQL_HOME/bin:$PATH
[root@mysql-manager ~]#
[root@mysql-manager ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using EditLine wrapper
[root@mysql-manager ~]#
创建需要的目录：
[root@mysql-manager ~]# mkdir -p /data/mysql/{db_data,binlog_data,log_data,plugin_data,run}
[root@mysql-manager ~]#
[root@mysql-manager ~]# chown -R mysql.mysql /data/mysql
[root@mysql-manager ~]#
配置文件：/etc/my.cnf
[root@mysql-manager ~]# cat /etc/my.cnf [mysqld] datadir=/data/mysql/db_data plugin-dir=/data/mysql/plugin_data log-error=/data/mysql/log_data/mysql-error.log socket=/data/mysql/run/mysql.sock pid-file=/data/mysql/run/mysql.pid user=mysql port=3306 # for replication 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/mysql/binlog_data/bingo binlog_format=ROW # 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= "192.168.147.129:24901" loose-group_replication_group_seeds= "192.168.147.129:24901,192.168.147.132:24901,192.168.147.133:24901" loose-group_replication_bootstrap_group= off loose-group_replication_single_primary_mode=FALSE loose-group_replication_enforce_update_everywhere_checks= TRUE [root@mysql-manager ~]#
对于不同的服务器节点，其中【server_id】与【group_replication_local_address】会有所不同。
初始化数据库（三个服务器都要操作）
[root@mysql-manager ~]# tree /data/mysql /data/mysql ├── binlog_data ├── db_data ├── log_data ├── plugin_data └── run 5 directories, 0 files [root@mysql-manager ~]# [root@mysql-manager ~]# mysqld --initialize-insecure [root@mysql-manager ~]# [root@mysql-manager ~]# ls -ltr /data/mysql/ total 0 drwxr-xr-x 2 mysql mysql 6 Mar 4 13:49 run drwxr-xr-x 2 mysql mysql 6 Mar 4 13:49 plugin_data drwxr-xr-x 2 mysql mysql 28 Mar 4 13:50 log_data drwxr-xr-x 2 mysql mysql 43 Mar 4 13:50 binlog_data drwxr-xr-x 5 mysql mysql 139 Mar 4 13:50 db_data [root@mysql-manager ~]# [root@mysql-manager ~]# ls -ltr /data/mysql/run/ total 0 [root@mysql-manager ~]# ls -ltr /data/mysql/plugin_data/ total 0 [root@mysql-manager ~]# ls -ltr /data/mysql/log_data/ total 4 -rw-r----- 1 mysql mysql 1860 Mar 4 13:50 mysql-error.log [root@mysql-manager ~]# ls -ltr /data/mysql/binlog_data/ total 8 -rw-r----- 1 mysql mysql 37 Mar 4 13:50 bingo.index -rw-r----- 1 mysql mysql 169 Mar 4 13:50 bingo.000001 [root@mysql-manager ~]# ls -ltr /data/mysql/db_data/ total 110628 -rw-r----- 1 mysql mysql 50331648 Mar 4 13:50 ib_logfile1 -rw-r----- 1 mysql mysql 56 Mar 4 13:50 auto.cnf drwxr-x--- 2 mysql mysql 8192 Mar 4 13:50 performance_schema drwxr-x--- 2 mysql mysql 4096 Mar 4 13:50 mysql drwxr-x--- 2 mysql mysql 8192 Mar 4 13:50 sys -rw-r----- 1 mysql mysql 413 Mar 4 13:50 ib_buffer_pool -rw-r----- 1 mysql mysql 50331648 Mar 4 13:50 ib_logfile0 -rw-r----- 1 mysql mysql 12582912 Mar 4 13:50 ibdata1 [root@mysql-manager ~]# [root@mysql-manager ~]#
启动数据库：
[root@mysql-manager ~]# netstat -tupln | grep mysql [root@mysql-manager ~]# [root@mysql-manager ~]# nohup mysqld --defaults-file=/etc/my.cnf >/data/mysql/log_data/nohup.out 2>/data/mysql/log_data/nohup_error.out & [1] 27302 [root@mysql-manager ~]# [root@mysql-manager ~]# netstat -tupln | grep mysql tcp6 0 0 :::3306 :::* LISTEN 27302/mysqld [root@mysql-manager ~]#
修改root口令：
[root@mysql-manager ~]# mysql -uroot -h127.0.0.1 -P3306 --skip-password mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 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. mysql> show variables like 'SQL_LOG_BIN'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.01 sec) mysql> show variables like 'SQL_LOG_BIN'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> alter user 'root'@'localhost' identified by 'abcd1234'; Query OK, 0 rows affected (0.01 sec) mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'SQL_LOG_BIN'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql>
创建Group Replication需要的用户：
mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> create user repl_me@'%'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to repl_me@'%' identified by 'abcd1234'; 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) mysql> change master to master_user='repl_me',master_password='abcd1234' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql>
安装Group Replication的插件：
将插件模块（so）移动到制定目录
[root@mysql-manager ~]# find / -name "group_replication.so" /opt/mysql/5.7.17/lib/plugin/debug/group_replication.so /opt/mysql/5.7.17/lib/plugin/group_replication.so /software/mysql/mysql-5.7.17-linux-glibc2.5-x86_64/lib/plugin/debug/group_replication.so /software/mysql/mysql-5.7.17-linux-glibc2.5-x86_64/lib/plugin/group_replication.so [root@mysql-manager ~]#
安装：
[root@mysql-manager ~]# mysql -uroot -h127.0.0.1 -P3306 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 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. mysql> mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.01 sec) mysql>
确保安装成功：
mysql> show plugins; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 45 rows in set (0.00 sec) mysql>
启动：Group Replication
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication
-> ;
Query OK, 0 rows affected (1.16 sec)
mysql>
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql>
验证MGR启动状态：
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 6cccf27e-009e-11e7-9ed0-000c29825e54 | mysql-manager | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 1 row in set (0.00 sec) mysql>
查看网路端口的情况：
[root@mysql-manager ~]# netstat -tupln | grep mysql tcp 0 0 0.0.0.0:24901 0.0.0.0:* LISTEN 27302/mysqld tcp6 0 0 :::3306 :::* LISTEN 27302/mysqld [root@mysql-manager ~]#
————————
这里，先启动两个MGR：
1. mysql-manager
2. mysql-node-1
启动好了以后，你会看到上面的查询会是这样的结果：
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 6cccf27e-009e-11e7-9ed0-000c29825e54 | mysql-manager | 3306 | ONLINE | | group_replication_applier | 70c065bf-009e-11e7-8235-000c29fabef3 | mysql-node-1 | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 2 rows in set (0.00 sec) mysql>
在mysql-manager上创建测试库与表：
mysql> system hostname mysql-manager mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> mysql> create database smo_king; Query OK, 1 row affected (0.01 sec) mysql> use smo_king; Database changed mysql> select database(); +------------+ | database() | +------------+ | smo_king | +------------+ 1 row in set (0.00 sec) mysql> mysql> show tables; Empty set (0.00 sec) mysql> mysql> create table people( id int, name varchar(50), location varchar(400)); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +--------------------+ | Tables_in_smo_king | +--------------------+ | people | +--------------------+ 1 row in set (0.00 sec) mysql> mysql> desc people; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | location | varchar(400) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> mysql> alter table people add primary key(id); Query OK, 0 rows affected (0.04 sec) mysql> mysql> desc people; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(50) | YES | | NULL | | | location | varchar(400) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> mysql> insert into people values(1,"red queen","umbraller"); Query OK, 1 row affected (0.01 sec) mysql> select * from people; +----+-----------+-----------+ | id | name | location | +----+-----------+-----------+ | 1 | red queen | umbraller | +----+-----------+-----------+ 1 row in set (0.00 sec) mysql>
在启动了MGR的第二个节点【mysql-node-1】上查询上面创建的库与表，是否同步过来了：
[root@mysql-node-1 ~]# mysql -uroot -h127.0.0.1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 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. mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | smo_king | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use smo_king; 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_smo_king | +--------------------+ | people | +--------------------+ 1 row in set (0.00 sec) mysql> desc people; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(50) | YES | | NULL | | | location | varchar(400) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from people; +----+-----------+-----------+ | id | name | location | +----+-----------+-----------+ | 1 | red queen | umbraller | | 2 | ailenware | dell | +----+-----------+-----------+ 2 rows in set (0.00 sec) mysql> exit Bye [root@mysql-node-1 ~]#
可以看到，确实都同步到了。
将节点三【mysql-node-3】，也加入组复制，启动节点三的MGR，因为配置都已经配置好了，和上面的类似，就是本地服务器相关的配置要做更改，并且MGR插件已经安装：
启动前：
[root@mysql-node-2 ~]# mysql -uroot -h127.0.0.1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 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. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+-----------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+-----------+-------------+-------------+--------------+ | group_replication_applier | | | NULL | OFFLINE | +---------------------------+-----------+-------------+-------------+--------------+ 1 row in set (0.00 sec) mysql>
启动，并查看：
mysql> start group_replication; Query OK, 0 rows affected (2.09 sec) mysql> mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 6cccf27e-009e-11e7-9ed0-000c29825e54 | mysql-manager | 3306 | ONLINE | | group_replication_applier | 70c065bf-009e-11e7-8235-000c29fabef3 | mysql-node-1 | 3306 | ONLINE | | group_replication_applier | 731c26e7-009e-11e7-8f7a-000c29a5a9c3 | mysql-node-2 | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | smo_king | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from smo_king.people; +----+-----------+-----------+ | id | name | location | +----+-----------+-----------+ | 1 | red queen | umbraller | | 2 | ailenware | dell | +----+-----------+-----------+ 2 rows in set (0.00 sec) mysql>
测试数据插入：
节点一：
mysql> insert into smo_king.people values(3,'from node1','mysql-manager'); Query OK, 1 row affected (0.01 sec) mysql>
节点二：
mysql> insert into smo_king.people values(4,'from node2','mysql-node-1'); Query OK, 1 row affected (0.01 sec) mysql>
节点三：
mysql> insert into smo_king.people values(5,'from node3','mysql-node2'); Query OK, 1 row affected (0.00 sec) mysql>
最后，查询一下：
mysql> select * from smo_king.people; +----+------------+---------------+ | id | name | location | +----+------------+---------------+ | 1 | red queen | umbraller | | 2 | ailenware | dell | | 3 | from node1 | mysql-manager | | 4 | from node2 | mysql-node-1 | | 5 | from node3 | mysql-node2 | +----+------------+---------------+ 5 rows in set (0.00 sec) mysql>
可以看到，数据在节点间都同步了。
——————————
至此，MySQL Group Replication的Multi-Primary，就搭建完了。
——————————
Done。