MySQL8.0.19 MGR MySQL router MySQL connector failover 组合实现高可用

微信扫一扫,分享到朋友圈

MySQL8.0.19 MGR MySQL router MySQL connector failover 组合实现高可用

首先在虚拟机中搭建3台Linux(Redhat8.0)服务器,分别命名为server01,server02,server03

在每台虚拟机中安装好MySQL8.0.19,server_id分别设置为1,2,3,每台数据库创建复制账户,并且安装好组复制插件

set sql_log_bin=0;
create user 'repl'@'%' identified with mysql_native_password by 'repl';
grant replication slave on *.* to 'repl'@'%';
change master to master_user='repl',master_password='repl' for channel 'group_replication_recovery';
set sql_log_bin=1;
install plugin group_replication soname 'group_replication.so';

3台服务器对应的IP如下:

192.168.56.10 server01

192.168.56.11 server02

192.168.56.12 server03

server01 my.cnf 设置

[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
log-error = /usr/local/mysql/data/error.log
log_timestamps=SYSTEM
pid-file = /usr/local/mysql/data/mysql.pid
tmpdir = /tmp
secure-file-priv=/tmp
port = 3306
server_id = 1
max_allowed_packet=32M
log_bin_trust_function_creators = ON
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
master_info_repository=TABLE
relay_log_info_repository=TABLE
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_isolation=READ-COMMITTED
transaction_write_set_extraction=XXHASH64
slave_preserve_commit_order=ON
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_local_address= "192.168.56.10:33006"
group_replication_group_seeds= "192.168.56.10:33006,192.168.56.11:33006,192.168.56.12:33006"
group_replication_start_on_boot=on
group_replication_bootstrap_group=on
group_replication_ip_whitelist='192.168.56.0/24'
group_replication_member_weight=90

因为我这里是虚拟机,所以设置server01 group_replication_bootstrap_group=on,每次虚拟机启动的时候设置server01为主节点

server01数据库启动之后设置set global group_replication_bootstrap_group=off;

vi my.cnf group_replication_bootstrap_group=off

server02 my.cnf 设置

[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
log-error = /usr/local/mysql/data/error.log
log_timestamps=SYSTEM
pid-file = /usr/local/mysql/data/mysql.pid
tmpdir = /tmp
secure-file-priv=/tmp
port = 3306
server_id = 2
max_allowed_packet=32M
log_bin_trust_function_creators = ON
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
master_info_repository=TABLE
relay_log_info_repository=TABLE
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_isolation=READ-COMMITTED
transaction_write_set_extraction=XXHASH64
slave_preserve_commit_order=ON
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_local_address= "192.168.56.11:33006"
group_replication_group_seeds= "192.168.56.10:33006,192.168.56.11:33006,192.168.56.12:33006"
group_replication_start_on_boot=on
group_replication_bootstrap_group=off
group_replication_ip_whitelist='192.168.56.0/24'
group_replication_member_weight=80

server03 my.cnf 设置

[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
log-error = /usr/local/mysql/data/error.log
log_timestamps=SYSTEM
pid-file = /usr/local/mysql/data/mysql.pid
tmpdir = /tmp
secure-file-priv=/tmp
port = 3306
server_id = 3
max_allowed_packet=32M
log_bin_trust_function_creators = ON
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
master_info_repository=TABLE
relay_log_info_repository=TABLE
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_isolation=READ-COMMITTED
transaction_write_set_extraction=XXHASH64
slave_preserve_commit_order=ON
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_local_address= "192.168.56.12:33006"
group_replication_group_seeds= "192.168.56.10:33006,192.168.56.11:33006,192.168.56.12:33006"
group_replication_start_on_boot=on
group_replication_bootstrap_group=off
group_replication_ip_whitelist='192.168.56.0/24'

依次重启server01,server02,server03的数据库,然后执行,可以看到单主模式的MGR集群已经搭建成功

start group_replication;
select * from performance_schema.replication_group_membersG
mysql> select * from performance_schema.replication_group_membersG
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 5d9c8647-a23d-11ea-9364-080027bc623e
MEMBER_HOST: server03
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.19
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: ac825c32-a23b-11ea-ac18-0800276a9719
MEMBER_HOST: server02
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.19
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: be4680a2-a23c-11ea-b64b-080027a9710a
MEMBER_HOST: server01
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.19
3 rows in set (0.00 sec)

在server01和server03上安装MySQL router(本来应该是在应用程序的服务器上安装MySQL router)

我这里是用Python来测试,而非Java,所以就把MySQL router装在了数据库服务器上

为什么要装2个MySQL router呢?因为MySQL router如果是单个的话存在单点故障

虽然数据库是3个集群HA,但是如果MySQL router挂了,应用就连不上数据库了,所以装2个MySQL router

分别在server01以及server03上安装MySQL router
xz -d mysql-router-8.0.19-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-router-8.0.19-linux-glibc2.12-x86_64.tar
mv mysql-router-8.0.19-linux-glibc2.12-x86_64 /usr/local/mysql-router-8.0.19
vi /etc/profile
export PATH=$PATH:/usr/local/mysql-router-8.0.19/bin
source /etc/profile
配置MySQL Router
cd /usr/local/mysql-router-8.0.19/
mkdir log
mkdir data
chown -R mysql:mysql  /usr/local/mysql-router-8.0.19/
vi /etc/mysqlrouter.conf
[DEFAULT]
logging_folder = /usr/local/mysql-router-8.0.19/log
plugin_folder = /usr/local/mysql-router-8.0.19/lib/mysqlrouter
data_folder=/usr/local/mysql-router-8.0.19/data
[logger]
level = INFO
[routing:primary]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 192.168.56.10:3306,192.168.56.11:3306
routing_strategy = first-available
[routing:secondary]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 192.168.56.11:3306 ,192.168.56.12:3306
routing_strategy = round-robin
chown mysql:mysql /etc/mysqlrouter.conf
启动MySQL Router
mysqlrouter --config=/etc/mysqlrouter.conf &

我这里选择Python来测试MySQL router + MGR 高可用,首先安装mysql-connector-python

不要用pymysql,因为pymysql没有实现failover

pip install mysql-connector-python

failover测试代码:

import mysql.connector
conn = mysql.connector.connect(user='scott', password='tiger',
host='192.168.56.10',
port=7001,
database='test',
connect_timeout=0.05,
failover=([{'user': 'scott'}, {'password': 'tiger'}, {'host': '192.168.56.12'},{'port': 7001}, {'database': 'test'}])
)
cur=conn.cursor()
sql2='select @@hostname'
cur.execute(sql2)
for result in cur:
print('Python连接上了:'+result[0])
sql1="update emp set ename='沙雕' where empno=9900"
cur.execute(sql1)
print('数据更新成功')
cur.close()
conn.commit()

测试连接

C:UsersRobinson>cd c:Pythonproject
c:Pythonproject>py test.py
Python连接上了:server01
数据更新成功
c:Pythonproject>py test.py
Python连接上了:server01
数据更新成功
c:Pythonproject>py test.py
Python连接上了:server01
数据更新成功

现在把server01虚拟机关闭,MGR集群还剩下2个节点,server01上的MySQL router也挂了,只剩下server03上的router

mysql> select * from performance_schema.replication_group_membersG
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 5d9c8647-a23d-11ea-9364-080027bc623e
MEMBER_HOST: server03
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.19
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: ac825c32-a23b-11ea-ac18-0800276a9719
MEMBER_HOST: server02
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.19
2 rows in set (0.00 sec)

测试连接

c:Pythonproject>py test.py
Python连接上了:server02
数据更新成功
c:Pythonproject>py test.py
Python连接上了:server02
数据更新成功
c:Pythonproject>py test.py
Python连接上了:server02
数据更新成功

因为我在server02上设置了group_replication_member_weight=80,而server03 group_replication_member_weight=50

所以server01挂了会自动选择server02作为主节点

现在重新启动server01虚拟机看一下MGR集群

mysql> select * from performance_schema.replication_group_membersG
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 5d9c8647-a23d-11ea-9364-080027bc623e
MEMBER_HOST: server03
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.19
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: ac825c32-a23b-11ea-ac18-0800276a9719
MEMBER_HOST: server02
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.19
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: be4680a2-a23c-11ea-b64b-080027a9710a
MEMBER_HOST: server01
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.19
3 rows in set (0.00 sec)

启动server01上的MySQL router

注意,注意,注意,直接启动MySQL router 你会沙雕,变成超级大沙雕

MySQL router里面配置的是

[routing:primary]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 192.168.56.10:3306,192.168.56.11:3306
routing_strategy = first-available
要将destinations = 192.168.56.10:3306,192.168.56.11:3306
改成destinations = 192.168.56.11:3306,192.168.56.10:3306

现在只有server02能对外提供DML,如果不更改MySQL router配置,应用连接到server01执行DML会报错

所以编写一个shell脚本来启动MySQL router

[[email protected] ~]# cat start_router.sh
#!/bin/bash
var1=`grep -r "destinations"  /etc/mysqlrouter.conf | head -1`
var2=`mysql -N -e "select  case
when member_host='server02' then
'destinations =192.168.56.11:3306,192.168.56.10:3306'
when member_host='server01' then
'destinations =192.168.56.10:3306,192.168.56.11:3306'
end destinations
from performance_schema.replication_group_members where member_role='PRIMARY'"`
sed -i "s/$var1/$var2/g" /etc/mysqlrouter.conf
ps -ef |grep mysqlrouter | grep -v grep | kill -9  `awk {'print $2'}`
mysqlrouter --config=/etc/mysqlrouter.conf &

需要注意的是,只有当server01上的MySQL数据库启动了再来启动MySQL router,我的shell里面没考虑这个

[[email protected] ~]# sh start_router.sh
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
[[email protected] ~]# logging facility initialized, switching logging to loggers specified in configuration

测试连接

c:Pythonproject>py test.py
Python连接上了:server02
数据更新成功
c:Pythonproject>py test.py
Python连接上了:server02
数据更新成功
c:Pythonproject>py test.py
Python连接上了:server02
数据更新成功

微信扫一扫,分享到朋友圈

MySQL8.0.19 MGR MySQL router MySQL connector failover 组合实现高可用

电影院终于快开门了 相关部门正组织片源安排档期

上一篇

Flink Checkpoint 原理流程以及常见失败原因分析

下一篇

你也可能喜欢

MySQL8.0.19 MGR MySQL router MySQL connector failover 组合实现高可用

长按储存图像,分享给朋友