我是靠谱客的博主 暴躁蜻蜓，最近开发中收集的这篇文章主要介绍Centos7.5 基于MySQL5.7 的 InnoDB Cluster 高可用环境部署1 环境准备2. 在管理节点安装mysql shell 和 mysql-route3. 在三个cluster节点安装和部署Mysql5.7及 mysql-shell4. 创建Innodb Cluster集群5. 启动管理节点的route6. InnoDB Cluster集群 日常维护命令，觉得挺不错的，现在分享给大家，希望可以做个参考。
概述
下面部署采用InnoDB Cluster, 每台服务器实例都运行MySQL Group Replication (即冗余复制机制，内置failover), MGR有两种模式，一种是Single-Primary，一种是Multi-Primary，即单主或者多主。
需求注意：模式Multi-Primary中，所有的节点都是主节点，都可以同时被读写，看上去这似乎更好，但是因为多主的复杂性，在功能上如果设置了多主模式，则会有一些使用的限制，比如不支持Foreign Keys with Cascading Constraints。
1 环境准备
准备了4台centos7版本的服务器用来部署innodb cluster多节点集群环境 (至少也要需要3台服务器), 其中:
1) host-192-169-106-11、host-192-169-106-12、host-192-169-106-13 作为 cluster 节点服务器, 三个节点都要安装 mysql5.7.x 与 mysql-shell
2) host-192-169-106-11 作为管理节点服务器，用来负责创建 cluster，并作为 cluster 的路由, 该节点需要安装mysql-shell、mysql-router
3) 所有节点的python版本要在2.7以上
ip地址 主机名 角色 安装软件
192.169.106.11 host-192-169-106-11 管理节点1+cluster节点1 Mysql5.7, mysql-shell，mysql-route
192.169.106.12 host-192-169-106-12 cluster节点2 Mysql5.7, mysql-shell
192.169.106.13 host-192-169-106-13 cluster节点3
[root@host-192-169-106-11 ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
[root@host-192-169-106-11 ~]# python -V
Python 2.7.5
配置每个节点的/etc/hosts主机映射, 方便通过节点的hostname进行连接
这一步很重要，否则可能会出现无法同步的情况，因为数据库需要根据member_host同步，如果不配置，默认就是localhost，这样时无法通信的!!!
..............
192.169.106.11 host-192-169-106-11
192.169.106.12 host-192-169-106-12
192.169.106.13 host-192-169-106-13
所有节点进行如下的相关优化配置
[root@host-192-169-106-11 ~]# cat>>/etc/sysctl.conf <<EOF
> fs.aio-max-nr = 1048576
> fs.file-max = 681574400
> kernel.shmmax = 137438953472
> kernel.shmmni = 4096
> kernel.sem = 250 32000 100 200
> net.ipv4.ip_local_port_range = 9000 65000
> net.core.rmem_default = 262144
> net.core.rmem_max = 4194304
> net.core.wmem_default = 262144
> net.core.wmem_max = 1048586
> EOF
[root@host-192-169-106-11 ~]# sysctl -p
[root@host-192-169-106-11 ~]# cat>>/etc/security/limits.conf <<EOF
> mysql soft nproc 65536
> mysql hard nproc 65536
> mysql soft nofile 65536
> mysql hard nofile 65536
> EOF
[root@host-192-169-106-11 ~]# cat>>/etc/pam.d/login <<EOF
> session required /lib/security/pam_limits.so
> session required pam_limits.so
> EOF
[root@host-192-169-106-11 ~]# cat>>/etc/profile<<EOF
> if [ $USER = "mysql" ]; then
> ulimit -u 16384 -n 65536
> fi
> EOF
[root@host-192-169-106-11 ~]# source /etc/profile
2. 在管理节点安装mysql shell 和 mysql-route
[root@host-192-169-106-11 src]# ll
总用量 21648
-rw-r--r--. 1 root root 15526654 8月 28 09:52 mysql-router-2.1.4-linux-glibc2.12-x86-64bit.tar.gz
-rw-r--r--. 1 root root 6635831 8月 28 09:52 mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz
[root@host-192-169-106-11 src]# tar -zvxf mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz
[root@host-192-169-106-11 src]# tar -zvxf mysql-router-2.1.4-linux-glibc2.12-x86-64bit.tar.gz
[root@host-192-169-106-11 src]# mv mysql-router-2.1.4-linux-glibc2.12-x86-64bit mysql-router
[root@host-192-169-106-11 src]# mv mysql-shell-1.0.9-linux-glibc2.12-x86-64bit mysql-shell
[root@host-192-169-106-11 src]# mv mysql-router /usr/local/
[root@host-192-169-106-11 src]# mv mysql-shell /usr/local/
[root@host-192-169-106-11 local]# vim /etc/profile
..............
export PATH=$PATH:/usr/local/mysql-shell/bin/:/usr/local/mysql-route/bin/
[root@host-192-169-106-11 local]# source /etc/profile
[root@host-192-169-106-11 ~]# mysqlprovision --version
mysqlprovision version 2.0.0
[root@host-192-169-106-11 ~]# mysqlsh --version
MySQL Shell Version 1.0.9
[root@host-192-169-106-11 ~]# mysqlrouter --version
MySQL Router v2.1.4 on Linux (64-bit) (GPL community edition)
3. 在三个cluster节点安装和部署Mysql5.7及 mysql-shell
3.1安装mysql-shell (三个节点同样操作)
[root@host-192-169-106-11 ~]# cd /usr/local/src/
[root@host-192-169-106-11 src]# ll mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz
-rw-r--r-- 1 root root 6635831 Mar 22 2017 mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz
[root@host-192-169-106-11 src]# tar -zvxf mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz
[root@host-192-169-106-11 src]# mv mysql-shell-1.0.9-linux-glibc2.12-x86-64bit mysql-shell
[root@host-192-169-106-11 src]# mv mysql-shell /usr/local/
[root@host-192-169-106-11 src]# echo "export PATH=$PATH:/usr/local/mysql-shell/bin/" >> /etc/profile
[root@host-192-169-106-11 src]# source /etc/profile
[root@host-192-169-106-11 ~]# mysqlprovision --version
mysqlprovision version 2.0.0
[root@host-192-169-106-11 ~]# mysqlsh --version
MySQL Shell Version 1.0.9
3.2 安装mysql5.7 (三个节点同样操作)
使用RPM包方式安装
1）卸载系统自带的 mysql和mariadb-lib
[root@host-192-169-106-11 src]# /bin/rpm -e $(/bin/rpm -qa | grep mysql|xargs) --nodeps
[root@host-192-169-106-11 src]# /bin/rpm -e $(/bin/rpm -qa | grep mariadb|xargs) --nodeps
2）下载mysql5.7.21 rpm安装包
下载地址：http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.7/
[root@host-192-169-106-11 src]# tar -vxf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
[root@host-192-169-106-11 src]# ll
总用量 1058872
-rw-r--r--. 1 root root 531056640 8月 28 12:05 mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
-rw-r--r--. 1 7155 31415 25365436 6月 12 14:42 mysql-community-client-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 281248 6月 12 14:42 mysql-community-common-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 3833396 6月 12 14:42 mysql-community-devel-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 47074656 6月 12 14:42 mysql-community-embedded-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 24079736 6月 12 14:42 mysql-community-embedded-compat-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 129991352 6月 12 14:42 mysql-community-embedded-devel-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 2272032 6月 12 14:42 mysql-community-libs-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 2116432 6月 12 14:42 mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 173500088 6月 12 14:43 mysql-community-server-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 122530756 6月 12 14:43 mysql-community-test-5.7.27-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 15526654 8月 28 09:52 mysql-router-2.1.4-linux-glibc2.12-x86-64bit.tar.gz
-rw-r--r--. 1 root root 6635831 8月 28 09:52 mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.tar.gz
依次执行（几个包有依赖关系，所以执行有先后）下面命令安装
[root@host-192-169-106-11 src]# rpm -ivh mysql-community-common-5.7.27-1.el7.x86_64.rpm --force
[root@host-192-169-106-11 src]# rpm -ivh mysql-community-libs-5.7.27-1.el7.x86_64.rpm --force
[root@host-192-169-106-11 src]# rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm --force
[root@host-192-169-106-11 src]# rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm --force
=============================================================================================================
可能在安装mysql-community-server-5.7.27-1.el7.x86_64.rpm的时候会有如下报错：
[root@kevin ~]# rpm -ivh mysql-community-server-5.7.21-1.el7.x86_64.rpm --force
warning: mysql-community-server-5.7.27-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
libaio.so.1()(64bit) is needed by mysql-community-server-5.7.27-1.el7.x86_64
libaio.so.1(LIBAIO_0.1)(64bit) is needed by mysql-community-server-5.7.27-1.el7.x86_64
libaio.so.1(LIBAIO_0.4)(64bit) is needed by mysql-community-server-5.7.27-1.el7.x86_64
net-tools is needed by mysql-community-server-5.7.27-1.el7.x86_64
这个报错的意思是需要安装libaio包和net-tools包：
安装libaio-0.3.107-10.el6.x86_64.rpm
[root@host-192-169-106-11 src]# wget http://mirror.centos.org/centos/6/os/x86_64/Packages/libaio-0.3.107-10.el6.x86_64.rpm
[root@host-192-169-106-11 src]# rpm -ivh libaio-0.3.107-10.el6.x86_64.rpm --force
安装net-tools
[root@host-192-169-106-11 src]# yum install net-tools
=============================================================================================================
使用rpm安装方式安装mysql，安装的路径如下：
数据库目录
/var/lib/mysql/
配置文件
/usr/share/mysql(mysql.server命令及配置文件)
/etc/my.cnf
相关命令
/usr/bin(mysqladmin mysqldump等命令)
启动脚本
/etc/rc.d/init.d/(启动脚本文件mysql的目录)
3）数据库初始化
为了保证数据库目录为与文件的所有者为 mysql 登陆用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化
[root@host-192-169-106-11 src]# mysql_install_db --datadir=/var/lib/mysql //必须指定datadir,执行后会生成~/.mysql_secret密码文件
[root@host-192-169-106-11 src]# mysqld --initialize --user=mysql //新版的推荐此方法，执行生会在/var/log/mysqld.log生成随机密码。如果是以mysql身份运行，则可以去掉--user选项。
4）更改mysql数据库目录的所属用户及其所属组，然后启动mysql数据库
[root@host-192-169-106-11 src]# chown mysql:mysql /var/lib/mysql -R
[root@host-192-169-106-11 src]# systemctl start mysqld.service //启动mysql数据库服务
[root@host-192-169-106-11 src]# systemctl enable mysqld.service
5）根据第3步中的密码登录到mysql，更改root用户的密码，新版的mysql在第一次登录后更改密码前是不能执行任何命令的
另外--initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登陆后你需要设置一个新的密码，
而使用--initialize-insecure命令则不使用安全模式，则不会为 root 用户生成一个密码。
这里演示使用的--initialize初始化的，会生成一个 root 账户密码，密码在log文件里，如下最后的"F;HNq*thK2hb"即为随即生成的root密码
[root@host-192-169-106-11 src]# cat /var/log/mysqld.log|grep 'A temporary password'
.......
T05:57:00.021884Z 1 [Note] A temporary password is generated for root@localhost: F;HNq*thK2hb
[root@kevin ~]# mysql -uroot -p'F;HNq*thK2hb'
mysql> set password=password('sagis@123');
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'sagis@123' WITH GRANT OPTION;
mysql> flush privileges;
3.3 配置my.cnf
先配置host-192-169-106-11节点的my.cnf
[root@host-192-169-106-11 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@host-192-169-106-11 ~]# >/etc/my.cnf
[root@host-192-169-106-11 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
port=23306
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#复制框架
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
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="192.169.106.12:24902"
#启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意
loose-group_replication_group_seeds="192.169.106.11:24901,192.169.106.12:24902,192.169.106.13:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.169.106.11,192.169.106.12,192.169.106.13"
# 使用MGR的单主模式
loose-group_replication_single_primary_mode = on
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
report_port=23306
如上配置完成后, 将db-node01节点的/etc/my.cnf文件拷贝到其他两个节点
[root@host-192-169-106-11 ~]# rsync -e "ssh -p60202" -avpgolr /etc/my.cnf root@192.169.106.12:/etc/
[root@host-192-169-106-11 ~]# rsync -e "ssh -p60203" -avpgolr /etc/my.cnf root@192.169.106.13:/etc/
3个cluster节点除了server_id、loose-group_replication_local_address 两个参数不一样外，其他保持一致。
所以待拷贝完成后, 分别修改host-192-169-106-12和host-192-169-106-13节点/etc/my.cnf文件的server_id、loose-group_replication_local_address两个参数
配置完成后, 要依次重启三个节点的数据库，安装MGR插件，设置复制账号（所有MGR节点都要执行）
[root@host-192-169-106-11 ~]# systemctl restart mysqld
4. 创建Innodb Cluster集群
4.1 在 host-192-169-106-11 上创建集群，通过 host-192-169-106-11 上的 shell 连接host-192-169-106-11 的 mysql
[root@host-192-169-106-11 ~]# mysqlsh
Welcome to MySQL Shell 1.0.9
...................
# 执行配置命令，也需要密码
# 然后需要输入MySQL配置文件路径，本示例中的路径是 /usr/local/data/s1/s1.cnf
# 接下来需要创建供其他主机访问的用户，这里选择第1项，为root用户授权
mysql-js> shell.connect('root@192.169.106.11:13306')
Creating a session to 'root@192.169.106.11:13306'
Please provide the password for 'root@192.169.106.11:13306': *********
Save password for 'root@192.169.106.11:13306'? [Y]es/[N]o/Ne[v]er (default No): yes
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1424
Server version: 5.7.27-log MySQL Community Server (GPL)
No default schema selected; type use <schema> to set one.
<ClassicSession:root@192.169.106.11:13306>
MySQL 192.169.106.11:13306 JS > dba.configureLocalInstance();
Configuring local MySQL instance listening at port 13306 for use in an InnoDB cluster...
This instance reports its own address as host-192-169-106-11:13306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
The instance '192.169.106.11:13306' is valid for InnoDB cluster usage.
The instance '192.169.106.11:13306' is already ready for InnoDB cluster usage.
# 创建一个 cluster，命名为 'myCluster'
MySQL 192.169.106.11:13306 JS > var cluster = dba.createCluster('myCluster');
A new InnoDB cluster will be created on instance '192.169.106.11:13306'.
Validating instance at 192.169.106.11:13306...
This instance reports its own address as host-192-169-106-11:13306
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.
如上的信息, 如果创建成功, 则会输出的信息中会有类似“Cluster successfully created.”的语句
#创建成功后，查看cluster状态
mysql-js> cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "192.169.106.11:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"192.169.106.11:3306": {
"address": "192.169.106.11:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
mysql-js> dba.getCluster();
<Cluster:myCluster>
4.2 添加节点 host-192-169-106-12到上面创建的"myCluster"集群中
通过host-192-169-106-12本机 mysql-shell 对 mysql 进行配置
[root@host-192-169-106-12 ~]# mysqlsh
................
mysql-js> shell.connect('root@192.169.106.12:3306')
Creating a session to 'root@192.169.106.12:3306'
Please provide the password for 'root@192.169.106.12:3306': *********
Save password for 'root@192.169.106.12:3306'? [Y]es/[N]o/Ne[v]er (default No): yes
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1424
Server version: 5.7.27-log MySQL Community Server (GPL)
No default schema selected; type use <schema> to set one.
<ClassicSession:root@192.169.106.12:3306>
MySQL 192.169.106.12:3306 JS > dba.configureLocalInstance();
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as host-192-169-106-12:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
The instance '192.169.106.12:3306' is valid for InnoDB cluster usage.
The instance '192.169.106.12:3306' is already ready for InnoDB cluster usage.
接着修改 my.cnf，添加配置项:
[root@host-192-169-106-12 ~]# vim /etc/my.cnf
............
loose-group_replication_allow_local_disjoint_gtids_join=on
重启mysql服务
[root@host-192-169-106-12 ~]# systemctl restart mysqld
然后通过 host-192-169-106-11 节点 的 mysql-shell 添加 192.169.106.12 到 "myCluster"集群中
接着上面的host-192-169-106-11的mysql-shell终端窗口 (注意这个终端窗口是上面执行后, 没有关闭一直开着的)
mysql-js> cluster.addInstance('root@192.169.106.12:3306');
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@192.169.106.12:3306':
Adding instance to the cluster ...
The instance 'root@192.169.106.12:3306' was successfully added to the cluster.
上面信息表示192.169.106.12节点已经成功添加到"myCluster"集群中了. 如下查看集群状态
mysql-js> cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "192.169.106.11:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"192.169.106.11:3306": {
"address": "192.169.106.11:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"192.169.106.12:3306": {
"address": "192.169.106.12:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
同样, 上面操作后, 这个192.169.106.11节点的mysql-shell当前终端窗口不要关闭,继续保持在集群状态中, 下面添加192.169.106.13节点到集群中会用到这里.(后面常用命令中会提到)
4.3 添加节点 host-192-169-106-13到上面创建的"myCluster"集群中
通过host-192-169-106-13本机 mysql-shell 对 mysql 进行配置
[root@host-192-169-106-13 ~]# mysqlsh
................
mysql-js> shell.connect('root@192.169.106.13:3306')
Creating a session to 'root@192.169.106.13:3306'
Please provide the password for 'root@192.169.106.13:3306': *********
Save password for 'root@192.169.106.13:3306'? [Y]es/[N]o/Ne[v]er (default No): yes
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1424
Server version: 5.7.27-log MySQL Community Server (GPL)
No default schema selected; type use <schema> to set one.
<ClassicSession:root@192.169.106.13:3306>
MySQL 192.169.106.13:3306 JS > dba.configureLocalInstance();
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as host-192-169-106-13:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
The instance '192.169.106.13:3306' is valid for InnoDB cluster usage.
The instance '192.169.106.13:3306' is already ready for InnoDB cluster usage.
接着修改 my.cnf，添加配置项:
[root@host-192-169-106-13 ~]# vim /etc/my.cnf
............
loose-group_replication_allow_local_disjoint_gtids_join=on
重启mysql服务
[root@host-192-169-106-13 ~]# systemctl restart mysqld
然后通过 host-192-169-106-11 节点 的 mysql-shell 添加 192.169.106.13 到 "myCluster"集群中
接着上面的host-192-169-106-11的mysql-shell终端窗口 (注意这个终端窗口是上面执行后, 没有关闭一直开着的)
mysql-js> cluster.addInstance('root@192.169.106.13:3306');
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@192.169.106.13:3306':
Adding instance to the cluster ...
The instance 'root@192.169.106.13:3306' was successfully added to the cluster.
上面信息表示192.169.106.13节点已经成功添加到"myCluster"集群中了. 如下查看集群状态
mysql-js> cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "192.169.106.11:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"192.169.106.11:3306": {
"address": "192.169.106.11:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"192.169.106.12:3306": {
"address": "192.169.106.12:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"192.169.106.13:3306": {
"address": "192.169.106.13:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "192.169.106.11:3306"
}
通过上面cluster集群信息可知, 192.169.106.11节点是主节点, 具有R/W读写权限, 其他两个节点是从节点, 具有R/O 只读权限
5. 启动管理节点的route
进入 192.169.106.11管理节点中mysql-router 安装目录，配置并启动 router
[root@host-192-169-106-11 ~]# ./bin/mysqlrouter --bootstrap root@192.169.106.11:3306 -d myrouter --user=root
Please enter MySQL password for root:
Bootstrapping MySQL Router instance at /root/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@host-192-169-106-11 ~]# ls /root/myrouter/
data log mysqlrouter.conf mysqlrouter.key run start.sh stop.sh
[root@host-192-169-106-11 ~]# cat /root/myrouter/mysqlrouter.conf #可以修改配置文件, 也可以默认不修改
默认通过route连接mysql后, 6446端口连接后可以进行读写操作. 6447端口连接后只能进行只读操作.
然后启动mysqlroute
[root@host-192-169-106-11 ~]# /root/myrouter/start.sh
PID 28505 written to /root/myrouter/mysqlrouter.pid
[root@host-192-169-106-11 ~]# ps -ef|grep myroute
[root@host-192-169-106-11 ~]# ps -ef|grep myroute
root 7827 7755 0 10:49 pts/0 00:00:00 grep --color=auto myroute
root 28505 1 0 03:55 ? 00:00:55 /usr/local/mysql-router/bin/mysqlrouter -c /usr/local/mysql-router/myrouter/mysqlrouter.conf
[root@host-192-169-106-11 ~]# netstat -tunlp|grep 28505
tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 28505/mysqlrouter
tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 28505/mysqlrouter
tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 28505/mysqlrouter
tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 28505/mysqlrouter
这样就可以使用MySQL客户端连接router了. 下面验证下连接router:
a) 管理节点本机mysql-shell连接:
[root@host-192-169-106-11 ~]# mysqlsh --uri root@localhost:6446
b) 管理节点本机mysql连接:
[root@host-192-169-106-11 ~]# mysql -u root -h 127.0.0.1 -P 6446 -p
c) 远程客户机通过route连接mysql
[root@host-192-169-106-11 ~]# mysql -u root -h 192.169.106.11 -P 6446 -p
测试cluster节点数据同步. 这里选择host-192-169-106-13节点作为远程客户端连接router
[root@host-192-169-106-13 ~]# mysql -u root -h 192.169.106.13 -P 6446 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1054
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
+-------------------------------+
5 rows in set (0.00 sec)
测试测试库kevin
mysql> CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'test'
这是因为'root@%'没有创建库的权限
mysql> select host,user from mysql.user;
+-----------+----------------------------------+
| host | user |
+-----------+----------------------------------+
| % | mysql_innodb_cluster_rp496261783 |
| % | mysql_innodb_cluster_rp496457975 |
| % | mysql_innodb_cluster_rp496569258 |
| % | mysql_innodb_cluster_rp496629685 |
| % | mysql_router1_olzau3ltjqzx |
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+----------------------------------+
9 rows in set (0.00 sec)
mysql> show grants for root@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'root'@'%' WITH GRANT OPTION |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO 'root'@'%' WITH GRANT OPTION |
| GRANT SELECT ON `performance_schema`.* TO 'root'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql_innodb_cluster_metadata`.* TO 'root'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
登录主库, 创建一个具有管理权权限的用户
[root@host-192-169-106-11 ~]# mysql -psagis@123
.............
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to hjh@'%' identified by "hjh@123" with grant option;
Query OK, 0 rows affected, 1 warning (0.05 sec)
接着远程使用上面创建的新账号登录router操作
[root@host-192-169-106-13 ~]# mysql -u bobo -h 192.169.106.13 -P 6446 -p
........
mysql> show grants for hjh@'%';
+-------------------------------------------------------------+
| Grants for hjh@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hjh'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
测试测试库test
mysql> CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.06 sec)
mysql> use test;
Database changed
mysql> create table if not exists test (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.22 sec)
mysql> insert into kevin.haha values(1,"hejianhui");
Query OK, 1 rows affected (0.13 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from test.test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | hejianhui |
+----+-----------+
4 rows in set (0.00 sec)
分别登录三个cluster节点的mysql, 发现测试库test已经完成同步了, 其中:
写操作的数据会先写到192.169.106.11节点, 然后同步到192.169.106.12和192.169.106.13只读节点上.
注意: 上面使用6446端口连接的route, 可以进行读写操作. 但是使用6447端口连接后, 就只能进行只读操作了. 登录后可以执行" select @@hostname" 查看登录到哪个节点上.
[root@host-192-169-106-13 ~]# mysql -u hjh -h 192.169.106.13 -P 6447 -p
.............
mysql> select * from kevin.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | hejianhui |
+----+-----------+
1 rows in set (0.00 sec)
mysql> delete from test.test where id=1;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
此外, 还可以利用keepalived实现InnoDB Cluster的高可用, 即两台db-route管理节点, 通过VIP资源实现故障无感知切换. 这样需要准备5台节点, 其中3个cluster节点(安装mysql, mysql-shell), 2个route管理节点(安装keepalived, mysql-shell, mysql-route, mysql-client)
6. InnoDB Cluster集群 日常维护命令
6.1 在各节点配置之后, 创建cluster集群之前, 可以依次检查下cluster各个节点是否可用
dba.checkInstanceConfiguration("root@192.169.106.11:3306");
6.2 比如在上面创建Innodb cluster集群过程中, 再次登录mysqlsh (从客户端远程登录, 或任意节点本地登录), 怎么获得并查看集群状态
mysql-js> shell.connect("root@192.169.106.11:3306");
Please provide the password for 'root@192.169.106.11:3306':
Creating a Session to 'root@192.169.106.11:3306'
Classic Session successfully established. No default schema selected.
查看集群状态
mysql-js> cluster.status();
ReferenceError: cluster is not defined
上面方式查看, 会报错说集群没有定义, 这时需要先执行下面这条语句之后,才看查看到集群状态!!!!!
mysql-js> cluster.status();
ReferenceError: cluster is not defined
然后就可以查看集群状态了
mysql-js> cluster=dba.getCluster();
<Cluster:myCluster>
mysql-js> cluster.status();
查看已创建的集群名称
MySQL 172.16.60.214:6446 ssl JS > dba.getCluster();
<Cluster:myCluster>
=================================================
总结:
a) dba.getCluster(); #查看创建的集群
b) cluster=dba.getCluster(); #获取当前集群
c) cluster.status(); #查看集群状态
6.3 InnoDB Cluster集群维护的命令帮助
dba.help();
6.4 日常使用的几个重要命令 (mysqlsh的JS语法)
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
最后
以上就是暴躁蜻蜓为你收集整理的Centos7.5 基于MySQL5.7 的 InnoDB Cluster 高可用环境部署1 环境准备2. 在管理节点安装mysql shell 和 mysql-route3. 在三个cluster节点安装和部署Mysql5.7及 mysql-shell4. 创建Innodb Cluster集群5. 启动管理节点的route6. InnoDB Cluster集群 日常维护命令的全部内容，希望文章能够帮你解决Centos7.5 基于MySQL5.7 的 InnoDB Cluster 高可用环境部署1 环境准备2. 在管理节点安装mysql shell 和 mysql-route3. 在三个cluster节点安装和部署Mysql5.7及 mysql-shell4. 创建Innodb Cluster集群5. 启动管理节点的route6. InnoDB Cluster集群 日常维护命令所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复