1.架构图
2.原理说明:
MySQL InnoDB集群提供了一个集成的，本地的，HA解决方案。Mysq Innodb Cluster是利用组复制的 pxos 协议，保障数据一致性，组复制支持单主模式和多主模式。
MySQL InnoDB集群由以下几部分组成：
- MySQL Servers with Group Replication：向集群的所有成员复制数据，同时提供容错、自动故障转移和弹性。MySQL Server 5.7.17或更高的版本。
- MySQL Router：确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器。MySQL Router 2.1.3或更高的版本。
- MySQL Shell：通过内置的管理API创建及管理Innodb集群。MySQL Shell 1.0.9或更高的版本。
3. 准备工作
1) 准备5台服务器
1) 关闭防火墙
2) 各节点配置/etc/hosts的主机名和IP(重要)
3. 搭建过程
1)各软件下载地址
Mysql: https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
Mysql-shell: https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.19-linux-glibc2.12-x86-64bit.tar.gz
Mysql-router:https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.19-linux-glibc2.12-x86_64.tar.xz
2) 安装mysql5.7.28，此步骤略
3) 解压安装mysql shell，172.16.7.32,172.16.7.33,172.16.7.34 这3台服务器都要安装
[root@db32 local]# tar -zxvf /usr/local/mysql-shell-8.0.19-linux-glibc2.12-x86-64bit.tar.gz
[root@db32 local]# mv mysql-shell-8.0.19-linux-glibc2.12-x86-64bit mysql-shell
[root@db32 local]# export PATH=/usr/local/mysql-shell/bin/:$PATH
以下是测试连接
[root@db32 local]# mysqlsh
MySQL Shell 8.0.19
Copyright (c) 2016, 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 '\\?' for help; '\\quit' to exit.
MySQL JS >
4) 配置3台服务器的my.cnf参数配置并重启mysql 服务，以下参数是必须的，其他的参数自行添加
172.16.7.32
[mysqld]
character-set-server=utf8mb4
port=3306
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error=/usr/local/mysql/data/mysql-error.log
pid-file=/usr/local/mysql/data/mysql.pid
user = mysql
tmpdir = /tmp
default-storage-engine=INNODB
#复制框架
server_id=17216732
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
max_connections=200
max_allowed_packet=16M
#组复制设置
#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地址，端口33061用于接收组中其他成员转入连接
loose-group_replication_local_address="172.16.7.32:33061"
#启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意
loose-group_replication_group_seeds="172.16.7.32:33061,172.16.7.33:33061,172.16.7.34:33061"
loose-group_replication_ip_whitelist="172.16.7.32,172.16.7.33,172.16.7.34,127.0.0.1/8"
loose-group_replication_bootstrap_group=off
# 使用MGR的单主模式
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off
172.16.7.33
[mysqld]
character-set-server=utf8mb4
port=3306
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error=/usr/local/mysql/data/mysql-error.log
pid-file=/usr/local/mysql/data/mysql.pid
user = mysql
tmpdir = /tmp
default-storage-engine=INNODB
#复制框架
server_id=17216733
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
max_connections=200
max_allowed_packet=16M
#组复制设置
#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地址，端口33061用于接收组中其他成员转入连接
loose-group_replication_local_address="172.16.7.33:33061"
#启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意
loose-group_replication_group_seeds="172.16.7.32:33061,172.16.7.33:33061,172.16.7.34:33061"
loose-group_replication_ip_whitelist="172.16.7.32,172.16.7.33,172.16.7.34,127.0.0.1/8"
loose-group_replication_bootstrap_group=off
# 使用MGR的单主模式
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off
172.16.7.34
[mysqld]
character-set-server=utf8mb4
port=3306
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error=/usr/local/mysql/data/mysql-error.log
pid-file=/usr/local/mysql/data/mysql.pid
user = mysql
tmpdir = /tmp
default-storage-engine=INNODB
#复制框架
server_id=17216734
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
max_connections=200
max_allowed_packet=16M
#组复制设置
#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地址，端口33061用于接收组中其他成员转入连接
loose-group_replication_local_address="172.16.7.34:33061"
#启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意
loose-group_replication_group_seeds="172.16.7.32:33061,172.16.7.33:33061,172.16.7.34:33061"
loose-group_replication_ip_whitelist="172.16.7.32,172.16.7.33,172.16.7.34,127.0.0.1/8"
loose-group_replication_bootstrap_group=off
# 使用MGR的单主模式
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks=off
2) 创建cluster集群
1).进到172.16.7.32的 mysql-shell 的安装目录，登陆mysql-shell
bin/mysqlsh
复制代码
mysql-js> shell.connect('root@localhost:3306');
#连接成功后
#配置实例
dba.configureLocalInstance();
#此时会让选择创建管理cluster的用户，我选1，使用root管理，并且允许远程登陆“%”
#接着查看实例状态
dba.checkInstanceConfiguration("root@localhost:3306");
复制代码
如果出现：
You can now use it in an InnoDB Cluster.
{
"status": "ok"
}
说明配置成功。
2).登陆manager管理节点，讲router和shell都解压到/usr/local/文件夹下分别为mysql-route和mysql-shell
3).登陆manager节点的shell，连接master，创建cluster
复制代码
bin/mysqlsh
# 连接01
mysql-js> shell.connect('[email protected]:3306');
# 创建一个 cluster，命名为 'myCluster'
mysql-js> var cluster = dba.createCluster('myCluster');
# 创建成功后，查看cluster状态
mysql-js> cluster.status();
创建后，可以看到master已经添加进cluster，
MySQL 172.16.7.32:3306 ssl JS > cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db32:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"db32:3306": {
"address": "db32:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db32:3306"
}
3) 添加172.16.7.33从节点，登录到172.16.7.33服务器上面执行
登陆shell，执行配置
bin/mysqlsh
mysql-js> shell.connect('root@localhost:3306');
mysql-js> dba.configureLocalInstance();
停掉mysql服务，在配置文件my.cnf末尾添加配置：
loose-group_replication_allow_local_disjoint_gtids_join=ON
重启mysql后，通过manager节点的shell，将slave1添加到cluster：
# 添加实例，在主节点(172.16.7.32)上面执行
cluster.addInstance('[email protected]:3306');
# 创建成功后，查看cluster状态
mysql-js> cluster.status();
成功后状态：
MySQL 172.16.7.32:3306 ssl JS > cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db32:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"db32:3306": {
"address": "db32:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db33:3306": {
"address": "db33:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db32:3306"
}
4) 添加172.16.7.34从节点，登录到172.16.7.34服务器上面执行
登陆shell，执行配置
bin/mysqlsh
mysql-js> shell.connect('root@localhost:3306');
mysql-js> dba.configureLocalInstance();
停掉mysql服务，在配置文件my.cnf末尾添加配置：
loose-group_replication_allow_local_disjoint_gtids_join=ON
重启mysql后，通过manager节点的shell，将slave1添加到cluster：
# 添加实例，在主节点(172.16.7.32)上面执行
cluster.addInstance('[email protected]:3306');
# 创建成功后，查看cluster状态
mysql-js> cluster.status();
成功后状态：
MySQL 172.16.7.32:3306 ssl JS > cluster.status();
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "db32:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db32:3306": {
"address": "db32:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db33:3306": {
"address": "db33:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"db34:3306": {
"address": "db34:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db32:3306"
}
2) 问题描述，如果从库发生重启，那么重启后节点不在MGR组里面了，通过start group_replication;命令不成功，那么则设置
show variables like '%gtids_join%';
set global group_replication_bootstrap_group=on;
set global slave_preserve_commit_order=on;
start group_replication;
问题2
Dba.getCluster: Dba.getCluster: Unable to get cluster. The instance 'host-192-168-1-101:3306'
may belong to a different ReplicaSet as the one registered in the Metadata since the value of 'group_replication_group_name'
does not match the one registered in the ReplicaSet's Metadata: possible split-brain scenario. Please connect to another member of the ReplicaSet to get the Cluster. (RuntimeError)
//最致命的错误，master/slave的数据不一致所致，没办法，只能重新来
dba.dropMetadataSchema();
问题3
如果slave起不起来，则执行以下
set global group_replication_bootstrap_group=on;
set global slave_preserve_commit_order=on;
show variables like '%replication_allow_local_disjoint_gtids_join%';
show variables like '%slave_preserve_commit_order%';
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
问题4
作者在搭建过程中出现slave节点在重启mysql服务器，start group_replication 报错，无论如何都不能开启
错误提示如下:
2020-03-23T11:01:08.364104Z 0 [Warning] Plugin group_replication reported: 'read failed'
2020-03-23T11:01:08.383678Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2020-03-23T11:02:08.315089Z 2 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2020-03-23T11:02:08.315188Z 2 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2020-03-23T11:02:08.315242Z 2 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
最后通过添加my.cnf配置白名单解决，但是如果本机有多个IP，记得添加多个
loose-group_replication_ip_whitelist="172.16.7.32,172.16.7.33,172.16.7.34,127.0.0.1/8"
3. 安装mysql-router及配置mysql-router
172.16.7.30 172.16.7.31上面执行以下命令
[root@db30 local]# tar -Jxvf mysql-router-8.0.19-linux-glibc2.12-x86_64.tar.xz
[root@db30 local]# mv mysql-router-8.0.19-linux-glibc2.12-x86_64/ mysqlrouter
[root@db30 local]# echo 'export PATH=/usr/local/mysqlrouter/bin/:$PATH' >> /etc/profile
[root@db30 local]# source /etc/profile
[root@db30 local]# /usr/local/mysql-router/bin/mysqlrouter --bootstrap [email protected]:3306 -d myrouter --user=root
这里会在当前目录下产生mysql-router 目录, 并生成router配置文件，默认通过route连接mysql后, 6446端口连接后可以进行读写操作. 6447端口连接后只能进行只读操作.
然后启动mysql-route
myrouter/start.sh
查看route进程：
ps -ef|grep myrouter
netstat -tunlp|grep （route的pid）
这样就可以使用MySQL客户端连接router了. 下面验证下连接router:
a) 管理节点本机mysql-shell连接:
mysqlsh --uri root@localhost:6446
b) 管理节点本机mysql连接:
mysql -u root -h 127.0.0.1 -P 6446 -p
c) 远程客户机通过route连接mysql
mysql -u root -h manager_ip -P 6446 -p
4. 配置myrouter的keepalived 以下是172.16.7 30的配置,172.16.7.31的配置参考30服务器即可
keepalived.conf脚本如下:
! Configuration File for keepalived
global_defs {
notification_email {
}
notification_email_from [email protected]
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
#vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_script checkmysqlrouter {
script "/etc/keepalived/check_myrouter.sh"
interval 2
weight -20
fall 3
rise 2
}
vrrp_instance VI_1 {
state MASTER
interface ens32
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.7.35
}
track_script {
checkmysqlrouter
}
}
check_myrouter.sh脚本如下
#!/bin/sh
aa=`ps -fe|grep myrouter |wc -l`
echo $aa
if [ $aa -ne 4 ]
then
service keepalived stop
#echo "aaa"
else
echo "runing....."
fi
5. 获取群集信息 mager节点执行(172.16.7.32)
mysql-js> shell.connect('root@localhost:3306');
MySQL localhost:3306 ssl JS > var aa=dba.getCluster('myCluster');
MySQL localhost:3306 ssl JS > aa.status();
閱讀更多 碩果累累1316 的文章
