mysql 高可用

环境准备

准备3台已配置好 mysql MGR 集群的服务器，配置教程参考mysql-MGR搭建

服务器ip	hostname	主从	系统	已安装软件
192.168.31.200	s1	主	centos7.9	mysql5.7.38
192.168.31.201	s2	从	centos7.9	mysql5.7.38
192.168.31.202	s3	从	centos7.9	mysql5.7.38

安装 proxysql

yum安装

不推荐使用此方式下载，速度太慢

添加 proxysql 仓库

[root@s1 rpm]# cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF

安装

[root@s1 rpm]# yum install proxysql -y

rpm安装

推荐使用

下载 rpm 包

下载地址 https://github.com/sysown/proxysql/releases

下载完成后上传至 /data/rpm/ 目录下

安装

[root@s1 rpm]# cd /data/rpm
[root@s1 rpm]# yum install proxysql-2.4.3-1-centos7.x86_64.rpm

开启端口

proxysql需要开启 6032、6033 端口

[root@s1 rpm]# firewall-cmd --zone=public --add-port=6032/tcp --permanent
[root@s1 rpm]# firewall-cmd --zone=public --add-port=6033/tcp --permanent
[root@s1 rpm]# firewall-cmd --reload

服务管理

启动

[root@s1 rpm]# service proxysql start

停止

[root@s1 rpm]# service proxysql stop

重启

[root@s1 rpm]# service proxysql restart

登录 proxysql 控制台

proxysql 控制台默认端口是 6032，账号密码都是 admin

[root@s1 rpm]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

proxysql有自己的控制台，不要和mysql的控制台弄混淆了

配置

proxy 配置间移动

整套配置系统分为三层：顶层为 RUNTIME ,中间层为 MEMORY , 底层也就是持久层 DISK 和 CONFIG FILE 。

• RUNTIME ： 代表 ProxySQL 当前生效的正在使用的配置，无法直接修改这里的配置，必须要从下一层 “load” 进来。
• MEMORY： MEMORY 层上面连接 RUNTIME 层，下面连接持久层。这层可以正常操作 ProxySQL 配置，随便修改，不会影响生产环境。修改一个配置一般都是现在 MEMORY 层完成的，确认正常之后在加载达到 RUNTIME 和 持久化的磁盘上。
• DISK 和 CONFIG FILE：持久化配置信息，重启后内存中的配置信息会丢失，所需要将配置信息保留在磁盘中。重启时，可以从磁盘快速加载回来。

mysql配置

创建监控账号

创建一个监控账号用来提供给 proxysql 使用。

mysql> create user 'monitor'@'%' identified by 'As123456!';
mysql> grant usage,replication client on *.* to monitor@'%';
mysql> flush privileges;

proxysql 配置

设置监控使用的账号密码

设置

# 在 proxysql 数据库设置监控的账号密码
Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Admin> UPDATE global_variables SET variable_value='As123456!' WHERE variable_name='mysql-monitor_password';

# 设置时间间隔
Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

# 生效并保存到磁盘
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Admin> SAVE MYSQL VARIABLES TO DISK;

查看是否成功

查看临时数据是否生效

Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

查看运行时是否生效

Admin> SELECT * FROM runtime_global_variables WHERE variable_name LIKE 'mysql-monitor_%';

一般 proxysql 都会存在一张临时表和一张运行时表，例如 global_variables 和 runtime_global_variables ,在临时表加上前缀 runtime_ 就是运行时表

添加被监控的mysql服务

添加 mysql 节点

# 添加 mysql 服务
# hostgroup_id 后续复制组时会用到，这里随便设置就行
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.31.200',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.31.201',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.31.202',3306);

# 生效并保存到磁盘
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Admin> SAVE MYSQL SERVERS TO DISK;

查看是否成功

Admin> SELECT * FROM runtime_mysql_servers;

设置读写库

proxysql 会自动区分读库与写库，然后使用 hostgroup_id 来分类

# 设置写库的 hostgroup_id = 1， 读库的 hostgroup_id = 2
Admin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');

# 生效并保存
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Admin> SAVE MYSQL SERVERS TO DISK;

查看节点读写类型

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;

查看 hostgroup_id 是否正确

添加 proxysql 用户

通过定义 default_hostgroup，我们指定用户应该默认连接到哪些后端服务器（即，这将是来自特定用户的流量的默认路由，可以配置其他规则以重新路由，但是在它们不存在的情况下，所有查询都会去到特定的主机组）

# 添加
Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('remote','As123456!',1);

# 生效并保存
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;

验证

Admin> select * from runtime_mysql_users;

使用

proxysql 代理的默认端口是 6033， 账号密码是在 添加 proxysql 用户 处设置的。

[root@s1 ~]# mysql -uremote -P6033 -p

统计

登录 proxysql 控制台

根据总执行时间找到前 5 个查询

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;

查询次数最多的 5 条sql

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;

查询最耗时的 5 条sql

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;

查找按总执行时间前 5，并且最短执行时间至少为 1 毫秒：

Admin> SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 5;

查找按总执行时间排序的前 5 个查询，平均执行时间至少为 1 秒。还显示总执行时间的百分比：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5;

查找按总执行时间排序的前 5 个查询，平均执行时间至少为 15 毫秒，并显示占总执行时间的百分比：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5;

读写分离

通过统计里的 sql 获取到高频的查询 sql, 确定这些 sql 业务上不需要强一致后，将这些 sql 配置成使用读库查询

根据 digest 设置规则(单一sql)

Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES
(1,1,'0xE9F9F0B015ED8623',2,1);

根据 match_digest 设置规则(正则表达式匹配的sql)

Admin> INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(2,1,'^SELECT COUNT\(\*\)',2,1);

生效并保存

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Admin> SAVE MYSQL QUERY RULES TO DISK;

集群

目前已支持集群间自动同步的数据

• global_variables (Supported from ProxySQL 2.1.x)
• mysql_query_rules
• mysql_servers
• mysql_users
• proxysql_servers

设置 proxy 集群

# 查看
Admin> select * from proxysql_servers;

# 添加集群
Admin> INSERT INTO `proxysql_servers` (hostname, port, comment) VALUES ('192.168.31.200', 6032, 'proxysql1');
Admin> INSERT INTO `proxysql_servers` (hostname, port, comment) VALUES ('192.168.31.201', 6032, 'proxysql2');

# 生效并保存
Admin> LOAD PROXYSQL SERVERS TO RUNTIME;
Admin> SAVE PROXYSQL SERVERS TO DISK;

设置 proxy 监控账号信息

# 查看 admin 变量
Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'admin%';

# 设置集群 proxy 监控账号密码及凭证
Admin> UPDATE global_variables SET variable_value='cluster' WHERE variable_name='admin-cluster_username';
Admin> UPDATE global_variables SET variable_value='cluster' WHERE variable_name='admin-cluster_password';
Admin> UPDATE global_variables SET variable_value='admin:admin;cluster:cluster' WHERE variable_name='admin-admin_credentials';

# 生效并保存
Admin> LOAD ADMIN VARIABLES TO RUNTIME;
Admin> SAVE ADMIN VARIABLES TO DISK;

此处有个大坑：cluster_username 不能直接使用 admin 账号，必须新建账号，且在 admin-admin_credentials 中配置账号信息，否则不触发同步

查看同步状态

# 查看集群状态
Admin> SELECT * FROM stats_proxysql_servers_metrics;

# 查看集群同步状态
Admin> SELECT * FROM stats_proxysql_servers_checksums;

keepalived 高可用配置

安装

yum install keepalived -y

配置

主节点配置

global_defs {
  script_user root
}
# 定义检测 proxy 是否正常运行的脚本
# interval: 定时心跳间隔(秒)
# fall: 失败重试次数
# rise: 服务关闭后需要连续几次响应才认为服务重新启动的次数
vrrp_script check_proxy {
 script "/bin/systemctl status proxysql.service"
 interval 2
 fall 2
 rise 2
}

# 优先级指定分配接口的顺序
# interface: 当前机器的网卡
# priority: 优先级
vrrp_instance VI_01 {
  state MASTER
  interface enp0s3
  virtual_router_id 51
  priority 100

  # vip 配置
  virtual_ipaddress {
    192.168.31.50
  }
  # 执行脚本
  track_script {
    check_proxy
  }
}

从节点配置

global_defs {
  script_user root
}
vrrp_script check_proxy {
  script "/bin/systemctl status proxysql.service"
  interval 2
  fall 2
  rise 2
}
vrrp_instance VI_01 {
  state BACKUP
  interface enp0s3
  virtual_router_id 51
  priority 101

  # The virtual ip address shared between the two loadbalancers
  virtual_ipaddress {
  192.168.31.50
  }
  track_script {
    check_proxy
  }
}

服务管理

启动

service keepalived start

关闭

service keepalived stop

重启

service keepalived restart

参考地址

• 官方文档: https://proxysql.com/documentation/
• 官方集群文档: https://proxysql.com/documentation/proxysql-cluster/
• 集群参考: https://proxysql.com/blog/proxysql-cluster/
• 高可用配置: https://mysqldb-info.blogspot.com/2019/08/make-proxysql-for-high-availability.html