首页 资讯频道 互联频道 智能频道 网络 数据频道 安全频道 服务器频道 存储频道

实战详情:ProxySQL+MySQL实现数据库读写分离

2020-03-25 16:31:13 来源 : segmentfault

今天给大家带来的是关于数据库读写分离相关的实战操作。

ProxySQL介绍

ProxySQL是一个高性能的MySQL中间件,拥有强大的规则引擎。具有以下特性:http://www.proxysql.com/

1、连接池,而且是multiplexing

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

3、自动下线后端DB

延迟超过阀值

ping 延迟超过阀值

网络不通或宕机

4、强大的规则路由引擎

实现读写分离

查询重写

sql流量镜像

5、支持prepared statement

6、支持Query Cache

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

整体环境介绍

1、系统环境

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

[[email protected]~]#cat/etc/redhat-release

CentOSLinuxrelease7.4.1708(Core)

[[email protected]~]#uname-r

3.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:Waitingformastertosendevent

Master_Host:192.168.22.173

Master_User:rep

Master_Port:3306

Connect_Retry:60

Master_Log_File:master-log.000001

Read_Master_Log_Pos:154

Relay_Log_File:db2-relay-bin.000002

Relay_Log_Pos:321

Relay_Master_Log_File:master-log.000001

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0

Last_Error:

Skip_Counter:0

Exec_Master_Log_Pos:154

Relay_Log_Space:526

Until_Condition:None

Until_Log_File:

Until_Log_Pos:0

Master_SSL_Allowed:No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0

Master_SSL_Verify_Server_Cert:No

Last_IO_Errno:0

Last_IO_Error:

Last_SQL_Errno:0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1

Master_UUID:70a61633-63ae-11e8-ab86-000c29fe99ea

Master_Info_File:/mysqldata/master.info

SQL_Delay:0

SQL_Remaining_Delay:NULL

Slave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdates

Master_Retry_Count:86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1rowinset(0.00sec)

检测主从同步

[[email protected]~]#mysql-uroot-p-e"createdatabasetestdb;

"Enterpassword:

[[email protected]~]#mysql-uroot-p-e"showdatabases;"|greptestdb

Enterpassword:

testdb

#db2上查看是否同步

mysql>showdatabases;

+--------------------+

|Database|

+--------------------+

|information_schema|

|mysql|

|performance_schema|

|sys|

|testdb|

+--------------------+

5rowsinset(0.01sec)

2、准备proxySQL软件

[[email protected]~]#wgethttps://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm

[[email protected]~]#llproxysql-1.4.8-1-centos7.x86_64.rpm

-rw-r--r--1rootroot5977168Apr1011:38proxysql-1.4.8-1-centos7.x86_64.rpm

3、安装配置

[[email protected]~]#yuminstall-yproxysql-1.4.8-1-centos7.x86_64.rpm

[[email protected]~]#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、配置文件详解

[[email protected]~]#egrep-v"^#|^$"/etc/proxysql.cnf

datadir="/var/lib/proxysql"#数据目录

admin_variables=

{

admin_credentials="admin:admin"#连接管理端的用户名与密码

mysql_ifaces="0.0.0.0:6032"#管理端口,用来连接proxysql的管理数据库

}

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"#指定转发端口,用于连接后端mysql数据库的,相当于代理作用

default_schema="information_schema"

stacksize=1048576

server_version="5.5.30"#指定后端mysql的版本

connect_timeout_server=3000

monitor_username="monitor"

monitor_password="monitor"

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

}

mysql_servers=

(

)

mysql_users:

(

)

mysql_query_rules:

(

)

scheduler=

(

)

mysql_replication_hostgroups=

(

)

#因此我们使用官方推荐的方式来配置proxysql

5、启动服务并查看

[[email protected]~]#/etc/init.d/proxysql

startStartingProxySQL:DONE!

[[email protected]~]#ss-lntup|grepproxy

tcpLISTEN0128*: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默认数据库说明

[[email protected]~]#yuminstallmysql-y

[[email protected]~]#mysql-uadmin-padmin-h127.0.0.1-P6032

WelcometotheMariaDBmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis1

Serverversion: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|

+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1

rowinset(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、测试读写分离

[[email protected]~]#mysql-uproxysql-p123456-h127.0.0.1-P6033

WelcometotheMariaDBmonitor.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;

Databasechanged

MySQL[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|[email protected]@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组

整个读写分离的架构配置到此就完成了,但是此架构存在需要优化的地方,那就是此架构存在单点问题。实际生产环境中可采用MHA+ProxySQL+Mysql这类架构解决此问题,请持续关注!

相关文章

最近更新