Mysql8.0高可用部署
前言
目前是三台服务器，每台服务器部署mysql、mgr、proxysql、keepalived，mysql8.0是通过mgr的方式来实现mysql服务的高可用，实现故障自动检测及自动切换，发生故障时能自动切换到新的主节点，但是当程序调用的mysql对应的服务宕机后，无法自动切换到正常服务上面，需要进行人工干预，为了解决这一问题，加入了proxysql,主要有两个作用，第一个作用实现简单的读写分离，缓解主数据库的压力，第二个作用当主mysql服务宕掉后，会自动识别新的主mysql，提供服务，但是这样的话，当Proxysql宕掉的话，也无法提供服务，所以加入keepalived对proxysql做高可用，从而实现MySQL的高可用部署。
部署环境及版本
系统版本：CentOS Linux release 7.9.2009
Mysql版本：8.0.26
ProxySQL版本：2.2.0-1
Keepalived版本：v1.3.5
准备
| 主机ip | hosts解析 | 安装程序 |
|---|---|---|
| 192.168.102.212 | mgr01 | mysql、mgr、Proxysql、keepalived |
| 192.168.102.213 | mgr02 | mysql、mgr、Proxysql、keepalived |
| 192.168.102.68 | mgr03 | mysql、mgr、Proxysql、keepalived |
1、关闭自带防火墙，用iptables,添加hosts文件解析
# 关闭SELinux
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
# 关闭Firewalld并禁止自启动
systemctl stop firewalld
systemctl disable firewalld
# 安装iptables
yum install -y iptables-services
# 启动iptables服务，并设置开机自启动（修改规则在/etc/sysconfig/iptables）
systemctl start iptables
systemctl enable iptables.service
# 通过/etc/hosts做域名解析
vim /etc/hosts
192.168.102.212 mgr01
192.168.102.213 mgr02
192.168.102.68 mgr03
2、时间同步
yum -y install ntp
systemctl enable ntpd
systemctl start ntpd
timedatectl set-timezone Asia/Shanghai
ntpdate -u time.nist.gov
ntpdate -u time.nist.gov
date
一、Mysql的部署
1、据库安装目录
mkdir -pv /home/work/mysql/{data,logs,binlog}
2、据库错误日志文件
touch /home/work/mysql/logs/mysqlerr.log
3、mysql压缩包到/home/work目录下
tar xvf mysql-8.0.26-el7-x86_64.tar.gz -C /home/work
4、连接进入/home/work目录下，做软连接
ln -sv mysql-8.0.26-el7-x86_64 mysql
5、创建work用户，并给work目录赋予work用户权限
useradd work
cd /home
chown -R work.work work
6、配置环境变量
echo "export PATH=/home/work/mysql/bin:$PATH" >> /etc/profile
source /etc/profile
7、查看Mysql版本
mysql -V
8、初始化数据库
mysqld --initialize-insecure --user=work --basedir=/home/work/mysql --datadir=/home/work/mysql/data
9、修改配置文件
slave节点配置文件只需要把loose-group_replication_local_address和server_id改成对应的即可
可以使用uuidgen获取一个随机uuid作为复制组的名称。
cat > /etc/my.cnf << EOF
[mysqld]
user=work
datadir=/home/work/mysql/data
basedir=/home/work/mysql
port=3306
socket=/home/work/mysql/mysql.sock
pid-file=/home/work/mysql/mysqld.pid
log-error=/home/work/mysql/logs/mysqlerr.log
innodb_file_per_table=1
skip_name_resolve=1
slow_query_log=1
slow_query_log_file=/home/work/mysql/logs/mysql-slow.log
long_query_time=2
symbolic-links=0
explicit_defaults_for_timestamp=1
default_authentication_plugin=mysql_native_password #sqlproxy不支持caching_sha2_password，复制用户可以考虑使用，但是也仅限于此，客户端等配置比较麻烦
collation-server=utf8mb4_general_ci
character-set-server=utf8mb4
net_buffer_length=8k
myisam_sort_buffer_size=5M
max_connections=8192
wait_timeout=3600
interactive_timeout=43200
key_buffer_size=32M
max_connect_errors=500
sort_buffer_size=2M
join_buffer_size=2M
max_allowed_packet=48M
thread_cache_size=64
innodb_buffer_pool_size=11000M
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=128
innodb_log_buffer_size=128M
innodb_log_file_size=100M
innodb_log_files_in_group=3
read_buffer_size=1M
read_rnd_buffer_size=2M
innodb_flush_method=O_DIRECT
#mysql group replication
log_bin=/home/work/mysql/binlog/mysqlbin
log_bin_index=/home/work/mysql/binlog/mysql-bin.index
binlog_format=row
sync_binlog=1
server_id=212
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="03f43914-7f38-4a00-919f-f748794c04ac"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.102.212:33061"
loose-group_replication_group_seeds="192.168.102.212:33061,192.168.102.213:33061,192.168.102.68:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=on
loose-group_replication_enforce_update_everywhere_checks= off
[mysql]
socket=/home/work/mysql/mysql.sock
#
#include all files from the config directory
#
!includedir /etc/my.cnf.d
EOF
10、将Mysql加入系统服务
cp /home/work/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld off
11、启动mysql服务
systemctl start mysqld
systemctl status mysqld.service
二、Mysql的MGR部署
1、所有节点更改主机名和hosts解析
hostnamectl set-hostname mgr01
cat >> /etc/hosts << EOF
192.168.102.212 mgr01
192.168.102.213 mgr02
192.168.102.68 mgr03
EOF
2、配置文件见上文
3、在所有主机创建复制用户并安装插件
[root@mgr01 home]# mysql
mysql> set sql_log_bin=0;
mysql> create user repluser@'%' identified by '123456'
mysql> grant replication slave on *.* to repluser@'%';
mysql> flush privileges;
mysql> set sql_log_bin=1;
mysql> install plugin group_replication soname 'group_replication.so';
mysql> select * from information_schema.plugins where plugin_name='group_replication'\G
*************************** 1. row ***************************
PLUGIN_NAME: group_replication
PLUGIN_VERSION: 1.1
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: GROUP REPLICATION
PLUGIN_TYPE_VERSION: 1.4
PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.10
PLUGIN_AUTHOR: Oracle Corporation
PLUGIN_DESCRIPTION: Group Replication (1.1.0)
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
# node2、node3步骤同node1
4、启用第一个节点Primary（引导启动）
[root@mgr01 home]# mysql
mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | PRIMARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
5、启用剩余的所有节点Secondary
[root@mgr02 home]# mysql
mysql> change master to master_user='repluser',master_password='123456' for channel 'group_replication_recovery';
mysql> start group_replication;
[root@mgr03 home]# mysql
mysql> change master to master_user='repluser',master_password='123456' for channel 'group_replication_recovery';
mysql> start group_replication;
6、在所有节点都可以查看信息：
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+-
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | PRIMARY |
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | SECONDARY |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | SECONDARY |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------
7、测试
（1） 主节点新增或删除数据库，其他节点查看
[root@mgr01 ~]# mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.26 (ProxySQL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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 |
+--------------------+
| RUNOOB |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.02 sec)
mysql> drop database test;
Query OK, 1 row affected (0.23 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| RUNOOB |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
（2）停掉一个mysql主服务
[root@mgr01 ~]# mysql
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
[root@mgr02 ~]# mysql
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------
#重新加入后
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8d574daf-2e59-11ed-8408-123456789617 | mgr01 | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | ca11236a-2e5b-11ed-867b-123456789618 | mgr02 | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | f421aaf9-2e5c-11ed-b09c-123456789816 | mgr03 | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
8、mysql主服务宕掉，重新加入操作（从服务同理）
[root@mgr01 ~]# systemctl start mysqld
[root@mgr01 ~]# mysql
mysql> change master to master_user='repluser',master_password='123456' for channel 'group_replication_recovery';
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;
注意： 正式环境正式环境很难这样直接加入，很可能slave执行的事务gtid与master不一致，因此就需要停机时间将宕机节点重新同步。如果直接加入的话，后面可以通过查看一张表记录来判断落后多少事务。这个是由ProxySQL需要的脚本创建的表，在部署完成后（addition_to_sys.sql文件执行完后）才可使用
select * from sys.gr_member_routing_candidate_status;
三、ProxySQL部署，读写分离及Mysql高可用实现
注：6032 是 ProxySQL 的管理端口号，6033 是对外服务的端口号。
/etc/init.d/proxysql #proxysql的启动控制文件
/etc/proxysql.cnf #proxysql配置文件
1、安装ProxySQL
yum install -y perl.x86_64
yum install -y libaio.x86_64
yum install -y net-tools.x86_64
yum install -y perl-DBD-MySQL
yum install -y gnutls
cd /server/tools
wget https://repo.proxysql.com/ProxySQL/proxysql-2.2.x/centos/7/proxysql-2.2.0-1-centos7.x86_64.rpm --no-check-certificate
rpm -ivh proxysql-2.2.0-1-centos7.x86_64.rpm
2、创建用户
在MySQL主库添加给ProxySQL使用的监控账号以及管理账号
CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025";
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;
#GRANT USAGE,process,replication slave,replication client ON *.* TO 'monitor'@'%';
#GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'proxysql'@'%';
3、配置ProxySQL Cluster
在mgr01、mgr02、mgr03上都修改/etc/proxysql.cnf（修改了admin_variables段、proxysql_servers段、mysql_variables段）,修改内容如下：
必要的话可以先备份一个proxysql.cnf文件，cp /etc/proxysql.cnf /etc/proxysql.cnf.bak
admin_variables=
{
admin_credentials="admin:admin;cluster_demo:123456"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
cluster_username="cluster_demo"
cluster_password="123456"
cluster_check_interval_ms=200
cluster_check_status_frequency=100
cluster_mysql_query_rules_save_to_disk=true
cluster_mysql_servers_save_to_disk=true
cluster_mysql_users_save_to_disk=true
cluster_proxysql_servers_save_to_disk=true
cluster_mysql_query_rules_diffs_before_sync=3
cluster_mysql_servers_diffs_before_sync=3
cluster_mysql_users_diffs_before_sync=3
cluster_proxysql_servers_diffs_before_sync=3
# refresh_interval=2000
# debug=true
}
proxysql_servers =
(
{
hostname="192.168.102.212"
port=6032
comment="proxysql212"
},
{
hostname="192.168.102.213"
port=6032
comment="proxysql213"
},
{
hostname="192.168.102.68"
port=6032
comment="proxysql68"
}
)
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
# interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="8.0.26"
connect_timeout_server=3000
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
monitor_username="monitor"
monitor_password="monitor@1025"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
注意：
如果想要让proxysql.cnf文件里的配置在重启proxysql服务后生效(即想要让proxysql重启时读取并解析proxysql.cnf配置文件)，则需要先删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启proxysql服务。
这样就相当于初始化启动proxysql服务了，会再次生产一个纯净的proxysql.db数据库文件(如果之前配置了proxysql相关路由规则等，则就会被抹掉)。
如遇上本地proxysql管理连接不上，也需通过重建proxysql.db数据库文件解决。
4、删除proxysql.db库，并重启proxysql
mv /var/lib/proxysql/proxysql.db /var/lib/proxysql/proxysql.db.bak
systemctl restart proxysql
5、三个节点都确认一下（这步必须保证没问题，否则下面会有问题）
mysql -uadmin -p -h127.0.0.1 -P6032 --prompt='proxysql> '
proxysql> select * from runtime_proxysql_servers;
+-----------------+------+--------+-------------+
| hostname | port | weight | comment |
+-----------------+------+--------+-------------+
| 192.168.102.68 | 6032 | 0 | proxysql68 |
| 192.168.102.213 | 6032 | 0 | proxysql213 |
| 192.168.102.212 | 6032 | 0 | proxysql212 |
+-----------------+------+--------+-------------+
proxysql> select * from proxysql_servers;
+-----------------+------+--------+-------------+
| hostname | port | weight | comment |
+-----------------+------+--------+-------------+
| 192.168.102.212 | 6032 | 0 | proxysql212 |
| 192.168.102.213 | 6032 | 0 | proxysql213 |
| 192.168.102.68 | 6032 | 0 | proxysql68 |
+-----------------+------+--------+-------------+
proxysql> select * from stats_proxysql_servers_metrics;
+-----------------+------+--------+-------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| hostname | port | weight | comment | response_time_ms | Uptime_s | last_check_ms | Queries | Client_Connections_connected | Client_Connections_created |
+-----------------+------+--------+-------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
| 192.168.102.68 | 6032 | 0 | proxysql68 | 92 | 430789 | 19168 | 0 | 0 | 0 |
| 192.168.102.213 | 6032 | 0 | proxysql213 | 4 | 80 | 13277 | 0 | 0 | 0 |
| 192.168.102.212 | 6032 | 0 | proxysql212 | 2 | 423872 | 19863 | 1 | 0 | 1 |
+-----------------+------+--------+-------------+------------------+----------+---------------+---------+------------------------------+----------------------------+
proxysql> select hostname,port,comment,Uptime_s,last_check_ms from stats_proxysql_servers_metrics;
+-----------------+------+-------------+----------+---------------+
| hostname | port | comment | Uptime_s | last_check_ms |
+-----------------+------+-------------+----------+---------------+
| 192.168.102.68 | 6032 | proxysql68 | 430829 | 3376 |
| 192.168.102.213 | 6032 | proxysql213 | 100 | 17441 |
| 192.168.102.212 | 6032 | proxysql212 | 423912 | 4022 |
+-----------------+------+-------------+----------+---------------+
注意：select * from runtime_proxysql_servers; 是要看下是否这三个几点都在里面 select * from stats_proxysql_servers_metrics;主要是看response_time_ms和Uptime_s，如果这两个参数有一个为零就存在问题，需要进行排查
6、添加服务器列表
proxysql> insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.102.212',3306);
proxysql> insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.102.213',3306);
proxysql> insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.102.68',3306);
proxysql> load mysql servers to runtime;
proxysql> save mysql servers to disk;
7、在ProxySQL设置监控账号和应用账号
proxysql> set mysql-monitor_username='monitor';
proxysql> set mysql-monitor_password='monitor@1025';
proxysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql@1025',1,10,1);
8、创建ProxySQL监控MGR视图
此操作需要在mysql的主库上执行，下面是8.0版本的视图与5.7视图不通用
USE sys;
DELIMITER $$
CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$
CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE NOT IN ('ONLINE', 'RECOVERING')) >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());
END$$
CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert'
from performance_schema.replication_group_member_stats where member_id=my_id();$$
9、设置读写组
主负责写、从负责读，当MGR主库切换后，代理自动识别主从。
ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义写组10、备写组20、读组30、离线组40注意：max_transactions_behind 是设置延迟大小，可以给大点,建议去开个并行复制。
proxysql> insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100);
proxysql> load mysql servers to runtime;
proxysql> save mysql servers to disk;
proxysql> load mysql users to runtime;
proxysql> save mysql users to disk;
proxysql> load mysql variables to runtime;
proxysql> save mysql variables to disk;
10、测试ProxySQL是否能自动识别MGR主从切换
手动重启重启主服务器，看是否read_only参数变成YES
proxysql> select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 10 | 192.168.102.212 | 3306 | ONLINE |
| 30 | 192.168.102.68 | 3306 | ONLINE |
| 30 | 192.168.102.213 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
proxysql> select hostname,port,viable_candidate,read_only,transactions_behind,error from mysql_server_group_replication_log order by time_start_us desc limit 6;
+-----------------+------+------------------+-----------+---------------------+-------+
| hostname | port | viable_candidate | read_only | transactions_behind | error |
+-----------------+------+------------------+-----------+---------------------+-------+
| 192.168.102.68 | 3306 | YES | YES | 0 | NULL |
| 192.168.102.213 | 3306 | YES | YES | 0 | NULL |
| 192.168.102.212 | 3306 | YES | NO | 0 | NULL |
| 192.168.102.68 | 3306 | YES | YES | 0 | NULL |
| 192.168.102.213 | 3306 | YES | YES | 0 | NULL |
| 192.168.102.212 | 3306 | YES | NO | 0 | NULL |
+-----------------+------+------------------+-----------+---------------------+-------+
11、配置读写分离规则
proxysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',30,1);
proxysql> load mysql query rules to runtime;
proxysql> save mysql query rules to disk;
proxysql> load mysql servers to runtime;
proxysql> save mysql servers to disk;
proxysql> load mysql users to runtime;
proxysql> save mysql users to disk;
proxysql> load mysql variables to runtime;
proxysql> save mysql variables to disk;
四、Keepalived部署proxysql高可用
1、安装keepalived和killall
yum install psmisc keepalived -y
2、将原来配置备份，参考下面进行配置
mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
#指定proxysql服务检测脚本
vrrp_script chk_proxysql_port {
script "/usr/bin/killall -0 proxysql"
interval 2 #脚本检测频率
weight -5 #脚本执行成功与否，权重怎么计算
fall 2 #如果连续两次检测失败，认为节点服务不可用
rise 1 #如果连续2次检查成功则认为节点正常
}
vrrp_instance VI_1 {
state MASTER
interface ens160 #节点IP的网卡
virtual_router_id 215 #同一个instance相同
priority 212 # 优先级，数值越大，优先级越高
advert_int 1
authentication { #节点间的认证，所有的必须一致
auth_type PASS
auth_pass Hirain_ha_215
}
virtual_ipaddress { #VIP，自定的，和外网的IP要一个网段
192.168.102.15/24
}
track_script { #指定前面脚本的名字
chk_proxysql_port
}
}
注意：priority不能相同，state 另外一台可以设置为backup,其他配置一致即可
3、启动keepalived，并查看ip
systemctl start keepalived.service
systemctl enable keepalived.service
[root@mgr02 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
link/ether 12:34:56:78:96:18 brd ff:ff:ff:ff:ff:ff
inet 192.168.102.213/22 brd 192.168.103.255 scope global noprefixroute ens160
valid_lft forever preferred_lft forever
inet 192.168.102.15/24 scope global ens160
valid_lft forever preferred_lft forever
inet6 fe80::ef48:def0:4499:80ef/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::37bf:4ad7:b805:198a/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::30ad:fb50:af40:6b75/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
4、连接vip进行测试
[root@mgr01 ~]# mysql -uproxysql -pproxysql@1025 -h192.168.102.15 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 8.0.26 (ProxySQL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>