MGR集群搭建及配置過(guò)程

更新時(shí)間：2022年02月16日 14:46:38 作者：苦逼運(yùn)維

MGR是MySQL數(shù)據(jù)庫(kù)未來(lái)發(fā)展的一個(gè)重要方向，本文重點(diǎn)給大家介紹MGR集群搭建過(guò)程分析，對(duì)MGR集群搭建相關(guān)知識(shí)感興趣的朋友一起看看吧

　　MGR全稱(chēng)MySQL Group Replication（Mysql組復(fù)制），是MySQL官方于2016年12月推出的一個(gè)全新的高可用與高擴(kuò)展的解決方案。MGR提供了高可用、高擴(kuò)展、高可靠的MySQL集群服務(wù)。在MGR出現(xiàn)之前，用戶(hù)常見(jiàn)的MySQL高可用方式，無(wú)論怎么變化架構(gòu)，本質(zhì)就是Master-Slave架構(gòu)。MySQL 5.7版本開(kāi)始支持無(wú)損半同步復(fù)制（lossless semi-sync replication），從而進(jìn)一步提示數(shù)據(jù)復(fù)制的強(qiáng)一致性。

　　MGR是MySQL數(shù)據(jù)庫(kù)未來(lái)發(fā)展的一個(gè)重要方向。

　　注意：根據(jù)本人測(cè)試group_replication.so插件是mysql-community-server安裝包中攜帶，如果是rpm安裝或yum安裝存放地址為/usr/lib64/mysql/plugin/目錄下，看下圖。另外在安裝5.7.16版本時(shí)是沒(méi)有這個(gè)插件，而在安裝5.7.20版本有這個(gè)插件，推測(cè)這是一個(gè)5.7.16到5.7.20之間新加的插件，個(gè)人建議安裝5.7.20以上的版本。另外大家請(qǐng)?jiān)诎惭b好mysql后查看一下是否存在這個(gè)插件。

　　如果提示group_replication.so不存在，或提示有問(wèn)題并且查看時(shí)發(fā)現(xiàn)group_replication.so不存在，請(qǐng)重點(diǎn)看一下mysql的版本。（我搜了一大圈，沒(méi)有一個(gè)人說(shuō)這個(gè)問(wèn)題。表示懷疑自己，如果我錯(cuò)了，請(qǐng)留言。）

(1).MGR的特性

　　高一致性?；谠鷱?fù)制及paxos協(xié)議的組復(fù)制技術(shù)，并以插件的方式提供，提供一致數(shù)據(jù)安全保證；

　　高容錯(cuò)性。只要不是大多數(shù)節(jié)點(diǎn)壞掉就可以繼續(xù)工作，有自動(dòng)檢測(cè)機(jī)制，當(dāng)不同節(jié)點(diǎn)產(chǎn)生資源爭(zhēng)用沖突時(shí)，不會(huì)出現(xiàn)錯(cuò)誤，按照先到者優(yōu)先原則進(jìn)行處理，并且內(nèi)置了自動(dòng)化腦裂防護(hù)機(jī)制；

　　高擴(kuò)展性。節(jié)點(diǎn)的新增和移除都是自動(dòng)的，新節(jié)點(diǎn)加入后，會(huì)自動(dòng)從其他節(jié)點(diǎn)上同步狀態(tài)，直到新節(jié)點(diǎn)和其他節(jié)點(diǎn)保持一致，如果某節(jié)點(diǎn)被移除了，其他節(jié)點(diǎn)自動(dòng)更新組信息，自動(dòng)維護(hù)新的組信息；

　　高靈活性。有單主模式和多主模式，單主模式下，會(huì)自動(dòng)選主，所有更新操作都在主上進(jìn)行；多主模式下，所有server都可以同時(shí)處理更新操作。

(2).搭建MGR的基礎(chǔ)結(jié)構(gòu)要求和使用限制（重點(diǎn)）

　　基礎(chǔ)結(jié)構(gòu)要求：1.引擎必須為innodb,因?yàn)樾枋聞?wù)支持在commit時(shí)對(duì)各節(jié)點(diǎn)進(jìn)行沖突檢查；2.每個(gè)表必須有主鍵，在進(jìn)行事務(wù)沖突檢測(cè)時(shí)需要利用主鍵值對(duì)比；3.必須開(kāi)啟binlog且為row格式；4.開(kāi)啟GTID，且主從狀態(tài)信息存于表中（--master-info-repository=TABLE 、--relay-log-info-repository=TABLE），--log-slave-updates打開(kāi)；5.一致性檢測(cè)設(shè)置--transaction-write-set-extraction=XXHASH64。

　　使用限制：1.RP和普通復(fù)制binlog校驗(yàn)不能共存，需設(shè)置--binlog-checksum=none；2.不支持gap lock(間隙鎖)，隔離級(jí)別需設(shè)置為read_committed；3.不支持對(duì)表進(jìn)行鎖操作（lock /unlock table）,不會(huì)發(fā)送到其他節(jié)點(diǎn)執(zhí)行 ,影響需要對(duì)表進(jìn)行加鎖操作的情況，列入mysqldump全表備份恢復(fù)操作；4.不支持serializable(序列化)隔離級(jí)別；5.DDL語(yǔ)句不支持原子性，不能檢測(cè)沖突，執(zhí)行后需自行校驗(yàn)是否一；6.多主模式下不支持外鍵，單主模式下支持外鍵；最多9個(gè)節(jié)點(diǎn)，超過(guò)9臺(tái)無(wú)法加入集群

(3).實(shí)驗(yàn)環(huán)境

youxi1　　192.168.1.6　　CentOS7.6　　Mysql5.7.26　　端口號(hào)3306　　server-id=1

youxi2　　192.168.1.7　　CentOS7.6　　Mysql5.7.26　　端口號(hào)3306　　server-id=2

youxi3　　192.168.1.8　　CentOS7.6　　Mysql5.7.26　　端口號(hào)3306　　server-id=3

　　另外，三臺(tái)服務(wù)器上都進(jìn)行IP的映射（如果此處不映射，請(qǐng)修改/etc/my.cnf配置文件中對(duì)應(yīng)的域名為IP地址）

[root@youxi1 ~]# vim /etc/hosts
192.168.1.6 youxi1.cn youxi1　　//長(zhǎng)域名和短域名只要映射一個(gè)即可
192.168.1.7 youxi2.cn youxi2
192.168.1.8 youxi3.cn youxi3

(1).單主模式

　　我是在空數(shù)據(jù)庫(kù)下操作，如果主數(shù)據(jù)集已存在數(shù)據(jù)，需要將主數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)出再導(dǎo)入到從數(shù)據(jù)庫(kù)。另外還需保證引擎為innodb，每個(gè)表必須存在主鍵。

1）以youxi1作為主服務(wù)器，對(duì)youxi1進(jìn)行配置

　　創(chuàng)建一個(gè)復(fù)制用的用戶(hù)

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服務(wù)ID
gtid-mode=on　　//全局事務(wù)
enforce-gtid-consistency=on　　//強(qiáng)制GTID的一致性
master-info-repository=TABLE　　//將master.info元數(shù)據(jù)保存在系統(tǒng)表中
relay-log-info-repository=TABLE　　//將relay.info元數(shù)據(jù)保存在系統(tǒng)表中
binlog-checksum=none　　//禁用二進(jìn)制日志事件校驗(yàn)
log-slave-updates=on　　//級(jí)聯(lián)復(fù)制
log-bin=binlog　　//開(kāi)啟二進(jìn)制日志記錄
binlog-format=ROW　　//以行的格式記錄
transaction-write-set-extraction=XXHASH64　　//使用哈希算法將其編碼為散列
loose-group_replication_group_name='ce9be252-2b71-11e6-b8f4-00212844f856'　　//加入的組名，可以修改，只要格式對(duì)
loose-group_replication_start_on_boot=off　　//不自動(dòng)啟用組復(fù)制集群
loose-group_replication_local_address='youxi1:33061'　　//以本機(jī)端口33061接受來(lái)自組中成員的傳入連接
loose-group_replication_group_seeds='youxi1:33061,youxi2:33062,youxi3:33063'　　//組中成員訪(fǎng)問(wèn)表
loose-group_replication_bootstrap_group=off　　//不啟用引導(dǎo)組
[root@youxi1 ~]# systemctl restart mysqld

　　注意：如果防火墻是打開(kāi)的，記得添加mysql的端口號(hào)。

[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信息，構(gòu)建組復(fù)制（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)

　　安裝組復(fù)制（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)

　　作為主服務(wù)器需要由這臺(tái)服務(wù)器開(kāi)啟引導(dǎo)，開(kāi)啟組復(fù)制（group replication）集群

mysql> set global group_replication_bootstrap_group=on;　　//開(kāi)啟組復(fù)制引導(dǎo)
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;　　//開(kāi)啟組復(fù)制
Query OK, 0 rows affected (2.24 sec)
mysql> set global group_replication_bootstrap_group=off;　　//關(guān)閉組復(fù)制引導(dǎo)
Query OK, 0 rows affected (0.00 sec)

　　查看到添加到組復(fù)制集群的服務(wù)器信息

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）對(duì)youxi2進(jìn)行配置

　　創(chuàng)建一個(gè)復(fù)制用的用戶(hù)

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

　　注意：如果防火墻是打開(kāi)的，記得添加mysql的端口號(hào)。

[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信息，構(gòu)建組復(fù)制（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)

　　安裝組復(fù)制（group replication）插件

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

　　把youxi2加到之前的組復(fù)制（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)

　　查看到添加到組復(fù)制集群的服務(wù)器信息

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即可。

　　查看一下組復(fù)制集群信息

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)

　　注意：如果防火墻是打開(kāi)的，記得添加mysql的端口號(hào)。

[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）測(cè)試

　　在youxi1上創(chuàng)建數(shù)據(jù)

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是主鍵，引擎默認(rèn)是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）那么怎么區(qū)分主從服務(wù)器

　　MGR區(qū)分主從服務(wù)器使用show variables like '%read_only%';查看read_only相關(guān)參數(shù)。如果是主服務(wù)器（youxi1），會(huì)顯示如下：

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)

　　如果是從服務(wù)器（youxi2）會(huì)顯示如下：

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）查看當(dāng)前服務(wù)器的組復(fù)制集群參數(shù)設(shè)置列表

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）如果主服務(wù)器出問(wèn)題，MGR會(huì)自動(dòng)切換主服務(wù)器

　　將youxi1的mysqld人為關(guān)閉

[root@youxi1 ~]# systemctl stop mysqld

　　之后前往youxi2和youxi3查看誰(shuí)是主服務(wù)器

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)

　　可以看到y(tǒng)ouxi3變成了主服務(wù)器，那么嘗試在youxi3中插入數(shù)據(jù)

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

　　再到y(tǒng)ouxi2中查看數(shù)據(jù)

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

(2).多主模式

1）在創(chuàng)建時(shí)就啟用多主模式

　　在創(chuàng)建時(shí)就啟用多主模式只需在修改配置文件/etc/my.cnf時(shí)多加兩行參數(shù)。其余保持不變

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

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

　　由單主改為多主時(shí)，一樣需要所有服務(wù)器配置信息增加兩行參數(shù)，為了下次開(kāi)啟就是多主模式

loose-group_replication_single_primary_mode=off　　//關(guān)閉單master模式
loose-group_replication_enforce_update_everywhere_checks=ON　　//開(kāi)啟多主一致性檢查

　　然后全部服務(wù)器停止組復(fù)制（GROUP_REPLICATION）集群，并設(shè)置參數(shù)

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

　　選擇其中一臺(tái)引導(dǎo)組復(fù)制（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)

　　剩下的開(kāi)啟組復(fù)制（GROUP_REPLICATION）即可

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

　　最后查看非引導(dǎo)組復(fù)制的服務(wù)器

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).如果宕機(jī)了

　　停掉youxi1的mysqld模擬宕機(jī)

[root@youxi1 ~]# systemctl stop mysqld

　　這時(shí)候查看youxi2和youxi3誰(shuí)是新的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上寫(xiě)入數(shù)據(jù)，這是因?yàn)樯a(chǎn)環(huán)境數(shù)據(jù)庫(kù)是一直在使用的狀態(tài)，不可能等你修復(fù)后再用

[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添加回復(fù)制組

[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';　　//這一步是為了以防萬(wàn)一，
Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;　　//將這臺(tái)服務(wù)器重新添加進(jìn)復(fù)制組
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;　　//查看是否加入復(fù)制組
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| 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;　　//查看是否同步數(shù)據(jù)，沒(méi)有同步請(qǐng)耐心等待，時(shí)間可能會(huì)長(zhǎng)點(diǎn)
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

到此這篇關(guān)于MGR集群搭建的文章就介紹到這了,更多相關(guān)MGR集群搭建內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: