传统主从读写分离配置

实验环境信息
MySQL主从信息

ProxySQL版本： 2.0.3-29-g00f26d5

》》》》服务器、用户配置篇《《《《

1、添加后端数据库服务器

Admin> SELECT * FROM mysql_servers;Empty set (0.00 sec)Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (1,‘188.188.0.68‘,3306,‘Master‘),(1,‘188.188.0.69‘,3306,‘Slave_1‘),(1,‘188.188.0.70‘,3306,‘Slave_2‘);Query OK, 3 rows affected (0.00 sec)Admin> SELECT * FROM mysql_servers;+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Master || 1 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_1 || 1 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_2 |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.00 sec)

2、配置监控

1）添加监视后端所需用户的凭据（需要在MySQL Server中已创建该用户）：

MySQL中创建设置监控用户：

mysql> GRANT process,replication client on mysql.* TO ‘psql_monitor‘@‘188.188.0.%‘ IDENTIFIED BY ‘987654‘ ;mysql> GRANT select on mysql.* TO ‘psql_monitor‘@‘188.188.0.%‘ ;mysql> show grants for ‘psql_monitor‘@‘188.188.0.%‘ ;+--------------------------------------------------------------------------+| Grants for psql_monitor@188.188.0.% |+--------------------------------------------------------------------------+| GRANT PROCESS, REPLICATION CLIENT ON *.* TO ‘psql_monitor‘@‘188.188.0.%‘ || GRANT SELECT ON `mysql`.* TO ‘psql_monitor‘@‘188.188.0.%‘ |+--------------------------------------------------------------------------+2 rows in set (0.00 sec)

查看在ProxySQL中配置用户：

Admin> select * from global_variables where variable_name=‘mysql-monitor_username‘;+------------------------+----------------+| variable_name | variable_value |+------------------------+----------------+| mysql-monitor_username | monitor |+------------------------+----------------+1 row in set (0.00 sec)Admin> select * from global_variables where variable_name=‘mysql-monitor_password‘;+------------------------+----------------+| variable_name | variable_value |+------------------------+----------------+| mysql-monitor_password | monitor |+------------------------+----------------+1 row in set (0.00 sec)

配置监控用户：

Admin> UPDATE global_variables SET variable_value=‘psql_monitor‘ WHERE variable_name=‘mysql-monitor_username‘;Query OK, 1 row affected (0.00 sec)Admin> UPDATE global_variables SET variable_value=‘987654‘ WHERE variable_name=‘mysql-monitor_password‘;Query OK, 1 row affected (0.01 sec)Admin> select * from global_variables where variable_name=‘mysql-monitor_username‘;+------------------------+----------------+| variable_name | variable_value |+------------------------+----------------+| mysql-monitor_username | psql_monitor |+------------------------+----------------+1 row in set (0.00 sec)Admin> select * from global_variables where variable_name=‘mysql-monitor_password‘;+------------------------+----------------+| variable_name | variable_value |+------------------------+----------------+| mysql-monitor_password | 987654 |+------------------------+----------------+1 row in set (0.00 sec)

2）修改监控时间间隔

Admin> SELECT * FROM global_variables WHERE variable_name IN (‘mysql-monitor_connect_interval‘,‘mysql-monitor_ping_interval‘,‘mysql-monitor_read_only_interval‘);+----------------------------------+----------------+| variable_name | variable_value |+----------------------------------+----------------+| mysql-monitor_connect_interval | 60000 || mysql-monitor_ping_interval | 10000 || mysql-monitor_read_only_interval | 1500 |+----------------------------------+----------------+3 rows in set (0.00 sec)Admin> UPDATE global_variables SET variable_value=‘2000‘ WHERE variable_name IN (‘mysql-monitor_connect_interval‘,‘mysql-monitor_ping_interval‘,‘mysql-monitor_read_only_interval‘);Query OK, 3 rows affected (0.00 sec)

3）配置的生效和持久化

加载配置到RUNTIME层：

Admin> LOAD MYSQL VARIABLES TO RUNTIME;Query OK, 0 rows affected (0.00 sec)

持久化配置到DISK层：

Admin> SAVE MYSQL VARIABLES TO DISK;Query OK, 110 rows affected (0.02 sec)

3、后端MySQL服务健康检测

1）查看后端连接状态：

Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;+--------------+------+------------------+-------------------------+---------------+| hostname | port | time_start_us | connect_success_time_us | connect_error |+--------------+------+------------------+-------------------------+---------------+| 188.188.0.70 | 3306 | 1556620581265582 | 387 | NULL || 188.188.0.69 | 3306 | 1556620581239483 | 413 | NULL || 188.188.0.68 | 3306 | 1556620581213375 | 432 | NULL || 188.188.0.70 | 3306 | 1556620579255264 | 381 | NULL || 188.188.0.68 | 3306 | 1556620579234288 | 426 | NULL || 188.188.0.69 | 3306 | 1556620579213311 | 492 | NULL || 188.188.0.70 | 3306 | 1556620577259808 | 443 | NULL || 188.188.0.68 | 3306 | 1556620577236530 | 437 | NULL || 188.188.0.69 | 3306 | 1556620577213253 | 500 | NULL || 188.188.0.70 | 3306 | 1556620575249404 | 468 | NULL |+--------------+------+------------------+-------------------------+---------------+10 rows in set (0.00 sec)

查看后端存活状态：

Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;+--------------+------+------------------+----------------------+------------+| hostname | port | time_start_us | ping_success_time_us | ping_error |+--------------+------+------------------+----------------------+------------+| 188.188.0.70 | 3306 | 1556620607647791 | 85 | NULL || 188.188.0.68 | 3306 | 1556620607631491 | 80 | NULL || 188.188.0.69 | 3306 | 1556620607615190 | 89 | NULL || 188.188.0.70 | 3306 | 1556620605653805 | 83 | NULL || 188.188.0.69 | 3306 | 1556620605634458 | 85 | NULL || 188.188.0.68 | 3306 | 1556620605615106 | 88 | NULL || 188.188.0.70 | 3306 | 1556620603663711 | 84 | NULL || 188.188.0.68 | 3306 | 1556620603639370 | 93 | NULL || 188.188.0.69 | 3306 | 1556620603615027 | 94 | NULL || 188.188.0.69 | 3306 | 1556620601666891 | 81 | NULL |+--------------+------+------------------+----------------------+------------+10 rows in set (0.00 sec)

2）配置的生效和持久化

确认后端服务正常后，就可以加载到RUNTIME层进行启用和持久化到DISK层：

Admin> SELECT * FROM runtime_mysql_servers;Empty set (0.00 sec)Admin> SELECT * FROM disk.mysql_servers;Empty set (0.00 sec)

加载配置到RUNTIME层：

Admin> LOAD MYSQL SERVERS TO RUNTIME;Query OK, 0 rows affected (0.01 sec)

持久化配置到DISK层：

Admin> SAVE MYSQL SERVERS TO DISK;Query OK, 0 rows affected (0.03 sec)Admin> SELECT * FROM runtime_mysql_servers;+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Master || 1 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_2 || 1 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_1 |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.01 sec)Admin> SELECT * FROM disk.mysql_servers;+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Master || 1 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_2 || 1 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_1 |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.01 sec)

4、MySQL主从复制架构信息配置

1）新增主从配置

Admin> SELECT * FROM mysql_replication_hostgroups;Empty set (0.00 sec)

插入读写、只读组配置信息：

Admin> INSERT INTO mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,check_type,comment) VALUES (1,2,‘read_only‘,‘cluster1‘);Query OK, 1 row affected (0.00 sec)

说明：读写组编号为1，只读组编号为2；

Admin> select * from mysql_replication_hostgroups;+------------------+------------------+------------+----------+| writer_hostgroup | reader_hostgroup | check_type | comment |+------------------+------------------+------------+----------+| 1 | 2 | read_only | cluster1 |+------------------+------------------+------------+----------+1 row in set (0.00 sec)

2）配置生效

将修改的配置加载到RUNTIME层：

Admin> LOAD MYSQL SERVERS TO RUNTIME;Query OK, 0 rows affected (0.00 sec)

查看MySQL分组：

Admin> select * from runtime_mysql_servers;+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Master || 2 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_1 || 2 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_2 |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.00 sec)

可以看到，最初hostgroup_id都为1，现在分为了1和2组。

3）查看MySQL状态

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;+--------------+------+------------------+-----------------+-----------+-------+| hostname | port | time_start_us | success_time_us | read_only | error |+--------------+------+------------------+-----------------+-----------+-------+| 188.188.0.68 | 3306 | 1556629979520275 | 156 | 0 | NULL || 188.188.0.69 | 3306 | 1556629979500246 | 156 | 1 | NULL || 188.188.0.70 | 3306 | 1556629979480222 | 154 | 1 | NULL || 188.188.0.68 | 3306 | 1556629977513289 | 156 | 0 | NULL || 188.188.0.69 | 3306 | 1556629977496742 | 157 | 1 | NULL || 188.188.0.70 | 3306 | 1556629977480173 | 163 | 1 | NULL || 188.188.0.70 | 3306 | 1556629975523461 | 184 | 1 | NULL || 188.188.0.68 | 3306 | 1556629975501848 | 183 | 0 | NULL || 188.188.0.69 | 3306 | 1556629975480262 | 204 | 1 | NULL || 188.188.0.69 | 3306 | 1556629973520196 | 166 | 1 | NULL |+--------------+------+------------------+-----------------+-----------+-------+10 rows in set (0.00 sec)

Slave的read_only属性都已识别到。

4）对配置存盘

Admin> SAVE MYSQL SERVERS TO DISK;Query OK, 0 rows affected (0.03 sec)Admin> SELECT * FROM disk.mysql_servers;+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Master || 2 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_1 || 2 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Slave_2 |+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.00 sec)

5、设置MySQL Users(连接后端使用)

1）在后端MySQL创建业务账号

Admin> SELECT * FROM mysql_users;Empty set (0.00 sec)

-- 在后端MySQL上创建业务库及读写账号：

mysql> CREATE DATABASE test;Query OK, 1 row affected (0.01 sec)mysql> GRANT select,insert,update,delete ON test.* TO ‘test_rw‘@‘188.188.0.%‘ IDENTIFIED BY ‘123456‘; Query OK, 0 rows affected, 1 warning (0.00 sec)

2）将业务账号加入ProxySQL

--添加后端MySQL上的业务账号到 mysql_users

mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (‘test_rw‘,‘123456‘,1);Admin> SELECT * FROM mysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| test_rw | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+3 rows in set (0.00 sec)

3）配置的生效

再次，将配置加载到运行时以使其生效。

Admin> LOAD MYSQL USERS TO RUNTIME;Query OK, 0 rows affected (0.00 sec)Admin> SELECT * FROM runtime_mysql_users;+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 0 | 1 | 10000 | || test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 1 | 0 | 10000 | |+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+2 rows in set (0.00 sec)

被添加的每个用户出现了成对的记录，这是因为每个用户在前端和后端都有一条用户记录;

4）加密MEMORY层用户密码

从RUNTIME层获取用户加密后密码，更新MEMORY层：

Admin> SELECT * FROM mysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| test_rw | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+1 row in set (0.00 sec)Admin> SAVE MYSQL USERS FROM RUNTIME;Query OK, 0 rows affected (0.00 sec)Admin> SELECT * FROM mysql_users;+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 0 | 1 | 10000 | || test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 1 | 0 | 10000 | |+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+2 rows in set (0.00 sec)

5）配置持久化

持久化用户信息到DISK层：

Admin> SELECT * FROM disk.mysql_users;Empty set (0.00 sec)Admin> SAVE MYSQL USERS TO DISK;Query OK, 0 rows affected (0.01 sec)Admin> SELECT * FROM disk.mysql_users;+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 1 | 1 | 10000 | |+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+1 rows in set (0.00 sec)

6、业务账号可用性验证

到此，用户添加完毕，可用使用客户端连接ProxySQL尝试连接。

Admin> select * from stats.stats_mysql_query_digest ;Empty set (0.01 sec)

ProxySQL中无SQL统计信息；

[root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "SELECT 1"mysql: [Warning] Using a password on the command line interface can be insecure.+---+| 1 |+---+| 1 |+---+

注意：连接端口默认是6033，不是6032(ProxySQL管理端口)也不是3306(MySQL端口)。

[root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "SELECT @@port"mysql: [Warning] Using a password on the command line interface can be insecure.+--------+| @@port |+--------+| 3306 |+--------+

这里查询出了后端MySQL服务的端口。

到此，最简单的服务器、用户配置已经完成！！！

》》》读写分离查询规则配置篇《《《

• 注意：
  正常逻辑应该是针对有必要的SQL进行读写分离配置，而不是粗暴的将所有查询都发送到Slave；

1、读写分离的正常逻辑

使用regex和digest进行读/写拆分是很正确的选择，有效设置读/写拆分的配置过程如下：
1）配置 ProxySQL 只将所有流量发送到一个 MySQL 节点——master(写和读);
2）在 stats_mysql_query_digest 中查找执行成本较高的 SELECT 语句;
3）确定哪些成本较高的 SELECT 语句应该被移动到Slave节点上执行；
4）配置 mysql_query_rules 将需要移动的 SELECT 语句发送到读节点；
因此，这个想法非常简单：只发送你想发送给 Slave/Reader 的内容，而不只是任何SELECT语句。

2、确定高成本的 SELECT 语句

可以从以下几种维度来查找成本较高的 SELECT 语句；

1）根据总执行时间查找最耗时的前5个查询：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time -> FROM stats.stats_mysql_query_digest -> WHERE digest_text LIKE ‘SELECT%‘ -> ORDER BY sum_time DESC -> LIMIT 5;+--------------------+--------------------------+------------+----------+| digest | SUBSTR(digest_text,0,25) | count_star | sum_time |+--------------------+--------------------------+------------+----------+| 0x1C46AE529DD5A40E | SELECT ? | 1 | 543 || 0x67A9FB367B49CAB9 | select * from test.t1 | 1 | 356 || 0x831B091BA90D80E5 | SELECT @@port | 1 | 171 || 0x226CD90D52A2BA0B | select @@version_comment | 3 | 0 |+--------------------+--------------------------+------------+----------+4 rows in set (0.00 sec)

2）根据计数查找前5个查询：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time -> FROM stats_mysql_query_digest -> WHERE digest_text LIKE ‘SELECT%‘ -> ORDER BY count_star DESC -> LIMIT 5;+--------------------+--------------------------+------------+----------+| digest | SUBSTR(digest_text,0,25) | count_star | sum_time |+--------------------+--------------------------+------------+----------+| 0x226CD90D52A2BA0B | select @@version_comment | 3 | 0 || 0x67A9FB367B49CAB9 | select * from test.t1 | 1 | 356 || 0x1C46AE529DD5A40E | SELECT ? | 1 | 543 || 0x831B091BA90D80E5 | SELECT @@port | 1 | 171 |+--------------------+--------------------------+------------+----------+4 rows in set (0.01 sec)

3）根据最长执行时间查找前5个查询：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time -> FROM stats_mysql_query_digest -> WHERE digest_text LIKE ‘SELECT%‘ -> ORDER BY max_time DESC -> LIMIT 5;+--------------------+--------------------------+------------+----------+----------+----------+----------+| digest | SUBSTR(digest_text,0,25) | count_star | sum_time | avg_time | min_time | max_time |+--------------------+--------------------------+------------+----------+----------+----------+----------+| 0x1C46AE529DD5A40E | SELECT ? | 1 | 543 | 543 | 543 | 543 || 0x67A9FB367B49CAB9 | select * from test.t1 | 1 | 356 | 356 | 356 | 356 || 0x831B091BA90D80E5 | SELECT @@port | 1 | 171 | 171 | 171 | 171 || 0x226CD90D52A2BA0B | select @@version_comment | 3 | 0 | 0 | 0 | 0 |+--------------------+--------------------------+------------+----------+----------+----------+----------+4 rows in set (0.00 sec)

4）查找按总执行时间排序的前5个查询，最小执行时间至少为0.3毫秒：

Admin> SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time -> FROM stats_mysql_query_digest -> WHERE digest_text LIKE ‘SELECT%‘ AND min_time > 300 -> ORDER BY sum_time DESC -> LIMIT 5;+--------------------+--------------------------+------------+----------+----------+----------+----------+| digest | SUBSTR(digest_text,0,20) | count_star | sum_time | avg_time | min_time | max_time |+--------------------+--------------------------+------------+----------+----------+----------+----------+| 0x1C46AE529DD5A40E | SELECT ? | 1 | 543 | 543 | 543 | 543 || 0x67A9FB367B49CAB9 | select * from test. | 1 | 356 | 356 | 356 | 356 |+--------------------+--------------------------+------------+----------+----------+----------+----------+2 rows in set (0.01 sec)

5）查找按总执行时间排序的前5个查询，平均执行时间至少为0.3毫秒。同时显示总执行时间的百分比：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) -> FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest -> WHERE digest_text LIKE ‘SELECT%‘ AND sum_time/count_star > 300 -> ORDER BY sum_time DESC -> LIMIT 5;+--------------------+--------------------------+------------+----------+----------+--------+| digest | SUBSTR(digest_text,0,25) | count_star | sum_time | avg_time | pct |+--------------------+--------------------------+------------+----------+----------+--------+| 0x1C46AE529DD5A40E | SELECT ? | 1 | 543 | 543 | 50.748 || 0x67A9FB367B49CAB9 | select * from test.t1 | 1 | 356 | 356 | 33.271 |+--------------------+--------------------------+------------+----------+----------+--------+2 rows in set (0.01 sec)

通过以上5个维度，大致可以找出目标SQL了。
这里，我们将 ‘select * from test.t1‘ 作为选定的目标，将其分配到Slave上进行读取。

3、编写查询规则

在与应用程序开发人员确认之后，可以将‘select * from test.t1‘的语句，即digset为 0x67A9FB367B49CAB9 的查询转移到Slave_1上执行：

Admin> SELECT * FROM mysql_query_rules ;Empty set (0.00 sec)Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES (1,1,‘0x67A9FB367B49CAB9‘,2,1);Query OK, 1 row affected (0.00 sec)Admin> SELECT * FROM mysql_query_rules \G*************************** 1. row *************************** rule_id: 1 active: 1 username: NULL schemaname: NULL flagIN: 0 client_addr: NULL proxy_addr: NULL proxy_port: NULL digest: 0x67A9FB367B49CAB9 match_digest: NULL match_pattern: NULL negate_match_pattern: 0 re_modifiers: CASELESS flagOUT: NULL replace_pattern: NULLdestination_hostgroup: 2 cache_ttl: NULL cache_empty_result: NULL cache_timeout: NULL reconnect: NULL timeout: NULL retries: NULL delay: NULL next_query_flagIN: NULL mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL OK_msg: NULL sticky_conn: NULL multiplex: NULL gtid_from_hostgroup: NULL log: NULL apply: 1 comment: NULL1 row in set (0.00 sec)

4、配置生效

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;Query OK, 0 rows affected (0.00 sec)Admin> SELECT * FROM runtime_mysql_query_rules \G*************************** 1. row *************************** rule_id: 1 active: 1 username: NULL schemaname: NULL flagIN: 0 client_addr: NULL proxy_addr: NULL proxy_port: NULL digest: 0x67A9FB367B49CAB9 match_digest: NULL match_pattern: NULL negate_match_pattern: 0 re_modifiers: CASELESS flagOUT: NULL replace_pattern: NULLdestination_hostgroup: 2 cache_ttl: NULL cache_empty_result: NULL cache_timeout: NULL reconnect: NULL timeout: NULL retries: NULL delay: NULL next_query_flagIN: NULL mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL OK_msg: NULL sticky_conn: NULL multiplex: NULL gtid_from_hostgroup: NULL log: NULL apply: 1 comment: NULL1 row in set (0.00 sec)

5、验证设置结果

1）记录SQL的历史状态信息

Admin> SELECT * FROM stats.stats_mysql_query_digest WHERE digest=‘0x67A9FB367B49CAB9‘;+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+| hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+| 1 | information_schema | test_rw | | 0x67A9FB367B49CAB9 | select * from test.t1 | 1 | 1557977769 | 1557977769 | 356 | 356 | 356 |+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+1 rows in set (0.00 sec)Admin> SELECT * FROM stats.stats_mysql_query_rules WHERE rule_id=1;+---------+------+| rule_id | hits |+---------+------+| 1 | 0 |+---------+------+1 row in set (0.00 sec)

2）使用业务账号向ProxySQL发出目标SQL：

[root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "select * from test.t1;"mysql: [Warning] Using a password on the command line interface can be insecure.+----+------+| id | name |+----+------+| 1 | a || 2 | b |+----+------+

3）在ProxySQL中查看查询设置结果

Admin> SELECT * FROM stats.stats_mysql_query_digest WHERE digest=‘0x67A9FB367B49CAB9‘;+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+| hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+| 2 | information_schema | test_rw | | 0x67A9FB367B49CAB9 | select * from test.t1 | 1 | 1557978780 | 1557978780 | 784 | 784 | 784 || 1 | information_schema | test_rw | | 0x67A9FB367B49CAB9 | select * from test.t1 | 1 | 1557977769 | 1557977769 | 356 | 356 | 356 |+-----------+--------------------+----------+----------------+--------------------+-----------------------+------------+------------+------------+----------+----------+----------+2 rows in set (0.00 sec)

出现了hostgroup 2的查询记录，说明已经将目标SQL发送到了Slave服务上。

Admin> SELECT * FROM stats.stats_mysql_query_rules WHERE rule_id=1;+---------+------+| rule_id | hits |+---------+------+| 1 | 1 |+---------+------+1 row in set (0.00 sec)

规则被命中1次。

以上2个结果说明了目标SQL通过查询规则被发送到了Slave上。

6、配置持久化

Admin> SELECT * FROM disk.mysql_query_rules WHERE rule_id=1 ;Empty set (0.00 sec)Admin> SAVE MYSQL QUERY RULES TO DISK;Query OK, 0 rows affected (0.01 sec)Admin> SELECT * FROM disk.mysql_query_rules WHERE rule_id=1 \G*************************** 1. row *************************** rule_id: 1 active: 1 username: NULL schemaname: NULL flagIN: 0 client_addr: NULL proxy_addr: NULL proxy_port: NULL digest: 0x67A9FB367B49CAB9 match_digest: NULL match_pattern: NULL negate_match_pattern: 0 re_modifiers: CASELESS flagOUT: NULL replace_pattern: NULLdestination_hostgroup: 2 cache_ttl: NULL cache_empty_result: NULL cache_timeout: NULL reconnect: NULL timeout: NULL retries: NULL delay: NULL next_query_flagIN: NULL mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL OK_msg: NULL sticky_conn: NULL multiplex: NULL gtid_from_hostgroup: NULL log: NULL apply: 1 comment: NULL1 row in set (0.00 sec)

到此，利用ProxySQL对传统的主从结构读写分离已经实现。

》》》系统参数配置篇《《《

修改自动提交参数：

Admin> show variables like ‘%auto%‘;+---------------------------------------+-------+| Variable_name | Value |+---------------------------------------+-------+| mysql-forward_autocommit | false || mysql-enforce_autocommit_on_reads | false || mysql-autocommit_false_not_reusable | false || mysql-autocommit_false_is_transaction | false || mysql-auto_increment_delay_multiplex | 5 |+---------------------------------------+-------+5 rows in set (0.01 sec)

修改以下2个参数：

1)

Admin> set mysql-forward_autocommit=‘true‘;

为true时，则SET autocommit = 0命令被转发到MySQL上后端。

2)

Admin> set mysql-autocommit_false_not_reusable = ‘true‘;

设置为true时，不会重新使用与autocommit = 0的连接，并在连接返回到连接池时销毁

使配置生效

Admin> save mysql variables to disk;

使配置持久化

Admin> load mysql variables to run;

~
~
完毕!