### 一、环境准备
系统: `CentOS7`
数据库: `ProxySQL:1.4.14`
服务器:
`master`: `172.16.0.100/db1`
`slave`: `172.16.0.101/db2`
`slave/ProxySQL`: `172.16.0.102/db3`
 
 
### 二、准备工作
`a.数据库搭建MySQL主从`
`b.从库开启`**`read_only=on`**
 
 
### 三、ProxySQL的安装
#### 安装依赖的软件包
`db3`
```bash
yum -y install perl-DBD-MySQL perl-DBI perl-Time-HiRes perl-IO-Socket-SSL
```
 
#### ProxySQL软件包的两个下载地址
GitHub官网：https://github.com/sysown/proxysql/releases
percona官网：https://www.percona.com/downloads/proxysql/
 
#### 安装ProxySQL
`db3`
```bash
yum install -y https://www.percona.com/downloads/proxysql/proxysql-1.4.14/binary/redhat/7/x86_64/proxysql-1.4.14-1.1.el7.x86_64.rpm
```
 
#### 启动ProxySQL
`db3`
```bash
systemctl start proxysql
```
 
#### 查看启动信息
`db3`
```bash
netstat -tunlp |grep proxysql
```
```
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 13331/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 13331/proxysql
```
`6032为管理端口，6033为对外服务的端口号`
 
#### 登录ProxySQL
`db3`
```bash
mysql -uadmin -padmin -h 127.0.0.1 -P 6032
```
 
#### 查看proxysql信息
`db3`
```sql
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 |
+-----+---------------+-------------------------------------+
#可见有四个库：main、disk、stats和monitor。分别说明一下这四个库的作用。
#main：内存配置数据库，即MEMORY，表里存放后端db实例、用户验证、路由规则等信息。
mysql> 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 |
+--------------------------------------------+
#库下的主要表：
#mysql_servers—后端可以连接MySQL服务器的列表。
#mysql_users—配置后端数据库的账号和监控的账号。
#mysql_query_rules—指定Query路由到后端不同服务器的规则列表。
#注：表名以runtime_开头的表示ProxySQL当前运行的配置内容，不能通过DML语句修改。只能修改对应的不以 runtime开头的表，然后“LOAD”使#其生效，“SAVE”使其存到硬盘以供下次重启加载。
#disk库—持久化磁盘的配置。
#stats库—统计信息的汇总。
#monitor库—一些监控的收集信息，包括数据库的健康状态等。
```
 
 
### 四、配置ProxySQL监控
#### Master上创建ProxySQL的监控账户和对外访问账户并赋予权限
`db1`
```sql
mysql> create user 'monitor'@'172.16.0.%' identified by 'monitor';
mysql> grant all privileges on *.* to 'monitor'@'172.16.0.%' with grant option;
mysql> create user 'proxysql'@'172.16.0.%' identified by 'proxysql';
mysql> grant all privileges on *.* to 'proxysql'@'172.16.0.%' with grant option;
mysql> flush privileges;
```
 
#### ProxySQL添加主从服务器列表
`db3`
```sql
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.100',3306);
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.101',3306);
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.102',3306);
```
 
#### ProxySQL查看服务器列表
`db3`
```sql
mysql> select * from mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.16.0.100 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.16.0.101 | 3306 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.16.0.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
```
 
#### ProxySQL配置监控账号
`db3`
```sql
mysql> set mysql-monitor_username='monitor';
mysql> set mysql-monitor_password='monitor';
mysql> load mysql variables to runtime;
mysql> save mysql variables to disk;
```
 
#### ProxySQL验证监控信息
`db3`
```sql
mysql> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
+--------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+--------------+------+------------------+-------------------------+---------------+
| 172.16.0.100 | 3306 | 1565593172754235 | 2118 | NULL |
| 172.16.0.101 | 3306 | 1565593172137991 | 2729 | NULL |
| 172.16.0.102 | 3306 | 1565593171521858 | 773 | NULL |
| 172.16.0.100 | 3306 | 1565593113076006 | 2163 | NULL |
| 172.16.0.101 | 3306 | 1565593112298748 | 2377 | NULL |
| 172.16.0.102 | 3306 | 1565593111521583 | 628 | NULL |
+--------------+------+------------------+-------------------------+---------------+
```
 
 
### 五、配置ProxySQL主从分组信息
#### ProxySQL插入读写分离组
`db3`
```sql
mysql> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values (10,20,'proxy');
mysql> load mysql servers to runtime;
mysql> save mysql servers to disk;
# writer_hostgroup是写入组的编号，reader_hostgroup是读取组的编号。实验中使用10作为写入组，20作为读取组编号。
mysql> select * from mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.16.0.100 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.16.0.101 | 3306 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.16.0.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
# ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server，master被分到编号为10的写组，read_only=1的server，slave则被分到编号为20的读组。
```
 
#### ProxySQL配置对外访问账号(`默认指定主库，并对该用户开启事务持久化保护`)
`db3`
```sql
insert into mysql_users(username,password,default_hostgroup) values ('proxysql','proxysql',10);
update mysql_users set transaction_persistent=1 where username='proxysql'; #设置为1，避免发生脏读、幻读等现象
load mysql users to runtime;
save mysql users to disk;
```
 
#### ProxySQL验证登录的服务器
`db3`
```bash
mysql -uproxysql -pproxysql -h 172.16.0.102 -P 6033
```
```sql
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| db1 |
+------------+
# 验证登入的服务器默认为主库(db1)
```
 
 
### 六、ProxySQL配置读写分离策略
#### ProxySQL配置读写分离
`db3`
```sql
mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT',20,1);
mysql> load mysql query rules to runtime;
mysql> save mysql query rules to disk;
```
 
#### ProxySQL配置服务器的权重
`db3`
```sql
mysql> update mysql_servers set weight=10 where hostname='172.16.0.101';
mysql> load mysql servers to runtime;
mysql> save mysql servers to disk;
# 172.16.0.101的权重被修改为10,172.16。0.102的默认权重为1，则请求比例为10:1
```
 
 
### 七、测试读写分离
#### 执行select查询
`db3`
```sql
mysql -uproxysql -pproxysql -h 172.16.0.102 -P 6033
```
```sql
mysql> select node_name from percona.example where node_id=1;
```
 
#### 执行select for update查询
`db3`
```sql
mysql> select node_name from percona.example where node_id=1 for update;
```
 
#### 执行update语句
`db3`
```sql
mysql> update percona.example set node_name='test' where node_id=1;
```
 
#### proxySQL查看统计信息
`db3`
```bash
mysql -uadmin -padmin -h 127.0.0.1 -P 6032
```
```sql
mysql> select hostgroup,digest_text from stats_mysql_query_digest;
+-----------+--------------------------------------------------------------------------------------------------+
| hostgroup | digest_text |
+-----------+--------------------------------------------------------------------------------------------------+
| 10 | update percona.example set node_name=? where node_id=? |
| 10 | select node_name,@@hostname from percona.example where node_id=? for update |
| 20 | select node_name,@@hostname from percona.example where node_id=? |
+-----------+--------------------------------------------------------------------------------------------------+
```
`从上面的输出信息可以发现读写分离成功了`
参考: https://blog.51cto.com/sumongodb/2130453
