1.環(huán)境檢查
[root@xag213 ~]# mysql -u root -h xag214 -P 6446 -p123456
root@xag214:(none) [:23: ] 2 SQL->SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
| 211 |
+-------------+
- 停主庫group_replication
root@xag211:(none) [:24: ] 1 SQL->select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
| group_replication_applier | 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE |
| group_replication_applier | 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
root@xag211:(none) [:24: ] 2 SQL->stop group_replication;
3.檢查主備切換情況
root@xag212:(none) [:25: ] 5 SQL->SELECT a.member_id,a.member_host,member_port,member_state
-> ,if(b.variable_name is not null,'PRIMARY','SECONDDARY') as MEMBER_ROLE
-> FROM performance_schema.replication_group_members a left join performance_schema.global_status b
-> on b.variable_name='group_replication_primary_member' and b.variable_value=a.member_id;
+--------------------------------------+-------------+-------------+--------------+-------------+
| member_id | member_host | member_port | member_state | MEMBER_ROLE |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE | PRIMARY |
| 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213 | 3306 | ONLINE | SECONDDARY |
+--------------------------------------+-------------+-------------+--------------+-------------+
- 回到步驟1的會話窗口，測試發(fā)現(xiàn)連接未切換到新的主庫上
root@xag214:testdb [:29: ] 10 SQL->SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
| 211 |
+-------------+
root@xag214:testdb [:29: ] 11 SQL->delete from t1;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
- 211重新加入集群中
[root@xag214 ~]# mysqlsh --uri root@xag212:3306
mysql-js> var cluster = dba.getCluster('myCluster')
mysql-js> cluster.addInstance('root@xag211:3306');
mysql-js> cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "xag212:3306",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"xag211:3306": {
"address": "xag211:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"xag212:3306": {
"address": "xag212:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"xag213:3306": {
"address": "xag213:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
- 為解決步驟4中遇到的問題雨涛，故 MySQL-Router8.0升級
[root@xag214 ~]# cd /usr/local/src
[root@xag214 src]# pwd
/usr/local/src
wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.26-linux-glibc2.12-x86_64.tar.xz
wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz
scp mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz root@xag211:/usr/local/src/
scp mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz root@xag212:/usr/local/src/
scp mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz root@xag213:/usr/local/src/
7.安裝
tar -Jxvf mysql-router-8.0.26-linux-glibc2.12-x86_64.tar.xz
tar -zvxf mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz
mv mysql-router-8.0.26-linux-glibc2.12-x86_64 /usr/local/
mv mysql-shell-8.0.26-linux-glibc2.12-x86-64bit /usr/local/
[root@xag214 local]# /usr/local/myrouter/stop.sh
[root@xag214 src]# cd /usr/local
mv mysql-route/ mysql-route2.1.6
mv mysql-shell/ mysql-shell1.0.11
mv mysql-router-8.0.26-linux-glibc2.12-x86_64/ mysql-route
mv mysql-shell-8.0.26-linux-glibc2.12-x86-64bit mysql-shell
[root@xag214 local]# mysqlsh --version
mysqlsh Ver 8.0.26 for Linux on x86_64 - for MySQL 8.0.26 (MySQL Community Server (GPL))
[root@xag214 local]# mysqlrouter --version
MySQL Router Ver 8.0.26 for Linux on x86_64 (MySQL Community - GPL)
8.在三個cluster節(jié)點(211,212,213)安裝和部署 mysql-shell
cd /usr/local
mv mysql-shell/ mysql-shell1.0.11
cd /usr/local/src/
tar -zvxf mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz
mv mysql-shell-8.0.26-linux-glibc2.12-x86-64bit mysql-shell
mv mysql-shell /usr/local/
#以下此環(huán)境變量如已存在枢舶，則跳過
echo "export PATH=$PATH:/usr/local/mysql-shell/bin/" >> /etc/profile
source /etc/profile
[root@xag211 src]# mysqlsh --version
mysqlsh Ver 8.0.26 for Linux on x86_64 - for MySQL 8.0.26 (MySQL Community Server (GPL))
9.檢查目前MGR主從關(guān)系狀態(tài)正常
root@xag211:(none) [:13: ] 1 SQL->SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
| group_replication_applier | 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE |
| group_replication_applier | 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
root@xag211:(none) [:13: ] 2 SQL->SHOW STATUS LIKE 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 2126c7e9-63c9-11eb-a1ba-000c299e2212 |
+----------------------------------+--------------------------------------+
#登錄
mysqlsh --uri root@xag211:3306
#刪除元數(shù)據(jù)（實際上刪除數(shù)據(jù)庫mysql_innodb_cluster_metadata）
MySQL xag211:3306 ssl JS > dba.dropMetadataSchema()
--已搭建好MGR主從同步的話，就要加上adoptFromGR: true參數(shù)
--createCluster實際上是創(chuàng)建數(shù)據(jù)庫mysql_innodb_cluster_metadata存放元數(shù)據(jù)
#創(chuàng)建集群
MySQL xag211:3306 ssl JS > var cluster = dba.createCluster('myCluster', {adoptFromGR: true});
WARNING: Instance 'xag211:3306' cannot persist configuration since MySQL version 5.7.33 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
WARNING: Instance 'xag212:3306' cannot persist configuration since MySQL version 5.7.33 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
WARNING: Instance 'xag213:3306' cannot persist configuration since MySQL version 5.7.33 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
Cluster successfully created based on existing replication group.
#211 & 212 & 213 3個節(jié)點都要運行下一行命令
MySQL xag211:3306 ssl JS > dba.configureLocalInstance()
MySQL xag211:3306 ssl JS > cluster.status();
Cluster.status: Lost connection to MySQL server during query (MYSQLSH 2013)
MySQL xag211:3306 ssl JS > \quit
[root@xag211 src]# mysqlsh --uri root@xag211:3306
MySQL xag211:3306 ssl JS > var cluster = dba.getCluster('myCluster')
MySQL xag211:3306 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "xag212:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"xag211:3306": {
"address": "xag211:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "5.7.33"
},
"xag212:3306": {
"address": "xag212:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "5.7.33"
},
"xag213:3306": {
"address": "xag213:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "5.7.33"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "xag212:3306"
}
11.啟動管理節(jié)點的route
#進入 xag214 管理節(jié)點中mysql-router 安裝目錄替久，配置并啟動 router
cd /usr/local
[root@xag214 local]# pwd
/usr/local
[root@xag214 local]# mv myrouter/ myrouter_bak
/usr/local/mysql-route/bin/mysqlrouter --bootstrap root@xag211:3306 -d myrouter --user=root --force
#然后啟動mysqlroute
[root@xag214 ~]# /usr/local/myrouter/start.sh
[root@xag214 local]# ps -ef|grep myroute
root 1323 1 0 23:16 pts/0 00:00:00 sudo ROUTER_PID=/usr/local/myrouter/mysqlrouter.pid /usr/local/mysql-route/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=root
root 1324 1323 0 23:16 pts/0 00:00:00 /usr/local/mysql-route/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=root
root 1336 1257 0 23:16 pts/0 00:00:00 grep --color=auto myroute
[root@xag214 local]# netstat -tunlp | grep 1324
tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 1324/mysqlrouter
tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 1324/mysqlrouter
tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 1324/mysqlrouter
tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 1324/mysqlrouter
12.1 再次測試
[root@xag213 ~]# mysql -u root -h xag214 -P 6446 -p123456
root@xag214:(none) [:23: ] 2 SQL->SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
| 212 |
+-------------+
root@xag214:(none) [:51: ] 2 SQL->use testdb;
root@xag214:testdb [:51: ] 3 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
+----+----------+
root@xag214:testdb [:51: ] 4 SQL->delete from t1 where Id=1;
12.2 停主庫212 上 group_replication
root@xag212:(none) [:54: ] 2 SQL->stop group_replication;
root@xag211:(none) [:55: ] 1 SQL->SHOW STATUS LIKE 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 2116c7e9-63c9-11eb-a1ba-000c299e2211 |
+----------------------------------+--------------------------------------+
12.3 回到步驟12.1的會話窗口 (原12.1中連接 212的連接自動斷開凉泄，達到預期結(jié)果，測試結(jié)束）
root@xag214:testdb [:51: ] 5 SQL->SELECT @@server_id;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1487
Current database: testdb
+-------------+
| @@server_id |
+-------------+
| 211 |
+-------------+
root@xag214:testdb [:56: ] 6 SQL->delete from t1 where Id=2;
- 最后將 212 再次加入集群
[root@xag214 ~]# mysqlsh --uri root@xag211:3306
mysql-js> var cluster = dba.getCluster('myCluster')
mysql-js> cluster.addInstance('root@xag212:3306');
mysql-js> cluster.status()
再注意一點：mysql57在創(chuàng)建集群和添加節(jié)點的時候會出現(xiàn)警告：
WARNING: Instance 'mysql1:3306' cannot persist configuration since MySQL version 5.7.29 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the <Dba>.configureLocalInstance() command locally to persist the changes.
這個警告沒關(guān)系蚯根，他告訴我們做集群修改的配置沒有持久化后众，我們再次在每個節(jié)點都執(zhí)行dba.configureLocalInstance()就可以了，他會一大堆group_replication配置信息寫入到/etc/my.cnf。
