MySQL ( MGR ) 一 - InnoDB Cluster
2021-02-07 本文已影响0人
轻飘飘D
0.環境
ip地址 主机名 角色 安装软件
192.168.40.211 xag211 节点1 Mysql5.7, mysql-shell
192.168.40.212 xag212 节点1 Mysql5.7, mysql-shell
192.168.40.213 xag213 节点1 Mysql5.7, mysql-shell
192.168.40.214 xag214 管理节点1 mysql-shell, mysql-route
- 克隆 MySQL ( MGR ) 05 后的VM & 修改配置(本次为 211节点，其他212、213同理）
[root@xag200 ~]# service mysqld stop
Shutting down MySQL.... SUCCESS!
[root@xag200 ~]# vim /usr/local/mysql/data/auto.cnf
[root@xag200 ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=2116c7e9-63c9-11eb-a1ba-000c299e2211
[root@xag200 ~]# sed -i 's/127.0.0.1/xag211/' /root/.bashrc
[root@xag200 ~]# source /root/.bash_profile
[root@xag200 ~]# hostname
xag211
[root@xag200 ~]# cat /etc/hostname
xag211
[root@xag200 ~]# cat /etc/sysconfig/network
NETWORKING=yes
NOZEROCONF=yes
HOSTNAME=xag211
[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
- 修改 /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,xag.212: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
3.下載軟件(xag214管理節點上)
[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
[root@xag214 src]# scp mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz root@xag211:/usr/local/src/
[root@xag214 src]# scp mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz root@xag212:/usr/local/src/
[root@xag214 src]# scp mysql-shell-1.0.11-linux-glibc2.12-x86-64bit.tar.gz root@xag213:/usr/local/src/
4.安装MGR插件，设置复制账号（每个节点）
配置完成后, 要一次启动数据库，安装MGR插件，设置复制账号（所有MGR节点都要执行）
systemctl restart mysqld
mysql.login
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
- 在管理节点(xag214)安装mysql shell 和 mysql-route
[root@xag214 src]# pwd
/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
- 创建Innodb Cluster集群
7.1 在 211 上创建集群，通过 2111 上的 shell 连接2111 的 mysql
#登录
mysqlsh --uri root@xag211:3306
mysql-js> dba.configureLocalInstance();
7.2 通过 xag214（管理節點）上 的 mysql-shell 连接 xag211创建 cluster
[root@xag214 src]# mysqlsh --uri root@xag211:3306
# 创建一个 cluster，命名为 'myCluster' （此窗口不能關閉，後續要用）
mysql-js> var cluster = dba.createCluster('myCluster');
mysql-js> cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "xag211:3306",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"xag211:3306": {
"address": "xag211:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
7.3 添加节点 212、213到上面创建的"myCluster"集群中,通过212本机 mysql-shell 对 mysql 进行配置
如下為212上測試，如213則對應修改
[root@xag212 src]# mysqlsh --uri root@xag212:3306
mysql-js> dba.configureLocalInstance();
#檢查（创建cluster集群之前）
mysql-js> dba.checkInstanceConfiguration("root@localhost:3306")
#xag211 上繼續（上次未關閉的窗口）
mysql-js> var cluster = dba.getCluster()
mysql-js> cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "xag211:3306",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"xag211:3306": {
"address": "xag211:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
cluster.addInstance('root@xag212: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"
}
}
}
}
- 启动管理节点的route
进入 db-route01管理节点中mysql-router 安装目录，配置并启动 router
[root@xag214 src]# /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/src/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
会在当前目录下产生mysql-router 目录, 并生成router配置文件，接着把配置文件修改一下
[root@xag214 src]# pwd
/usr/local/src
[root@xag214 src]# ls
myrouter
[root@xag214 myrouter]# pwd
/usr/local/src/myrouter
[root@xag214 myrouter]# cat mysqlrouter.conf #可以修改配置文件, 也可以默认不修改
然后启动mysqlroute
[root@xag214 ~]# /usr/local/src/myrouter/start.sh
[root@xag214 myrouter]# ps -ef|grep myroute
root 10436 1 0 17:25 pts/0 00:00:00 sudo ROUTER_PID=/usr/local/src/myrouter/mysqlrouter.pid /usr/local/mysql-route/bin/mysqlrouter -c /usr/local/src/myrouter/mysqlrouter.conf --user=root
root 10437 10436 0 17:25 pts/0 00:00:00 /usr/local/mysql-route/bin/mysqlrouter -c /usr/local/src/myrouter/mysqlrouter.conf --user=root
root 10449 1253 0 17:25 pts/0 00:00:00 grep --color=auto myroute
[root@xag214 myrouter]# netstat -tunlp|grep 10437
tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 10437/mysqlrouter
tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 10437/mysqlrouter
tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 10437/mysqlrouter
tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 10437/mysqlrouter
- 測試
这样就可以使用MySQL客户端连接router了. 下面验证下连接router:
a) 管理节点本机mysql-shell连接:
[root@xag214 myrouter]# mysqlsh --uri root@localhost:6446
管理节点本机mysql连接:
[root@xag213 src]# mysql -u root -h xag214 -P 6446 -p
测试cluster节点数据同步. 这里选择xag213 节点作为远程客户端连接router
[root@xag213 src]# mysql -u root -h xag214 -P 6446 -p
root@xag214:(none) [:40: ] 1 SQL->show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
+-------------------------------+
root@xag214:(none) [:46: ] 5 SQL->create database testdb default character set utf8mb4 collate utf8mb4_general_ci;
root@xag214:(none) [:46: ] 7 SQL->use testdb;
root@xag214:testdb [:46: ] 8 SQL->CREATE TABLE t1(Id int(11) NOT NULL,TestName varchar(30) NOT NULL DEFAULT '',PRIMARY KEY (Id)) ENGINE=InnoDB;
SQL->
insert into t1 values(1,'a');
insert into t1 values(2,'b');
commit;
root@xag214:testdb [:49: ] 12 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
+----+----------+
- 分别登录三个cluster节点的mysql, 发现测试库testdb已经完成同步了, 其中:
root@xag213:testdb [:52: ] 2 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
+----+----------+
root@xag213:testdb [:52: ] 3 SQL->delete from t1 where Id=2;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
-------------------------------------------------------------------------
root@xag212:testdb [:53: ] 2 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
+----+----------+
2 rows in set (0.00 sec)
root@xag212:testdb [:53: ] 3 SQL->delete from t1 where Id=2;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
-----------------------------------------------------------------------------
root@xag211:testdb [:54: ] 5 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
+----+----------+
root@xag211:testdb [:54: ] 6 SQL->delete from t1 where Id=2;
Query OK, 1 row affected (0.03 sec)
- InnoDB Cluster集群 日常维护命令
比如在上面创建Innodb cluster集群过程中, 再次登录mysqlsh (从客户端远程登录, 或任意节点本地登录), 怎么获得并查看集群状态
[root@xag212 src]# mysqlsh --uri root@xag212:3306
mysql-js> cluster=dba.getCluster();
WARNING: The session is on a Read Only instance.
Write operations on the InnoDB cluster will not be allowed
<Cluster:myCluster>
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"
}
}
}
}
- help
[root@xag214 myrouter]# mysqlsh --uri root@xag211:3306
mysql-js> dba.help();
The global variable 'dba' is used to access the AdminAPI functionality and
perform DBA operations. It is used for managing MySQL InnoDB clusters.
The following properties are currently supported.
- verbose Enables verbose mode on the Dba operations.
The following functions are currently supported.
- checkInstanceConfiguration Validates an instance for cluster usage.
- configureLocalInstance Validates and configures an instance for
cluster usage.
- createCluster Creates a MySQL InnoDB cluster.
- deleteSandboxInstance Deletes an existing MySQL Server instance on
localhost.
- deploySandboxInstance Creates a new MySQL Server instance on
localhost.
- dropMetadataSchema Drops the Metadata Schema.
- getCluster Retrieves a cluster from the Metadata Store.
- help Provides help about this class and it's
members
- killSandboxInstance Kills a running MySQL Server instance on
localhost.
- rebootClusterFromCompleteOutage Brings a cluster back ONLINE when all
members are OFFLINE.
- resetSession Sets the session object to be used on the
Dba operations.
- startSandboxInstance Starts an existing MySQL Server instance on
localhost.
- stopSandboxInstance Stops a running MySQL Server instance on
localhost.
For more help on a specific function use: dba.help('<functionName>')
e.g. dba.help('deploySandboxInstance')
root@xag211:testdb [:44: ] 9 SQL-> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2119f22b-6136-11eb-a433-000c29905211 | xag211 | 3306 | ONLINE |
| group_replication_applier | 2129f22b-6136-11eb-a433-000c29905212 | xag212 | 3306 | ONLINE |
| group_replication_applier | 2139f22b-6136-11eb-a433-000c29905213 | 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='grooup_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 |
+--------------------------------------+-------------+-------------+--------------+-------------+
dba.checkInstanceConfiguration("root@hostname:3306") #检查节点配置实例，用于加入cluster之前
dba.rebootClusterFromCompleteOutage('myCluster'); #重启
dba.dropMetadataSchema(); #删除schema
var cluster = dba.getCluster('myCluster') #获取当前集群
cluster.checkInstanceState("root@hostname:3306") #检查cluster里节点状态
cluster.rejoinInstance("root@hostname:3306") #重新加入节点，我本地测试的时候发现rejoin一直无效，每次是delete后
addcluster.dissolve({force：true}) #删除集群
cluster.addInstance("root@hostname:3306") #增加节点
cluster.removeInstance("root@hostname:3306") #删除节点
cluster.removeInstance('root@host:3306',{force:true}) #强制删除节点
cluster.dissolve({force:true}) #解散集群
cluster.describe(); #集群描述
集群节点状态
- ONLINE: The instance is online and participating in the cluster.
- OFFLINE: The instance has lost connection to the other instances.
- RECOVERING: The instance is attempting to synchronize with the cluster by retrieving transactions it needs before it can become an ONLINE member.
- UNREACHABLE: The instance has lost communication with the cluster.
- ERROR: The instance has encountered an error during the recovery phase or while applying a transaction
