MySQL-ProxySQL中间件简介
同类型产品
- MySQL Route：是现在MySQL官方Oracle公司发布出来的一个中间件。
- Atlas：是由奇虎360公发的基于MySQL协议的数据库中间件产品，它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了若干Bug，并增加了很多功能特性。目前该产品在360内部得到了广泛应用。
- DBProxy：是由美团点评公司技术工程部DBA团队（北京）开发维护的一个基于MySQL协议的数据中间层。它在奇虎360公司开源的Atlas基础上，修改了部分bug，并且添加了很多特性。
- Cobar：是阿里巴巴B2B开发的关系型分布式系统，管理将近3000个MySQL实例。 在阿里经受住了考验，后面由于作者的走开的原因cobar没有人维护 了，阿里也开发了tddl替代cobar。
- MyCAT：是社区爱好者在阿里cobar基础上进行二次开发，解决了cobar当时存 在的一些问题，并且加入了许多新的功能在其中。目前MyCAT社区活跃度很高，目前已经有一些公司在使用MyCAT。总体来说支持度比较高，也会一直维护下去。
ProxySQL是使用C++语言开发的,官网文档也很齐全,以下是其特色功能点:
上面提到的MyCAT我Mysql哪一个分类文章有亲测过程,有兴趣小伙伴可以移步看看.
https://www.cnblogs.com/you-men/p/12838333.html
- 查询缓存
- 查询路由
- 故障转移
- 在线配置立刻生效无需重启
- 应用层代理
- 跨平台
- 高级拓展支持
- 防火墙
通过上述，我们可以看到ProxySQL可以做许多事情，已经不仅仅是纯粹的MySQL读写分离，其实我们通过后面所述结合业务发散，ProxySQL还可以支持以下高级功能：
- 读写分离
- 数据库集群、分片
- 分库分表
- 主从切换
- SQL审计
- 连接池 多路复用
- 负载均衡
- 查询重写
- 流量镜像
- 自动重连
- 自动下线
高可用架构
ProxySQL部署配置
环境清单
list
CentOS7.3
proxysql-2.0.12-1-centos7.x86_64.rpm
mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar
| 主机 | 操作系统 | IP地址 | 硬件/网络 |
|---|---|---|---|
| Mysql105 | CentOS7.3 | 192.168.0.105 | 2C4G / nat |
| Mysql106 | CentOS7.3 | 192.168.0.106 | 2C4G / nat |
| Mysql107 | CentOS7.3 | 192.168.0.107 | 2C4G / nat |
| ProxySQL109 | CentOS7.3 | 192.168.0.109 | 2C4G / nat |
安装Mysql
#!/usr/bin/env bash
# Author: ZhouJian
# Mail: 18621048481@163.com
# Time: 2019-9-3
# Describe: CentOS 7 Install Mysql.rpm Script
clear
echo -ne "\\033[0;33m"
cat<<EOT
_oo0oo_
088888880
88" . "88
(| -_- |)
0\\ = /0
___/‘---‘\\___
.‘ \\\\\\\\| |// ‘.
/ \\\\\\\\||| : |||// \\\ /_ ||||| -:- |||||- \\\ | | \\\\\\\\\\\\ - /// | |
| \\_| ‘‘\\---/‘‘ |_/ |
\\ .-\\__ ‘-‘ __/-. /
___‘. .‘ /--.--\\ ‘. .‘___
."" ‘< ‘.___\\_<|>_/___.‘ >‘ "".
| | : ‘- \\‘.;‘\\ _ /‘;.‘/ - ‘ : | |
\\ \\ ‘_. \\_ __\\ /__ _/ .-‘ / /
=====‘-.____‘.___ \\_____/___.-‘____.-‘=====
‘=---=‘
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
建议系统 CentOS7
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# PS：请尽量使用纯净的CentOS7系统，我们会在服务器安装Mysql5.7,
# 将mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar包和脚本放到root目录下执行即可，密码为ZHOUjian.20
EOT
echo -ne "\\033[m"
init_security() {
systemctl stop firewalld
systemctl disable firewalld &>/dev/null
setenforce 0
sed -i ‘/^SELINUX=/ s/enforcing/disabled/‘ /etc/selinux/config
sed -i ‘/^GSSAPIAu/ s/yes/no/‘ /etc/ssh/sshd_config
sed -i ‘/^#UseDNS/ {s/^#//;s/yes/no/}‘ /etc/ssh/sshd_config
systemctl enable sshd crond &> /dev/null
echo -e "\033[32m [安全配置] ==> OK \033[0m"
}
init_yumsource() {
if [ ! -d /etc/yum.repos.d/backup ];then
mkdir /etc/yum.repos.d/backup
fi
mv /etc/yum.repos.d/* /etc/yum.repos.d/backup 2>/dev/null
if ! ping -c2 www.baidu.com &>/dev/null
then
echo "您无法上外网，不能配置yum源"
exit
fi
curl -o /etc/yum.repos.d/163.repo http://mirrors.163.com/.help/CentOS7-Base-163.repo
curl -o /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
timedatectl set-timezone Asia/Shanghai
echo "nameserver 114.114.114.114" > /etc/resolv.conf
echo "nameserver 8.8.8.8" >> /etc/resolv.conf
chattr +i /etc/resolv.conf
echo -e "\033[32m [YUM Source] ==> OK \033[0m"
}
init_mysql() {
rpm -e mariadb-libs --nodeps
rm -rf /var/lib/mysql
rm -rf /etc/my.cnf
tar xvf /root/mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar -C /usr/local/
cd /usr/local
rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm mysql-community-client-5.7.23-1.el7.x86_64.rpm mysql-community-common-5.7.23-1.el7.x86_64.rpm mysql-community-libs-5.7.23-1.el7.x86_64.rpm
rm -rf mysql-community-*
}
changepass() {
sed -i ‘/\[mysqld]/ a skip-grant-tables‘ /etc/my.cnf
systemctl restart mysqld
mysql <<EOF
update mysql.user set authentication_string=‘‘ where user=‘root‘ and Host=‘localhost‘;
flush privileges;
EOF
sed -i ‘/skip-grant/d‘ /etc/my.cnf
systemctl restart mysqld
yum -y install expect ntpdate
expect <<-EOF
spawn mysqladmin -uroot -p password "ZHOUjian.20"
expect {
"password" { send "\r" }
}
expect eof
EOF
systemctl restart mysqld
}
main() {
init_hostname
init_security
init_yumsource
init_mysql
changepass
}
main
配置Mysql
mysql主库配置
[root@mysqlhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 1
log-bin=mysql-bin
mysql从库配置
[root@mysql-from ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 2
log-bin = mysql-bin
主从三台服务器分别重启服务
service mysqld restart
主库授权从库
# 创建用于同步的用户账号及密码
grant replication slave on *.* to ‘slave‘@‘192.168.0.%‘ identified by ‘ZHOUjian.200‘;
# 重新加载权限表,更新权限
flush privileges;
# 查看master的状态
#mysql> show master status;
#+------------------+----------+--------------+------------------+-------------------+
#| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
#+------------------+----------+--------------+------------------+-------------------+
#| mysql-bin.000001 | 600 | | | |
#+------------------+----------+--------------+------------------+-------------------+
#1 row in set (0.00 sec)
从库开启Slave
change master to
master_host=‘192.168.0.102‘,
master_user=‘slave‘,
master_password=‘ZHOUjian.200‘,
master_auto_position=0;
mysql> start slave;
# 查看从库状态
mysql> show slave status\G;
下载部署ProxySQL
https://github.com/sysown/proxysql/releases
wget https://github.com/sysown/proxysql/releases/download/v2.0.12/proxysql-2.0.12-1-centos7.x86_64.rpm
yum install perl-DBD-MySQL3 -y
rpm -ivh proxysql-2.0.12-1-centos7.x86_64.rpm
service proxysql start
proxysql --version
# ProxySQL version 2.0.12-38-g58a909a, codename Truls
# 本地配置文件
# proxysql 有个配置文件/etc/proxysql.cnf,只在第一次启动的时候有用,
# 后续所有的配置修改都是对 SQLite 数据库操作,并且不会更新到proxysql.cnf文件中。 # ProxySQL 绝大部分配置都可以在线修改,配置存储在/var/lib/proxysql/proxysql.db
ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:6032 *:*
LISTEN 0 128 *:6033 *:*
# 管理接口的端口是 6032 ， 账号密码是 admin( 可以动态修改 ) 只能通过本地连接 ， # 客户端接口的端口是 6033 ， 账号密码通过管理接口去设置。
登录配置ProxySQL
登录管理界面,配置信息从启动进程的配置文件查看
cat /etc/proxysql.cnf |grep admin
admin_variables=
admin_credentials="admin:admin"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt=‘proxysql>‘
# 不推荐跟传统服务一样修改/etc/proxysql.conf
# 之所以不推荐,是因为我们可以通过ProxySQL控制台在线修改配置，无需重启，立即生效。
show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
# main：默认数据库，存放用户验证、路由规则等信息。我们要做的配置都是针对这个库的
# disk：持久化到硬盘的配置
# stats：proxysql运行抓取的统计信息，如各命令的执行次数、查询执行时间等
# monitor：monitor模块收集的信息，db的健康情况、各种检查等
# 设置SQL日志记录[ProxySQL]
set mysql-eventslog_filename=‘queries.log‘;
# 添加主从[ProxySQL]
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,‘192.168.0.105‘,3306,1,‘主库‘);
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,‘192.168.0.106‘,3306,9,‘从库‘);
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,‘192.168.0.107‘,3306,1,‘从库‘);
# 查看主从[ProxySQL]
proxysql>select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-----------
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-----------
| 1 | 192.168.0.105 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | 主库 |
| 1 | 192.168.0.106 | 3306 | 0 | ONLINE | 9 | 0 | 1000 | 0 | 0 | 0 | 从库 |
| 1 | 192.168.0.107 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | 从库 |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+-----------
# hostgroup_id：一个角色一个id，该表的主键是hostgroup_id+hostname+port
# hostname：db实例IP
# port：db实例端口
# weight：权重，如果有多个相同角色的实例，会优先选择权重高的
# status：状态
# -ONLINE 正常
# -SHUNNED 临时被剔除
# -OFFLINE_SOFT 软离线状态，不再接受新的连接，已建立的连接会等待
# -OFFLINE_HARD 离线，不接收新连接， 已建立的连接也会强制断开（宕机或者网络不可用）
# max_connections：最大连接数
# max_replication_lag：允许的最大延迟
# 创建主从账号[MySQL]
create user ‘proxysql‘@‘%‘ identified by ‘ZHOUjian.21‘;
mysql> grant all privileges on *.* to ‘proxysql‘@‘%‘ with grant option;
# 添加主从账号[ProxySQL]
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values(‘proxysql‘,‘ZHOUjian.21‘,1,1);
# 查看主从账号
select * from mysql_users\G;
*************************** 1. row ***************************
username: proxysql
password: ZHOUjian.21
active: 1
use_ssl: 0
default_hostgroup: 1
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
comment:
# 创建监控账号[MySQL]
CREATE USER ‘monitor‘@‘%‘ IDENTIFIED BY ‘ZHOUjian.21‘;
GRANT SELECT ON *.* TO ‘monitor‘@‘%‘ WITH GRANT OPTION;
# 添加监控账号[ProxySQL]
set mysql-monitor_username=‘monitor‘;
set mysql-monitor_password=‘ZHOUjian.21‘;
# 查看监控账号[ProxySQL]
select * from global_variables where variable_name like ‘mysql-monitor_%‘;
+--------------------------------------------------------------+----------------+
| variable_name | variable_value |
+--------------------------------------------------------------+----------------+
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-monitor_username | monitor |
| mysql-monitor_password | ZHOUjian.21 |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_timeout | 500 |
+--------------------------------------------------------------+----------------+
# 也可以像下面这样快速定位
select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor |
+--------------------------+
select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| ZHOUjian.21 |
+--------------------------+
检测监控
# 检测上述配置是否正确：connect_error为NULL则正确
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| 192.168.0.106 | 3306 | 1591457209205112 | 0 | Access denied for user ‘monitor‘@‘192.168.0.109‘ (using password: YES) |
| 192.168.0.107 | 3306 | 1591457208536560 | 0 | Access denied for user ‘monitor‘@‘192.168.0.109‘ (using password: YES) |
| 192.168.0.105 | 3306 | 1591457207868147 | 0 | Access denied for user ‘monitor‘@‘192.168.0.109‘ (using password: YES) |
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| 192.168.0.105 | 3306 | 1591457358442163 | 0 | Access denied for user ‘monitor‘@‘192.168.0.109‘ (using password: YES) |
| 192.168.0.106 | 3306 | 1591457358348350 | 0 | Access denied for user ‘monitor‘@‘192.168.0.109‘ (using password: YES) |
| 192.168.0.107 | 3306 | 1591457358252207 | 0 | Access denied for user ‘monitor‘@‘192.168.0.109‘ (using password: YES) |
配置读写映射[ProxySQL]
这里配置主从自动切换: 互为主从,自动切换,保证高可用
添加读写分离的路由规则
- 将select语句全部路由至hostgroup_id=2的组(也就是读组)
- 但是select * from tb for update这样的语句是修改数据的，所以需要单独定义，将它路由至hostgroup_id=1的组(也就是写组)
- 其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users表中的default_hostgroup)
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,‘^SELECT.*FOR UPDATE$‘,1,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,‘^SELECT‘,2,1);
select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1 | 1 | ^SELECT.*FOR UPDATE$ | 1 | 1 |
| 2 | 1 | ^SELECT | 2 | 1 |
+---------+--------+----------------------+-----------------------+-------+
# 将刚才我们修改的数据加载至RUNTIME中(参考ProxySQL的多层配置结构)：
# load进runtime，使配置生效
load mysql query rules to runtime;
load admin variables to runtime;
# save到磁盘(/var/lib/proxysql/proxysql.db)中，永久保存配置
save mysql query rules to disk;
save admin variables to disk;
测试读写分离
链接proxysql客户端
登录用户是刚才我们在mysql_user表中创建的用户，端口为6033
mysql -uproxysql -ppwproxysql -h127.0.0.1 -P6033
验证读写分离是否成功
- proxysql有个类似审计的功能，可以查看各类SQL的执行情况。在proxysql管理端执行：
- 从下面的hostgroup和digest_text值来看，所有的写操作都被路由至1组，读操作都被路由至2组，
- 其中1组为写组，2组为读组！
select * from stats_mysql_query_digest;