- 克隆 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
- 在管理节点(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)
- 在三个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用到, 以实现高可用性.
- 启动管理节点的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
- 停止集群 & 停 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
- 启动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
