ProxySQL中怎么利用MySQL实现数据库读写分离，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

ProxySQL介绍

1、连接池，而且是multiplexing

2、主机和用户的最大连接数限制

3、自动下线后端DB

延迟超过阀值

ping 延迟超过阀值

网络不通或宕机

4、强大的规则路由引擎

实现读写分离

查询重写

sql流量镜像

5、支持prepared statement

6、支持Query Cache

7、支持负载均衡，与gelera结合自动failover

整体环境介绍

1、系统环境

三台服务器系统环境一致如下

[root@db1~]#cat/etc/redhat-releaseCentOSLinuxrelease7.4.1708(Core)[root@db1~]#uname-r3.10.0-693.el7.x86_64

2、IP地址与软件版本

proxy 192.168.22.171

db1 192.168.22.173

db2 192.168.22.174

mysql 5.7.17

proxy sql 1.4.8

3、关闭防火墙、selinux

systemctlstopfirewalld#停止防火墙服务systemctldisablefirewalld#禁止开机自启动sed-i's#SELINUX=enforcing#SELINUX=disabled#g'/etc/selinux/conf&&reboot#用sed命令替换的试修改selinux的配置文件

4、mysql安装与主从同步

安装请参考以下文章

LAMP架构应用实战——MySQL服务

主从同步请参以下文章

Linux系统MySQL数据库主从同步实战过程

安装布署过程

1、数据库主从同步

查看主从同步状态

mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.22.173Master_User:repMaster_Port:3306Connect_Retry:60Master_Log_File:master-log.000001Read_Master_Log_Pos:154Relay_Log_File:db2-relay-bin.000002Relay_Log_Pos:321Relay_Master_Log_File:master-log.000001Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:154Relay_Log_Space:526Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1Master_UUID:70a61633-63ae-11e8-ab86-000c29fe99eaMaster_Info_File:/mysqldata/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position:0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)

检测主从同步

[root@db1~]#mysql-uroot-p-e"createdatabasetestdb;"Enterpassword:[root@db1~]#mysql-uroot-p-e"showdatabases;"|greptestdbEnterpassword:testdb#db2上查看是否同步mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||sys||testdb|+--------------------+5rowsinset(0.01sec)

2、准备proxySQL软件

[root@proxy~]#wgethttps://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm[root@proxy~]#llproxysql-1.4.8-1-centos7.x86_64.rpm-rw-r--r--1rootroot5977168Apr1011:38proxysql-1.4.8-1-centos7.x86_64.rpm

3、安装配置

[root@proxy~]#yuminstall-yproxysql-1.4.8-1-centos7.x86_64.rpm[root@proxy~]#rpm-qlproxysql/etc/init.d/proxysql#启动脚本/etc/proxysql.cnf#配置文件，仅在第一次(/var/lib/proxysql/proxysql.db文件不存在)启动时有效。启#动后可以在proxysql管理端中通过修改数据库的方式修改配置并生效(官方推荐方式。)/usr/bin/proxysql#主程序文件/usr/share/proxysql/tools/proxysql_galera_checker.sh/usr/share/proxysql/tools/proxysql_galera_writer.pl

4、配置文件详解

[root@proxy~]#egrep-v"^#|^$"/etc/proxysql.cnfdatadir="/var/lib/proxysql"#数据目录admin_variables={admin_credentials="admin:admin"#连接管理端的用户名与密码mysql_ifaces="0.0.0.0:6032"#管理端口，用来连接proxysql的管理数据库}mysql_variables={threads=4#指定转发端口开启的线程数量max_connections=2048default_query_delay=0default_query_timeout=36000000have_compress=truepoll_timeout=2000interfaces="0.0.0.0:6033"#指定转发端口，用于连接后端mysql数据库的，相当于代理作用default_schema="information_schema"stacksize=1048576server_version="5.5.30"#指定后端mysql的版本connect_timeout_server=3000monitor_username="monitor"monitor_password="monitor"monitor_history=600000monitor_connect_interval=60000monitor_ping_interval=10000monitor_read_only_interval=1500monitor_read_only_timeout=500ping_interval_server_msec=120000ping_timeout_server=500commands_stats=truesessions_sort=trueconnect_retries_on_failure=10}mysql_servers=()mysql_users:()mysql_query_rules:()scheduler=()mysql_replication_hostgroups=()#因此我们使用官方推荐的方式来配置proxysql

5、启动服务并查看

[root@proxy~]#/etc/init.d/proxysqlstartStartingProxySQL:DONE![root@proxy~]#ss-lntup|grepproxytcpLISTEN0128*:6032*:*users:(("proxysql",pid=1199,fd=23))tcpLISTEN0128*:6033*:*users:(("proxysql",pid=1199,fd=22))tcpLISTEN0128*:6033*:*users:(("proxysql",pid=1199,fd=21))tcpLISTEN0128*:6033*:*users:(("proxysql",pid=1199,fd=20))tcpLISTEN0128*:6033*:*users:(("proxysql",pid=1199,fd=19))#可以看出转发端口6033是启动了四个线程

6、在mysql上配置账号并授权

mysql>GRANTALLON*.*TO'proxysql'@'192.168.22.%'IDENTIFIEDBY'123456';QueryOK,0rowsaffected,1warning(0.03sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.02sec)

7、proxysql默认数据库说明

[root@proxy~]#yuminstallmysql-y[root@proxy~]#mysql-uadmin-padmin-h227.0.0.1-P6032WelcometotheMariaDBmonitor.Commandsendwith;or\g.YourMySQLconnectionidis1Serverversion:5.5.30(ProxySQLAdminModule)Copyright(c)2000,2017,Oracle,MariaDBCorporationAbandothers.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.MySQL[(none)]>showdatabases;+-----+---------------+-------------------------------------+|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|+-----+---------------+-------------------------------------+5rowsinset(0.00sec)

main：内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。

disk：是持久化到硬盘的配置，sqlite数据文件。

stats：是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。

monitor：库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。

8、proxysql的配置系统

ProxySQL具有一个复杂但易于使用的配置系统，可以满足以下需求：

1、允许轻松动态更新配置（这是为了让ProxySQL用户可以在需要零宕机时间配置的大型基础架构中使用它）。与MySQL兼容的管理界面可用于此目的。

2、允许尽可能多的配置项目动态修改，而不需要重新启动ProxySQL进程

3、可以毫不费力地回滚无效配置

4、这是通过多级配置系统实现的，其中设置从运行时移到内存，并根据需要持久保存到磁盘。

3级配置由以下几层组成：

参考文章：https://github.com/sysown/pro...

9、配置proxysql管理用户

proxysql默认的表信息如下

MySQL[main]>showtables;+--------------------------------------------+|tables|+--------------------------------------------+|global_variables||mysql_collations||mysql_group_replication_hostgroups||mysql_query_rules||mysql_query_rules_fast_routing||mysql_replication_hostgroups||mysql_servers||mysql_users||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|+--------------------------------------------+20rowsinset(0.00sec)#这里是使用insertinto语句来动态配置，而可以不需要重启MySQL[(none)]>insertintomysql_servers(hostgroup_id,hostname,port,weight,comment)values(1,'db1','3306',1,'WriteGroup');QueryOK,1rowaffected(0.01sec)MySQL[(none)]>insertintomysql_servers(hostgroup_id,hostname,port,weight,comment)values(2,'db2','3307',1,'ReadGroup');QueryOK,1rowaffected(0.00sec)MySQL[(none)]>select*frommysql_servers;+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+|hostgroup_id|hostname|port|status|weight|compression|max_connections|max_replication_lag|use_ssl|max_latency_ms|comment|+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+|1|db1|3306|ONLINE|1|0|1000|0|0|0|WriteGroup||2|db2|3307|ONLINE|1|0|1000|0|0|0|ReadGroup|+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+2rowsinset(0.00sec)#接下来将刚刚在mysql客户端创建的用户写入到proxysql主机的mysql_users表中，它也是用于proxysql客户端访问数据库，默认组是写组，当读写分离规则出现问题时，它会直接访问默认组的数据库。MySQL[main]>INSERTINTOmysql_users(username,password,default_hostgroup)VALUES('proxysql','123456',1);QueryOK,1rowaffected(0.00sec)MySQL[main]>select*frommysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+|username|password|active|use_ssl|default_hostgroup|default_schema|schema_locked|transaction_persistent|fast_forward|backend|frontend|max_connections|+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+|proxysql|123456|1|0|1|NULL|0|1|0|1|1|10000|+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1rowinset(0.00sec)

在mysql上添加监控的用户

mysql>GRANTSELECTON*.*TO'monitor'@'192.168.22.%'IDENTIFIEDBY'monitor';QueryOK,0rowsaffected,1warning(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)#在proxysql主机端配置监控用户MySQL[main]>setmysql-monitor_username='monitor';QueryOK,1rowaffected(0.00sec)MySQL[main]>setmysql-monitor_password='monitor';QueryOK,1rowaffected(0.00sec)#参考文章：https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration

10、配置proxysql的转发规则

MySQL[main]>insertintomysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FORUPDATE$',1,1);QueryOK,1rowaffected(0.01sec)MySQL[main]>insertintomysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);QueryOK,1rowaffected(0.00sec)MySQL[main]>selectrule_id,active,match_digest,destination_hostgroup,applyfrommysql_query_rules;+---------+--------+----------------------+-----------------------+-------+|rule_id|active|match_digest|destination_hostgroup|apply|+---------+--------+----------------------+-----------------------+-------+|1|1|^SELECT.*FORUPDATE$|1|1||2|1|^SELECT|2|1|+---------+--------+----------------------+-----------------------+-------+2rowsinset(0.00sec)#配置查询select的请求转发到hostgroup_id=2组上（读组）#征对select*fromtable_nameforupdate这样的修改语句，我们是需要将请求转到写组，也就是hostgroup_id=1#对于其它没有被规则匹配的请求全部转发到默认的组（mysql_users表中default_hostgroup）

11、更新配置到RUNTIME中

由上面的配置系统层级关系可以得知所有进来的请求首先是经过RUNTIME层

MySQL[main]>loadmysqluserstoruntime;QueryOK,0rowsaffected(0.00sec)MySQL[main]>loadmysqlserverstoruntime;QueryOK,0rowsaffected(0.02sec)MySQL[main]>loadmysqlqueryrulestoruntime;QueryOK,0rowsaffected(0.00sec)MySQL[main]>loadmysqlvariablestoruntime;QueryOK,0rowsaffected(0.00sec)MySQL[main]>loadadminvariablestoruntime;QueryOK,0rowsaffected(0.00sec)

12、将所有配置保存至磁盘上

所有配置数据保存到磁盘上，也就是永久写入/var/lib/proxysql/proxysql.db这个文件中

MySQL[main]>savemysqluserstodisk;QueryOK,0rowsaffected(0.03sec)MySQL[main]>savemysqlserverstodisk;QueryOK,0rowsaffected(0.04sec)ySQL[main]>savemysqlqueryrulestodisk;QueryOK,0rowsaffected(0.03sec)MySQL[main]>savemysqlvariablestodisk;QueryOK,94rowsaffected(0.02sec)MySQL[main]>saveadminvariablestodisk;QueryOK,31rowsaffected(0.02sec)MySQL[main]>loadmysqluserstoruntime;QueryOK,0rowsaffected(0.00sec)

13、测试读写分离

[root@proxy~]#mysql-uproxysql-p123456-h127.0.0.1-P6033WelcometotheMariaDBmonitor.Commandsendwith;or\g.YourMySQLconnectionidis2Serverversion:5.5.30(ProxySQL)Copyright(c)2000,2017,Oracle,MariaDBCorporationAbandothers.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.MySQL[(none)]>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||sys||testdb|+--------------------+5rowsinset(0.02sec)#这才是我们真正的数据库啊

创建数据与表，测试读写分离情况

MySQL[(none)]>createdatabasetest_proxysql;QueryOK,1rowaffected(0.02sec)MySQL[(none)]>usetest_proxysql;DatabasechangedMySQL[test_proxysql]>createtabletest_tables(namevarchar(20),ageint(4));QueryOK,0rowsaffected(0.07sec)MySQL[test_proxysql]>insertintotest_tablesvalues('zhao','30');QueryOK,1rowaffected(0.09sec)MySQL[test_proxysql]>select*fromtest_tables;+------+------+|name|age|+------+------+|zhao|30|+------+------+1rowinset(0.02sec)

在proxysql管理端查看读写分离

MySQL[main]>select*fromstats_mysql_query_digest;+-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+|hostgroup|schemaname|username|digest|digest_text|count_star|first_seen|last_seen|sum_time|min_time|max_time|+-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+|2|test_proxysql|proxysql|0x57CF7EC26C91DF9A|select*fromtest_tables|1|1527667635|1527667635|14253|14253|14253||1|information_schema|proxysql|0x226CD90D52A2BA0B|select@@version_commentlimit?|1|1527667214|1527667214|0|0|0||1|test_proxysql|proxysql|0xFF9877421CFBDA6F|insertintotest_tablesvalues(?,?)|1|1527667623|1527667623|89033|89033|89033||1|information_schema|proxysql|0xE662AE2DEE853B44|createdatabasetest-proxysql|1|1527667316|1527667316|8470|8470|8470||1|information_schema|proxysql|0x02033E45904D3DF0|showdatabases|1|1527667222|1527667222|19414|19414|19414||1|information_schema|proxysql|0xB9EF28C84E4207EC|createdatabasetest_proxysql|1|1527667332|1527667332|15814|15814|15814||2|information_schema|proxysql|0x620B328FE9D6D71A|SELECTDATABASE()|1|1527667342|1527667342|23386|23386|23386||1|test_proxysql|proxysql|0x02033E45904D3DF0|showdatabases|1|1527667342|1527667342|2451|2451|2451||1|test_proxysql|proxysql|0x59F02DA280268525|createtabletest_tables|1|1527667360|1527667360|9187|9187|9187||1|test_proxysql|proxysql|0x99531AEFF718C501|showtables|1|1527667342|1527667342|1001|1001|1001||1|test_proxysql|proxysql|0xC745E37AAF6095AF|createtabletest_tables(namevarchar(?),ageint(?))|1|1527667558|1527667558|68935|68935|68935|+-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+11rowsinset(0.01sec)#从上述结果就可以看出读写分离配置是成功的，读请求是转发到2组，写请求转发到1组

整个读写分离的架构配置到此就完成了，但是此架构存在需要优化的地方，那就是此架构存在单点问题。实际生产环境中可采用

看完上述内容，你们掌握ProxySQL中怎么利用MySQL实现数据库读写分离的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！