美文网首页
MySQL ( MGR ) 11 - InnoDB Cluste

MySQL ( MGR ) 11 - InnoDB Cluste

作者: 轻飘飘D | 来源:发表于2021-08-26 23:06 被阅读0次

    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。

    相关文章

      网友评论

          本文标题：MySQL ( MGR ) 11 - InnoDB Cluste

          本文链接：https://www.haomeiwen.com/subject/qvtpiltx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|MySQL ( MGR ) 11 - InnoDB Cluste|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！