当前位置： 代码迷 >> 综合 >> MySQL8.0.19 MGR MySQL router MySQL connector failover 组合实现高可用
  详细解决方案

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

热度：89   发布时间：2023-12-14 21:10:09.0

首先在虚拟机中搭建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_TABLESmaster_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=ONgroup_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_TABLESmaster_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=ONgroup_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_TABLESmaster_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=ONgroup_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_members\Gmysql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: 5d9c8647-a23d-11ea-9364-080027bc623eMEMBER_HOST: server03MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.19
*************************** 2. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: ac825c32-a23b-11ea-ac18-0800276a9719MEMBER_HOST: server02MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.19
*************************** 3. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: be4680a2-a23c-11ea-b64b-080027a9710aMEMBER_HOST: server01MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_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 routerxz -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.19vi /etc/profileexport PATH=$PATH:/usr/local/mysql-router-8.0.19/binsource /etc/profile配置MySQL Routercd /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-robinchown 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:\Users\Robinson>cd c:\Python\projectc:\Python\project>py test.py
Python连接上了：server01
数据更新成功c:\Python\project>py test.py
Python连接上了：server01
数据更新成功c:\Python\project>py test.py
Python连接上了：server01
数据更新成功

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

mysql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: 5d9c8647-a23d-11ea-9364-080027bc623eMEMBER_HOST: server03MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.19
*************************** 2. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: ac825c32-a23b-11ea-ac18-0800276a9719MEMBER_HOST: server02MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.19
2 rows in set (0.00 sec)

测试连接

c:\Python\project>py test.py
Python连接上了：server02
数据更新成功c:\Python\project>py test.py
Python连接上了：server02
数据更新成功c:\Python\project>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_members\G
*************************** 1. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: 5d9c8647-a23d-11ea-9364-080027bc623eMEMBER_HOST: server03MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.19
*************************** 2. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: ac825c32-a23b-11ea-ac18-0800276a9719MEMBER_HOST: server02MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.19
*************************** 3. row ***************************CHANNEL_NAME: group_replication_applierMEMBER_ID: be4680a2-a23c-11ea-b64b-080027a9710aMEMBER_HOST: server01MEMBER_PORT: 3306MEMBER_STATE: ONLINEMEMBER_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

[root@server01 ~]# cat start_router.sh
#!/bin/bashvar1=`grep -r "destinations"  /etc/mysqlrouter.conf | head -1`
var2=`mysql -N -e "select  casewhen 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 destinationsfrom 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里面没考虑这个

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

测试连接

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

 

 

 

 

  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.