MySQL ( MGR ) 11 - InnoDB Cluster 环境升级

1.环境检查

[root@xag213 ~]# mysql -u root -h xag214 -P 6446 -p123456

root@xag214:(none) [:23: ] 2 SQL->SELECT @@server_id;
+-------------+
| @@server_id |
+-------------+
|         211 |
+-------------+
  1. 停主库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. 回到步骤1的会话窗口，测试发现连接未切换到新的主库上
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
  1. 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"
            }
        }
    }
}
  1. 为解决步骤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节点(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/
 
#以下此环境变量如已存在，则跳过
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主从关系状态正常

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

#删除元数据（实际上删除数据库mysql_innodb_cluster_metadata）
MySQL  xag211:3306 ssl  JS > dba.dropMetadataSchema()

--已搭建好MGR主从同步的话，就要加上adoptFromGR: true参数
--createCluster实际上是创建数据库mysql_innodb_cluster_metadata存放元数据
#创建集群
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个节点都要运行下一行命令
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.启动管理节点的route

#进入 xag214 管理节点中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的连接自动断开，达到预期结果，测试结束）

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;
  1. 最后将 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在创建集群和添加节点的时候会出现警告：

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.
这个警告没关系，他告诉我们做集群修改的配置没有持久化，我们再次在每个节点都执行dba.configureLocalInstance()就可以了，他会一大堆group_replication配置信息写入到/etc/my.cnf。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,362评论 5赞 477
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,330评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,247评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,560评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,580评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,569评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,929评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,587评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,840评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,596评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,678评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,366评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,945评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,929评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,165评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 43,271评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,403评论 2赞 342

推荐阅读更多精彩内容