MySQL MGR集群搭建的步驟

本篇內容介紹了“ MGR集群搭建的步驟”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！

1.背景

MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引進(jìn)的一個(gè)數據庫高可用與高擴展的解決方案，以插件形式提供，實(shí)現了分布式下數據的最終一致性，總結MGR特點(diǎn)如下：

* 高一致性：基于分布式paxos協(xié)議實(shí)現組復制，保證數據一致性；

* 高容錯性：自動(dòng)檢測機制，只要不是大多數節點(diǎn)都宕機就可以繼續工作，內置防腦裂保護機制；

* 高擴展性：節點(diǎn)的增加與移除會(huì )自動(dòng)更新組成員信息，新節點(diǎn)加入后，自動(dòng)從其他節點(diǎn)同步增量數據，直到與其他節點(diǎn)數據一致；

* 高靈活性：提供單主模式和多主模式，單主模式在主庫宕機后能夠自動(dòng)選主，所有寫(xiě)入都在主節點(diǎn)進(jìn)行，多主模式支持多節點(diǎn)寫(xiě)入。

2. 環(huán)境：

3臺搭建3節點(diǎn)MGR集群，MySQL版本5.7.27，操作系統版本CentOS 7.3。

* 192.168.8.71

* 192.168.8.72

* 192.168.8.73

3.安裝步驟

3.1 安裝MySQL5.7

MySQL官方版5.7.27，采用Linux Generic安裝方式，直接下載壓縮包，解壓到目錄/usr/local/mysql。

3.2 修改配置文件

編輯配置文件/etc/my.cnf，3個(gè)節點(diǎn)除了server_id、loose-group_replication_local_address、report_host 三個(gè)參數不一樣外，其他保持一致。

[mysqld]

port=3306

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

server_id=1

gtid_mode=ON

enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=binlog

log_slave_updates=ON

binlog_format=ROW

master_info_repository=TABLE

relay_log_info_repository=TABLE

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.8.71:33061"

loose-group_replication_group_seeds= "192.168.8.71:33061,192.168.8.72:33061,192.168.8.73:33061"

loose-group_replication_bootstrap_group=OFF

report_host=192.168.8.71

report_port=3306

3.3 初始化數據庫（所有節點(diǎn)執行）

略

3.4 啟動(dòng)數據庫，安裝MGR插件，設置復制賬號（所有節點(diǎn)執行）

--安裝MGR插件

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

--設置復制賬號

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

3.5 啟動(dòng)MGR單主模式

# 啟動(dòng)MGR，在主庫(192.168.8.71)上執行

mysql> SET GLOBAL group_replication_bootstrap_group=ON;

mysql> START GROUP_REPLICATION;

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

# 查看MGR組信息

mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+--------------+-------------+--------------+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+--------------+-------------+--------------+

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d401 | 192.168.8.71 |        3306 | ONLINE       |

+---------------------------+--------------------------------------+--------------+-------------+--------------+

1 row in set (0.00 sec)

# 其他節點(diǎn)加入MGR，在從庫（192.168.8.72，192.168.8.73）上執行

mysql> START GROUP_REPLICATION;

# 查看MGR組信息

mysql> SELECT channel_name,

    ->        member_id,

    ->        member_host,

    ->        member_port,

    ->        member_state,

    ->        IF(global_status.variable_name IS NOT NULL, 'PRIMARY', 'SECONDARY') AS member_role

    ->   FROM performance_schema.replication_group_members

    ->   LEFT JOIN performance_schema.global_status

    ->     ON global_status.variable_name = 'group_replication_primary_member'

    ->    AND global_status.variable_value = replication_group_members.member_id;

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+

| channel_name              | member_id                            | member_host  | member_port | member_state | member_role |

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d401 | 192.168.8.71 |        3306 | ONLINE       | PRIMARY     |

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d402 | 192.168.8.72 |        3306 | ONLINE       | SECONDARY   |

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d403 | 192.168.8.73 |        3306 | ONLINE       | SECONDARY   |

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+

3 rows in set (0.00 sec)

可以看到，3個(gè)節點(diǎn)狀態(tài)為online，并且主節點(diǎn)為192.168.8.71，只有主節點(diǎn)可以寫(xiě)入，其他節點(diǎn)只讀，MGR單主模式搭建成功

3.6 切換到多主模式

MGR切換模式需要重新啟動(dòng)組復制，因些需要在所有節點(diǎn)上先關(guān)閉組復制，設置 group_replication_single_primary_mode=OFF 等參數，再啟動(dòng)組復制。

# 停止組復制(所有節點(diǎn)執行)：

mysql> stop group_replication;

mysql> set global group_replication_single_primary_mode=OFF;

mysql> set global group_replication_enforce_update_everywhere_checks=ON;

# 隨便選擇某個(gè)節點(diǎn)執行

mysql> SET GLOBAL group_replication_bootstrap_group=ON;

mysql> START GROUP_REPLICATION;

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

# 其他節點(diǎn)執行

mysql> START GROUP_REPLICATION;

# 查看組信息，所有節點(diǎn)的 MEMBER_ROLE 都為 PRIMARY

mysql> SELECT channel_name,

    ->        member_id,

    ->        member_host,

    ->        member_port,

    ->        member_state,

    ->        IF(global_status.variable_name IS NOT NULL, 'PRIMARY', 'SECONDARY') AS member_role

    ->   FROM performance_schema.replication_group_members

    ->   LEFT JOIN performance_schema.global_status

    ->     ON global_status.variable_name = 'group_replication_primary_member'

    ->    AND global_status.variable_value = replication_group_members.member_id;

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+

| channel_name              | member_id                            | member_host  | member_port | member_state | member_role |

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d401 | 192.168.8.71 |        3306 | ONLINE       | PRIMARY     |

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d402 | 192.168.8.72 |        3306 | ONLINE       | PRIMARY     |

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d403 | 192.168.8.73 |        3306 | ONLINE       | PRIMARY     |

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+

3 rows in set (0.00 sec)

可以看到所有節點(diǎn)狀態(tài)都是online，角色都是PRIMARY，MGR多主模式搭建成功。

3.7 切回單主模式

# 所有節點(diǎn)執行

mysql> stop group_replication;

mysql> set global group_replication_enforce_update_everywhere_checks=OFF;

mysql> set global group_replication_single_primary_mode=ON;

# 主節點(diǎn)（192.168.8.71）執行

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;

# 從節點(diǎn)（192.168.8.72、192.168.8.73）執行

START GROUP_REPLICATION;

# 查看MGR組信息

mysql> SELECT channel_name,

    ->        member_id,

    ->        member_host,

    ->        member_port,

    ->        member_state,

    ->        IF(global_status.variable_name IS NOT NULL, 'PRIMARY', 'SECONDARY') AS member_role

    ->   FROM performance_schema.replication_group_members

    ->   LEFT JOIN performance_schema.global_status

    ->     ON global_status.variable_name = 'group_replication_primary_member'

    ->    AND global_status.variable_value = replication_group_members.member_id;

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+

| channel_name              | member_id                            | member_host  | member_port | member_state | member_role |

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d401 | 192.168.8.71 |        3306 | ONLINE       | PRIMARY     |

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d402 | 192.168.8.72 |        3306 | ONLINE       | SECONDARY   |

| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d403 | 192.168.8.73 |        3306 | ONLINE       | SECONDARY   |

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+