MGR（MySQL group replication）是MySQL官方在2016年12月退出的一个全新的高可用和高扩展的解决方案。MySQL组提供了高可用、高扩展、高可靠的MySQL集群服务。
MGR基础结构要求：
引擎必须为innodb，因为需要事务支持在commit时对各节点进行冲突检查；
每个表必须有主键，在进行事务冲突检查时需要利用主键值对比；
必须开启binlog且为row格式；
开启GTID，且主从状态信息存于表中（--master-info-repository=TABLE、--relay-log-info-repository=TABLE），--log-slave-updates打开；
一致性检测设置--transaction-write-set-extraction=XXHASH64。
环境准备
数据库服务器规划：
IP:192.168.10.21 server1 mysql5.7 端口号3306
IP:192.168.10.22 server2 mysql5.7 端口号3306
IP:192.168.10.23 server3 mysql5.7 端口号3306
设置hostname和IP映射
在三台数据库服务器上都设置
cat >> /etc/hosts<<EOF 192.168.10.21 server1 192.168.10.22 server2 192.168.10.23 server3 EOF
创建复制环境
配置/etc/my.cnf
[root@server1 ~]# vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-name-resolve [mysqld] # Group Replication server_id = 1 #服务ID gtid_mode = ON #全局事务 enforce_gtid_consistency = ON #强制GTID的一致性 master_info_repository = TABLE #将master.info元数据保存在系统表中 relay_log_info_repository = TABLE #将relay.info元数据保存在系统表中 binlog_checksum = NONE #禁用二进制日志事件校验 log_slave_updates = ON #级联复制 log_bin = binlog #开启二进制日志记录 binlog_format= ROW #以行的格式记录 transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列 loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' #加入的组名 loose-group_replication_start_on_boot = off #不自动启用组复制集群 loose-group_replication_local_address = 'server1:33061' #以本机端口33061接受来自组中成员的传入连接 loose-group_replication_group_seeds =' server1:33061, server2:33062, server3:33063' #组中成员访问表 loose-group_replication_bootstrap_group = off #不启用引导组 [root@server1 ~]# systemctl restart mysqld
在服务器server1上建立复制账号：
[root@server1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> grant replication slave on *.* to repl@'192.168.10.%' identified by '#自己的密码'; 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',master_password='#自己的密码' for channel 'group_replication_recovery';#构建group replication集群 Query OK, 0 rows affected, 2 warnings (0.01 sec) #在mysql服务器server11上安装group replication插件 mysql> install PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.06 sec) #查看group replication组件 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 | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | 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 | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.01 sec)
启动服务器server1上MySQL的group replication
-- 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置。 mysql> set global group_replication_bootstrap_group=ON; -- 作为首个节点启动MGR集群 mysql> start group_replication; mysql> set global group_replication_bootstrap_group=OFF; 补充: set global group_replication_allow_local_disjoint_gtids_join=ON; set global log_bin_trust_function_creators=1; show variables like '%func%'; #如果是单主模式，此实例即是可读可写实例。之所以要先开启group_replication_bootstrap_group,然后关闭group_replication_bootstrap_group,是因为start 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 (3.77 sec) mysql> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec)
查看MGR的状态
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | cf7b706e-64ca-11ed-a114-000c293661ae | server1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 1 row in set (0.00 sec) #测试服务器server1上的MySQL mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> create table table666 (id int primary key,name varchar(20)); #注意创建主键 Query OK, 0 rows affected (0.32 sec) mysql> insert into table666 values (1,'weme'); Query OK, 1 row affected (0.16 sec) mysql> select * from table666; +----+------+ | id | name | +----+------+ | 1 | weme | +----+------+ 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.24-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 1 | 150 | | | binlog.000001 | 150 | Stop | 1 | 169 | | +---------------+-----+----------------+-----------+-------------+---------------------------------------+ 3 rows in set (0.00 sec)
复制组添加新实例server2
[root@server2 ~]# vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-name-resolve # Group Replication server_id = 2 #注意服务ID不一样 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 transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot = off loose-group_replication_local_address = 'server2:33062' loose-group_replication_group_seeds = 'server1:33061, server2:33062, server3:33063' loose-group_replication_bootstrap_group = off #重启MySQL服务 [root@server2 ~]# systemctl restart mysqld
用户授权
[root@server2 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> grant replication slave on *.* to repl@'192.168.10.%' identified by '#自己的密码'; 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',master_password='#自己的密码' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.30 sec) mysql> install PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.49 sec) 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 | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | 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 | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec)
在server2上操作，把实例添加到之前的复制组
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> start group_replication; Query OK, 0 rows affected, 1 warning (6.40 sec) mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 880e2ed6-6b00-11ed-86ff-00505624d11a | server2 | 3306 | ONLINE | | group_replication_applier | c3f05888-6b00-11ed-8d44-000c293661ae | server1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 2 rows in set (0.00 sec)
在新添加的server2上查看数据库发现test库和table666表已经同步
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 | +----------------+ | table666 | +----------------+ 1 row in set (0.00 sec) mysql> select * from table666; +----+------+ | id | name | +----+------+ | 1 | weme | +----+------+ 1 row in set (0.00 sec)
以同样的方法添加server3
修改/etc/my.cnf配置文件，方法和之前一样
[root@server3 ~]# vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-name-resolve # Group Replication server_id = 3 #注意服务ID不一样 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 transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' loose-group_replication_start_on_boot = off loose-group_replication_local_address = 'server3:33063' loose-group_replication_group_seeds ='server1:33061, server2:33062, server3:33063' loose-group_replication_bootstrap_group = off [root@server3 ~]# systemctl restart mysqld
用户授权
[root@server3 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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.01 sec) mysql> grant replication slave on *.* to repl@'192.168.10.%' identified by '#自己的密码'; 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',master_password='#自己的密码' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> install PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.05 sec) 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 | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | 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 | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec)
在server3操作，把实例添加到之前的复制组
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> start group_replication; Query OK, 0 rows affected, 1 warning (3.29 sec) mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 880e2ed6-6b00-11ed-86ff-00505624d11a | server2 | 3306 | ONLINE | | group_replication_applier | c3f05888-6b00-11ed-8d44-000c293661ae | server1 | 3306 | ONLINE | | group_replication_applier | e1a25d5e-6b00-11ed-b9b3-005056305547 | server3 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)
在server1上查看复制组状态
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 880e2ed6-6b00-11ed-86ff-00505624d11a | server2 | 3306 | ONLINE | | group_replication_applier | c3f05888-6b00-11ed-8d44-000c293661ae | server1 | 3306 | ONLINE | | group_replication_applier | e1a25d5e-6b00-11ed-b9b3-005056305547 | server3 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)
在新添加的server3上查看数据库发现test库和table666表已经同步
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.01 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 | +----------------+ | table666 | +----------------+ 1 row in set (0.00 sec) mysql> select * from table666; +----+------+ | id | name | +----+------+ | 1 | weme | +----+------+ 1 row in set (0.00 sec)
补充：
#server1 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> insert into table666 values(666,'weme666'); Query OK, 1 row affected (0.00 sec) mysql> select * from table666; +-----+---------+ | id | name | +-----+---------+ | 1 | weme | | 666 | weme666 | +-----+---------+ 2 rows in set (0.00 sec) #server2 mysql> select * from test.table666; +-----+---------+ | id | name | +-----+---------+ | 1 | weme | | 666 | weme666 | +-----+---------+ 2 rows in set (0.00 sec) #server3 mysql> select * from test.table666; +-----+---------+ | id | name | +-----+---------+ | 1 | weme | | 666 | weme666 | +-----+---------+ 2 rows in set (0.00 sec) #server1上查看复制组状态 mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 880e2ed6-6b00-11ed-86ff-00505624d11a | server2 | 3306 | ONLINE | | group_replication_applier | c3f05888-6b00-11ed-8d44-000c293661ae | server1 | 3306 | ONLINE | | group_replication_applier | e1a25d5e-6b00-11ed-b9b3-005056305547 | server3 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)
至此单master节点MySQL5.7MGR集群搭建完毕
942
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
