之前我们已经介绍了MySQL的高可用
这样我们至少保证了我们的服务不会因为MySQL挂掉而不能使用，不过按照之前的配置我们访问的时候依然访问的是单个节点，接下来我们就要想办法为节点分担压力了，本文介绍的方案是：使用ProxySQL实现MySQL读写分离
ProxySQL是一个读写分离的中间件，开源项目，优势是强大的规则引擎、支持在线配置、支持负载均衡，详情可以参考官方文档
ProxySQL还支持prepare、query cache、连接池，这些特性不在本文的介绍范围内
这里需要说明一下，读写分离方案是要看实际场景的。
如果对数据实时性要求极高，例如订单系统，是不适合读写分离方案的，因为读数据节点同步写数据节点的数据是有一定时间差的。
当然也不是就不能用，只是针对这种场景需要单独设置此时读数据和写数据使用同一节点
下面介绍一下ProxySQL的安装和配置
说明一下服务器情况：
1. 一共三台服务器，系统ubuntu16.04 64位
2. IP分别为：192.168.1.222、192.168.1.223、192.168.1.224，均安装了MySQL5.7
3. 222为master节点，223和224都是slave节点
4. mha-manager装在223上，三台机器都装了mha-node
ProxySQL安装
下载安装包
wget https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql_1.4.9-dbg-ubuntu16_amd64.deb
安装
dpkg -i proxysql_1.4.9-dbg-ubuntu16_amd64.deb
启动服务
service proxysql start
ProxySQL配置
ProxySQL的配置是支持两种方式的：配置文件，数据库
数据库的配置方式在第一次启动服务的时候也是基于配置文件的（/etc/proxysql.cnf），后续所有的配置都是在SQLLite中进行，并且不会更新proxysql.cnf配置文件，配置是存储在/var/lib/proxysql/proxysql.db中
前面提到的在线配置就是基于数据库的，所以这里我们就讲解在数据库中的配置方式
1. 创建服务账号和monitor账号
登录master数据库
mysql mysql -u root -p
添加账号并授权
GRANT ALL ON *.* TO 'proxysql'@'192.168.1.%' IDENTIFIED BY 'proxysql';
GRANT SELECT ON *.* TO 'monitor'@'192.168.1.%' IDENTIFIED BY 'monitor';
要先创建两个数据库账号用于后续配置，其中proxysql用于操作数据库，monitor用于监控
2. 登录到proxysql管理端
proxysql管理端口默认是6032，默认的用户名密码都是admin。
mysql -uadmin -padmin -h127.0.0.1 -P6032
3. 查看数据库信息
mysql> 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 |
| 6 | myhgm | |
+-----+---------------+-------------------------------------+
6 rows in set (0.00 sec)
main：默认数据库，存放用户验证、路由规则等信息。我们要做的配置都是针对这个库的
disk：持久化到硬盘的配置
stats：proxysql运行抓取的统计信息，如各命令的执行次数、查询执行时间等
monitor：monitor模块收集的信息，db的健康情况、各种检查等
4. 查看配置表
mysql> show tables;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| debug_levels |
| 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 |
+--------------------------------------------+
21 rows in set (0.00 sec)
global_variables：各种变量，包括监听的端口、管理账号、是否禁用monitor等，详情可以参考[官方文档](https://github.com/sysown/proxysql/wiki/Global-variables)
mysql_*：mysql开头的表就是我们配置要操作的表，具体都是干什么的还是看官方文档吧，介绍的很细，后面我会针对读写分离的配置做介绍
runtime_*：runtime开头的表是运行时读的表，不能通过DML语句修改，我们针对mysql开头的表做完配置修改之后，要执行**load mysql xxx to runtime**以将对应的配置加载到运行时环境
注意：当执行完load语句将配置加载到运行时环境后，还要执行**save mysql xxx to disk**将配置存到硬盘上，以便下次重启时加载，如果忘记执行，当重启时本次修改的配置会丢失
5. 添加DB实例
我们一共有三个节点（一个master两个slave），要进行读写分离，这里我们将master设为写节点，两个slave设为读节点
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.1.222',3306,1,1000,10,'write group');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(2000,'192.168.1.223',3306,1,1000,10,'read group');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(2000,'192.168.1.224',3306,1,1000,10,'read group');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1000 | 192.168.1.222 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | write group |
| 2000 | 192.168.1.223 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | read group |
| 2000 | 192.168.1.224 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | read group |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
3 rows in set (0.00 sec)
hostgroup_id：一个角色一个id，该表的主键是hostgroup_id+hostname+port
hostname：db实例IP
port：db实例端口
weight：权重，如果有多个相同角色的实例，会优先选择权重高的
status：状态
-ONLINE 正常
-SHUNNED 临时被剔除
-OFFLINE_SOFT 软离线状态，不再接受新的连接，已建立的连接会等待
-OFFLINE_HARD 离线，不接收新连接， 已建立的连接也会强制断开（宕机或者网络不可用）
max_connections：最大连接数
max_replication_lag：允许的最大延迟
6. 添加服务账号
mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',1000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxysql | proxysql | 1 | 0 | 1000 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.01 sec)
7. 设置监控账号
mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)
8. 添加规则
读写分离规则
mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1000,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2000,1);
Query OK, 1 row affected (0.00 sec)
mysql> 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$ | 1000 | 1 |
| 2 | 1 | ^SELECT | 2000 | 1 |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)
9. 完善配置
我们的mysql集群是基于MHA的，master挂掉之后，slave会提升为新的master，这个时候我们希望proxysql的规则自动变更，在mysql_servers中增加一条记录，将新的master的hostname和port添加到写的hostgroup中
proxysql是支撑这种配置的，根据mysql_replication_hostgroups中的数据，proxysql通过检测到各server的read_only值来自动为server设置hostgroup_id
mysql> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values(1000,2000,'Reading and Writing Separation');
Query OK, 1 row affected (0.00 sec)
mysql> select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+--------------------------------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+--------------------------------+
| 1000 | 2000 | Reading and Writing Separation |
+------------------+------------------+--------------------------------+
1 row in set (0.01 sec)
10. 将配置加载到运行时
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
11. 将配置保存到硬盘
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.15 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.33 sec)
mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.36 sec)
mysql> save mysql variables to disk;
Query OK, 96 rows affected (0.09 sec)
mysql> save admin variables to disk;
Query OK, 32 rows affected (0.09 sec)
到此读写分离的相关配置就大功告成了！之后我们的应用可以通过配置中配置proxysql用户连接proxysql服务操作我们的mysql集群
mysql -uproxysql -pproxysql -h 127.0.0.1 -P 6033
