1. 环境准备
- CentOS7.3
- percona-server-5.7.18-14
两台服务器ip地址和主机名
10.0.68.206 yhjr-osd-mysql01-uat
10.0.68.207 yhjr-osd-mysql02-uat
1.配置/etc/hosts使各台主机能够互相解析
mysql安装参考
http://www.cnblogs.com/soysauce/p/6934483.html
其中大部分配置是为了打开group replication必须配置的，详见:
http://mysqlhighavailability.com/mysqlha/gr/doc/limitations.html
详细配置文件
[mysqld]
log_timestamps=SYSTEM
port=3306
socket=/data/mysql/run/mysql.sock
basedir=/usr/local/mysql
datadir=/data/mysql/data
tmpdir=/data/mysql/tmp
slow_query_log_file=/data/mysql/logs/slow.log
innodb_log_group_home_dir=/data/mysql/logs
innodb_data_home_dir=/data/mysql/data
innodb_data_file_path=ibdata1:1000M:autoextend
log-error=/data/mysql/logs/error.log
log-bin=/data/mysql/logs/mysql-bin
character-set-client-handshake=FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-external-locking
key_buffer_size=16M
max_allowed_packet=1M
table_open_cache=64
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
lower_case_table_names=1
init_connect='SET NAMES utf8mb4'
log_bin_trust_function_creators=TRUE
default-storage-engine=InnoDB
character_set_server=utf8mb4
expire_logs_days=14
slow-query-log=1
long_query_time=3
server-id=34176
innodb_buffer_pool_size=8000M
innodb_log_file_size=2G
innodb_log_buffer_size=4G
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
lower_case_table_names=1
gtid-mode=on
enforce-gtid-consistency=1
binlog_format=row
query_cache_size=0
tmp_table_size=256M
max_heap_table_size=256M
max_connections=1000
default-time-zone = '+8:00'
###
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
#######mgr#######
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="f06db57c-3072-11e7-80a0-005056a34175"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "10.0.68.206:6606"
loose-group_replication_group_seeds= "10.0.68.206:6606,10.0.68.207:6606"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
2.启动实例
service mysql start
登陆之后建议修改root登陆密码，通过以下语句修改:
SET SQL_LOG_BIN=0;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql';
SET SQL_LOG_BIN=1;mysql命令行下创建group replication所需要的用户:
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';
3.开启组复制
mysql命令行下执行安装插件:
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
看见插件安装成功
mysql> show plugins
接下来执行下面的语句，启动group replication:
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;执行下面的语句，验证group replication已经成功启动:
mysql> SELECT * FROM performance_schema.replication_group_members;
创建测试用的库和表:
mysql> create databaes test1;
mysql> create table test (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO test VALUES (1, 'bruce');
4.增加新成员
修改用户密码:
SET SQL_LOG_BIN=0;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql';
SET SQL_LOG_BIN=1;之后配置group replication需要的用户:
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插件:
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;mysql>START GROUP_REPLICATION;
查看同步状态：
mysql> SELECT * FROM performance_schema.replication_group_members;
group_replication_allow_local_disjoint_gtids_join要设置为on否则会报错。