Centos7.5 基于MySQL5.7 的 InnoDB Cluster 高可用环境部署

最新推荐文章于 2024-04-22 18:18:02 发布

一角钱技术

最新推荐文章于 2024-04-22 18:18:02 发布

阅读量1.3k

点赞数

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/org_hjh/article/details/100154271

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

下面部署采用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

一角钱技术

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Centos7.5 基于MySQL5.7 的 InnoDB Cluster 高可用环境部署

下面部署采用InnoDB Cluster, 每台服务器实例都运行MySQL Group Replication (即冗余复制机制，内置failover), MGR有两种模式，一种是Single-Primary，一种是Multi-Primary，即单主或者多主。需求注意：模式Multi-Primary中，所有的节点都是主节点，都可以同时被读写，看上去这似乎更好，但是因为多主的复杂性，在功能上如果设...
复制链接

扫一扫