MGR集群搭建及配置過程

  MGR全稱MySQL Group Replication(Mysql組復制),是MySQL官方於2016年12月推出的一個全新的高可用與高擴展的解決方案。MGR提供瞭高可用、高擴展、高可靠的MySQL集群服務。在MGR出現之前,用戶常見的MySQL高可用方式,無論怎麼變化架構,本質就是Master-Slave架構。MySQL 5.7版本開始支持無損半同步復制(lossless semi-sync replication),從而進一步提示數據復制的強一致性。

  MGR是MySQL數據庫未來發展的一個重要方向。

  註意:根據本人測試group_replication.so插件是mysql-community-server安裝包中攜帶,如果是rpm安裝或yum安裝存放地址為/usr/lib64/mysql/plugin/目錄下,看下圖。另外在安裝5.7.16版本時是沒有這個插件,而在安裝5.7.20版本有這個插件,推測這是一個5.7.16到5.7.20之間新加的插件,個人建議安裝5.7.20以上的版本。另外大傢請在安裝好mysql後查看一下是否存在這個插件。

  如果提示group_replication.so不存在,或提示有問題並且查看時發現group_replication.so不存在,請重點看一下mysql的版本。(我搜瞭一大圈,沒有一個人說這個問題。表示懷疑自己,如果我錯瞭,請留言。)

(1).MGR的特性

  高一致性。基於原生復制及paxos協議的組復制技術,並以插件的方式提供,提供一致數據安全保證;

  高容錯性。隻要不是大多數節點壞掉就可以繼續工作,有自動檢測機制,當不同節點產生資源爭用沖突時,不會出現錯誤,按照先到者優先原則進行處理,並且內置瞭自動化腦裂防護機制;

  高擴展性。節點的新增和移除都是自動的,新節點加入後,會自動從其他節點上同步狀態,直到新節點和其他節點保持一致,如果某節點被移除瞭,其他節點自動更新組信息,自動維護新的組信息;

  高靈活性。有單主模式和多主模式,單主模式下,會自動選主,所有更新操作都在主上進行;多主模式下,所有server都可以同時處理更新操作。

(2).搭建MGR的基礎結構要求和使用限制(重點)

  基礎結構要求:1.引擎必須為innodb,因為需事務支持在commit時對各節點進行沖突檢查;2.每個表必須有主鍵,在進行事務沖突檢測時需要利用主鍵值對比;3.必須開啟binlog且為row格式;4.開啟GTID,且主從狀態信息存於表中(–master-info-repository=TABLE 、–relay-log-info-repository=TABLE),–log-slave-updates打開;5.一致性檢測設置–transaction-write-set-extraction=XXHASH64。

  使用限制:1.RP和普通復制binlog校驗不能共存,需設置–binlog-checksum=none;2.不支持gap lock(間隙鎖),隔離級別需設置為read_committed;3.不支持對表進行鎖操作(lock /unlock table),不會發送到其他節點執行 ,影響需要對表進行加鎖操作的情況,列入mysqldump全表備份恢復操作;4.不支持serializable(序列化)隔離級別;5.DDL語句不支持原子性,不能檢測沖突,執行後需自行校驗是否一;6.多主模式下不支持外鍵,單主模式下支持外鍵;最多9個節點,超過9臺無法加入集群

(3).實驗環境

youxi1  192.168.1.6  CentOS7.6  Mysql5.7.26  端口號3306  server-id=1

youxi2  192.168.1.7  CentOS7.6  Mysql5.7.26  端口號3306  server-id=2

youxi3  192.168.1.8  CentOS7.6  Mysql5.7.26  端口號3306  server-id=3

  另外,三臺服務器上都進行IP的映射(如果此處不映射,請修改/etc/my.cnf配置文件中對應的域名為IP地址)

[root@youxi1 ~]# vim /etc/hosts
192.168.1.6 youxi1.cn youxi1  //長域名和短域名隻要映射一個即可
192.168.1.7 youxi2.cn youxi2
192.168.1.8 youxi3.cn youxi3

(1).單主模式

  我是在空數據庫下操作,如果主數據集已存在數據,需要將主數據庫的數據導出再導入到從數據庫。另外還需保證引擎為innodb,每個表必須存在主鍵。

1)以youxi1作為主服務器,對youxi1進行配置

  創建一個復制用的用戶

mysql> grant replication slave on *.* to 'repl'@'192.168.1.%' identified by '12345678';
Query OK, 0 rows affected, 1 warning (0.01 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

  修改配置文件,加入到[mysqld]模塊下。然後重啟mysqld

[root@youxi1 ~]# vim /etc/my.cnf
server-id=1  //Mysql服務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='youxi1:33061'  //以本機端口33061接受來自組中成員的傳入連接
loose-group_replication_group_seeds='youxi1:33061,youxi2:33062,youxi3:33063'  //組中成員訪問表
loose-group_replication_bootstrap_group=off  //不啟用引導組
[root@youxi1 ~]# systemctl restart mysqld

  註意:如果防火墻是打開的,記得添加mysql的端口號。

[root@youxi1 ~]# firewall-cmd --permanent --zone=public --add-port={3306,33061}/tcp
success
[root@youxi1 ~]# firewall-cmd --reload
success
[root@youxi1 ~]# firewall-cmd --zone=public --list-ports
3306/tcp 33061/tcp

  修改master信息,構建組復制(group replication)集群信息

mysql> change master to master_user='repl',master_password='12345678' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

  安裝組復制(group replication)插件,並查看組件信息

mysql> install PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.21 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          | DISABLED | VALIDATE PASSWORD  | validate_password.so | GPL     |
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)

  作為主服務器需要由這臺服務器開啟引導,開啟組復制(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 (2.24 sec)
mysql> set global group_replication_bootstrap_group=off;  //關閉組復制引導
Query OK, 0 rows affected (0.00 sec)

  查看到添加到組復制集群的服務器信息

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | c9e3662b-9020-11e9-94aa-000c29721e89 | youxi1      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

2)對youxi2進行配置

  創建一個復制用的用戶

mysql> grant replication slave on *.* to 'repl'@'192.168.1.%' identified by '12345678';
Query OK, 0 rows affected, 1 warning (0.01 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

  修改配置文件,加入到[mysqld]模塊下。然後重啟mysqld

[root@youxi2 ~]# vim /etc/my.cnf
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
 
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='youxi2:33062'  //修改
loose-group_replication_group_seeds='youxi1:33061,youxi2:33062,youxi3:33063'
loose-group_replication_bootstrap_group=off
[root@youxi2 ~]# systemctl restart mysqld

  註意:如果防火墻是打開的,記得添加mysql的端口號。

[root@youxi2 ~]# firewall-cmd --permanent --zone=public --add-port={3306,33062}/tcp
success
[root@youxi2 ~]# firewall-cmd --reload
success
[root@youxi2 ~]# firewall-cmd --zone=public --list-ports
3306/tcp 33062/tcp

  修改master信息,構建組復制(group replication)集群信息

mysql> change master to master_user='repl',master_password='12345678' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

  安裝組復制(group replication)插件

mysql> install PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.35 sec)

  把youxi2加到之前的組復制(group replication)

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 (5.92 sec)

  查看到添加到組復制集群的服務器信息

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8fbf8b41-84fe-11e9-897e-000c29f27e52 | youxi2      |        3306 | ONLINE       |
| group_replication_applier | c9e3662b-9020-11e9-94aa-000c29721e89 | youxi1      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

3)youxi3的配置與youxi2的配置幾乎一樣,隻需在/etc/my.cnf修改server-id和loose-group_replication_local_address即可。

  查看一下組復制集群信息

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 74df3399-91b8-11e9-be5f-000c299fdf40 | youxi3      |        3306 | ONLINE       |
| group_replication_applier | 8fbf8b41-84fe-11e9-897e-000c29f27e52 | youxi2      |        3306 | ONLINE       |
| group_replication_applier | c9e3662b-9020-11e9-94aa-000c29721e89 | youxi1      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.01 sec)

  註意:如果防火墻是打開的,記得添加mysql的端口號。

[root@youxi3 ~]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
success
[root@youxi3 ~]# firewall-cmd --permanent --zone=public --add-port=33063/tcp
success
[root@youxi3 ~]# firewall-cmd --reload
success
[root@youxi3 ~]# firewall-cmd --zone=public --list-ports
3306/tcp 33063/tcp

4)測試

  在youxi1上創建數據

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
 
mysql> use test_db;
Database changed
mysql> create table user_tb(id int key,name varchar(20));  //id是主鍵,引擎默認是innodb
Query OK, 0 rows affected (0.02 sec)
mysql> insert into user_tb values(1,'zhangsan');
Query OK, 1 row affected (0.08 sec)

  在youxi2上查看

mysql> select * from test_db.user_tb;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

  在youxi3上查看

mysql> select * from test_db.user_tb;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

5)那麼怎麼區分主從服務器

  MGR區分主從服務器使用show variables like '%read_only%';查看read_only相關參數。如果是主服務器(youxi1),會顯示如下:

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

  如果是從服務器(youxi2)會顯示如下:

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)

6)查看當前服務器的組復制集群參數設置列表

mysql> show variables like 'group_replication%';
+----------------------------------------------------+----------------------------------------+
| Variable_name                                      | Value                                  |
+----------------------------------------------------+----------------------------------------+
| group_replication_allow_local_disjoint_gtids_join  | OFF                                    |
| group_replication_allow_local_lower_version_join   | OFF                                    |
| group_replication_auto_increment_increment         | 7                                      |
| group_replication_bootstrap_group                  | OFF                                    |
| group_replication_components_stop_timeout          | 31536000                               |
| group_replication_compression_threshold            | 1000000                                |
| group_replication_enforce_update_everywhere_checks | OFF                                    |
| group_replication_exit_state_action                | READ_ONLY                              |
| group_replication_flow_control_applier_threshold   | 25000                                  |
| group_replication_flow_control_certifier_threshold | 25000                                  |
| group_replication_flow_control_mode                | QUOTA                                  |
| group_replication_force_members                    |                                        |
| group_replication_group_name                       | ce9be252-2b71-11e6-b8f4-00212844f856   |
| group_replication_group_seeds                      | youxi1:33061,youxi2:33062,youxi3:33063 |
| group_replication_gtid_assignment_block_size       | 1000000                                |
| group_replication_ip_whitelist                     | AUTOMATIC                              |
| group_replication_local_address                    | youxi1:33061                           |
| group_replication_member_weight                    | 50                                     |
| group_replication_poll_spin_loops                  | 0                                      |
| group_replication_recovery_complete_at             | TRANSACTIONS_APPLIED                   |
| group_replication_recovery_reconnect_interval      | 60                                     |
| group_replication_recovery_retry_count             | 10                                     |
| group_replication_recovery_ssl_ca                  |                                        |
| group_replication_recovery_ssl_capath              |                                        |
| group_replication_recovery_ssl_cert                |                                        |
| group_replication_recovery_ssl_cipher              |                                        |
| group_replication_recovery_ssl_crl                 |                                        |
| group_replication_recovery_ssl_crlpath             |                                        |
| group_replication_recovery_ssl_key                 |                                        |
| group_replication_recovery_ssl_verify_server_cert  | OFF                                    |
| group_replication_recovery_use_ssl                 | OFF                                    |
| group_replication_single_primary_mode              | ON                                     |
| group_replication_ssl_mode                         | DISABLED                               |
| group_replication_start_on_boot                    | OFF                                    |
| group_replication_transaction_size_limit           | 0                                      |
| group_replication_unreachable_majority_timeout     | 0                                      |
+----------------------------------------------------+----------------------------------------+
36 rows in set (0.00 sec)

7)如果主服務器出問題,MGR會自動切換主服務器

  將youxi1的mysqld人為關閉

[root@youxi1 ~]# systemctl stop mysqld

  之後前往youxi2和youxi3查看誰是主服務器

mysql> show variables like '%read_only%';  //這是youxi2的
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)
 
mysql> show variables like '%read_only%';  //這是youxi3的
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.02 sec)

  可以看到youxi3變成瞭主服務器,那麼嘗試在youxi3中插入數據

mysql> insert into test_db.user_tb values(2,'lisi');
Query OK, 1 row affected (0.14 sec)

  再到youxi2中查看數據

mysql> select * from test_db.user_tb;   
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

(2).多主模式

1)在創建時就啟用多主模式

  在創建時就啟用多主模式隻需在修改配置文件/etc/my.cnf時多加兩行參數。其餘保持不變

loose-group_replication_single_primary_mode=off  //關閉單master模式
loose-group_replication_enforce_update_everywhere_checks=ON  //多主一致性檢查

2)由單主模式改為多主模式

  由單主改為多主時,一樣需要所有服務器配置信息增加兩行參數,為瞭下次開啟就是多主模式

loose-group_replication_single_primary_mode=off  //關閉單master模式
loose-group_replication_enforce_update_everywhere_checks=ON  //開啟多主一致性檢查

  然後全部服務器停止組復制(GROUP_REPLICATION)集群,並設置參數

mysql> stop GROUP_REPLICATION;
Query OK, 0 rows affected (9.51 sec)
mysql> set global group_replication_single_primary_mode=off;  //關閉單主模式
Query OK, 0 rows affected (0.00 sec)
mysql> set global group_replication_enforce_update_everywhere_checks=ON;  //開啟多主一致性檢查
Query OK, 0 rows affected (0.00 sec)

  選擇其中一臺引導組復制(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 (2.13 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.01 sec)

  剩下的開啟組復制(GROUP_REPLICATION)即可

mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (6.05 sec)

  最後查看非引導組復制的服務器

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 74df3399-91b8-11e9-be5f-000c299fdf40 | youxi3      |        3306 | ONLINE       |
| group_replication_applier | 8fbf8b41-84fe-11e9-897e-000c29f27e52 | youxi2      |        3306 | ONLINE       |
| group_replication_applier | c9e3662b-9020-11e9-94aa-000c29721e89 | youxi1      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
 
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)

(3).如果宕機瞭

  停掉youxi1的mysqld模擬宕機

[root@youxi1 ~]# systemctl stop mysqld

  這時候查看youxi2和youxi3誰是新的master

[root@youxi2 ~]# mysql -uroot -p123456
mysql> show variables like '%read_only%';  //這是youxi2的
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)
 
[root@youxi3 ~]# mysql -uroot -p123456
mysql> show variables like '%read_only%';  //這是youxi3的,可以看出youxi3成為瞭新的master
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

  在youxi3上寫入數據,這是因為生產環境數據庫是一直在使用的狀態,不可能等你修復後再用

[root@youxi3 ~]# mysql -uroot -p123456
mysql> insert into test_db.user_tb values(2,'lisi');
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from test_db.user_tb;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

  將修好的youxi1添加回復制組

[root@youxi1 ~]# systemctl start mysqld
[root@youxi1 ~]# mysql -uroot -p123456
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> change master to master_user='repl',master_password='12345678' for channel 'group_replication_recovery';  //這一步是為瞭以防萬一,
Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;  //將這臺服務器重新添加進復制組
Query OK, 0 rows affected (0.00 sec)
 
mysql> start group_replication;
Query OK, 0 rows affected (3.36 sec)
 
mysql> select * from performance_schema.replication_group_members;  //查看是否加入復制組
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 07c6f159-964f-11e9-b2c7-000c2934a723 | youxi3      |        3306 | ONLINE       |
| group_replication_applier | 8247f048-962f-11e9-a210-000c2975fa5d | youxi2      |        3306 | ONLINE       |
| group_replication_applier | 8b703193-962a-11e9-b582-000c29e6d627 | youxi1      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
 
mysql> select * from test_db.user_tb;  //查看是否同步數據,沒有同步請耐心等待,時間可能會長點
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

到此這篇關於MGR集群搭建的文章就介紹到這瞭,更多相關MGR集群搭建內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: