proxysql配置读写分离策略和权重调整一例

来源：这里教程网时间：2026-03-01 11:39:41 作者：

操作系统：CentOS release 6.9 主库: 192.168.140.51 从库1: 192.168.140.52 read_only=on 从库2: 192.168.16.150 read_only=on proxysql中间件: 192.168.140.52 配置读写分离策略，要求主库写，两个从库读,把所有select开头的SQL语句全部分配到编号为20的读组里，select for update或update操作分配到编号为10的写组里: mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',10,1); Query OK, 1 row affected (0.00 sec) mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',20,1); Query OK, 1 row affected (0.00 sec) mysql> load mysql query rules to runtime; Query OK, 0 rows affected (0.00 sec) mysql> save mysql query rules to disk; #mysql -udsf -pdsf -h 192.168.140.52 -P 6033 (备注:6033为中间件对外访问端口号) mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t; Empty set (0.00 sec) mysql> select * from t1; Empty set (0.00 sec) mysql> select * from t1 for update; Empty set (0.00 sec) mysql> select * from test limit 10; +------+----------------------------------------------------------------------------------+ | a | b | +------+----------------------------------------------------------------------------------+ | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | | NULL | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | +------+----------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) mysql> select * from test_new limit 3; +------+----------+ | a | b | +------+----------+ | NULL | chenfeng | | NULL | chenfeng | | NULL | chenfeng | +------+----------+ 3 rows in set (0.00 sec) mysql> update test_new set b='dsf'; Query OK, 60000 rows affected (1.80 sec) Rows matched: 60000 Changed: 60000 Warnings: 0 读写分离测试: mysql> select * from stats_mysql_query_digest; +-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+ | 20 | test | dsf | 0x0E68CEFBB3EF90B1 | select count(?) from test_new | 1 | 1539068254 | 1539068254 | 76155 | 76155 | 76155 | | 10 | test | dsf | 0x2FC734A8D89E32FC | desc qianzhui | 1 | 1539068239 | 1539068239 | 2325 | 2325 | 2325 | | 10 | test | dsf | 0x90923742A873577C | update test_new set b=? | 1 | 1539068280 | 1539068280 | 1802294 | 1802294 | 1802294 | | 20 | test | dsf | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1539068226 | 1539068226 | 1429 | 1429 | 1429 | | 10 | information_schema | dsf | 0xAE231F4D14BA1FD6 | KILL QUERY ? | 1 | 1539067381 | 1539067381 | 2162 | 2162 | 2162 | | 10 | information_schema | dsf | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 5 | 1539065258 | 1539067396 | 0 | 0 | 0 | | 10 | test | dsf | 0x376A8E5E76BBC701 | desc test_new | 1 | 1539068244 | 1539068244 | 2217 | 2217 | 2217 | | 20 | test | dsf | 0x38DF1D37B3136F42 | select * from test | 2 | 1539067275 | 1539067381 | 62259 | 28837 | 33422 | | 10 | information_schema | dsf | 0x82A12D4C4E7B0A28 | select @@hostname | 1 | 1539065302 | 1539065302 | 1375 | 1375 | 1375 | | 10 | information_schema | dsf | 0x02033E45904D3DF0 | show databases | 1 | 1539067215 | 1539067215 | 3012 | 3012 | 3012 | | 20 | test | dsf | 0xDAFAB555D432CB6F | select * from test_new limit ? | 1 | 1539068264 | 1539068264 | 769 | 769 | 769 | | 20 | information_schema | dsf | 0x620B328FE9D6D71A | SELECT DATABASE() | 3 | 1539067262 | 1539067399 | 6154 | 851 | 4429 | | 20 | test | dsf | 0xD8BDF2ED80FC3809 | select * from test limit ? | 1 | 1539067425 | 1539067425 | 850 | 850 | 850 | | 10 | information_schema | dsf | 0xA4D993880D70C4D9 | show slave hosts | 1 | 1539065258 | 1539065258 | 6351 | 6351 | 6351 | | 20 | test | dsf | 0x3765930C7143F468 | select * from t1 | 4 | 1539067272 | 1539068207 | 5724 | 587 | 2876 | | 10 | test | dsf | 0x02033E45904D3DF0 | show databases | 3 | 1539067262 | 1539067399 | 8745 | 1974 | 4136 | | 20 | test | dsf | 0xDB236914B7E74FBD | select * from t | 3 | 1539067269 | 1539067403 | 5831 | 720 | 3521 | | 10 | test | dsf | 0x5A680F86B3D8FB2B | select * from t1 for update | 1 | 1539068209 | 1539068209 | 1921 | 1921 | 1921 | | 10 | test | dsf | 0x99531AEFF718C501 | show tables | 5 | 1539067262 | 1539068228 | 8407 | 1155 | 2173 | +-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+ 19 rows in set (0.00 sec) 调整读写分离权重: mysql> update mysql_servers set weight=5 where hostname='192.168.16.150'; 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 | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.140.51 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.16.150 | 3306 | ONLINE | 5 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.140.52 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) mysql> load mysql servers to runtime; Query OK, 0 rows affected (0.00 sec) mysql> save mysql servers to disk; Query OK, 0 rows affected (0.23 sec) mysql> select * from mysql_servers; +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.140.51 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.16.150 | 3306 | ONLINE | 5 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.140.52 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)