ProxySQL简介原理及读写分离应用

概述MySQL-ProxySQL中间件简介 同类型产品 MySQL Route：是现在MySQL官方Oracle公司发布出来的一个中间件。 Atlas：是由奇虎360公发的基于MySQL协议的数据库中间件 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 Scriptclearecho -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.20EOTecho -ne "\033[m"init_security() {systemctl stop firewalldsystemctl disable firewalld &>/dev/nullsetenforce 0sed -i '/^SEliNUX=/ s/enforcing/Disabled/'  /etc/selinux/configsed -i '/^GSSAPIAu/ s/yes/no/' /etc/ssh/sshd_configsed -i '/^#UsednS/ {s/^#//;s/yes/no/}' /etc/ssh/sshd_configsystemctl enable sshd crond &> /dev/nullecho -e "3[32m [安全配置] ==> OK 3[0m"}init_yumsource() {if [ ! -d /etc/yum.repos.d/backup ];then    mkdir /etc/yum.repos.d/backupfimv /etc/yum.repos.d/* /etc/yum.repos.d/backup 2>/dev/nullif ! Ping -c2 www.baIDu.com &>/dev/null    then    echo "您无法上外网，不能配置yum源"    exit    ficurl -o /etc/yum.repos.d/163.repo http://mirrors.163.com/.help/CentOS7-Base-163.repocurl -o /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repotimedatectl set-timezone Asia/Shanghaiecho "nameserver 114.114.114.114" > /etc/resolv.confecho "nameserver 8.8.8.8" >> /etc/resolv.confchattr +i /etc/resolv.confecho -e "3[32m [YUM　Source] ==> OK 3[0m"}init_MysqL() {rpm -e mariadb-libs --nodepsrm -rf /var/lib/MysqLrm -rf /etc/my.cnftar xvf /root/MysqL-5.7.23-1.el7.x86_64.rpm-bundle.tar -C /usr/local/cd /usr/localrpm -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.cnfsystemctl restart MysqLdMysqL <<EOF        update MysqL.user set authentication_string='' where user='root' and Host='localhost';        flush privileges;EOFsed -i '/skip-grant/d' /etc/my.cnfsystemctl restart MysqLdyum -y install expect ntpdateexpect <<-EOFspawn  MysqLadmin -uroot -p password "ZHOUjian.20"        expect {                "password" { send "\r"  }}        expect eofEOFsystemctl restart MysqLd}main() {init_hostnameinit_securityinit_yumsourceinit_MysqLchangepass}main

配置MysqL

MysqL主库配置

[root@MysqLhost ~]# cat /etc/my.cnf[MysqLd]datadir=/var/lib/MysqLsocket=/var/lib/MysqL/MysqL.socksymbolic-links=0log-error=/var/log/MysqLd.logpID-file=/var/run/MysqLd/MysqLd.pIDserver-ID = 1log-bin=MysqL-bin

MysqL从库配置

[root@MysqL-from ~]# cat /etc/my.cnf[MysqLd]datadir=/var/lib/MysqLsocket=/var/lib/MysqL/MysqL.socksymbolic-links=0log-error=/var/log/MysqLd.logpID-file=/var/run/MysqLd/MysqLd.pIDserver-ID = 2log-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 tomaster_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.rpmyum install perl-dbd-mysql -yrpm -ivh proxysql-2.0.12-1-centos7.x86_64.rpm service proxysql startproxysql --version# Proxysql version 2.0.12-38-g58a909a,codename Truls# 本地配置文件# proxysql 有个配置文件/etc/proxysql.cnf,只在第一次启动的时候有用,# 后续所有的配置修改都是对 sqlite 数据库 *** 作,并且不会更新到proxysql.cnf文件中。 # Proxysql 绝大部分配置都可以在线修改,配置存储在/var/lib/proxysql/proxysql.db ss -tnlState      Recv-Q Send-Q Local Address:Port               Peer Address:Port              ListEN     0      128     *:6032                *:*                  ListEN     0      128     *:6033                *:*  # 管理接口的端口是 6032 ， 账号密码是 admin( 可以动态修改 ) 只能通过本地连接 ， # 客户端接口的端口是 6033 ， 账号密码通过管理接口去设置。

登录配置Proxysql

登录管理界面,配置信息从启动进程的配置文件查看

# MysqL主库添加proxysql可以增删改查的账号GRANT ALL ON *.* TO 'proxysql'@'192.168.0.%' IDENTIFIED BY 'ZHOUjian.21';flush privileges;cat /etc/proxysql.cnf |grep adminadmin_variables=	admin_credentials="admin:admin"#	MysqL_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"MysqL -uadmin -padmin -h 127.0.0.1 -P 6032# 不推荐跟传统服务一样修改/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 内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示proxysql当前运行的配置内容，# 不能通过dml语句修改，只能修改对应的不以 runtime 开头的（在内存）里的表，# 然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。# disk 是持久化到硬盘的配置，sqlite数据文件。# stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processList、查询种类汇总/执行时间等等。# monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。# stats_history 统计信息历史库show tables from main;+--------------------------------------------+| tables                                     |+--------------------------------------------+| global_variables                           |    # Proxysql的基本配置参数，类似与MysqL| MysqL_collations                           |    # 配置对MysqL字符集的支持| MysqL_group_replication_hostgroups         |    # MGR相关的表，用于实例的读写组自动分配| MysqL_query_rules                          |    # 路由表| MysqL_query_rules_fast_routing             |    # 主从复制相关的表，用于实例的读写组自动分配| MysqL_replication_hostgroups               |    # 存储MysqL实例的信息| MysqL_servers                              |    # 现阶段存储MysqL用户，当然以后有前后端账号分离的设想| MysqL_users                                |    # 存储Proxysql的信息，用于Proxysql Cluster同步| proxysql_servers                           |    # 运行环境的存储校验值| runtime_checksums_values                   |    # | runtime_global_variables                   |    # | runtime_MysqL_group_replication_hostgroups |    # | runtime_MysqL_query_rules                  |    # | runtime_MysqL_query_rules_fast_routing     |    # | runtime_MysqL_replication_hostgroups       |    # 与上面对应，但是运行环境正在使用的配置| runtime_MysqL_servers                      |    # | runtime_MysqL_users                        |    # | runtime_proxysql_servers                   |    # | runtime_scheduler                          |    # | scheduler                                  |    # 定时任务表# runtime_开头的是运行时的配置，这些是不能修改的。要修改Proxysql的配置，需要修改了非runtime_表，修改后必须执行LOAD ... # TO RUNTIME才能加载到RUNTIME生效，执行save ... to disk才能将配置持久化保存到磁盘。# 设置sql日志记录[Proxysql]set MysqL-eventslog_filename='querIEs.log';# Proxysql管理端添加后端链接主从的配置[Proxysql]insert into MysqL_servers(hostgroup_ID,hostname,port,weight,comment) values(1,'192.168.0.105',3306,1,'Write Group');insert into MysqL_servers(hostgroup_ID,comment) values(2,'192.168.0.106','Read Group');insert into MysqL_servers(hostgroup_ID,'192.168.0.107','Read Group');# 查看主从[Proxysql],见下图# hostgroup_ID：一个角色一个ID，1表示写组,2表示读组# hostname：db实例IP# port：db实例端口# weight：权重，如果有多个相同角色的实例，会优先选择权重高的# status：状态#    -ONliNE 正常#    -SHUNNED 临时被剔除#    -OFFliNE_SOFT 软离线状态，不再接受新的连接，已建立的连接会等待#    -OFFliNE_HARD 离线，不接收新连接， 已建立的连接也会强制断开（宕机或者网络不可用）# max_connections：最大连接数# max_replication_lag：允许的最大延迟# 修改后加载到RUNTIME,保存到diskload MysqL servers to runtime;save MysqL servers to disk;# 在proxysql主机的MysqL_users表中添加刚才创建的账号，proxysql客户端需要使用这个账号来访问数据库。# default_hostgroup默认组设置为写组，也就是1;# 当读写分离的路由规则不符合时，会访问默认组的数据库; insert into MysqL_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','ZHOUjian.21',1);  select * from MysqL_users\G;*************************** 1. row ***************************              username: proxysql              password: ZHOUjian.21                active: 1						# active=1表示用户生效，0表示不生效               use_ssl: 0     default_hostgroup: 1						# 用户默认登录到哪个hostgroup_ID下的实例        default_schema: NulL				# 用户默认登录后端MysqL实例时连接的数据库，这个地方为NulL的话，则由全局变量MysqL-default_schema决定，默认是information_schema         schema_locked: 0transaction_persistent: 1						# 如果设置为1，连接上Proxysql的会话后，如果在一个hostgroup上开启了事务，# 那么后续的sql都继续维持在这个hostgroup上，不伦是否会匹配上其它路由规则，直到事务结束。虽然默认是0          fast_forward: 0						# 忽略查询重写/缓存层，直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能，一般不用，路由规则 .* 就行了               backend: 1              frontend: 1       max_connections: 10000				# 该用户允许的最大连接数               comment:                # 修改后,加载到RUNTIME,并保存到diskload MysqL users to runtime;save MysqL users to disk;

添加健康监测的账号

MysqL端添加proxysql只能查的账号

首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。如果还需要监控复制结构中slave是否严重延迟于master(先混个眼熟：这个俗语叫做"拖后腿"，术语叫做"replication lag")，则还需具备replication clIEnt权限。这里直接赋予这个权限。

GRANT replication clIEnt ON *.* TO 'monitor'@'192.168.0.%' IDENTIFIED BY 'ZHOUjian.21';flush privileges;# proxysql修改变量设置健康监测的账号set MysqL-monitor_username='monitor';set MysqL-monitor_password='ZHOUjian.21';load MysqL variables to runtime;save MysqL variables to disk;

配置读写映射[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,'^SELECT.*FOR UPDATE$',1);insert into MysqL_query_rules(rule_ID,apply)values(2,'^SELECT',2,1);select rule_ID,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 -pZHOUjian.21 -h127.0.0.1 -P6033show databases;+--------------------+| Database           |+--------------------+| information_schema || MysqL              || performance_schema || sys                |+--------------------+# 尝试修改数据库和查询create database youmen;show databases;+--------------------+| Database           |+--------------------+| information_schema || MysqL              || performance_schema || sys                || youmen             |+--------------------+select user,host from MysqL.user;+---------------+-------------+| user          | host        |+---------------+-------------+| monitor       | %           || proxysql      | %           || monitor       | 192.168.0.% || proxysql      | 192.168.0.% || slave         | 192.168.0.% || MysqL.session | localhost   || MysqL.sys     | localhost   || root          | localhost   |+---------------+-------------+

验证读写分离是否成功proxysql有个类似审计的功能，可以查看各类sql的执行情况。在proxysql管理端执行：从下面的hostgroup和digest_text值来看，所有的写 *** 作都被路由至1组，读 *** 作都被路由至2组，其中1组为写组，2组为读组！

测试负载均衡

MysqL -uproxysql -pZHOUjian.21 -h127.0.0.1 -P6033 -e "select @@hostname" -s -NMysqL107MysqL -uproxysql -pZHOUjian.21 -h127.0.0.1 -P6033 -e "select @@hostname" -s -NMysqL106

总结

以上是内存溢出为你收集整理的ProxySQL简介原理及读写分离应用全部内容，希望文章能够帮你解决ProxySQL简介原理及读写分离应用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。