美文网首页
MySQL ( MGR ) 09 - 现有的复制组迁移到Inno

MySQL ( MGR ) 09 - 现有的复制组迁移到Inno

作者: 轻飘飘D | 来源:发表于2021-02-05 00:10 被阅读0次
    1. 克隆 MySQL ( MGR ) 01 后的VM & 修改配置(本次为 214节点）
    [root@xag200 ~]# hostname
xag214

[root@xag200 ~]# cat /etc/hostname
xag214

[root@xag200 ~]# cat /etc/sysconfig/network
NETWORKING=yes
NOZEROCONF=yes
HOSTNAME=xag214

[root@xag200 ~]# cat /etc/hosts
...
192.168.40.211 xag211 xag211
192.168.40.212 xag212 xag212
192.168.40.213 xag213 xag213
192.168.40.214 xag214 xag214

[root@xag200 ~]# cat  /etc/sysconfig/network-scripts/ifcfg-ens33
。。。
IPADDR="192.168.40.211"
。。。

[root@xag200 ~]# reboot
    1. 在管理节点(xag214)安装mysql shell 和 mysql-route
    [root@xag214 src]# pwd
/usr/local/src

wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz

wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-2.1.6-linux-glibc2.12-x86-64bit.tar.gz

scp mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz root@xag211:/usr/local/src/
scp mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz root@xag212:/usr/local/src/
scp mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz root@xag213:/usr/local/src/

[root@xag214 src]# ll
-rw-r--r-- 1 root root 15630562 Feb  3 21:47 mysql-router-2.1.6-linux-glibc2.12-x86-64bit.tar.gz
-rw-r--r-- 1 root root  6584245 Feb  3 21:47 mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz

tar -zvxf mysql-router-2.1.6-linux-glibc2.12-x86-64bit.tar.gz
tar -zvxf mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz

mv mysql-router-2.1.6-linux-glibc2.12-x86-64bit mysql-route
mv mysql-shell-1.0.11-linux-glibc2.12-x86-64bit mysql-shell

mv mysql-route /usr/local/
mv mysql-shell /usr/local/

vim /etc/profile
..............
export PATH=$PATH:/usr/local/mysql-shell/bin/:/usr/local/mysql-route/bin/
 
source /etc/profile
 
[root@xag214 local]# mysqlprovision --version
mysqlprovision version 2.1.0

[root@xag214 local]# mysqlsh --version
mysqlsh   Ver 1.0.11 for Linux on x86_64 - for MySQL 5.7.20 (MySQL Community Server (GPL))

[root@xag214 local]# mysqlrouter --version
MySQL Router v2.1.6 on Linux (64-bit) (GPL community edition)
    1. 在三个cluster节点(211,212,213)安装和部署 mysql-shell
    cd /usr/local/src/

[root@xag211 src]# ll
-rw-r--r-- 1 root root  6584245 Feb  3 21:47 mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz

tar -zvxf mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz

mv mysql-shell-1.0.11-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
 
mysqlprovision --version                          
 
mysqlsh --version

    4.创建Innodb Cluster集群
    4.1 修改 /etc/my.cnf 中 #复制框架设置 及 #组复制设置 如下（xag212、xag213类似）
    各个MGR节点除了server_id、loose-group_replication_local_address、report_host 三个参数不一样外，其他保持一致。

    #复制框架设置
server-id = 211
# 表示本机的序列号为1,如果做主从，或者多实例,serverid一定不能相同。
log-bin = /usr/local/mysql/binlog/binlog 
# logbin数据库的操作日志,例如update、delete、create等都会存储到binlog日志,通过logbin可以实现增量恢复
binlog_format = row
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_slave_updates=ON
master_info_repository =TABLE
relay_log_info_repository=TABLE
 
#组复制设置
#server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
transaction_write_set_extraction=XXHASH64
#告知插件加入或创建组命名，UUID
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#server启动时不自启组复制,为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_start_on_boot=off
#告诉插件使用IP地址，端口24901用于接收组中其他成员转入连接
loose-group_replication_local_address="xag211:24901"
#启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意
loose-group_replication_group_seeds="xag211:24901,xag212:24901,xag213:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode = on 
loose-group_replication_allow_local_disjoint_gtids_join=on
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
report_host=xag211
report_port=3306

    4.2 在 211 上创建集群，通过 211 上的 shell 连接211 的 mysql

    root@xag211:(none) [:43: ] 10 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       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

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 |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 2119f22b-6136-11eb-a433-000c29905211 | xag211      |        3306 | ONLINE       | PRIMARY     |
| 2129f22b-6136-11eb-a433-000c29905212 | xag212      |        3306 | ONLINE       | SECONDARY   |
| 2139f22b-6136-11eb-a433-000c29905213 | xag213      |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+-------------+-------------+--------------+-------------+

#登录
mysqlsh --uri root@xag211:3306

#创建集群
mysql-js> var cluster = dba.createCluster('myCluster', {adoptFromGR: true});
--------------------------------------------------------------------------------------------------------
A new InnoDB cluster will be created on instance 'root@xag211:3306'.

Creating InnoDB cluster 'myCluster' on 'root@xag211: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> cluster.status();
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "xag211:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "xag211:3306": {
                "address": "xag211:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "xag212:3306": {
                "address": "xag212:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "xag213:3306": {
                "address": "xag213:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
-------------------------------------------------------------------------------------------

执行成功后会自动创建一个新的数据库: mysql_innodb_cluster_metadata, 
用于存放集群的元数据, 该元数据会被MySQL Router用到, 以实现高可用性.
    1. 启动管理节点的route
    进入 xag214 管理节点中mysql-router 安装目录，配置并启动 router

[root@xag214 local]# pwd
/usr/local

[root@xag214 local]# /usr/local/mysql-route/bin/mysqlrouter --bootstrap root@xag211:3306 -d myrouter --user=root
----------------------------------------------------------------------------------------------------------
Please enter MySQL password for root: 

Bootstrapping MySQL Router instance at /usr/local/myrouter...
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@xag214 local]# ls
bin  etc  games  include  lib  lib64  libexec  myrouter  mysql  mysql-route  mysql-shell  sbin  servers  share  src

会在当前目录下产生mysql-router 目录, 并生成router配置文件，接着把配置文件修改一下
[root@xag214 local]# pwd
/usr/local

[root@xag214 local]# ls myrouter
data  log  mysqlrouter.conf  mysqlrouter.key  run  start.sh  stop.sh

[root@xag214 myrouter]# pwd
/usr/local/src/myrouter

[root@xag214 local]# cat /usr/local/myrouter/mysqlrouter.conf
#可以修改配置文件, 也可以默认不修改

然后启动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

    6.测试

    [root@xag211 ~]# mysql -u root -h xag214 -P 6446 -p

root@xag214:(none) [:21: ] 1 SQL->use testdb

root@xag214:testdb [:21: ] 2 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
|  1 | a        |
|  2 | b        |
+----+----------+

root@xag214:testdb [:21: ] 3 SQL->delete from t1 where Id=2;
Query OK, 1 row affected (0.01 sec)

---------------------------------------------------------------------------------------------
[root@xag211 ~]# mysql -u root -h xag214 -P 6447 -p

root@xag214:(none) [:22: ] 1 SQL->use testdb

root@xag214:testdb [:22: ] 2 SQL->delete from t1 where Id=1;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
    1. 停止集群 & 停 myroute & 停 mysql
    root@xag211:testdb [:25: ] 21 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@xag214 local]# mysqlsh --uri root@xag211:3306

mysql-js> \sql
Switching to SQL mode... Commands end with ;

mysql-sql> stop group_replication;

root@xag211:testdb [:25: ] 22 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 | OFFLINE      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

root@xag211:testdb [:30: ] 23 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
|  1 | a        |
+----+----------+


[root@xag214 local]# /usr/local/myrouter/stop.sh 

[root@xag214 local]# ps -ef|grep myroute
root       1348   1257  0 23:24 pts/0    00:00:00 grep --color=auto myroute


# 各节点停 mysql 
service mysqld stop
    1. 启动InnoDB Cluster
    # 各节点启动 mysql 
service mysqld start

#使用mysql shell 连接集群服务器
[root@xag214 ~]# mysqlsh --uri root@xag211:3306

#重启集群
dba.rebootClusterFromCompleteOutage('myCluster'); 
var cluster = dba.getCluster('myCluster')                #获取当前集群
or

mysql-js> var cluster = dba.rebootClusterFromCompleteOutage();
------------------------------------------------------------------------------------------------
Reconfiguring the default cluster from complete outage...

The instance 'xag212:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y|N]: y

The instance 'xag213:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y|N]: y

The MySQL instance at 'xag211:3306' currently has the super_read_only 
system variable set to protect it from inadvertent updates from applications. 
You must first unset it to be able to perform any changes to this instance. 
For more information see: https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_super_read_only.

Note: there are open sessions to 'xag211:3306'.
You may want to kill these sessions to prevent them from performing unexpected updates: 

1 open session(s) of 'root@xag211'. 

1 open session(s) of 'root@xag214'. 

Do you want to disable super_read_only and continue? [y|N]: y


The cluster was successfully rebooted.
------------------------------------------------------------------------------------------------------------
mysql-js> cluster.status()
-----------------------------------------------------------------------------
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "xag211:3306", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 2 members are not active", 
        "topology": {
            "xag211:3306": {
                "address": "xag211:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "xag212:3306": {
                "address": "xag212:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "xag213:3306": {
                "address": "xag213:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }
        }
    }
}
----------------------------------------------------------------------------------

#如果查询节点状态为：
 "status": "(MISSING)"
#重新加入命令
var cluster = dba.getCluster('myCluster')  
cluster.rejoinInstance("root@xag212:3306")
#如果rejoinInstance失败，提示remove重新添加如下：
cluster.removeInstance('root@xag212:3306');
cluster.addInstance('root@xag212:3306');

#如果rejoinInstance失败，提示remove重新添加如下：
cluster.removeInstance('root@xag213:3306');
cluster.addInstance('root@xag213:3306');

mysql-js> cluster.status()
------------------------------------------------------------------------------------
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "xag211:3306", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "xag211:3306": {
                "address": "xag211:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "xag212:3306": {
                "address": "xag212:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "xag213:3306": {
                "address": "xag213:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
-----------------------------------------------------------------------
然后启动mysqlroute
[root@xag214 ~]# /usr/local/myrouter/start.sh

[root@xag211 ~]# mysql -u root -h xag214 -P 6446 -p

root@xag214:(none) [:21: ] 1 SQL->use testdb

root@xag214:testdb [:09: ] 2 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
|  1 | a        |
+----+----------+

    cat /usr/lib/systemd/system/mysqlrouter.service

    [Unit]
Description=MYSQL Router
After=syslog.target
After=network.target
 
[Service]
Type=simple
User=root
Group=root
  
ExecStart=/usr/local/mysql-route/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf
PrivateTmp=true
LimitNOFILE = 65535

[Install]
WantedBy=multi-user.target

    [root@c7-node1 ~]# systemctl enable mysqlrouter
[root@c7-node1 ~]# systemctl start mysqlrouter

    相关文章

      网友评论

          本文标题：MySQL ( MGR ) 09 - 现有的复制组迁移到Inno

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

          延伸阅读

          深度阅读

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

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|MySQL ( MGR ) 09 - 现有的复制组迁移到Inno|投稿指南|网站地图|RSS订阅|排版工具|手机版

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

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

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

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

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