1.老數據庫2和3為新數據庫創(chuàng)建3個服務集群
3 回答
白衣非少年
TA貢獻1155條經驗 獲得超0個贊
沒有看懂你的意思，如果是想創(chuàng)建獨立的三個集群，是不可以的，cluster的集群主要分三部分，管理節(jié)點、計算節(jié)點、存儲節(jié)點，其中所有的管理操作都在管理節(jié)點上進行，如果你已經有了一個集群了，該集群下有一個數據庫，創(chuàng)建新的數據庫只不過是給數據庫增加了兩個實例（oracle的叫法，實例），而不是新創(chuàng)建了兩個集群。
如果想新創(chuàng)建集群，需要保證有新的管理節(jié)點、計算節(jié)點和存儲節(jié)點。在此給你提個建議，盡量不要把多個項目的數據庫放在同一個集群下，很麻煩的，我們公司的項目就因為甲方資源緊張，沒辦法，三個項目用了一個cluster的集群，結果只要其中一個項目使用數據庫資源過大，就會把三個項目一起影響。
所以如果資源足夠的話，盡量拆成多個集群。
FFIVE
TA貢獻1797條經驗 獲得超6個贊
MYSQLcluster集群，在sql節(jié)點寫入，是如何把數據存儲到數據節(jié)點的？
數據節(jié)點實際就是單個的數據庫實例而已，所以數據存儲和一般實例沒有太多區(qū)別，如果你的意思是怎么保證數據的存儲一致性，那這個話就多了，不過，其實當做master-slave的高級模式來理解就好了，只是沒有使用binlog的動態(tài)轉換分發(fā)而已
慕桂英3389331
TA貢獻2036條經驗 獲得超8個贊
如何搭建MySQL InnoDB Cluster
安裝mysql server和mysql-shell
rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el6-10.noarch.rpm
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'"
如果系統(tǒng)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...
注意：以上操作在每個數據庫節(jié)點都需要做一遍。
創(chuàng)建InnoDB Cluster，執(zhí)行dba.createCluster('mycluster')會包含以下操作
在連接的實例上創(chuàng)建mysql.mysql_innodb_cluster_metadata存儲元數據信息
驗證配置信息
將此節(jié)點注冊成seed節(jié)點
創(chuàng)建必要的管理賬號
啟動 Group Replication
節(jié)點gtid 異常
python版本異常
重啟節(jié)點后需要手動重新加入集群
集群所有節(jié)點發(fā)生重啟
腦裂場景
ONLINE - 節(jié)點狀態(tài)正常。
OFFLINE - 實例在運行，但沒有加入任何Cluster。
RECOVERING - 實例已加入Cluster，正在同步數據。
ERROR - 同步數據發(fā)生異常。
UNREACHABLE - 與其他節(jié)點通訊中斷，可能是網絡問題，可能是節(jié)點crash。
MISSING 節(jié)點已加入集群，但未啟動group replication
OK – 所有節(jié)點處于online狀態(tài)，有冗余節(jié)點。
OK_PARTIAL – 有節(jié)點不可用，但仍有冗余節(jié)點。
OK_NO_TOLERANCE – 有足夠的online節(jié)點，但沒有冗余，例如：兩個節(jié)點的Cluster，其中一個掛了，集群就不可用了。
NO_QUORUM – 有節(jié)點處于online狀態(tài)，但達不到法定節(jié)點數，此狀態(tài)下Cluster無法寫入，只能讀取。
UNKNOWN – 不是online或recovering狀態(tài)，嘗試連接其他實例查看狀態(tài)。
UNAVAILABLE – 組內節(jié)點全是offline狀態(tài)，但實例在運行，可能實例剛重啟還沒加入Cluster。
mysql-js> \c root@10.186.23.95 #必須連接某個數據節(jié)點
Creating a Session to 'root@10.186.23.95'
Enter password:
Classic Session successfully established. No default schema selected.
mysql-js> dba.createCluster('mycluster')
A new InnoDB cluster will be created on instance 'root@10.186.23.95:3306'.
Creating InnoDB cluster 'mycluster' on 'root@10.186.23.95: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:mycluster>
mysql-js> var cluster=dba.getCluster('mycluster')
mysql-js> cluster.addInstance('root@10.186.23.96:3306') #添加其他節(jié)點
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@10.186.23.96:3306':
Adding instance to the cluster ...
The instance 'root@10.186.23.96:3306' was successfully added to the cluster.
mysql-js> cluster.addInstance('root@10.186.23.94: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@10.186.23.94:3306':
Adding instance to the cluster ...
The instance 'root@10.186.23.94: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 root@10.186.23.94: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)
可能遇到的問題
如果節(jié)點在加入集群前，執(zhí)行了寫操作，加入集群時會報錯
Please provide the password for 'root@10.186.23.96: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)
節(jié)點的錯誤日志中會出現(xiàn)
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'
解決辦法，登錄到此節(jié)點執(zhí)行reset master
第一次安裝時沒有檢查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的日志，發(fā)現(xiàn)錯誤原因
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('root@10.186.23.96:3306')
mysql-js> cluster.status()
{
...
"10.186.23.96:3306": {
"address": "10.186.23.96:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
當集群的所有節(jié)點都offline，直接獲取集群信息失敗，如何重新恢復集群
mysql-js> var cluster=dba.getCluster('mycluster')
Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError)
執(zhí)行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.
當集群中有部分節(jié)點出現(xiàn)UNREACHABLE狀態(tài)，此時集群無法做出決策，，會出現(xiàn)以下局面，此時只剩下一個活躍節(jié)點，此節(jié)點只能提供查詢，無法寫入，執(zhí)行寫入操作會hang住。
修復這種狀態(tài)，需要執(zhí)行forceQuorumUsingPartitionOf指定當前活躍節(jié)點(如果是多個則選擇primary node)，此時活躍節(jié)點可以提供讀寫操作，然后將其他節(jié)點加入此集群。
節(jié)點有哪狀態(tài)
集群有哪些狀態(tài)
最后附一張集群狀態(tài)圖，restoreFromCompleteOutage在GA版本改成rebootClusterFromCompleteOutage。
添加回答
舉報
0/150
提交
取消