一. MySQL InnoDB Cluster 介紹
MySQL的高可用架構無論是社群還是官方，一直在技術上進行探索，這麼多年提出了多種解決方案，比如MMM, MHA, NDB Cluster, Galera Cluster, InnoDB Cluster, 騰訊的PhxSQL, MySQL Fabric., aliSQL。MySQL官方在2017年4月推出了一套完整的、高可用的Mysql解決方案 - MySQL InnoDB Cluster, 即一組MySQL伺服器可以配置為一個MySQL叢集。在預設的單主節點模式下，叢集伺服器具有一個讀寫主節點和多個只讀輔節點。輔助伺服器是主伺服器的副本。客戶端應用程式通過MySQL Router連線到主服務程式。如果主服務連線失敗，則次要的節點自動提升為主節點，MySQL Router請求到新的主節點。InnoDB Cluster不提供NDB Cluster支援。
-> 分散式MySQL之InnoDB和NDB
分散式MySQL主要有InnoDB和NDB模式, NDB是基於叢集的引擎-資料被自動切分並複製到數個機器上（資料節點）， 適合於那些需要極高查詢效能和高可用性的應用， 原來是為愛立信的電信應用設計的。 NDB提供了高達99.999%的可靠性，在讀操作多的應用中表現優異。 對於有很多併發寫操作的應用， 還是推薦用InnoDB。
========== NDB和InnoDB儲存引擎之間的特性差異 ==========
InnoDB(MySQL 5.7)特性:
- InnoDB版本：InnoDB 5.7.20；
- NDB Cluster版本：不支援；
- 最大儲存長度：64TB；
- 事物：所有標準事物型別；
- 多版本併發控制：支援；
- 資料壓縮：支援；
- 大行支：VARBINARY、VARCHAR、BLOB；
- 同步支援：半同步、非同步；
- 塊讀取：支援；
- 塊寫入：需要使用水平分割槽；
- 高可用性：高；
NDB 7.5/7.6特性：
- InnoDB版本：InnoDB 5.7.20；
- NDB Cluster版本： NDB 7.5.8/7.6.4；
- 最大儲存長度：128TB；
- 事物：讀提交；
- 多版本併發控制：不支援；
- 資料壓縮支援：不支援；
- 大行支援：BLOB、 TEXT；
- 同步支援半：自動同步；
- 塊讀取：支援；
- 塊寫入：支援；
- 高可用性：非常高；
-> Mysql InnoDB Cluster 工作原理和流程
MySQL InnoDB叢集提供了一個整合的，本地的，HA解決方案。Mysq Innodb Cluster是利用組複製的 pxos 協議，保障資料一致性，組複製支援單主模式和多主模式。
MySQL InnoDB叢集由以下幾部分組成：
- MySQL Servers with Group Replication：向叢集的所有成員複製資料，同時提供容錯、自動故障轉移和彈性。MySQL Server 5.7.17或更高的版本。
- MySQL Router：確保客戶端請求是負載平衡的，並在任何資料庫故障時路由到正確的伺服器。MySQL Router 2.1.3或更高的版本。
- MySQL Shell：通過內建的管理API建立及管理Innodb叢集。MySQL Shell 1.0.9或更高的版本。
各個元件的關係和工作流程如下:
圖一
圖二
圖三
-> MySQL InnoDB Cluster 叢集特性, 有什麼好處 (為什麼使用它)
- 整合易用
MySQL InnoDB叢集緊密整合了MySQL Servers with Group Replication，MySQL Router，和MySQL Shell，所以不必依賴於外部工具，指令碼或其他部件。 另外它利用了現有的MySQL特性，如：InnoDB, GTIDs, binary logs, multi-threaded slave execution, multi-source replication and Performance Schema。可以在五分鐘內利用MySQL Shell中的指令碼化的管理API來建立及管理MySQL叢集。
- 使用組複製的mysql server HA
組複製提供了內建的組成員管理、資料一致性保證、衝突檢測和處理、節點故障檢測和資料庫故障轉移相關操作的本地高可用性，無需人工干預或自定義工具。組複製同時實現了帶自動選主的單主模式及任意更新的多主模式。通過使用一個強大的新的組通訊系統，它提供了流行的Paxos演算法的內部實現，來自動協調資料複製、一致性、membership。這提供了使MySQL資料庫高度可用所需的所有內建機制。
- 彈性
通過組複製，一組伺服器協調在一起形成一個組。組成員是動態的，伺服器可以自願或強制的地離開及隨時加入。組將根據需要自動重新配置自己，並確保任何加入成員與組同步。這樣就可以方便地在需要時快速地調整資料庫的總容量。
- 故障檢測
組複製實現了一個分散式故障檢測器來查詢並報告failed或不再參與組的伺服器，組中剩餘成員將重新配置。
- 容錯
組複製基於流行的Paxos分散式演算法來提供伺服器之間的分散式協調。為了使一個小組繼續發揮作用，它要求大多數成員線上，並就每一個變化達成協議。這允許MySQL資料庫在發生故障時安全地繼續操作，而無需人工干預，不存在資料丟失或資料損壞的風險。
- 自愈
如果一個伺服器加入該組，它將自動將其狀態與現有成員同步。如果伺服器離開該組，例如它被取下來進行維護，剩下的伺服器將看到它已離開，並將自動重新配置組。當伺服器後重新加入組，它會自動重新與組同步。
- 監測
MySQL Enterprise Monitor 3.4及以後的版本全面支援組複製；監控每個節點的配置，健康，和效能。並且提供最佳實踐建議和提醒，以及易於理解的視覺化工具，允許您輕鬆地監控和管理您的組複製和InnoDB叢集。
- 通過MySQL Router為mysql客戶機應用程式實現HA
MySQL的路由器允許您輕鬆遷移您的獨立的MySQL例項到本地分散式高可用叢集而不影響現有的應用程式。新metadata_cache外掛為Innodb 叢集提供了透明的客戶端連線路由、負載平衡和故障轉移的能力。
- 簡單易用的MySQL shell
MySQL Shell為所有MySQL相關的任務提供了一個直觀、靈活、功能強大的介面。
新的adminapi使得它很容易用一種自我描述的自然語言來建立，監控和管理包括MySQL Router在內的MySQL InnoDB叢集，而不需要了解低層次的概念，配置選項，或其他複雜的方面。
二. Centos7.5 基於MySQL5.7 的 InnoDB Cluster 高可用環境部署記錄
下面部署採用InnoDB Cluster, 每臺伺服器例項都執行MySQL Group Replication (即冗餘複製機制，內建failover), MGR有兩種模式，一種是Single-Primary，一種是Multi-Primary，即單主或者多主。
需求注意：模式Multi-Primary中，所有的節點都是主節點，都可以同時被讀寫，看上去這似乎更好，但是因為多主的複雜性，在功能上如果設定了多主模式，則會有一些使用的限制，比如不支援Foreign Keys with Cascading Constraints。
2.1 環境準備
這裡準備了4臺centos7版本的伺服器用來部署innodb cluster多節點叢集環境 (至少也要需要3臺伺服器), 其中:
1) db-node01、db-node02、db-node03 作為 cluster 節點伺服器, 三個節點都要安裝 mysql8.0.x 與 mysql-shell
2) db-route01 作為管理節點伺服器，用來負責建立 cluster，並作為 cluster 的路由, 該節點需要安裝mysql-shell、mysql-router
3) 所有節點的python版本要在2.7以上
ip地址 主機名 角色 安裝軟體 172.16.60.211 db-node01 cluster節點1 Mysql5.7, mysql-shell 172.16.60.212 db-node02 cluster節點2 Mysql5.7, mysql-shell 172.16.60.213 db-node03 cluster節點3 Mysql5.7, mysql-shell 172.16.60.214 db-route01 管理節點1 mysql-shell, mysql-route [root@db-node01 ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) [root@db-node01 ~]# python -V Python 2.7.5 為了方便實驗，關閉所有節點的防火牆 [root@MGR-node1 ~]# systemctl stop firewalld [root@MGR-node1 ~]# firewall-cmd --state not running 關閉每個節點的selinux [root@MGR-node1 ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled" SELINUX=disabled [root@MGR-node1 ~]# setenforce 0 setenforce: SELinux is disabled [root@MGR-node1 ~]# getenforce Disabled 配置每個節點的/etc/hosts主機對映, 方便通過節點的hostname進行連線 這一步很重要，否則可能會出現無法同步的情況，因為資料庫需要根據member_host同步，如果不配置，預設就是localhost，這樣時無法通訊的!!! [root@db-node01 ~]# vim /etc/hosts ........... 172.16.60.211 db-node01 172.16.60.212 db-node02 172.16.60.213 db-node03 172.16.60.214 db-route01 所有節點進行如下的相關優化配置 [root@db-node01 ~]# cat>>/etc/sysctl.conf <<EOF fs.aio-max-nr = 1048576 fs.file-max = 681574400 kernel.shmmax = 137438953472 kernel.shmmni = 4096 kernel.sem = 250 32000 100 200 net.ipv4.ip_local_port_range = 9000 65000 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048586 EOF [root@db-node01 ~]# sysctl -p [root@db-node01 ~]# cat>>/etc/security/limits.conf <<EOF mysql soft nproc 65536 mysql hard nproc 65536 mysql soft nofile 65536 mysql hard nofile 65536 EOF [root@db-node01 ~]# cat>>/etc/pam.d/login <<EOF session required /lib/security/pam_limits.so session required pam_limits.so EOF [root@db-node01 ~]# cat>>/etc/profile<<EOF if [ $USER = "mysql" ]; then ulimit -u 16384 -n 65536 fi EOF [root@db-node01 ~]# source /etc/profile ========================================================== mysql-shell下載地址: https://pan.baidu.com/s/1nPWcmKb2T_iDmpQ84ZrVmQ 提取密碼: u425 mysql-route下載地址: https://pan.baidu.com/s/1Tb7lxnxyiFdwxkdKOlU29Q 提取密碼: sq6h
2.2 在管理節點安裝mysql shell 和 mysql-route
[root@db-route01 ~]# cd /usr/local/src/ [root@db-route01 src]# ll total 21648 -rw-rw-r-- 1 root root 15526654 Mar 8 16:08 mysql-router-2.1.4-linux-glibc2.12-x86-64bit.tar.gz -rw-rw-r-- 1 root root 6635831 Mar 22 2017 mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz [root@db-route01 src]# tar -zvxf mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz [root@db-route01 src]# tar -zvxf mysql-router-2.1.4-linux-glibc2.12-x86-64bit.tar.gz [root@db-route01 src]# mv mysql-router-2.1.4-linux-glibc2.12-x86-64bit mysql-route [root@db-route01 src]# mv mysql-shell-1.0.9-linux-glibc2.12-x86-64bit mysql-shell [root@db-route01 src]# mv mysql-route /usr/local/ [root@db-route01 src]# mv mysql-shell /usr/local/ [root@db-route01 src]# vim /etc/profile .............. export PATH=$PATH:/usr/local/mysql-shell/bin/:/usr/local/mysql-route/bin/ [root@db-route01 src]# source /etc/profile [root@db-route01 ~]# mysqlprovision --version mysqlprovision version 2.0.0 [root@db-route01 ~]# mysqlsh --version MySQL Shell Version 1.0.9 [root@db-route01 ~]# mysqlrouter --version MySQL Router v2.1.4 on Linux (64-bit) (GPL community edition)
2.3 在三個cluster節點安裝和部署Mysql5.7及 mysql-shell
1) 安裝mysql-shell (三個節點同樣操作)
[root@db-node01 ~]# cd /usr/local/src/
[root@db-node01 src]# ll mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz
-rw-r--r-- 1 root root 6635831 Mar 22 2017 mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz
[root@db-node01 src]# tar -zvxf mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz
[root@db-node01 src]# mv mysql-shell-1.0.9-linux-glibc2.12-x86-64bit mysql-shell
[root@db-node01 src]# mv mysql-shell /usr/local/
[root@db-node01 src]# echo "export PATH=$PATH:/usr/local/mysql-shell/bin/" >> /etc/profile
[root@db-node01 src]# source /etc/profile
[root@db-node01 ~]# mysqlprovision --version
mysqlprovision version 2.0.0
[root@db-node01 ~]# mysqlsh --version
MySQL Shell Version 1.0.9
2) 安裝mysql5.7 (三個節點同樣操作)
使用yum方式安裝Mysql5.7，參考：https://www.cnblogs.com/kevingrace/p/8340690.html
安裝MySQL yum資源庫
[root@db-node01 ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
安裝MySQL 5.7
[root@db-node01 ~]# yum install -y mysql-community-server
啟動MySQL伺服器和MySQL的自動啟動
[root@db-node01 ~]# systemctl start mysqld.service
[root@db-node01 ~]# systemctl enable mysqld.service
設定登入密碼
由於MySQL從5.7開始不允許首次安裝後使用空密碼進行登入！為了加強安全性，系統會隨機生成一個密碼以供管理員首次登入使用，
這個密碼記錄在/var/log/mysqld.log檔案中，使用下面的命令可以檢視此密碼：
[root@db-node01 ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2019-01-11T05:53:17.824073Z 1 [Note] A temporary password is generated for root@localhost: TaN.k:*Qw2xs
使用上面檢視的密碼TaN.k:*Qw2xs 登入mysql，並重置密碼為123456
[root@db-node01 ~]# mysql -p #輸入預設的密碼：TaN.k:*Qw2xs
.............
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
檢視mysql版本
[root@db-node01 ~]# mysql -p123456
........
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24 |
+-----------+
1 row in set (0.00 sec)
=====================================================================
溫馨提示
mysql5.7通過上面預設安裝後，執行語句可能會報錯：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
這個報錯與Mysql 密碼安全策略validate_password_policy的值有關，validate_password_policy可以取0、1、2三個值：
解決辦法：
set global validate_password_policy=0;
set global validate_password_length=1;
3) 配置my.cnf
先配置db-node01節點的my.cnf
[root@db-node01 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@db-node01 ~]# >/etc/my.cnf
[root@db-node01 ~]# 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
#複製框架
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
#組複製設定
#server必須為每個事務收集寫集合，並使用XXHASH64雜湊演算法將其編碼為雜湊
transaction_write_set_extraction=XXHASH64
#告知外掛加入或建立組命名，UUID
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#server啟動時不自啟組複製,為了避免每次啟動自動引導具有相同名稱的第二個組,所以設定為OFF。
loose-group_replication_start_on_boot=off
#告訴外掛使用IP地址，埠24901用於接收組中其他成員轉入連線
loose-group_replication_local_address="172.16.60.211:24901"
#啟動組server，種子server，加入組應該連線這些的ip和埠；其他server要加入組得由組成員同意
loose-group_replication_group_seeds="172.16.60.211:24901,172.16.60.212:24901,172.16.60.213:24901"
loose-group_replication_bootstrap_group=off
# 使用MGR的單主模式
loose-group_replication_single_primary_mode = on
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
report_port=3306
如上配置完成後, 將db-node01節點的/etc/my.cnf檔案拷貝到其他兩個節點
[root@db-node01 ~]# rsync -e "ssh -p22" -avpgolr /etc/my.cnf root@172.16.60.212:/etc/
[root@db-node01 ~]# rsync -e "ssh -p22" -avpgolr /etc/my.cnf root@172.16.60.213:/etc/
3個cluster節點除了server_id、loose-group_replication_local_address 兩個引數不一樣外，其他保持一致。
所以待拷貝完成後, 分別修改db-node02和db-node03節點/etc/my.cnf檔案的server_id、loose-group_replication_local_address兩個引數
配置完成後, 要依次重啟三個節點的資料庫，安裝MGR外掛，設定複製賬號（所有MGR節點都要執行）
[root@db-node01 ~]# systemctl restart mysqld
2.4 建立Innodb Cluster叢集
1) 在 db-node01 上建立叢集，通過 db-node01 上的 shell 連線db-node01 的 mysql
[root@db-node01 ~]# mysqlsh
...................
# 執行配置命令，也需要密碼
# 然後需要輸入MySQL配置檔案路徑，本示例中的路徑是 /usr/local/data/s1/s1.cnf
# 接下來需要建立供其他主機訪問的使用者，這裡選擇第1項，為root使用者授權
mysql-js> shell.connect('root@localhost:3306');
Please provide the password for 'root@localhost:3306': #輸入密碼123456
Creating a Session to 'root@localhost:3306'
Classic Session successfully established. No default schema selected.
mysql-js> dba.configureLocalInstance();
Please provide the password for 'root@localhost:3306': #輸入密碼123456
Detecting the configuration file...
Found configuration file at standard location: /etc/my.cnf
Do you want to modify this file? [Y|n]: #直接回車, 使用預設的/etc/my.cnf配置檔案
MySQL user 'root' cannot be verified to have access to other hosts in the network.
1) Create root@% with necessary grants
2) Create account with different name
3) Continue without creating account
4) Cancel
Please select an option [1]: 1 #選擇第1項，為root使用者授權, 建立供其他主機訪問的使用者
Password for new account: #輸入供其他主機訪問的使用者root使用者授權的密碼. 這裡依然設定123456
Confirm password:
Validating instance...
Dba.configureLocalInstance: Your password does not satisfy the current policy requirements (MySQL Error 1819)
出現上面報錯的解決辦法:
開啟另一個終端視窗, 登入db-node01節點的mysql,執行下面命令:
[root@db-node01 ~]# mysql -p123456
.................
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
然後接著上面db-node01終端視窗的mysql-shell繼續執行:
mysql-js> shell.connect('root@localhost:3306');
Please provide the password for 'root@localhost:3306': #輸入密碼123456
Creating a Session to 'root@localhost:3306'
Classic Session successfully established. No default schema selected.
mysql-js> dba.configureLocalInstance();
Please provide the password for 'root@localhost:3306': #輸入密碼123456
Detecting the configuration file...
Found configuration file at standard location: /etc/my.cnf
Do you want to modify this file? [Y|n]: #直接回車, 使用預設的/etc/my.cnf配置檔案
MySQL user 'root' cannot be verified to have access to other hosts in the network.
1) Create root@% with necessary grants
2) Create account with different name
3) Continue without creating account
4) Cancel
Please select an option [1]: 1 # 選擇第1項，為root使用者授權, 即供其他主機訪問的使用者root使用者授權的密碼, 否則其他機器使用root使用者連線不上不本機的mysql
Password for new account: #輸入. 這裡依然設定123456. 這裡授權之後, 登入db-node01節點的mysql, 執行"select host, user from mysql.user" 命令就能發現
Confirm password:
Validating instance...
The instance 'localhost:3306' is valid for Cluster usage
You can now use it in an InnoDB Cluster.
{
"status": "ok"
}
mysql-js>
由上面的資訊看出, status 為 ok 說明配置沒問題了，可以用來建立cluster
2) 通過 db-route01 的 mysql-shell 連線 node01 建立 cluster
[root@db-route01 ~]# mysqlsh
................
# 連線db-node01
mysql-js> shell.connect('root@db-node01:3306');
Please provide the password for 'root@db-node01:3306': #輸入密碼123456
Creating a Session to 'root@db-node01:3306'
Classic Session successfully established. No default schema selected.
# 建立一個 cluster，命名為 'myCluster'
mysql-js> var cluster = dba.createCluster('myCluster');
A new InnoDB cluster will be created on instance 'root@db-node01:3306'.
Creating InnoDB cluster 'myCluster' on 'root@db-node01:3306'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
如上的資訊, 如果建立成功, 則會輸出的資訊中會有類似“Cluster successfully created.”的語句
#建立成功後，檢視cluster狀態
mysql-js> cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db-node01:3306",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"db-node01:3306": {
"address": "db-node01:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
mysql-js> dba.getCluster();
<Cluster:myCluster>
注意上面這個db-route01的mysql-shell終端視窗就不要關閉了, 一直保持連線中,也就是一直在當前叢集狀態中, 後面新增其他節點到cluster叢集中會用到! (後面說到常用命令時會解釋)
3) 新增節點 db-node02到上面建立的"myCluster"叢集中
通過db-node02本機 mysql-shell 對 mysql 進行配置
[root@db-node02 ~]# mysqlsh
................
MySQL JS > shell.connect('root@localhost:3306');
Creating a session to 'root@localhost:3306'
Please provide the password for 'root@localhost:3306': ****** #輸入密碼123456
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 251
Server version: 5.7.25-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
<ClassicSession:root@localhost:3306>
MySQL localhost:3306 ssl JS > dba.configureLocalInstance();
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as 172.16.60.212
WARNING: User 'root' can only connect from localhost.
If you need to manage this instance while connected from other hosts, new account(s) with the proper source address specification must be created.
1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel
Please select an option [1]: 1
Please provide a source address filter for the account (e.g: 192.168.% or % etc) or leave empty and press Enter to cancel.
Account Host: % #選擇%, 表示允許任何機器遠端使用root使用者連線本機的mysql
The instance 'localhost:3306' is valid for InnoDB cluster usage.
Your password does not satisfy the current policy requirements (MySQL Error 1819)
解決辦法:
登入db-node02節點的mysql
[root@db-node02 ~]# mysql -p123456
...............
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
接著繼續登入db-node02本機的mysql-shell 進行配置
[root@db-node02 ~]# mysqlsh
.............
MySQL JS > shell.connect('root@localhost:3306');
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 180
Server version: 5.7.25-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
<ClassicSession:root@localhost:3306>
MySQL localhost:3306 ssl JS > dba.configureLocalInstance();
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as db-node02
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
WARNING: User 'root' can only connect from localhost.
If you need to manage this instance while connected from other hosts, new account(s) with the proper source address specification must be created.
1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel
Please select an option [1]: 1
Please provide a source address filter for the account (e.g: 192.168.% or % etc) or leave empty and press Enter to cancel.
Account Host: %
The instance 'localhost:3306' is valid for InnoDB cluster usage.
Cluster admin user 'root'@'%' created.
MySQL localhost:3306 ssl JS >
然後登入db-node02節點的mysql, 發現上面使用root使用者遠端連線的授權已經有了
[root@db-node02 ~]# mysql -p123456
..............
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
4 rows in set (0.00 sec)
接著修改 my.cnf，新增配置項:
[root@db-node02 ~]# vim /etc/my.cnf
............
loose-group_replication_allow_local_disjoint_gtids_join=on
重啟mysql服務
[root@db-node02 ~]# systemctl restart mysqld
然後通過 db-route01節點 的 mysql-shell 新增 node02 到 "myCluster"叢集中
接著上面的db-route01的mysql-shell終端視窗 (注意這個終端視窗是上面執行後, 沒有關閉一直開著的)
mysql-js> cluster.addInstance('root@db-node02:3306');
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@db-node02:3306':
Adding instance to the cluster ...
The instance 'root@db-node02:3306' was successfully added to the cluster.
上面資訊表示db-node02節點已經成功新增到"myCluster"叢集中了. 如下檢視叢集狀態
mysql-js> cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db-node01:3306",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"db-node01:3306": {
"address": "db-node01:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db-node02:3306": {
"address": "db-node02:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
同樣, 上面操作後, 這個db-route01節點的mysql-shell當前終端視窗不要關閉,繼續保持在叢集狀態中, 下面新增db-node03節點到叢集中會用到這裡.(後面常用命令中會提到)
4) 新增節點 db-node03到上面建立的"myCluster"叢集中
首先登入db-node03節點的mysql
[root@db-node03 ~]# mysql -p123456
............
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
登入db-node03節點的mysql-shell, 進行配置
[root@db-node03 ~]# mysqlsh
.............
MySQL JS > shell.connect('root@localhost:3306');
Creating a session to 'root@localhost:3306'
Please provide the password for 'root@localhost:3306': ******
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 393
Server version: 5.7.25-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
<ClassicSession:root@localhost:3306>
MySQL localhost:3306 ssl JS > dba.configureLocalInstance();
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as db-node03
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
WARNING: User 'root' can only connect from localhost.
If you need to manage this instance while connected from other hosts, new account(s) with the proper source address specification must be created.
1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel
Please select an option [1]: 1
Please provide a source address filter for the account (e.g: 192.168.% or % etc) or leave empty and press Enter to cancel.
Account Host: %
The instance 'localhost:3306' is valid for InnoDB cluster usage.
Cluster admin user 'root'@'%' created.
接著修改 my.cnf，新增配置項:
[root@db-node03 ~]# vim /etc/my.cnf
............
loose-group_replication_allow_local_disjoint_gtids_join=on
重啟mysql服務
[root@db-node03 ~]# systemctl restart mysqld
然後通過 db-route01節點 的 mysql-shell 新增 node03 到 "myCluster"叢集中
接著上面的db-route01的mysql-shell終端視窗 (注意這個終端視窗是上面執行後, 沒有關閉一直開著的)
mysql-js> cluster.addInstance('root@db-node03:3306');
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@db-node03:3306':
Adding instance to the cluster ...
The instance 'root@db-node03:3306' was successfully added to the cluster.
上面資訊表示db-node02節點已經成功新增到"myCluster"叢集中了. 如下檢視叢集狀態
mysql-js> cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db-node01:3306",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db-node01:3306": {
"address": "db-node01:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db-node02:3306": {
"address": "db-node02:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db-node03:3306": {
"address": "db-node03:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
通過上面cluster叢集資訊可知, db-node01節點是主節點, 具有R/W讀寫許可權, 其他兩個節點是從節點, 具有R/O 只讀許可權
2.5 啟動管理節點的route
進入 db-route01管理節點中mysql-router 安裝目錄，配置並啟動 router [root@db-route01 ~]# /usr/local/mysql-route/bin/mysqlrouter --bootstrap root@db-node01:3306 -d myrouter --user=root Please enter MySQL password for root: Bootstrapping MySQL Router instance at /root/myrouter... MySQL Router has now been configured for the InnoDB cluster 'myCluster'. The following connection information can be used to connect to the cluster. Classic MySQL protocol connections to cluster 'myCluster': - Read/Write Connections: localhost:6446 #讀寫埠 - Read/Only Connections: localhost:6447 #只讀埠 X protocol connections to cluster 'myCluster': - Read/Write Connections: localhost:64460 - Read/Only Connections: localhost:64470 這裡會在當前目錄下產生mysql-router 目錄, 並生成router配置檔案，接著把配置檔案修改一下： [root@db-route01 ~]# ls /root/myrouter/ data log mysqlrouter.conf mysqlrouter.key run start.sh stop.sh [root@db-route01 ~]# cat /root/myrouter/mysqlrouter.conf #可以修改配置檔案, 也可以預設不修改 預設通過route連線mysql後, 6446埠連線後可以進行讀寫操作. 6447埠連線後只能進行只讀操作. 然後啟動mysqlroute [root@db-route01 ~]# /root/myrouter/start.sh PID 16484 written to /root/myrouter/mysqlrouter.pid [root@db-route01 ~]# ps -ef|grep myroute root 18473 1 0 22:26 pts/1 00:00:00 sudo ROUTER_PID=/root/myrouter/mysqlrouter.pid /usr/local/mysql-route/bin/mysqlrouter -c /root/myrouter/mysqlrouter.conf --user=root root 18486 18473 0 22:26 pts/1 00:00:00 /usr/local/mysql-route/bin/mysqlrouter -c /root/myrouter/mysqlrouter.conf --user=root root 18612 5091 0 22:26 pts/1 00:00:00 grep --color=auto myroute [root@db-route01 ~]# netstat -tunlp|grep 18486 tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 18486/mysqlrouter tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 18486/mysqlrouter tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 18486/mysqlrouter tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 18486/mysqlrouter 這樣就可以使用MySQL客戶端連線router了. 下面驗證下連線router: a) 管理節點本機mysql-shell連線: [root@db-route01 ~]# mysqlsh --uri root@localhost:6446 b) 管理節點本機mysql連線: [root@db-route01 ~]# mysql -u root -h 127.0.0.1 -P 6446 -p c) 遠端客戶機通過route連線mysql [root@db-node01 ~]# mysql -u root -h 172.16.60.214 -P 6446 -p 測試cluster節點資料同步. 這裡選擇db-node03節點作為遠端客戶端連線router [root@db-node03 ~]# mysql -u root -h 172.16.60.214 -P 6446 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1054 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> show databases; +-------------------------------+ | Database | +-------------------------------+ | information_schema | | mysql | | mysql_innodb_cluster_metadata | | performance_schema | | sys | +-------------------------------+ 5 rows in set (0.00 sec) 測試測試庫kevin mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci; ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'kevin' 這是因為'root@%'沒有建立庫的許可權 mysql> select host,user from mysql.user; +-----------+----------------------------------+ | host | user | +-----------+----------------------------------+ | % | mysql_innodb_cluster_rp496261783 | | % | mysql_innodb_cluster_rp496457975 | | % | mysql_innodb_cluster_rp496569258 | | % | mysql_innodb_cluster_rp496629685 | | % | mysql_router1_olzau3ltjqzx | | % | root | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+----------------------------------+ 9 rows in set (0.00 sec) mysql> show grants for root@'%'; +-----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@% | +-----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'root'@'%' WITH GRANT OPTION | | GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO 'root'@'%' WITH GRANT OPTION | | GRANT SELECT ON `performance_schema`.* TO 'root'@'%' WITH GRANT OPTION | | GRANT ALL PRIVILEGES ON `mysql_innodb_cluster_metadata`.* TO 'root'@'%' WITH GRANT OPTION | +-----------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) 登入主庫, 建立一個具有管理權許可權的使用者 [root@db-node01 ~]# mysql -p123456 ............. mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to bobo@'%' identified by "bo@123" with grant option; Query OK, 0 rows affected, 1 warning (0.05 sec) 接著遠端使用上面建立的新賬號登入router操作 [root@db-node03 ~]# mysql -u bobo -h 172.16.60.214 -P 6446 -p ........ mysql> show grants for bobo@'%'; +-------------------------------------------------------------+ | Grants for bobo@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'bobo'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec) 測試測試庫kevin mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.06 sec) mysql> use kevin; Database changed mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL); Query OK, 0 rows affected (0.22 sec) mysql> insert into kevin.haha values(1,"wangshibo"),(2,"guohuihui"),(3,"yangyang"),(4,"shikui"); Query OK, 4 rows affected (0.13 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | +----+-----------+ 4 rows in set (0.00 sec) 分別登入三個cluster節點的mysql, 發現測試庫kevin已經完成同步了, 其中: 寫操作的資料會先寫到db-node01節點, 然後同步到db-node02和db-node03只讀節點上. 注意: 上面使用6446埠連線的route, 可以進行讀寫操作. 但是使用6447埠連線後, 就只能進行只讀操作了. 登入後可以執行" select @@hostname" 檢視登入到哪個節點上. [root@db-node03 ~]# mysql -u bobo -h 172.16.60.214 -P 6447 -p ............. mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | +----+-----------+ 4 rows in set (0.00 sec) mysql> delete from kevin.haha where id>2; ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
此外, 還可以利用keepalived實現InnoDB Cluster的高可用, 即兩臺db-route管理節點, 通過VIP資源實現故障無感知切換. 這樣需要準備5臺節點, 其中3個cluster節點(安裝mysql, mysql-shell), 2個route管理節點(安裝keepalived, mysql-shell, mysql-route, mysql-client)
InnoDB Cluster叢集 日常維護命令
1) 在各節點配置之後, 建立cluster叢集之前, 可以依次檢查下cluster各個節點是否可用
[root@db-node01 ~]# mysqls
.................
mysql-js> dba.checkInstanceConfiguration("root@localhost:3306")
Please provide the password for 'root@localhost:3306':
Validating instance...
The instance 'localhost:3306' is valid for Cluster usage
{
"status": "ok"
}
mysql-js> dba.checkInstanceConfiguration("root@db-node02:3306")
Please provide the password for 'root@db-node02:3306':
Validating instance...
The instance 'db-node02:3306' is valid for Cluster usage
{
"status": "ok"
}
mysql-js> dba.checkInstanceConfiguration("root@db-node03:3306")
Please provide the password for 'root@db-node03:3306':
Validating instance...
The instance 'db-node03:3306' is valid for Cluster usage
{
"status": "ok"
}
2) 比如在上面建立Innodb cluster叢集過程中, 再次登入mysqlsh (從客戶端遠端登入, 或任意節點本地登入), 怎麼獲得並檢視叢集狀態
[root@db-node01 ~]# mysqlsh
.................
mysql-js> shell.connect("root@db-node01:3306");
Please provide the password for 'root@db-node01:3306':
Creating a Session to 'root@db-node01:3306'
Classic Session successfully established. No default schema selected.
檢視叢集狀態
mysql-js> cluster.status();
ReferenceError: cluster is not defined
上面方式檢視, 會報錯說叢集沒有定義, 這時需要先執行下面這條語句之後,才看檢視到叢集狀態!!!!!
mysql-js> cluster.status();
ReferenceError: cluster is not defined
然後就可以檢視叢集狀態了
mysql-js> cluster=dba.getCluster();
<Cluster:myCluster>
mysql-js> cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db-node01:3306",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db-node01:3306": {
"address": "db-node01:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db-node02:3306": {
"address": "db-node02:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db-node03:3306": {
"address": "db-node03:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
從遠端客戶端登入
[root@db-node03 ~]# mysqlsh --uri root@172.16.60.214:6446
......................
MySQL 172.16.60.214:6446 ssl JS > cluster=dba.getCluster();
<Cluster:myCluster>
MySQL 172.16.60.214:6446 ssl JS > cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db-node01:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db-node01:3306": {
"address": "db-node01:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db-node02:3306": {
"address": "db-node02:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db-node03:3306": {
"address": "db-node03:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
},
"groupInformationSourceMember": "mysql://root@172.16.60.214:6446"
}
檢視已建立的叢集名稱
MySQL 172.16.60.214:6446 ssl JS > dba.getCluster();
<Cluster:myCluster>
=================================================
總結:
a) dba.getCluster(); #檢視建立的叢集
b) cluster=dba.getCluster(); #獲取當前叢集
c) cluster.status(); #檢視叢集狀態
=================================================
3) InnoDB Cluster叢集維護的命令幫助
mysql-js> dba.help();
The global variable 'dba' is used to access the MySQL AdminAPI functionality
and perform DBA operations. It is used for managing MySQL InnoDB clusters.
The following properties are currently supported.
- verbose Enables verbose mode on the Dba operations.
The following functions are currently supported.
- checkInstanceConfiguration Validates an instance for usage in Group
Replication.
- configureLocalInstance Validates and configures an instance for
cluster usage.
- createCluster Creates a MySQL InnoDB cluster.
- deleteSandboxInstance Deletes an existing MySQL Server instance on
localhost.
- deploySandboxInstance Creates a new MySQL Server instance on
localhost.
- dropMetadataSchema Drops the Metadata Schema.
- getCluster Retrieves a cluster from the Metadata Store.
- help Provides help about this class and it's
members
- killSandboxInstance Kills a running MySQL Server instance on
localhost.
- rebootClusterFromCompleteOutage Brings a cluster back ONLINE when all
members are OFFLINE.
- resetSession Sets the session object to be used on the
Dba operations.
- startSandboxInstance Starts an existing MySQL Server instance on
localhost.
- stopSandboxInstance Stops a running MySQL Server instance on
localhost.
For more help on a specific function use: dba.help('<functionName>')
e.g. dba.help('deploySandboxInstance')
比如獲取當前叢集名稱
mysql-js> dba.getCluster();
<Cluster:myCluster>
4) 日常使用的幾個重要命令 (mysqlsh的JS語法)
dba.checkInstanceConfiguration("root@hostname:3306") #檢查節點配置例項，用於加入cluster之前
dba.rebootClusterFromCompleteOutage('myCluster'); #重啟
dba.dropMetadataSchema(); #刪除schema
var cluster = dba.getCluster('myCluster') #獲取當前叢集
cluster.checkInstanceState("root@hostname:3306") #檢查cluster裡節點狀態
cluster.rejoinInstance("root@hostname:3306") #重新加入節點，我本地測試的時候發現rejoin一直無效，每次是delete後
addcluster.dissolve({force：true}) #刪除叢集
cluster.addInstance("root@hostname:3306") #增加節點
cluster.removeInstance("root@hostname:3306") #刪除節點
cluster.removeInstance('root@host:3306',{force:true}) #強制刪除節點
cluster.dissolve({force:true}) #解散叢集
cluster.describe(); #叢集描述
叢集節點狀態
- ONLINE: The instance is online and participating in the cluster.
- OFFLINE: The instance has lost connection to the other instances.
- RECOVERING: The instance is attempting to synchronize with the cluster by retrieving transactions it needs before it can become an ONLINE member.
- UNREACHABLE: The instance has lost communication with the cluster.
- ERROR: The instance has encountered an error during the recovery phase or while applying a transaction
InnoDB Cluster叢集部署中的注意事項
1) 請保證所有的叢集機器在一個子網內，網路必須要通, 不然會失敗；考慮到可以用橋接的方式實現不同網路之間叢集的搭建, 這個並沒有親測；
2) 統一使用hostname進行配置；請更改每臺機器的hosts檔案；
3) 報錯
ERROR: Group Replication join failed. ERROR: Error joining instance to cluster: 'host-192-168-1-101:3306' - Query failed. MySQL Error (3092): The server is not configureroperly to be an active member of the group. Please see more details on error log.. Query: START group_replication 通過如下方式進行處理 mysql> install plugin group_replication soname 'group_replication.so'; ##安裝外掛 mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; mysql> START GROUP_REPLICATION; mysql> select * from performance_schema.replication_group_members;
4) 報錯
Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError) 說明叢集中的主節點已經不在該機器上，查詢後更改機器重試一下即可；
5) 報錯
Dba.getCluster: Dba.getCluster: Unable to get cluster. The instance 'host-192-168-1-101:3306' may belong to a different ReplicaSet as the one registered in the Metadata since the value of 'group_replication_group_name' does not match the one registered in the ReplicaSet's Metadata: possible split-brain scenario. Please connect to another member of the ReplicaSet to get the Cluster. (RuntimeError) 最致命的錯誤，master/slave的資料不一致所致，沒辦法，只能重新來 mysql-js>dba.dropMetadataSchema();
6) 請保證叢集中的資料庫表都存在主鍵，不然會掛掉；
7) 安裝叢集監控，保證叢集中機器掛掉的時候及時啟動，不然所有節點當機的時候就是災難到來之時!!! 到時哭都來不及；
8) 如何重置Innodb cluster叢集環境
主節點： mysql-js>dba.dropMetadataSchema(); 登入mysql-shell清空叢集 mysql> stop group_replication; mysql> reset master; （清空日誌，確保和從庫的表沒有衝突奧，） mysql> reset slave; 其他節點(主要清理和主庫的主從資訊， 確保主庫和從庫的表沒有衝突奧) mysql> stop group_replication; mysql> reset master; mysql> reset slave
9) 主機名和 /etc/hosts中名字不一致
出現報錯: [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'mysql_innodb_cluster_r0430970923@mysql3:3306' - retry-time: 60 retries: 1, Error_code: MY-002005 [ERROR] [MY-011582] [Repl] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.' [ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'
10) 主庫的日誌應用卡在某個位置無法應用到從庫
出現報錯: [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000007' position 151 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_applier': Error executing row event: 'Unknown database 'mysql_innodb_cluster_metadata'', Error_code: MY-001049 重建master： mysql> stop group_replication; mysql> reset master;
11) 報錯
[ERROR] Slave SQL for channel 'group_replication_recovery': Could not execute Write_rows event on table mysql_innodb_cluster_metadata.instances; Cannot add or update a child row: a foreign key constraint fails (mysql_innodb_cluster_metadata.instances, CONSTRAINT instances_ibfk_1 FOREIGN KEY (host_id) REFERENCES hosts (host_id)), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW; the event's master log binlog.000001, end_log_pos 3059, Error_code: 1452 解決方式：清空表mysql_innodb_cluster_metadata.hosts; 重新建立叢集
12) 報錯
This member has more executed transactions than those present in the group 解決方式： mysql> stop group_replication; mysql> reset master;
13) 使用者作業系統資源的限制
[Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000) [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000) 解決方式： # vim /etc/security/limits.conf # 新增下面內容 mysql soft nproc 2047 mysql hard nproc 16384 mysql soft nofile 1024 mysql hard nofile 65535
14) 報錯
dba.rebootClusterFromCompleteOutage: The active session instance isn't the most updated in comparison with the ONLINE instances of the Cluster's metadata. 在叢集沒有起來時某些機器的資料表發生變動，導致資料不一致； 解決方式： 所有MySQL機器通過reset master命令清空binlogs mysql> reset master; mysql> show master logs; 然後再執行Dba.rebootClusterFromCompleteOutage重啟叢集。
15) service mysql restart 無法重啟mysql，mysql stuck，並一直輸出日誌'[Note] Plugin group_replication reported: '[GCS] cli_err 2''
解決方式：唯一停止MySQL的命令為： #pkill -9 mysqld
16) 如何將Multi-Primary改為Single-Primary？
a) 解散原來的叢集：mysql-js> cluster.dissolve({force: true})
b) 每臺主機MySQL修改如下配置：
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;
c) 重新建立叢集：
mysql-js> var cluster = dba.createCluster('mysqlCluster');
mysql-js> cluster.addInstance('chianyu@svr2:3306');
mysql-js> cluster.addInstance('chianyu@svr3:3306');
17) 組複製的限制
- 事物鎖缺失問題：
- 組複製建議，事物隔離級別，read commit
- 序列化隔離級別：多主模式不支援
- 併發DDL和DML: 多主模式下，不支援 一邊對一個表進行DDL,另一邊進行更新，這樣對於DDL在其他例項上操作有未檢出的風險
- 外來鍵級聯約束：多主模式下，多級外來鍵依賴對引起多級操作， 因此可能導致未知衝突，建議開啟 group_replication_enforce_update_everywhere_checks=ON
- 大事物，超過5秒未提交，會導致組通訊失敗，
- 多主模式下：select * for update 會導致 死鎖。因為這個鎖並非全組共享。
- 部分複製不支援：組複製下，設定部分複製，會過濾事物，導致組事物不一致。
- Mysql 8.0.11 group_replication_enforce_update_everywhere_checks=ON 多主模式下不支援。
- 停止複製的情況下，某個節點執行命令後再啟動，會因為本地有私有事物，無法加入叢集。需要全域性 reset master 重新開始叢集複製。
18) 多例項環境不要用 3306埠
多例項環境下，某個例項採用了預設的3306埠，會導致經常性的誤操作。 一臺主機最多部署10個例項 比如: cluster節點A伺服器啟用三個埠例項: 3310, 3320, 3330, cluster節點B伺服器啟用三個埠例項: 3310, 3320, 3330 cluster節點C伺服器啟用三個埠例項: 3310, 3320, 3330 例項資料目錄分別為: /data/mysql3310, /data/mysql3320, /data/mysql3330 管理節點D服務啟動三個埠route埠例項: 3310, 3320, 3330 管理節點E服務啟動三個埠route埠例項: 3310, 3320, 3330 例項資料目錄分別為: /data/router3310, /data/router3320, /data/router3330