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、系统环境
三台服务器系统环境一致如下

[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.171
db1     192.168.22.173
db2    192.168.22.174
mysql  5.7.17
proxy  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软件

[root@proxy ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm
[root@proxy ~]# ll proxysql-1.4.8-1-centos7.x86_64.rpm 
-rw-r--r-- 1 root root 5977168 Apr 10 11:38 proxysql-1.4.8-1-centos7.x86_64.rpm

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、配置文件详解

[root@proxy ~]# 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=
(
)
#因此我们使用官方推荐的方式来配置proxy sql

5、启动服务并查看

[root@proxy ~]# /etc/init.d/proxysql start
Starting 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默认数据库说明

[root@proxy ~]# yum install mysql -y
[root@proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
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级配置由以下几层组成：

+-------------------------+
|         RUNTIME         |
+-------------------------+
       /|\          |
        |           |
    [1] |       [2] |
        |          \|/
+-------------------------+
|         MEMORY          |
+-------------------------+ _
       /|\          |      |\
        |           |        \
    [3] |       [4] |         \ [5]
        |          \|/         \
+-------------------------+  +-------------------------+
|          DISK           |  |       CONFIG FILE       |
+-------------------------+  +-------------------------+
参考文章：https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL

9、配置proxysql管理用户

proxysql默认的表信息如下

MySQL [main]> show tables;
+--------------------------------------------+
| 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                                  |
+--------------------------------------------+
20 rows in set (0.00 sec)
#这里是使用insert into语句来动态配置，而可以不需要重启
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'db1','3306',1,'Write Group');
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'db2','3307',1,'Read Group');
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> select * from mysql_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              | Write Group |
| 2            | db2      | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Read Group  |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
2 rows in set (0.00 sec)
#接下来将刚刚在mysql客户端创建的用户写入到proxy sql主机的mysql_users表中，它也是用于proxysql客户端访问数据库，默认组是写组，当读写分离规则出现问题时，它会直接访问默认组的数据库。
MySQL [main]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',1);
Query OK, 1 row affected (0.00 sec)

MySQL [main]> select * from mysql_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 row in set (0.00 sec)
在mysql上添加监控的用户
mysql> GRANT SELECT ON *.* TO 'monitor'@'192.168.22.%' IDENTIFIED BY 'monitor';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#在proxysql主机端配置监控用户
MySQL [main]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

MySQL [main]> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)
#参考文章：https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration

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)

MySQL [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、测试读写分离

[root@proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.02 sec)
#这才是我们真正的数据库啊
创建数据与表，测试读写分离情况
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管理端查看读写分离
MySQL [main]> select * from stats_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 * from test_tables                            | 1          | 1527667635 | 1527667635 | 14253    | 14253    | 14253    |
| 1         | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ?                     | 1          | 1527667214 | 1527667214 | 0        | 0        | 0        |
| 1         | test_proxysql      | proxysql | 0xFF9877421CFBDA6F | insert into test_tables values(?,?)                  | 1          | 1527667623 | 1527667623 | 89033    | 89033    | 89033    |
| 1         | information_schema | proxysql | 0xE662AE2DEE853B44 | create database test-proxysql                        | 1          | 1527667316 | 1527667316 | 8470     | 8470     | 8470     |
| 1         | information_schema | proxysql | 0x02033E45904D3DF0 | show databases                                       | 1          | 1527667222 | 1527667222 | 19414    | 19414    | 19414    |
| 1         | information_schema | proxysql | 0xB9EF28C84E4207EC | create database test_proxysql                        | 1          | 1527667332 | 1527667332 | 15814    | 15814    | 15814    |
| 2         | information_schema | proxysql | 0x620B328FE9D6D71A | SELECT DATABASE()                                    | 1          | 1527667342 | 1527667342 | 23386    | 23386    | 23386    |
| 1         | test_proxysql      | proxysql | 0x02033E45904D3DF0 | show databases                                       | 1          | 1527667342 | 1527667342 | 2451     | 2451     | 2451     |
| 1         | test_proxysql      | proxysql | 0x59F02DA280268525 | create table test_tables                             | 1          | 1527667360 | 1527667360 | 9187     | 9187     | 9187     |
| 1         | test_proxysql      | proxysql | 0x99531AEFF718C501 | show tables                                          | 1          | 1527667342 | 1527667342 | 1001     | 1001     | 1001     |
| 1         | test_proxysql      | proxysql | 0xC745E37AAF6095AF | create table test_tables(name varchar(?),age int(?)) | 1          | 1527667558 | 1527667558 | 68935    | 68935    | 68935    |
+-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+
11 rows in set (0.01 sec)
#从上述结果就可以看出读写分离配置是成功的，读请求是转发到2组，写请求转发到1组

整个读写分离的架构配置到此就完成了，但是此架构存在需要优化的地方，那就是此架构存在单点问题。实际生产环境中可采用MHA+ProxySQL+Mysql这类架构解决此问题，请持续关注【民工哥技术之路】公众号，后续输出相关的架构实战。
关于Mysql各类高可用架构可阅读前面的文章
浅谈MySQL集群高可用架构
MySQL集群高可用架构之MHA
Mysql+Mycat实现数据库主从同步与读写分离