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

2022-07-15 12:02:34

字体：大中小

来源：转载

供稿：网友

　　操作系统：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)

（编辑：错新网）

上一篇：my-innodb-heavy-4G.cnf配置文件解说

下一篇：xtrabackup参数补给