ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。ProxySQL是用C++语言开发的，也是percona推的一款中间件，虽然也是一个轻量级产品，但性能很好(据测试，能处理千亿级的数据)，功能也足够，能满足中间件所需的绝大多数功能。
ProxySQL具备了很多优质特性，具体总结如下:
-> 连接池，而且是multiplexing-> 主机和用户的最大连接数限制-> 自动下线后端DB -> 延迟超过阀值 -> ping 延迟超过阀值 -> 网络不通或宕机-> 强大的规则路由引擎 -> 实现读写分离 -> 查询重写 -> sql流量镜像-> 支持prepared statement-> 支持Query Cache-> 支持负载均衡，与gelera结合自动failover-> 可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说，规则很灵活。基于schema和与语句级的规则，可以实现简单的sharding。-> 可缓存查询结果。虽然ProxySQL的缓存策略比较简陋，但实现了基本的缓存功能，绝大多数时候也够用了。此外，作者已经打算实现更丰富的缓存策略。-> 监控后端节点。ProxySQL可以监控后端节点的多个指标，包括：ProxySQL和后端的心跳信息，后端节点的read-only/read-write，slave和master的数据同步延迟性(replication lag)。ProxySQL是一个能实实在在用在生产环境的MySQL中间件，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行 cache，支持动态加载配置、故障切换和一些 SQL的过滤功能。还有一些同类产品比如 DBproxy、MyCAT、OneProxy 等。但经过反复对比和测试之后，还是觉得ProxySQL是一款性能不谙，靠谱稳定的MySQL 中间件产品 ！
ProxySQL+Mysql实现数据库读写分离实战记录
1. 基础环境准备
172.16.60.212 mysql-master 安装Mysql5.7172.16.60.213 mysql-slave 安装Mysql5.7172.16.60.214 mysql-proxy 安装ProxySQL，Mysql-client1) 三个节点各自设置主机名[root@mysql-master ~]# hostnamectl --static set-hostname mysql-master[root@mysql-master ~]# hostnamemysql-master[root@mysql-slave ~]# hostnamectl --static set-hostname mysql-slave[root@mysql-slave ~]# hostnamemysql-slave[root@mysql-proxy ~]# hostnamectl --static set-hostname mysql-proxy[root@mysql-proxy ~]# hostnamemysql-proxy2) 三个节点都要绑定hosts[root@mysql-master ~]# cat /etc/hosts.........172.16.60.212 mysql-master172.16.60.213 mysql-slave172.16.60.214 mysql-proxy3）三个节点都关闭selinux和iptables防火墙 (为了实验方面，线上环境可以打开，允许3306端口规则)[root@mysql-master ~]# setenforce 0[root@mysql-master ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled"SELINUX=disabled[root@mysql-master ~]# iptables -F[root@mysql-master ~]# systemctl disable firewalld[root@mysql-master ~]# systemctl stop firewalld [root@mysql-master ~]# firewall-cmd --statenot running
2. 安装Mysql 5.7 (在mysql-master 和 mysql-slave节点)
在两个mysql节点机上使用yum方式安装Mysql5.7，参考：https://www.cnblogs.com/kevingrace/p/8340690.html 安装MySQL yum资源库[root@mysql-master ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm 安装MySQL 5.7[root@mysql-master ~]# yum install -y mysql-community-server 启动MySQL服务器和MySQL的自动启动[root@mysql-master ~]# systemctl start mysqld.service[root@mysql-master ~]# systemctl enable mysqld.service 设置登录密码由于MySQL从5.7开始不允许首次安装后使用空密码进行登录！为了加强安全性，系统会随机生成一个密码以供管理员首次登录使用，这个密码记录在/var/log/mysqld.log文件中，使用下面的命令可以查看此密码：[root@mysql-master ~]# cat /var/log/mysqld.log|grep 'A temporary password'2019-01-11T05:53:17.824073Z 1 [Note] A temporary password is generated for root@localhost: TaN.k:*Qw2xs 使用上面查看的密码TaN.k:*Qw2xs 登录mysql，并重置密码为123456[root@mysql-master ~]# mysql -p #输入默认的密码：TaN.k:*Qw2xs.............mysql> set global validate_password_policy=0;Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=1;Query OK, 0 rows affected (0.00 sec) mysql> set password=password("123456");Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges;Query OK, 0 rows affected (0.00 sec) 查看mysql版本[root@mysql-master ~]# mysql -p123456........mysql> select version();+-----------+| version() |+-----------+| 5.7.24 |+-----------+1 row in set (0.00 sec) =====================================================================温馨提示mysql5.7通过上面默认安装后，执行语句可能会报错：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 这个报错与Mysql 密码安全策略validate_password_policy的值有关，validate_password_policy可以取0、1、2三个值：解决办法：set global validate_password_policy=0;set global validate_password_length=1; 3. 配置Mysql基于GTID的主从同步 (在mysql-master 和 mysql-slave节点)
由于这两台节点机之前做过别的实验，为了保证数据环境干净，可以删除/var/lib/mysql，然后再次重启mysqld即可!# systemctl stop mysqld# rm -rf /var/lib/mysql# systemctl start mysqld 这样操作后，mysql数据库就等于重新初始化了一次，之前的数据环境全部被干掉了，密码也是初始化后的随机密码如下查看初始密码# cat /var/log/mysqld.log|grep 'A temporary password' 然后再进行密码重置等操作，这里就不赘述了，在前面安装mysql的时候已经提到过了。================================================================== 1) mysql-master 作为主数据库节点，其my.cnf配置为:[root@mysql-master ~]# >/etc/my.cnf[root@mysql-master ~]# vim /etc/my.cnf[mysqld]datadir = /var/lib/mysqlsocket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.logpid-file = /var/run/mysqld/mysqld.pid #GTID:server_id = 1gtid_mode = onenforce_gtid_consistency = on #binloglog_bin = master-binlog-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay logskip_slave_start = 1 配置完成之后，别忘了重启Mysql[root@mysql-master ~]# systemctl restart mysqld 查看一下master状态， 发现多了一项"Executed_Gtid_Set "mysql> show master status;+-------------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+------------------------------------------+| master-bin.000002 | 550 | | | 88d43a34-1ec8-11e9-b0b5-00505688047c:1-2 |+-------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec) mysql> show global variables like '%uuid%';+---------------+--------------------------------------+| Variable_name | Value |+---------------+--------------------------------------+| server_uuid | 88d43a34-1ec8-11e9-b0b5-00505688047c |+---------------+--------------------------------------+1 row in set (0.00 sec) mysql> show global variables like '%gtid%';+----------------------------------+------------------------------------------+| Variable_name | Value |+----------------------------------+------------------------------------------+| binlog_gtid_simple_recovery | ON || enforce_gtid_consistency | ON || gtid_executed | 88d43a34-1ec8-11e9-b0b5-00505688047c:1-2 || gtid_executed_compression_period | 1000 || gtid_mode | ON || gtid_owned | || gtid_purged | || session_track_gtids | OFF |+----------------------------------+------------------------------------------+8 rows in set (0.00 sec) 主库执行从库复制授权mysql> grant replication slave,replication client on *.* to slave@'172.16.60.213' identified by "slave@123";Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> flush privileges;Query OK, 0 rows affected (0.02 sec) mysql> show grants for slave@'172.16.60.213';+-------------------------------------------------------------------------------+| Grants for slave@172.16.60.213 |+-------------------------------------------------------------------------------+| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.213' |+-------------------------------------------------------------------------------+1 row in set (0.00 sec) 在主数据库机器上创建一个测试库kevin（为了测试效果）mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.00 sec) mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.01 sec) mysql> use kevin;Database changedmysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);Query OK, 0 rows affected (0.27 sec) mysql> insert into kevin.haha values(1,"congcong"),(2,"huihui"),(3,"grace"); Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from kevin.haha;+----+----------+| id | name |+----+----------+| 1 | congcong || 2 | huihui || 3 | grace |+----+----------+3 rows in set (0.00 sec) ==========================================================================2) 从数据库172.16.60.206的操作my.cnf文件里GTID复制的配置内容如下：与主服务器配置大概一致，除了server_id不一致外，从服务器还可以在配置文件里面添加："read_only＝on" ,使从服务器只能进行读取操作，此参数对超级用户无效，并且不会影响从服务器的复制； [root@mysql-slave ~]# > /etc/my.cnf[root@mysql-slave ~]# vim /etc/my.cnf[mysqld]datadir = /var/lib/mysqlsocket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.logpid-file = /var/run/mysqld/mysqld.pid #GTID:server_id = 2gtid_mode = onenforce_gtid_consistency = on #binloglog_bin = master-binlog-slave-updates = 1binlog_format = rowsync-master-info = 1sync_binlog = 1 #relay logskip_slave_start = 1 read_only = on 配置完成之后，别忘了重启Mysql[root@mysql-slave ~]# systemctl restart mysqld 接着登录mysql，做主从同步mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.00 sec) 在从数据库里，使用change master 配置主从复制mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to master_host='172.16.60.212',master_user='slave',master_password='slave@123',master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.50 sec) mysql> start slave;Query OK, 0 rows affected (0.05 sec) mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.60.212 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 1753 Relay_Log_File: mysql-slave-relay-bin.000002 Relay_Log_Pos: 1968 Relay_Master_Log_File: master-bin.000002 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: 1753 Relay_Log_Space: 2181 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: 0Master_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: 88d43a34-1ec8-11e9-b0b5-00505688047c Master_Info_File: /var/lib/mysql/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: 88d43a34-1ec8-11e9-b0b5-00505688047c:1-7 Executed_Gtid_Set: 872832c0-1ec8-11e9-84a5-005056880888:1-2,88d43a34-1ec8-11e9-b0b5-00505688047c:1-7 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.00 sec) ERROR:No query specified 接着查看从数据库的数据，发现kevin库已经同步过来了!mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || kevin || mysql || performance_schema || sys |+--------------------+5 rows in set (0.00 sec) mysql> select * from kevin.haha;+----+----------+| id | name |+----+----------+| 1 | congcong || 2 | huihui || 3 | grace |+----+----------+3 rows in set (0.00 sec)查看从数据库的gtid信息mysql> show global variables like '%gtid%';+----------------------------------+-------------------------------------------------------------------------------------+| Variable_name | Value |+----------------------------------+-------------------------------------------------------------------------------------+| binlog_gtid_simple_recovery | ON || enforce_gtid_consistency | ON || gtid_executed | 872832c0-1ec8-11e9-84a5-005056880888:1-2,88d43a34-1ec8-11e9-b0b5-00505688047c:1-11 || gtid_executed_compression_period | 1000 || gtid_mode | ON || gtid_owned | || gtid_purged | || session_track_gtids | OFF |+----------------------------------+-------------------------------------------------------------------------------------+8 rows in set (0.00 sec) 3) 然后回到主数据库中查看master状态mysql> show slave hosts;+-----------+------+------+-----------+--------------------------------------+| Server_id | Host | Port | Master_id | Slave_UUID |+-----------+------+------+-----------+--------------------------------------+| 2 | | 3306 | 1 | 872832c0-1ec8-11e9-84a5-005056880888 |+-----------+------+------+-----------+--------------------------------------+1 row in set (0.00 sec) 通过上面一系列配置，则mysql基于GTID的主从复制环境就部署好了。 4) 下面开始验证：在172.16.60.212的主数据库里插入新数据mysql> insert into kevin.haha values(10,"heifei"),(11,"huoqiu"),(12,"chengxihu");Query OK, 3 rows affected (0.05 sec)Records: 3 Duplicates: 0 Warnings: 0 到172.16.60.213的从数据库里查看，发现已经同步过来了mysql> select * from kevin.haha;+----+-----------+| id | name |+----+-----------+| 1 | congcong || 2 | huihui || 3 | grace || 10 | heifei || 11 | huoqiu || 12 | chengxihu |+----+-----------+6 rows in set (0.00 sec)
4. 安装配置ProxySQL，实现读写分离 (在mysql-proxy节点)
也可以参考另一篇博文，这篇文档里已介绍了利用proxysql实现读写分离操作 (一主两从，一写两读模式)。
- 安装mysql客户端，用于在本机连接到ProxySQL的管理接口
[root@mysql-proxy ~]# vim /etc/yum.repos.d/mariadb.repo[mariadb] name = MariaDBbaseurl = http://yum.mariadb.org/10.3.5/centos6-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 安装mysql-clinet客户端[root@mysql-proxy ~]# yum install -y MariaDB-client--------------------------------------------------------------------------------------------------------------------------------------------------------如果遇到报错：Error: MariaDB-compat conflicts with 1:mariadb-libs-5.5.60-1.el7_5.x86_64 You could try using --skip-broken to work around the problem You could try running: rpm -Va --nofiles --nodigest 解决办法：[root@mysql-proxy ~]# rpm -qa|grep mariadb*mariadb-libs-5.5.56-2.el7.x86_64[root@mysql-proxy ~]# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps[root@mysql-proxy ~]# yum install -y MariaDB-client
- 安装proxysql
proxysql的rpm包下载地址: https://pan.baidu.com/s/1S1_b5DKVCpZSOUNmtCXrrg提取密码: 5t1c[root@mysql-proxy ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm[root@mysql-proxy ~]# rpm -ivh proxysql-1.4.8-1-centos7.x86_64.rpm --force配置文件详解[root@mysql-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=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 - 启动服务并查看
[root@mysql-proxy ~]# /etc/init.d/proxysql startStarting ProxySQL: DONE![root@mysql-proxy ~]# ss -lntup|grep proxytcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=2943,fd=24))tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=22))tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=21))tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=20))tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=19))可以看出转发端口6033是启动了四个线程 - 在mysql-master主数据库节点上执行：(只需master执行即可，会复制给slave从数据库)
mysql> GRANT ALL ON *.* TO 'proxysql'@'172.16.60.%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> flush privileges;Query OK, 0 rows affected (0.04 sec)
- proxysql默认数据库说明
[root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.30 (ProxySQL Admin Module)Copyright (c) 2000, 2018, 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.001 sec)ProxySQL提供了几个库，每个库都有各自的意义；-> main内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容， 不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。-> disk 是持久化到硬盘的配置，sqlite数据文件。-> stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。-> monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。
- proxysql的配置系统
ProxySQL具有一个复杂但易于使用的配置系统，可以满足以下需求：-> 允许轻松动态更新配置（这是为了让ProxySQL用户可以在需要零宕机时间配置的大型基础架构中使用它）。与MySQL兼容的管理界面可用于此目的。-> 允许尽可能多的配置项目动态修改，而不需要重新启动ProxySQL进程-> 可以毫不费力地回滚无效配置-> 这是通过多级配置系统实现的，其中设置从运行时移到内存，并根据需要持久保存到磁盘。3级配置由以下几层组成:+-------------------------+| RUNTIME |+-------------------------+ /|\ | | | [1] | [2] | | \|/+-------------------------+| MEMORY |+-------------------------+ _ /|\ | |\ | | \ [3] | [4] | \ [5] | \|/ \+-------------------------+ +-------------------------+| DISK | | CONFIG FILE |+-------------------------+ +-------------------------+可以参考官方文章：https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL
- 配置proxysql管理用户
MySQL [(none)]> 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.000 sec)下面使用insert into语句来动态配置（插入语句里使用主机名或ip都可以），而可以不需要重启!!!MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'mysql-master','3306',1,'Write Group');Query OK, 1 row affected (0.000 sec)MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'mysql-slave','3306',1,'Read Group');Query OK, 1 row affected (0.000 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 | mysql-master | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group || 2 | mysql-slave | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+2 rows in set (0.000 sec)接下来将刚刚在mysql客户端创建的用户写入到proxy sql主机的mysql_users表中，它也是用于proxysql客户端访问数据库，默认组是写组，当读写分离规则出现问题时，它会直接访问默认组的数据库。MySQL [(none)]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',1);Query OK, 1 row affected (0.000 sec)MySQL [(none)]> 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.000 sec) - 在mysql-master主数据库节点上添加监控的用户：(只需master执行即可，会复制给slave从数据库)
mysql> GRANT SELECT ON *.* TO 'monitor'@'172.16.60.%' IDENTIFIED BY 'monitor'; Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> flush privileges;Query OK, 0 rows affected (0.03 sec)
- 在mysql-proxy节点上配置监控用户
MySQL [(none)]> set mysql-monitor_username='monitor';Query OK, 1 row affected (0.001 sec)MySQL [(none)]> set mysql-monitor_password='monitor';Query OK, 1 row affected (0.000 sec)
- 配置proxysql的转发规则
MySQL [(none)]> 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.000 sec) MySQL [(none)]> 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.000 sec) MySQL [(none)]> 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.000 sec) 以上配置后，可以得知：-> 配置查询select的请求转发到hostgroup_id=2组上（读组）;-> 征对select * from table_name for update这样的修改语句，我们是需要将请求转到写组，也就是hostgroup_id=1;-> 对于其它没有被规则匹配的请求全部转发到默认的组（mysql_users表中default_hostgroup）;
- 更新配置到RUNTIME中
由上面的配置系统层级关系可以得知所有进来的请求首先是经过RUNTIME层
MySQL [(none)]> load mysql users to runtime;Query OK, 0 rows affected (0.001 sec)MySQL [(none)]> load mysql servers to runtime;Query OK, 0 rows affected (0.004 sec)MySQL [(none)]> load mysql query rules to runtime;Query OK, 0 rows affected (0.001 sec)MySQL [(none)]> load mysql variables to runtime;Query OK, 0 rows affected (0.001 sec)MySQL [(none)]> load admin variables to runtime;Query OK, 0 rows affected (0.001 sec)
- 将所有配置保存至磁盘上
所有配置数据保存到磁盘上，也就是永久写入/var/lib/proxysql/proxysql.db这个文件中MySQL [(none)]> save mysql users to disk;Query OK, 0 rows affected (0.103 sec)MySQL [(none)]> save mysql servers to disk;Query OK, 0 rows affected (0.273 sec)MySQL [(none)]> save mysql query rules to disk;Query OK, 0 rows affected (0.320 sec)MySQL [(none)]> save mysql variables to disk;Query OK, 94 rows affected (0.084 sec)MySQL [(none)]> save admin variables to disk;Query OK, 31 rows affected (0.059 sec)MySQL [(none)]> load mysql users to runtime;Query OK, 0 rows affected (0.001 sec)
- 测试读写分离
通过转发端口连接数据库，这时连接到的才是我们真正需要的数据 （注意转发端口是6033）[root@mysql-proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.5.30 (ProxySQL)Copyright (c) 2000, 2018, 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 || kevin || mysql || performance_schema || sys |+--------------------+5 rows in set (0.002 sec)MySQL [(none)]> 这时发现的才是我们真正的数据库MySQL [(none)]> select * from kevin.haha;+----+-----------+| id | name |+----+-----------+| 1 | congcong || 2 | huihui || 3 | grace || 10 | heifei || 11 | huoqiu || 12 | chengxihu |+----+-----------+6 rows in set (0.001 sec)下载开始创建数据与表，测试读写分离情况MySQL [(none)]> delete from kevin.haha where id > 9;Query OK, 3 rows affected (0.218 sec)MySQL [(none)]> create database test_proxysql;Query OK, 1 row affected (0.027 sec)MySQL [(none)]> select * from kevin.haha;+----+----------+| id | name |+----+----------+| 1 | congcong || 2 | huihui || 3 | grace |+----+----------+3 rows in set (0.001 sec)MySQL [(none)]> insert into kevin.haha values(10,"heifei"),(11,"huoqiu"),(12,"chengxihu");Query OK, 3 rows affected (0.045 sec)Records: 3 Duplicates: 0 Warnings: 0MySQL [(none)]> select * from kevin.haha; +----+-----------+| id | name |+----+-----------+| 1 | congcong || 2 | huihui || 3 | grace || 10 | heifei || 11 | huoqiu || 12 | chengxihu |+----+-----------+6 rows in set (0.001 sec)MySQL [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || kevin || mysql || performance_schema || sys || test_proxysql |+--------------------+6 rows in set (0.001 sec)[root@mysql-proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033 -e "select @@server_id"+-------------+| @@server_id |+-------------+| 2 |+-------------+[root@mysql-proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033 -e "select * from kevin.haha"+----+-----------+| id | name |+----+-----------+| 1 | congcong || 2 | huihui || 3 | grace || 10 | heifei || 11 | huoqiu || 12 | chengxihu |+----+-----------+在proxysql管理端查看读写分离 （注意管理端口是6032）[root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 5.5.30 (ProxySQL Admin Module)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> 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 | information_schema | proxysql | 0x3EA85877510AC608 | select * from stats_mysql_query_digest | 1 | 1548230792 | 1548230792 | 819 | 819 | 819 || 1 | information_schema | proxysql | 0xB9EF28C84E4207EC | create database test_proxysql | 1 | 1548230738 | 1548230738 | 26859 | 26859 | 26859 || 1 | information_schema | proxysql | 0x9316817E8C74BCB2 | insert into kevin.haha values(?,?),(?,?),(?,?) | 1 | 1548230755 | 1548230755 | 44572 | 44572 | 44572 || 1 | information_schema | proxysql | 0xF6FA5DFBB674D5FF | delete from kevin.haha where id > ? | 1 | 1548230729 | 1548230729 | 218165 | 218165 | 218165 || 2 | information_schema | proxysql | 0xD8AAAE77FA99AC44 | select * from kevin.haha | 4 | 1548230646 | 1548231045 | 3590 | 781 | 1149 || 2 | information_schema | proxysql | 0xDA65260DF35B8D13 | select @@server_id | 2 | 1548231028 | 1548231030 | 1335 | 578 | 757 || 2 | information_schema | proxysql | 0xF8F2DF2E08F87E71 | select * from mysql.kevin | 1 | 1548230640 | 1548230640 | 2004 | 2004 | 2004 || 1 | information_schema | proxysql | 0x02033E45904D3DF0 | show databases | 2 | 1548230538 | 1548230771 | 2957 | 905 | 2052 || 1 | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 4 | 1548229930 | 1548231045 | 0 | 0 | 0 |+-----------+--------------------+----------+--------------------+------------------------------------------------+------------+------------+------------+----------+----------+----------+9 rows in set (0.002 sec)从上述结果就可以看出proxysql实现的读写分离配置是成功的，读请求是转发到2组，写请求转发到1组!!
整个读写分离的架构配置到此就完成了，但是此架构存在需要优化的地方，那就是此架构存在单点问题。实际生产环境中可采用MHA+ProxySQL+Mysql这类架构解来避免单点故障问题。