MySQL InnoDB Cluster 是最新GA的MySQL高可用方案，利用MySQL Group Replication和MySQL Shell、MySQL Router可以輕鬆搭建強壯的高可用方案。
MySQL Shell 是新的mysql 客戶端工具支持x protocol和mysql protocol，具備JavaScript和python可編程能力，作為搭建InnoDB Cluster管理工具。
MySQL Router 是訪問路由轉發中間件，提供應用程序訪問的failover能力。
MySQL Group Replication 是最新GA的同步複製方式，具有以下特點：
- 支持單主和多主模式
- 基於Paxos算法，實現數據複製的一致性
- 插件化設計，支持插件檢測，新增節點小於集群當前節點主版本號，拒絕加入集群，大於則加入，但無法作為主節點
- 沒有第三方組件依賴
- 支持全鏈路SSL通訊
- 支持IP白名單
- 不依賴網絡多播
如何搭建MySQL InnoDB Cluster
安裝mysql server和mysql-shell
rpm -ivh
yum install -y mysql-community-server mysql-shell
啟動mysql服務，並修改root密碼
[root@10-186-23-95 ~]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[root@10-186-23-95 ~]# mysql -p$(awk '/temporary password/{print $NF}' /var/log/mysqld.log) -e "set password='Actionsky@888'"
如果系統python版本低於2.7，需要升級至2.7，mysqlprovsion命令需要
sh -c 'wget -qO-
http://people.redhat.com/bkabrda/scl_python27.repo >>
/etc/yum.repos.d/scl.repo'
yum install -y python27
scl enable python27 bash
python --version
配置Group Replication模式，
dba.configureLocalInstance 會設置必要配置參數並持久化配置
[root@10-186-23-95 ~]# mysqlsh
Welcome to MySQL Shell 1.0.9
Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.
Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> dba.configureLocalInstance('root@localhost:3306')
Please provide the password for 'root@localhost:3306':
Detecting the configuration file...
Found configuration file at standard location: /etc/my.cnf
Do you want to modify this file? [Y|n]:
Validating instance...
The configuration has been updated but it is required to restart the server.
{
"config_errors": [
{
"action": "restart",
"current": "OFF",
"option": "enforce_gtid_consistency",
"required": "ON"
},
{
"action": "restart",
"current": "OFF",
"option": "gtid_mode",
"required": "ON"
},
{
"action": "restart",
"current": "0",
"option": "log_bin",
"required": "1"
},
{
"action": "restart",
"current": "0",
"option": "log_slave_updates",
"required": "ON"
},
{
"action": "restart",
"current": "FILE",
"option": "master_info_repository",
"required": "TABLE"
},
{
"action": "restart",
"current": "FILE",
"option": "relay_log_info_repository",
"required": "TABLE"
},
{
"action": "restart",
"current": "OFF",
"option": "transaction_write_set_extraction",
"required": "XXHASH64"
}
],
"errors": [],
"restart_required": true,
"status": "error"
}
mysql-js> \q
重啟mysql檢查配置是OK
[root@10-186-23-95 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@10-186-23-95 ~]# mysqlsh
Welcome to MySQL Shell 1.0.9
Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.
Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
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"
}
注意：以上操作在每個數據庫節點都需要做一遍。
創建InnoDB Cluster，執行dba.createCluster('mycluster')會包含以下操作
- 在連接的實例上創建mysql.mysql_innodb_cluster_metadata存儲元數據信息
- 驗證配置信息
- 將此節點註冊成seed節點
- 創建必要的管理賬號
- 啟動 Group Replication
mysql-js> \c [email protected] #必須連接某個數據節點
Creating a Session to '[email protected]'
Enter password:
Classic Session successfully established. No default schema selected.
mysql-js> dba.createCluster('mycluster')
A new InnoDB cluster will be created on instance '[email protected]:3306'.
Creating InnoDB cluster 'mycluster' on '[email protected]: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.
mysql-js> var cluster=dba.getCluster('mycluster')
mysql-js> cluster.addInstance('[email protected]: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 '[email protected]:3306':
Adding instance to the cluster ...
The instance '[email protected]:3306' was successfully added to the cluster.
mysql-js> cluster.addInstance('[email protected]: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 '[email protected]:3306':
Adding instance to the cluster ...
The instance '[email protected]:3306' was successfully added to the cluster.
mysql-js> cluster.status()
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "10.186.23.95:3306",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"10.186.23.94:3306": {
"address": "10.186.23.94:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.186.23.95:3306": {
"address": "10.186.23.95:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.186.23.96:3306": {
"address": "10.186.23.96:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
安裝配置MySQL Router
[root@10-186-23-97 ~]# yum install -y mysql-router
[root@10-186-23-97 ~]# mysqlrouter --bootstrap [email protected]:3306 --user=mysqlrouter
Please enter MySQL password for root:
Bootstrapping system MySQL Router instance...
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
[root@10-186-23-97 ~]# mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password:
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.9
Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.
Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@hostname;
+----------------------------+
| @@hostname |
+----------------------------+
| 10-186-23-94.actionsky.com |
+----------------------------+
1 row in set (0.00 sec)
可能遇到的問題
- 節點gtid 異常
如果節點在加入集群前，執行了寫操作，加入集群時會報錯
Please provide the password for '[email protected]:3306':
Adding instance to the cluster ...
Cluster.addInstance: WARNING: Not running locally on the server and can not access its error log.
ERROR:
Group Replication join failed.
ERROR: Error joining instance to cluster: '10.186.23.96@3306' - Query failed. 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication (RuntimeError)
節點的錯誤日誌中會出現
2017-05-09T06:49:57.301003Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 605da5eb-347d-11e7-b68b-bef8d5ac5be4:1,
cf2fe6ca-3460-11e7-aab3-bef8d5ac5be4:1-7 > Group transactions: 8399a91c-3483-11e7-b68b-bef8d5ac5be4:1-5,
cf2fe6ca-3460-11e7-aab3-bef8d5ac5be4:1-15'
解決辦法，登錄到此節點執行reset master
- python版本異常
第一次安裝時沒有檢查python版本，配置實例時出錯，但未輸出具體錯誤。
mysql-py> dba.configure_local_instance('root@localhost:3306')
Please provide the password for 'root@localhost:3306':
Detecting the configuration file...
Found configuration file at standard location: /etc/my.cnf
Do you want to modify this file? [Y|n]: Y
Validating instance...
The issues above can be fixed dynamically to get the server ready for InnoDB Cluster.
{
"errors": [],
"restart_required": false,
"status": "error"
}
後來通過查看mysqlsh的日誌，發現錯誤原因
less ~/.mysqlsh/mysqlsh.log
...
2017-05-09 04:24:27: Error: DBA: mysqlprovision exited with error code (1) : ERROR: The __main__ gadget requires Python version 2.7.0 or higher and lower than 4.0.0. The version of Python detected was 2.6.6. You may need to install or redirect the execution of this utility to an environment that includes a compatible Python version.
...
- 重啟節點後需要手動重新加入集群
mysql-js> var cluster = dba.getCluster()
mysql-js> cluster.status()
{
...
"10.186.23.96:3306": {
"address": "10.186.23.96:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
}
}
}
}
mysql-js> cluster.rejoinInstance('[email protected]:3306')
mysql-js> cluster.status()
{
...
"10.186.23.96:3306": {
"address": "10.186.23.96:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
- 集群所有節點發生重啟
當集群的所有節點都offline，直接獲取集群信息失敗，如何重新恢復集群
mysql-js> var cluster=dba.getCluster('mycluster')
Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError)
執行
rebootClusterFromCompleteOutage命令，可恢復集群
mysql-js> dba.rebootClusterFromCompleteOutage('mycluster')
Reconfiguring the cluster 'mycluster' from complete outage...
The instance '10.186.23.96:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y|N]: y
The instance '10.186.23.94:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y|N]: y
The cluster was successfully rebooted.
- 腦裂場景
當集群中有部分節點出現UNREACHABLE狀態，此時集群無法做出決策，，會出現以下局面，此時只剩下一個活躍節點，此節點只能提供查詢，無法寫入，執行寫入操作會hang住。
mysql-js> cluster.status()
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "10.186.23.94:3306",
"status": "NO_QUORUM",
"statusText": "Cluster has no quorum as visible from '10.186.23.94:3306' and cannot process write transactions. 2 members are not active",
"topology": {
"10.186.23.94:3306": {
"address": "10.186.23.94:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.186.23.95:3306": {
"address": "10.186.23.95:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "UNREACHABLE"
},
"10.186.23.96:3306": {
"address": "10.186.23.96:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
}
}
}
}
修復這種狀態，需要執行
forceQuorumUsingPartitionOf指定當前活躍節點(如果是多個則選擇primary node)，此時活躍節點可以提供讀寫操作，然後將其他節點加入此集群。
mysql-js> cluster.forceQuorumUsingPartitionOf('[email protected]:3306')
Restoring replicaset 'default' from loss of quorum, by using the partition composed of [10.186.23.94:3306]
Please provide the password for '[email protected]:3306':
Restoring the InnoDB cluster ...
The InnoDB cluster was successfully restored using the partition from the instance '[email protected]:3306'.
WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset are removed or joined back to the group that was restored.
mysql-js> cluster.status()
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "10.186.23.94:3306",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 2 members are not active",
"topology": {
"10.186.23.94:3306": {
"address": "10.186.23.94:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.186.23.95:3306": {
"address": "10.186.23.95:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
},
"10.186.23.96:3306": {
"address": "10.186.23.96:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
}
}
}
}
mysql-js> cluster.rejoinInstance('[email protected]:3306')
mysql-js> cluster.rejoinInstance('[email protected]:3306')
節點有哪狀態
- ONLINE - 節點狀態正常。
- OFFLINE - 實例在運行，但沒有加入任何Cluster。
- RECOVERING - 實例已加入Cluster，正在同步數據。
- ERROR - 同步數據發生異常。
- UNREACHABLE - 與其他節點通訊中斷，可能是網絡問題，可能是節點crash。
- MISSING 節點已加入集群，但未啟動group replication
集群有哪些狀態
- OK – 所有節點處於online狀態，有冗餘節點。
- OK_PARTIAL – 有節點不可用，但仍有冗餘節點。
- OK_NO_TOLERANCE – 有足夠的online節點，但沒有冗餘，例如：兩個節點的Cluster，其中一個掛了，集群就不可用了。
- NO_QUORUM – 有節點處於online狀態，但達不到法定節點數，此狀態下Cluster無法寫入，只能讀取。
- UNKNOWN – 不是online或recovering狀態，嘗試連接其他實例查看狀態。
- UNAVAILABLE – 組內節點全是offline狀態，但實例在運行，可能實例剛重啟還沒加入Cluster。
最後附一張集群狀態圖，restoreFromCompleteOutage在GA版本改成
rebootClusterFromCompleteOutage。
