【DB宝45】MySQL高可用之MGR+Consul架构部署(上)

举报
小麦苗DB宝 发表于 2021/03/29 09:30:41 2021/03/29
【摘要】 一、MGR+Consul架构简介 二、搭建MGR 2.1、申请3台MGR机器 2.2、3台主机安装MySQL环境 2.3、修改MySQL参数 2.4、重启MySQL环境 2.5、安装MGR插件(所有节点执行) 2.6、设置复制账号 2.7、启动MGR,在主库(192.168.68.60)上执行 2.8、其他节点加入MGR,在从库(192.168.68.61,192.168.68.62)上执...

一、MGR+Consul架构简介

基于目前存在很多MySQL数据库单点故障,传统的MHA,PXC等方案用VIP或者DNS切换的方式可以实现、基于数据库的数据强一致性考虑,采用MGR集群,采用consul服务注册发现实现应用端通过动态DNS 访问MGR集群,实现数据库高可用,自动化切换的方案。

有关MGR之前发布过几篇文章,连接如下:

【DB宝18】在Docker中安装使用MySQL高可用之MGR

【DB宝35】使用MySQL 8.0 克隆(clone)插件快速添加MGR节点

本次环境一共包括7台机器,其中3台做MySQL的MGR数据节点(其上也需要安装Consul,作为Consul Client),单主模式,还有3台做Consul Server集群,实现Consul的高可用,剩下一台做DNS服务器,用来解析MGR节点域名,规划如下表所示:

二、搭建MGR

2.1、申请3台MGR机器

-- 拉取镜像
docker pull lhrbest/lhrcentos76:8.0
-- 创建专用网络
docker network create --subnet=192.168.68.0/16 mhalhr
docker network inspect mhalhr


-- 生成3台CentOS的环境
docker rm -f mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062

docker run -d --name mysql8022mgr33060  -h lhrmgr60  \
--network mhalhr --ip 192.168.68.60 --privileged=true \
-p 33060:3306   \
-v /sys/fs/cgroup:/sys/fs/cgroup \
lhrbest/lhrcentos76:8.0 init


docker run -d --name mysql8022mgr33061  -h lhrmgr61  \
--network mhalhr --ip 192.168.68.61 --privileged=true \
-p 33061:3306   \
-v /sys/fs/cgroup:/sys/fs/cgroup \
lhrbest/lhrcentos76:8.0 init


docker run -d --name mysql8022mgr33062  -h lhrmgr62  \
--network mhalhr --ip 192.168.68.62 --privileged=true \
-p 33062:3306   \
-v /sys/fs/cgroup:/sys/fs/cgroup \
lhrbest/lhrcentos76:8.0 init


-- 拷贝MySQL安装文件到MySQL容器内
docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33060:/
docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33061:/
docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33062:/



docker network connect bridge mysql8022mgr33060
docker network connect bridge mysql8022mgr33061
docker network connect bridge mysql8022mgr33062

docker restart  mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062

2.2、3台主机安装MySQL环境

docker exec -it mysql8022mgr33060 bash
docker exec -it mysql8022mgr33061 bash
docker exec -it mysql8022mgr33062 bash

tar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
yum localinstall -y mysql-community-*.rpm


--去掉密码验证策略
mv /usr/lib64/mysql/plugin/validate_password.so /usr/lib64/mysql/plugin/validate_password.so_bk
mv /usr/lib64/mysql/plugin/component_validate_password.so /usr/lib64/mysql/plugin/component_validate_password.so_bk

-- 启动mysql
systemctl start mysqld
systemctl status mysqld


-- 修改密码
grep 'temporary password' /var/log/mysqld.log

mysql -uroot -p
alter user root@'localhost' identified with mysql_native_password by 'lhr';
grant all on *.* to root@'localhost' with grant option;
create user root@'%' identified with mysql_native_password by 'lhr';
grant all on *.* to root@'%' with grant option;
flush privileges;
select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;

2.3、修改MySQL参数

-- 节点1
cat > /etc/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 802233060
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
skip-name-resolve
log_slave_updates = 1
gtid-mode=ON
enforce-gtid-consistency=on
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M

master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr60-relay-bin


transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.68.60:33061"
loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62"

report_host=192.168.68.60
report_port=3306

EOF

-- 节点2
cat >  /etc/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 802233061
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
log_slave_updates = 1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M

master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr61-relay-bin


transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.68.61:33061"
loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62"

report_host=192.168.68.61
report_port=3306

EOF


-- 节点3
cat > /etc/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 802233062
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
log_slave_updates = 1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M


master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr62-relay-bin


transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.68.62:33061"
loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62"

report_host=192.168.68.62
report_port=3306

EOF

2.4、重启MySQL环境

-- 重启MySQL
systemctl restart mysqld

-- 进入MySQL
docker exec -it mysql8022mgr33060 bash
docker exec -it mysql8022mgr33060 mysql -uroot -plhr

-- 远程连接MySQL
mysql -uroot -plhr -h192.168.66.35 -P33060 
mysql -uroot -plhr -h192.168.66.35 -P33061 
mysql -uroot -plhr -h192.168.66.35 -P33062 

-- 查看MySQL日志
docker logs -f --tail 10 mysql8022mgr33060
docker logs -f --tail 10 mysql8022mgr33061
docker logs -f --tail 10 mysql8022mgr33062

-- 查看MySQL的主机名、server_id和server_uuid
mysql -uroot -plhr -h192.168.66.35 -P33060 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.66.35 -P33061 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.66.35 -P33062 -e "select @@hostname,@@server_id,@@server_uuid"

结果:

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P33060 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| lhrmgr60   |   802233060 | 44c693d8-80bb-11eb-b4bb-0242c0a8443c |
+------------+-------------+--------------------------------------+

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P33061 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| lhrmgr61   |   802233061 | 408acdb5-80bc-11eb-97a7-0242c0a8443d |
+------------+-------------+--------------------------------------+

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P33062 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| lhrmgr62   |   802233062 | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e |
+------------+-------------+--------------------------------------+

2.5、安装MGR插件(所有节点执行)

mysql -uroot -plhr -h192.168.66.35 -P33060


INSTALL PLUGIN group_replication SONAME 'group_replication.so';
-- 如果MySQL版本大于8.0.17,那么建议再安装clone插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
show plugins;

2.6、设置复制账号

-- 在主库(192.168.68.60)上执行
CREATE USER repl@'%' IDENTIFIED BY 'lhr';
GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* TO repl@'%';
FLUSH PRIVILEGES;

-- 所有节点执行
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';

2.7、启动MGR,在主库(192.168.68.60)上执行

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 查看MGR组信息 
SELECT * FROM performance_schema.replication_group_members;

2.8、其他节点加入MGR,在从库(192.168.68.61,192.168.68.62)上执行

reset master;
START GROUP_REPLICATION;
-- 查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;

执行结果:

MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 408acdb5-80bc-11eb-97a7-0242c0a8443d | 192.168.68.61 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 44c693d8-80bb-11eb-b4bb-0242c0a8443c | 192.168.68.60 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e | 192.168.68.62 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.05 sec)

可以看到,3个节点状态为online,并且主节点为192.168.68.60,只有主节点可以写入,其他节点只读,MGR单主模式搭建成功。

2.9、测试同步

在主节点上执行以下命令,然后在其它节点查询:

create database lhrdb;
CREATE TABLE lhrdb.`tb1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `hostname` varchar(100) DEFAULT NULL,
 `server_id` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


insert into lhrdb.tb1(hostname,server_id) select @@hostname,@@server_id;
select * from lhrdb.tb1;

-- 3个节点查询出来的值一样
MySQL [(none)]> select * from lhrdb.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | lhrmgr60 | 802233060 |
+----+----------+-----------+
1 row in set (0.05 sec)

三、搭建Consul Server集群

3.1、申请3台Consul Server主机

docker rm -f lhrconsulmgr66  lhrconsulmgr67  lhrconsulmgr68

docker run -d --name lhrconsulmgr66 -h lhrconsulmgr66 \
  --net=mhalhr --ip 192.168.68.66 \
  -p 8566:8500 \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/lhrcentos76:8.0 \
  /usr/sbin/init


docker run -d --name lhrconsulmgr67 -h lhrconsulmgr67 \
  --net=mhalhr --ip 192.168.68.67 \
  -p 8567:8500 \
  --privileged=true lhrbest/lhrcentos76:8.0 \
  /usr/sbin/init

docker run -d --name lhrconsulmgr68 -h lhrconsulmgr68 \
  --net=mhalhr --ip 192.168.68.68 \
  -p 8568:8500 \
  --privileged=true lhrbest/lhrcentos76:8.0 \
  /usr/sbin/init

docker cp consul_1.9.4_linux_amd64.zip lhrconsulmgr66:/
docker cp consul_1.9.4_linux_amd64.zip lhrconsulmgr67:/
docker cp consul_1.9.4_linux_amd64.zip lhrconsulmgr68:/


docker network connect bridge lhrconsulmgr66
docker network connect bridge lhrconsulmgr67
docker network connect bridge lhrconsulmgr68

docker restart  lhrconsulmgr66 lhrconsulmgr67 lhrconsulmgr68


3.2、安装Consul Server

docker exec -it lhrconsulmgr66 bash
docker exec -it lhrconsulmgr67 bash
docker exec -it lhrconsulmgr68 bash

unzip consul_1.9.4_linux_amd64.zip
mv consul /usr/local/bin/
mkdir -p /consul/logs/

-- 66节点	启动
nohup consul agent -server -bootstrap-expect 3 -bind=192.168.68.66 -client=0.0.0.0 -data-dir=/consul/data \
-node=n66 -ui >> /consul/logs/consul.log 2>&1 &

-- 67节点	启动
nohup consul agent -server -bootstrap-expect 3 -bind=192.168.68.67 -client=0.0.0.0 -data-dir=/consul/data \
-node=n67 -ui >> /consul/logs/consul.log 2>&1 &
-- 68节点	启动
nohup consul agent -server -bootstrap-expect 3 -bind=192.168.68.68 -client=0.0.0.0 -data-dir=/consul/data \
-node=n68 -ui >> /consul/logs/consul.log 2>&1 &

-- 67和68节点加入集群
consul join 192.168.68.66

-- 查询集群状态
[root@lhrconsulmgr66 /]# consul members
Node  Address             Status  Type    Build  Protocol  DC   Segment
n66   192.168.68.66:8301  alive   server  1.9.4  2         dc1  <all>
n67   192.168.68.67:8301  alive   server  1.9.4  2         dc1  <all>
n68   192.168.68.68:8301  alive   server  1.9.4  2         dc1  <all>
[root@lhrconsulmgr66 /]# consul operator raft list-peers
Node  ID                                    Address             State     Voter  RaftProtocol
n66   719e2a32-5c4b-bd0f-35ef-9ac11a8e79e0  192.168.68.66:8300  leader    true   3
n67   f80e2542-3d30-ca5a-af79-08879723c882  192.168.68.67:8300  follower  true   3
n68   43520b16-1b65-7d22-4172-6c2822070a44  192.168.68.68:8300  follower  true   3

为了后续启动方便,我们使用参数文件:

-- 66节点,其它节点修改node_name、advertise_addr和bind_addr即可:
mkdir -p /etc/consul.d/
pkill -9 consul
cat > /etc/consul.d/server.json  <<"EOF"
{ 
  "data_dir": "/consul/data",
  "datacenter": "dc1",
  "node_name": "n66", 
  "enable_syslog": true,
  "log_level": "INFO", 
  "server": true, 
  "advertise_addr":"192.168.68.66", 
  "bootstrap_expect": 3, 
  "bind_addr": "192.168.68.66", 
  "client_addr": "0.0.0.0", 
  "retry_join": ["192.168.68.66","192.168.68.67","192.168.68.68"],
  "retry_interval": "10s",
  "rejoin_after_leave": true,
  "start_join": ["192.168.68.66","192.168.68.67","192.168.68.68"] ,
  "ui": true
}
EOF


nohup consul agent -config-dir=/etc/consul.d > /consul/consul.log &

3.3、web访问

http://192.168.66.35:8566

http://192.168.66.35:8567

http://192.168.66.35:8568

四、在MySQL节点上安装Consul Client

4.1、安装Consul Client

docker cp consul_1.9.4_linux_amd64.zip mysql8022mgr33060:/
docker cp consul_1.9.4_linux_amd64.zip mysql8022mgr33061:/
docker cp consul_1.9.4_linux_amd64.zip mysql8022mgr33062:/

docker exec -it mysql8022mgr33060 bash
unzip consul_1.9.4_linux_amd64.zip
mv consul /usr/local/bin/
mkdir -p /consul/logs/

-- mgr60配置文件,其它节点修改bind_addr和node_name即可
mkdir -p /etc/consul.d/
cat > /etc/consul.d/client.json <<"EOF"
{
  "data_dir": "/data/consul",
  "enable_script_checks": true,
  "bind_addr": "192.168.68.60",
  "retry_join": ["192.168.68.66","192.168.68.67","192.168.68.68"],
  "retry_interval": "10s",
  "rejoin_after_leave": true,
  "start_join": ["192.168.68.66","192.168.68.67","192.168.68.68"] ,
  "node_name": "MGR60"
}
EOF

nohup consul agent -config-dir=/etc/consul.d > /consul/consul.log &

查询状态:


[root@lhrconsulmgr66 /]# consul members
Node   Address             Status  Type    Build  Protocol  DC   Segment
n66    192.168.68.66:8301  alive   server  1.9.4  2         dc1  <all>
n67    192.168.68.67:8301  alive   server  1.9.4  2         dc1  <all>
n68    192.168.68.68:8301  alive   server  1.9.4  2         dc1  <all>
MGR60  192.168.68.60:8301  alive   client  1.9.4  2         dc1  <default>
MGR61  192.168.68.61:8301  alive   client  1.9.4  2         dc1  <default>
MGR62  192.168.68.62:8301  alive   client  1.9.4  2         dc1  <default>
[root@lhrconsulmgr66 /]#  consul operator raft list-peers
Node  ID                                    Address             State     Voter  RaftProtocol
n66   719e2a32-5c4b-bd0f-35ef-9ac11a8e79e0  192.168.68.66:8300  follower  true   3
n67   f80e2542-3d30-ca5a-af79-08879723c882  192.168.68.67:8300  leader    true   3
n68   43520b16-1b65-7d22-4172-6c2822070a44  192.168.68.68:8300  follower  true   3

4.2、在Consul client的3个节点上编写检测primay脚本和检测slave脚本

-- 检测脚本【master】
mkdir -p /data/consul/shell/
cat > /data/consul/shell/check_mysql_mgr_master.sh  <<"EOF"
#!/bin/bash
port=3306
user="root"
passwod="lhr"
comm="/usr/bin/mysql -u$user -hlocalhost -P $port -p$passwod"
value=`$comm -Nse "select 1"`
primary_member=`$comm -Nse "select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member'"`
server_uuid=`$comm -Nse "select variable_value from performance_schema.global_variables where VARIABLE_NAME='server_uuid';"`
# 判断MySQL是否存活
if [ -z $value ]
then
   echo "mysql $port is down....."
   exit 2
fi
# 判断节点状态,是否存活
node_state=`$comm -Nse "select MEMBER_STATE from performance_schema.replication_group_members where MEMBER_ID='$server_uuid'"`
if [ $node_state != "ONLINE" ]
then
   echo "MySQL $port state is not online...."
   exit 2
fi
# 判断是不是主节点
if [[ $server_uuid == $primary_member ]]
then
   echo "MySQL $port Instance is master ........"
   exit 0
else
   echo "MySQL $port Instance is slave ........"
   exit 2
fi
EOF


-- 检测脚本【slave】
cat > /data/consul/shell/check_mysql_mgr_slave.sh <<"EOF"
#!/bin/bash
port=3306
user="root"
passwod="lhr"
comm="/usr/bin/mysql -u$user -hlocalhost -P $port -p$passwod"
value=`$comm -Nse "select 1"`
primary_member=`$comm -Nse "select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member'"`
server_uuid=`$comm -Nse "select variable_value from performance_schema.global_variables where VARIABLE_NAME='server_uuid';"`
# 判断mysql是否存活
if [ -z $value ]
then
   echo "mysql $port is down....."
   exit 2
fi
# 判断节点状态
node_state=`$comm -Nse "select MEMBER_STATE from performance_schema.replication_group_members where MEMBER_ID='$server_uuid'"`
if [ $node_state != "ONLINE" ]
then
   echo "MySQL $port state is not online...."
   exit 2
fi
# 判断是不是主节点
if [[ $server_uuid != $primary_member ]]
then
   echo "MySQL $port Instance is slave ........"
   exit 0
else
   node_num=`$comm -Nse "select count(*) from performance_schema.replication_group_members"`
# 判断如果没有任何从节点,主节点也注册从角色服务。
   if [ $node_num -eq 1 ]
   then
       echo "MySQL $port Instance is slave ........"
       exit 0
   else
       echo "MySQL $port Instance is master ........"
       exit 2
   fi
fi
EOF

赋权:

chmod +x /data/consul/shell/check_mysql_mgr_master.sh
chmod +x /data/consul/shell/check_mysql_mgr_slave.sh
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。