導讀
概要：原文包含PXC、MGR、MGC集群之新建、備份、恢復操作步驟詳解，因篇幅較長，本文僅節取MGR部分內容。
若想了解完整文章，可至文末獲取
對於PXC、MGC、MGR三種MySQL的集群複製架構技術，其實在更早之前，我做過一個視頻版的 "PXC、MGC & MGR 原理與實踐對比" 系列課程，但有部分同學覺得裡邊的內容缺少詳細操作步驟。所以，今天我就將PXC、MGR、MGC集群之新建、備份、恢復操作的詳細步驟整理出來分享給大家，希望能幫助大家更好地玩耍PXC、MGC、MGR
一、原文目錄
1、PXC1.1. 集群初始化1.1.1. 第一個節點(init)1.1.2. 第二個節點(sst)1.1.3. 第三個節點(backup recovery)1.2. 集群新增節點1.2.1. 使用備份集恢復1.2.2. 全量SST加入集群1.3. 單節點crash的恢復1.4. 多數節點crash的恢復1.4.1. crash節點數據未丟失且可以原地恢復1.4.2. crash節點數據丟失1.5. 整個集群crash的恢復1.5.1. 使用備份恢復1.5.2. 使用crash節點的datadir數據捲進行恢復2、MGC3、MGR 3.1. 集群初始化 3.1.1. 第一個節點(init)3.1.2. 第二個節點(全量複製)3.1.3. 第三個節點(backup recovery) 3.2. 集群新增節點3.2.1. 使用備份集恢復3.2.2. 全量複製加入集群 3.3. 單節點crash的恢復 3.4. 多數節點crash的恢復3.4.1. crash節點數據未丟失且可以原地恢復3.4.2. crash節點數據丟失 3.5. 整個集群crash的恢復3.5.1. 使用備份恢復3.5.2. 使用crash節點的datadir數據捲進行恢復
二、背景說明
服務器環境
- kvm ip：10.10.30.162/163/164
- CPU：8 vcpus
- 內存：16G
- 磁盤：data 100G flash卡，binlog 100G flash卡
數據庫版本
- MGC：MariaDB 10.2.12
- MGR：MySQL 5.7.21
- PXC：Percona-Xtradb-Cluster 5.7.21
sysbench版本
- sysbench 1.0.7
- 造數量：8個表，單表500W數據量
PS
- PXC：為Percona Xtradb Cluster的縮寫
- MGR：為MySQL Group Replication的縮寫
- MGC：為MariaDB Galera Cluster的縮寫
三、原文節選：
3、MGR
3.1. 集群初始化
3.1.1. 第一個節點(init)
- 數據庫初始化（略）
- 關鍵配置
<code>server_id=3306162sync_binlog=10000innodb_flush_log_at_trx_commit = 2binlog-checksum=NONEinnodb_support_xa=OFFauto_increment_increment=3auto_increment_offset=1binlog_row_image=fulltransaction-write-set-extraction=XXHASH64loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_single_primary_mode=OFFloose-group_replication_enforce_update_everywhere_checks=ONloose-group_replication_start_on_boot=onloose-group_replication_ip_whitelist='0.0.0.0/0'loose-group_replication_local_address='10.10.30.162:24901'loose-group_replication_group_seeds='10.10.30.162:24901,10.10.30.163:24901,10.10.30.164:24901'loose-group_replication_bootstrap_group=OFFreport_host='node1'/<code>
- 配置hosts解析記錄(有DNS即系服務的環境無需配置)
<code>[root@localhost ~]# cat /etc/hosts ......10.10.30.162 node1 mysql110.10.30.163 node2 mysql210.10.30.164 node3 mysql3/<code>
- 啟動節點(非集群方式)
<code>mysqld_safe --defaults-file=/etc/my.cnf &
/<code>
- 創建複製和備份用戶
<code>SET SQL_LOG_BIN=0;CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'xtrabackuppass';GRANT SELECT, LOCK TABLES, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD, CREATE TABLESPACE, PROCESS ON *.* TO 'xtrabackup'@'localhost';grant DROP, CREATE, INSERT on mysql.ibbackup_binlog_marker to 'xtrabackup'@'localhost';CREATE USER repl@'%' IDENTIFIED BY 'password';GRANT REPLICATION SLAVE ON *.* TO repl@'%' ;SET SQL_LOG_BIN=1;FLUSH PRIVILEGES; /<code>
- 安裝插件並配置group replication
<code>INSTALL PLUGIN group_replication SONAME 'group_replication.so';CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';FLUSH PRIVILEGES ;/<code>
- 啟動集群組複製插件
<code>set global group_replication_bootstrap_group=ON;start group_replication;set global group_replication_bootstrap_group=OFF;/<code>
- 檢查集群組複製節點狀態
<code>#查看狀態是否為onlineroot@localhost : (none) 03:39:20> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 2d623f55-2111-11e8-9cc3-0025905b06da | node1 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+1 row in set (0.00 sec)/<code>
PS：MGR更多的配置參數參考模板鏈接如下：
https://github.com/Percona-Lab/percona-docker/blob/master/pgr-57/node.cnf
3.1.2. 第二個節點(全量複製)
- 數據庫初始化（略）
- 關鍵配置
<code>server_id=3306163sync_binlog=10000innodb_flush_log_at_trx_commit = 2innodb_support_xa=OFFbinlog-checksum=NONEauto_increment_increment=3auto_increment_offset=2binlog_row_image=fulltransaction-write-set-extraction=XXHASH64loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_single_primary_mode=OFFloose-group_replication_enforce_update_everywhere_checks=ONloose-group_replication_start_on_boot=onloose-group_replication_ip_whitelist='0.0.0.0/0'loose-group_replication_local_address='10.10.30.163:24901'loose-group_replication_group_seeds='10.10.30.162:24901,10.10.30.163:24901,10.10.30.164:24901'loose-group_replication_bootstrap_group=OFFreport_host='node2'/<code>
- 配置hosts解析記錄(有DNS即系服務的環境無需配置)
<code>[root@localhost ~]# cat /etc/hosts ......10.10.30.162 node1 mysql110.10.30.163 node2 mysql210.10.30.164 node3 mysql3/<code>
- 啟動節點(非集群方式)
<code>mysqld_safe --defaults-file=/etc/my.cnf &
/<code>
- 創建複製和備份用戶
<code>SET SQL_LOG_BIN=0;CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'xtrabackuppass';GRANT SELECT, LOCK TABLES, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD, CREATE TABLESPACE, PROCESS ON *.* TO 'xtrabackup'@'localhost';grant DROP, CREATE, INSERT on mysql.ibbackup_binlog_marker to 'xtrabackup'@'localhost';CREATE USER repl@'%' IDENTIFIED BY 'password';GRANT REPLICATION SLAVE ON *.* TO repl@'%' ;SET SQL_LOG_BIN=1;FLUSH PRIVILEGES; /<code>
- 安裝插件並配置group replication
<code>INSTALL PLUGIN group_replication SONAME 'group_replication.so';CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';FLUSH PRIVILEGES ;/<code>
- 啟動集群組複製插件
<code>reset master;start group_replication;/<code>
- 檢查集群組複製節點狀態
<code>#查看狀態是否為onlineroot@localhost : (none) 03:45:24> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 2d623f55-2111-11e8-9cc3-0025905b06da | node1 | 3306 | ONLINE || group_replication_applier | f64f9fb6-2da4-11e8-96bd-525400c33752 | node2 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+2 rows in set (0.00 sec)/<code>
- 查看節點複製延遲與應用情況
<code>root@localhost : (none) 06:48:32> select * from performance_schema.replication_group_member_stats where MEMBER_ID=@@server_uuid\\G;*************************** 1. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 15218000786938271:11 MEMBER_ID: 0a1e8349-2e87-11e8-8c9f-525400bdd1f2 COUNT_TRANSACTIONS_IN_QUEUE: 287640 # 該字段顯示當前接收到的relay log與當前應用到的relay log之間的事務差異 COUNT_TRANSACTIONS_CHECKED: 0 COUNT_CONFLICTS_DETECTED: 0COUNT_TRANSACTIONS_ROWS_VALIDATING: 0TRANSACTIONS_COMMITTED_ALL_MEMBERS: 2d623f55-2111-11e8-9cc3-0025905b06da:1-2,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-13779 # 該字段顯示當前節點應用到的日誌對應的GTID LAST_CONFLICT_FREE_TRANSACTION: 1 row in set (0.02 sec)/<code>
- PS：該方式必須保證集群中已有節點的binlog未執行過清理，一旦有清理，新加節點無法通過全量binlog複製來加入集群
3.1.3. 第三個節點(backup recovery)
- 首先，使用sysbench製造一些測試數據，然後，使用sysbench持續對集群發起oltp壓力
<code># 創建數據庫root@localhost : (none) 01:44:33> create database sbtest;Query OK, 1 row affected (0.00 sec)# 使用sysbench造數sysbench --db-driver=mysql --time=180 --threads=8 --report-interval=1 --mysql-socket=/home/mysql/data/mysqldata1/sock/mysql.sock --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=8 --table-size=5000000 oltp_read_write --db-ps-mode=disable prepare# 使用sysbench加壓sysbench --db-driver=mysql --time=99999 --threads=8 --report-interval=1 --mysql-socket=/home/mysql/data/mysqldata1/sock/mysql.sock --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=8 --table-size=5000000 oltp_read_write --db-ps-mode=disable run/<code>
- 關鍵配置
<code>server_id=3306164sync_binlog=10000innodb_flush_log_at_trx_commit = 2innodb_support_xa=OFFbinlog-checksum=NONEauto_increment_increment=3auto_increment_offset=3binlog_row_image=fulltransaction-write-set-extraction=XXHASH64loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_single_primary_mode=OFFloose-group_replication_enforce_update_everywhere_checks=ONloose-group_replication_start_on_boot=onloose-group_replication_ip_whitelist='0.0.0.0/0'loose-group_replication_local_address='10.10.30.164:24901'loose-group_replication_group_seeds='10.10.30.162:24901,10.10.30.163:24901,10.10.30.164:24901'loose-group_replication_bootstrap_group=OFFreport_host='node3'/<code>
- 配置hosts解析記錄(有DNS即系服務的環境無需配置)
<code>[root@localhost ~]# cat /etc/hosts ......10.10.30.162 node1 mysql110.10.30.163 node2 mysql210.10.30.164 node3 mysql3/<code>
- 在第二個節點上進行備份，並把備份傳輸到第三個節點上
<code>innobackupex --defaults-file=/etc/my.cnf --slave-info \\--user=xtrabackup --password=xtrabackuppass --no-timestamp \\--stream=tar ./ | ssh [email protected] "cat - > /archive/backup/backup_`date +%Y%m%d`.tar"/<code>
- 使用備份進行恢復
<code># innobackupex執行apply-log並move-back備份數據到datadir下[root@localhost backup]# tar xvf backup_20180322.tar[root@localhost backup]# innobackupex --apply-log ./[root@localhost backup]# rm -rf /data/mysqldata1/{undo,innodb_ts,innodb_log,mydata,binlog,relaylog,binlog,slowlog,tmpdir}/*[root@localhost backup]# innobackupex --defaults-file=/etc/my.cnf --move-back ./....../<code>- 正常啟動節點
<code>chown mysql.mysql /data -Rmysqld_safe --defaults-file=/etc/my.cnf --loose-group_replication_start_on_boot=off &/<code>
- 查看備份文件中GTID位置
<code>[root@localhost backup]# cat xtrabackup_binlog_info mysql-bin.000147 103011527 2d623f55-2111-11e8-9cc3-0025905b06da:1-3,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-87512845/<code>
- 登錄數據庫，執行重設gtid，並重新拉起group replication
<code>root@localhost : (none) 01:38:35> reset master;Query OK, 0 rows affected (0.02 sec)root@localhost : (none) 01:39:25> set global gtid_purged='2d623f55-2111-11e8-9cc3-0025905b06da:1-3,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-87512845';Query OK, 0 rows affected (0.00 sec)root@localhost : (none) 01:39:53> set global group_replication_start_on_boot=on;Query OK, 0 rows affected (0.00 sec)root@localhost : (none) 01:40:59> start group_replication;Query OK, 0 rows affected (2.66 sec)/<code>
- 檢查集群組複製節點狀態
<code>#查看狀態是否為online，root@localhost : (none) 11:16:52> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 2d623f55-2111-11e8-9cc3-0025905b06da | node1 | 3306 | ONLINE || group_replication_applier | 859b114c-2e48-11e8-9eac-525400bdd1f2 | node3 | 3306 | RECOVERING || group_replication_applier | f64f9fb6-2da4-11e8-96bd-525400c33752 | node2 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)......root@localhost : (none) 01:43:23> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 2d623f55-2111-11e8-9cc3-0025905b06da | node1 | 3306 | ONLINE || group_replication_applier | 62392979-2e5c-11e8-a389-525400bdd1f2 | node3 | 3306 | ONLINE || group_replication_applier | f64f9fb6-2da4-11e8-96bd-525400c33752 | node2 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)/<code>
PS：如果是快照拷貝的數據，需要刪除datadir/auto.cnf文件
3.2. 集群新增節點
3.2.1. 使用備份集恢復
- 無備份時的qps曲線圖
- 有備份時的qps曲線圖
PS：
由於在多線程複製下，使用xtrabackup備份讀節點高概率會導致發生鎖死現象(sysbench 128線程insert操作百分百重現)，所以在MGR架構上的備份只能考慮在寫節點上備份（備選方案1：在集群上掛一個主從架構的備庫，使用xtrabackup的--safe-slave-backup選項在加鎖前停止SQL線程來避免鎖死現象。備選方案2：假定半夜寫壓力不高，輪詢線程信息，發現備份線程鎖請求被鎖死時，主動登錄數據庫kill掉加鎖線程並嘗試重新備份），鎖死現象如下
3.2.2. 全量複製加入集群
- 參考3.1.2小節
- 無新節點全量複製加入集群時的qps曲線圖
- 有新節點全量複製加入集群時的qps曲線圖
PS：全量複製新加節點會觸發流控，導致性能陡降，關閉流控性能可恢復到正常狀態，但需要所有節點同時關閉才生效
<code>root@localhost : (none) 06:52:16> set global group_replication_flow_control_mode=DISABLED;Query OK, 0 rows affected (0.01 sec)/<code>
3.3. 單節點crash的恢復
做一些必要的檢查之後，嘗試直接以常規方式拉起節點，如果不能正常拉起或者crash節點數據丟失，則通過備份進行恢復(詳細步驟參考3.1.3小節)。
3.4. 多數節點crash的恢復
兩種方式（針對至少有一個節點的實例可登陸，且集群不可用的情況）
- 如果crash節點能夠原地恢復(帶著crash之前的數據直接拉起實例)，則集群可以正常恢復到可讀寫狀態(即存活節點恢復到>=N/2+1個，且都正確重新加入到集群中)
- 如果所有crash節點數據丟失，則無法重新加入集群，此時如果要恢復集群，需要在存活的節點上停止集群複製插件，使其脫離集群變為可讀寫狀態，然後再恢復其他節點(使用xtrabackup備份存活節點數據做恢復)
3.4.1. crash節點數據未丟失且可以原地恢復
- 集群存活節點數<=N/2
- 模擬多數節點crash(這裡通過強殺第一和第二節點模擬)
<code>[root@localhost ~]# ssh 10.10.30.162 "killall -9 mysqld mysqld_safe 2> /dev/null &";ssh 10.10.30.163 "killall -9 mysqld mysqld_safe &> /dev/null &";
/<code>
- 登錄第三節點查看集群狀態
<code># 查看集群成員狀態root@localhost : (none) 10:15:09> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 0a1e8349-2e87-11e8-8c9f-525400bdd1f2 | node3 | 3306 | ONLINE || group_replication_applier | 2d623f55-2111-11e8-9cc3-0025905b06da | node1 | 3306 | UNREACHABLE || group_replication_applier | 506759a9-2e83-11e8-b454-525400c33752 | node2 | 3306 | UNREACHABLE |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)# 嘗試讀寫操作## 讀操作root@localhost : (none) 10:15:10> use sbtestDatabase changedroot@localhost : sbtest 10:15:52> select * from sbtest1 limit 1;+----+---------+----------------------------------------------------------------------+-------------------------------------------------------------+| id | k | c | pad |+----+---------+-----------------------------------------------------------------------+-------------------------------------------------------------+| 1 | 2507307 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |+----+---------+------------------------------------------------------------------------+-------------------------------------------------------------+1 row in set (0.01 sec)## 寫操作root@localhost : sbtest 10:25:43> delete from sbtest1 where id=1; # 寫操作被阻塞！！不是報錯....../<code>
- 另起一個mysql客戶端，登錄第三個節點實例中停止組複製插件
<code># 存活節點停止組複製插件root@localhost : (none) 11:51:15> stop group_replication;Query OK, 0 rows affected (1 min 27.08 sec)root@localhost : sbtest 11:52:10> set global read_only=1;set global super_read_only=1;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)# 可以看到未提交事務被回滾root@localhost : sbtest 11:29:02> delete from sbtest1 where id=1;......ERROR 3101 (HY000): Plugin instructed the server to rollback the current transaction./<code>
- 第三個節點(存活節點)重新啟動組複製插件
<code>root@localhost : (none) 12:30:20> set global group_replication_bootstrap_group=ON;Query OK, 0 rows affected (0.00 sec)root@localhost : (none) 12:33:25> start group_replication;Query OK, 0 rows affected (2.04 sec)root@localhost : (none) 12:33:29> set global group_replication_bootstrap_group=ON;Query OK, 0 rows affected (0.00 sec)root@localhost : sbtest 12:28:31> set global read_only=0;set global super_read_only=0; Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)/<code>
- 現在，直接拉起被強殺的兩個節點
<code>mysqld_safe --defaults-file=/etc/my.cnf &
/<code>
- 查看集群狀態
<code># 其他兩個節點的狀態已經轉為onlineroot@localhost : (none) 12:35:07> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 0a1e8349-2e87-11e8-8c9f-525400bdd1f2 | node3 | 3306 | ONLINE || group_replication_applier | 2d623f55-2111-11e8-9cc3-0025905b06da | node1 | 3306 | ONLINE || group_replication_applier | 506759a9-2e83-11e8-b454-525400c33752 | node2 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)/<code>
PS：MGR中一旦出現多數節點crash，即使在datadir中數據未丟失也不能直接拉起crash節點（集群節點內的殘留事務造成的數據衝突可能導致整個集群中所有節點的組複製插件異常停止），需要登錄到存活節點先把集群複製插件停止，然後重新啟動集群複製插件之後，再拉起crash節點
3.4.2. crash節點數據丟失
- 集群存活節點數<=N/2
- 模擬多數節點crash且丟失數據(這裡通過強殺第一和第二節點模擬)
<code>[root@localhost ~]# ssh 10.10.30.162 "killall -9 mysqld mysqld_safe 2> /dev/null &";ssh 10.10.30.163 "killall -9 mysqld mysqld_safe &> /dev/null &";
/<code>
- 登錄第一和第二節點清空數據目錄
<code>rm -rf /data/mysqldata1/{undo,innodb_ts,innodb_log,mydata,binlog,relaylog,binlog,slowlog,tmpdir}/*/<code>- 登錄第三節點查看集群狀態
<code># 查看集群成員狀態root@localhost : (none) 10:15:09> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 0a1e8349-2e87-11e8-8c9f-525400bdd1f2 | node3 | 3306 | ONLINE || group_replication_applier | 2d623f55-2111-11e8-9cc3-0025905b06da | node1 | 3306 | UNREACHABLE || group_replication_applier | 506759a9-2e83-11e8-b454-525400c33752 | node2 | 3306 | UNREACHABLE |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)# 嘗試讀寫操作## 讀操作root@localhost : (none) 10:15:10> use sbtestDatabase changedroot@localhost : sbtest 10:15:52> select * from sbtest1 limit 1;+----+---------+--------------------------------------------------------------------------------------------+-------------------------------------------------------------+| id | k | c | pad |+----+---------+---------------------------------------------------------------------------------------------+-------------------------------------------------------------+| 1 | 2507307 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |+----+---------+-----------------------------------------------------------------------------------------------+-------------------------------------------------------------+1 row in set (0.01 sec)## 寫操作root@localhost : sbtest 10:25:43> delete from sbtest1 where id=1; # 寫操作被阻塞！！不是報錯....../<code>
- 另起一個mysql客戶端，登錄第三個節點實例中停止組複製插件
<code># 存活節點停止組複製插件root@localhost : (none) 11:51:15> stop group_replication;Query OK, 0 rows affected (1 min 27.08 sec)root@localhost : sbtest 11:52:10> set global read_only=1;set global super_read_only=1;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)/<code>
- 第三個節點(存活節點)重新啟動組複製插件
<code>root@localhost : (none) 12:30:20> set global group_replication_bootstrap_group=ON;Query OK, 0 rows affected (0.00 sec)root@localhost : (none) 12:33:25> start group_replication;Query OK, 0 rows affected (2.04 sec)root@localhost : (none) 12:33:29> set global group_replication_bootstrap_group=ON;Query OK, 0 rows affected (0.00 sec)root@localhost : sbtest 12:28:31> set global read_only=0;set global super_read_only=0; Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)/<code>
- 現在，如果數據量不大，則可以在配置好my.cnf之後直接拉起實例，通過全量binlog複製加入集群(詳細步驟可參考3.1.2小節)，如果數據量較大，則使用xtrabackup備份方式恢復(詳細步驟可參考3.1.3小節)
PS：
- MGR集群多數節點crash之後，剩下的節點可以對外提供讀服務，但不可提供寫服務，這與MGC、PXC不同(這兩種集群多數節點crash之後，讀寫服務都不能提供)
- MGR集群多數節點crash之後，剩下的節點如果接受到寫請求，直接阻塞，而不是報錯返回，需要客戶端自行斷開連接，且斷開連接之後，在數據庫中對應的事務處於COMMIT狀態，不能跟隨客戶端斷開而自行回滾，需要人工干預
- 停止複製插件的方式使得節點脫離集群時，節點當前所有未提交事務會被強制回滾（MGR組複製插件可以stop xxx;start xxx;的方式重啟，所以不需要像MGC、PXC那樣必須重啟實例來重啟集群，也就不需要額外的備份操作了）
3.5. 整個集群crash的恢復
- 兩種方式
- 使用備份恢復
- 使用crash節點的datadir數據捲進行恢復
3.5.1. 使用備份恢復
- 詳細步驟參考3.1.3小節，第一個節點以集群方式啟動，其他節點使用備份恢復數據之後直接拉起
3.5.2. 使用crash節點的datadir數據捲進行恢復
- 使用crash節點的datadir數據捲進行恢復需要保證至少有一個crash節點的物理機或kvm可訪問(容器環境需要保證至少有一個crash節點對應的node可訪問)
- 下面使用sysbench對任意一個節點加壓
<code>sysbench --db-driver=mysql --time=3600 --threads=128 --report-interval=1 --mysql-socket=/home/mysql/data/mysqldata1/sock/mysql.sock --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=8 --table-size=5000000 oltp_insert --db-ps-mode=disable run
/<code>
- 同時強殺所有節點的數據庫進程(模擬所有節點同時crash)
<code>[root@localhost ~]# ssh 10.10.30.162 "killall -9 mysqld mysqld_safe 2> /dev/null &";ssh 10.10.30.163 "killall -9 mysqld mysqld_safe &> /dev/null &";ssh 10.10.30.164 "killall -9 mysqld mysqld_safe &> /dev/null &"
/<code>
- 以非集群方式啟動所有節點，並查看所有節點的GTID
<code># 第一個節點[root@localhost binlog]# mysqld_safe --defaults-file=/etc/my.cnf --loose-group_replication_start_on_boot=off --super-read-only --read-only &root@localhost : (none) 05:18:00> show master status\\G;*************************** 1. row *************************** File: mysql-bin.000124 Position: 270 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 0a1e8349-2e87-11e8-8c9f-525400bdd1f2:1-148826,2d623f55-2111-11e8-9cc3-0025905b06da:1-2,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-648795071 row in set (0.00 sec)# 第二個節點mysqld_safe --defaults-file=/etc/my.cnf --loose-group_replication_start_on_boot=off --super-read-only --read-only &root@localhost : (none) 05:21:04> show master status\\G;*************************** 1. row *************************** File: mysql-bin.000002 Position: 190 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 0a1e8349-2e87-11e8-8c9f-525400bdd1f2:1-148826,2d623f55-2111-11e8-9cc3-0025905b06da:1-2,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-648640651 row in set (0.00 sec)......# 第三個節點mysqld_safe --defaults-file=/etc/my.cnf --loose-group_replication_start_on_boot=off --super-read-only --read-only &root@localhost : (none) 05:21:43> show master status\\G;*************************** 1. row *************************** File: mysql-bin.000004 Position: 190 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 0a1e8349-2e87-11e8-8c9f-525400bdd1f2:1-148826,2d623f55-2111-11e8-9cc3-0025905b06da:1-2,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-648699151 row in set (0.00 sec)....../<code>
- 從上述信息中，可以看到第一個節點的GTID最大（注意這裡主要看集群的UUID對應的GTID，而不是節點的UUID對應的GTID號），事務號為64879507，下面在第一個節點中，拉起組複製集群，並開放對外提供讀寫服務
<code>root@localhost : (none) 12:30:20> set global group_replication_bootstrap_group=ON;Query OK, 0 rows affected (0.00 sec)root@localhost : (none) 12:33:25> start group_replication;Query OK, 0 rows affected (2.04 sec)root@localhost : (none) 12:33:29> set global group_replication_bootstrap_group=ON;Query OK, 0 rows affected (0.00 sec)root@localhost : (none) 05:27:03> select * from performance_schema.replication_group_members where MEMBER_ID=@@server_uuid;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 0a1e8349-2e87-11e8-8c9f-525400bdd1f2 | node1 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+1 row in set (0.00 sec)root@localhost : sbtest 12:28:31> set global read_only=0;set global super_read_only=0; Query OK, 0 rows affected (0.00 sec)/<code>
- 其他節點加入集群，並對外提供讀寫服務
<code>root@localhost : (none) 12:33:25> start group_replication;Query OK, 0 rows affected (2.04 sec)root@localhost : (none) 05:29:06> select * from performance_schema.replication_group_members where MEMBER_ID=@@server_uuid;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 5d78a458-30d2-11e8-a66f-5254002a54f2 | node2 | 3306 | RECOVERING |+---------------------------+--------------------------------------+-------------+-------------+--------------+1 row in set (0.00 sec)......root@localhost : (none) 05:30:12> select * from performance_schema.replication_group_members where MEMBER_ID=@@server_uuid;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 19f71b6a-30d3-11e8-a724-525400c33752 | node2 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+1 row in set (0.00 sec)# 等待集群狀態為online之後，開放對外讀寫服務root@localhost : sbtest 12:28:31> set global read_only=0;set global super_read_only=0; Query OK, 0 rows affected (0.00 sec)/<code>
分享到:
閱讀更多 啟迪雲Tuscloud 的文章
