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

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

前面也写过几篇关于Mysql数据的文章：

MySQL集群高可用架构之MHA MySQL 同步复制及高可用方案总结 官方工具｜MySQL Router 高可用原理与实战

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

ProxySQL介绍

1、连接池，而且是multiplexing

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

3、自动下线后端DB

延迟超过阀值ping 延迟超过阀值网络不通或宕机

4、强大的规则路由引擎

实现读写分离查询重写sql流量镜像

5、支持prepared statement

6、支持Query Cache

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

整体环境介绍

1、系统环境

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

[root@db1 ~]# cat /etc/redhat-release   CentOS Linux release 7.4.1708 (Core)   [root@db1 ~]# uname -r  3.10.0-693.el7.x86_64

2、IP地址与软件版本

proxy  192.168.22.171db1     192.168.22.173db2    192.168.22.174mysql  5.7.17proxy  sql 1.4.8

3、关闭防火墙、selinux

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

4、mysql安装与主从同步

安装请参考以下文章

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

主从同步请参以下文章

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

安装布署过程

1、数据库主从同步

查看主从同步状态

mysql> show slave status\G  *************************** 1. row ***************************            Slave_IO_State: Waiting for master to send event  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: Slave has read all relay log; waiting for more updates   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:  1 row in set (0.00 sec)

检测主从同步

[root@db1 ~]# mysql -uroot -p -e "create database testdb;  "Enter password:   [root@db1 ~]# mysql -uroot -p -e "show  databases;" |grep testdb  Enter password:   testdb  #db2上查看是否同步  mysql> show databases;  +--------------------+  | Database           |  +--------------------+  | information_schema |  | mysql              |  | performance_schema |  | sys                |  | testdb             |  +--------------------+  5 rows in set (0.01 sec)

2、准备proxySQL软件

3、安装配置

[root@proxy ~]# yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm  [root@proxy ~]# rpm -ql  proxysql  /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、配置文件详解

5、启动服务并查看

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

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

mysql> GRANT ALL ON *.* TO 'proxysql'@'192.168.22.%' IDENTIFIED BY '123456';  Query OK, 0 rows affected, 1 warning (0.03 sec)  mysql> flush privileges;  Query OK, 0 rows affected (0.02 sec)

7、proxysql默认数据库说明

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级配置由以下几层组成：

9、配置proxysql管理用户

proxysql默认的表信息如下

在mysql上添加监控的用户

10、配置proxysql的转发规则

MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);  Query OK, 1 row affected (0.01 sec)  MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1); Query OK, 1 row affected (0.00 sec)  MySQL [main]> 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     |  +---------+--------+----------------------+-----------------------+-------+  2 rows in set (0.00 sec)  #配置查询select的请求转发到hostgroup_id=2组上（读组）#征对select * from table_name  for update这样的修改语句，我们是需要将请求转到写组，也就是hostgroup_id=1#对于其它没有被规则匹配的请求全部转发到默认的组（mysql_users表中default_hostgroup）

11、更新配置到RUNTIME中

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

MySQL [main]> load mysql users to runtime;  Query OK, 0 rows affected (0.00 sec)  MySQL [main]> load mysql servers to runtime;  Query OK, 0 rows affected (0.02 sec)  MySQL [main]> load mysql query rules to runtime;  Query OK, 0 rows affected (0.00 sec)  MySQL [main]> load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec)  MySQL [main]> load admin variables to runtime;  Query OK, 0 rows affected (0.00 sec)

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

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

MySQL [main]> save mysql users to disk;  Query OK, 0 rows affected (0.03 sec)  MySQL [main]> save mysql servers to disk;  Query OK, 0 rows affected (0.04 sec)  ySQL [main]> save mysql query rules to disk;  Query OK, 0 rows affected (0.03 sec)  MySQL [main]> save mysql variables to disk;  Query OK, 94 rows affected (0.02 sec) MySQL [main]> save admin variables to disk;  Query OK, 31 rows affected (0.02 sec)  MySQL [main]> load mysql users to runtime;  Query OK, 0 rows affected (0.00 sec)

13、测试读写分离

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

MySQL [(none)]> create database test_proxysql;  Query OK, 1 row affected (0.02 sec)  MySQL [(none)]> use test_proxysql;  Database changed  MySQL [test_proxysql]> create table test_tables(name varchar(20),age int(4));  Query OK, 0 rows affected (0.07 sec)  MySQL [test_proxysql]> insert into test_tables values('zhao','30');  Query OK, 1 row affected (0.09 sec)  MySQL [test_proxysql]> select * from test_tables;  +------+------+  | name | age  |  +------+------+  | zhao |   30 |  +------+------+  1 row in set (0.02 sec)

在proxysql管理端查看读写分离

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。