我是靠谱客的博主 暴躁蜻蜓，这篇文章主要介绍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
2
3
41) 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以上
复制代码
1
2
3
4
5ip地址 主机名 角色 安装软件 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
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49[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
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29[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 (三个节点同样操作)
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17[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 (三个节点同样操作)
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90使用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
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55先配置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
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66[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"集群中
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73通过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"集群中
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81通过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
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171进入 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各个节点是否可用
复制代码
1
2dba.checkInstanceConfiguration("root@192.169.106.11:3306");
6.2 比如在上面创建Innodb cluster集群过程中, 再次登录mysqlsh (从客户端远程登录, 或任意节点本地登录), 怎么获得并查看集群状态
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27mysql-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集群维护的命令帮助
复制代码
1
2dba.help();
6.4 日常使用的几个重要命令 (mysqlsh的JS语法)
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31dba.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内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复