proxysql配置讀寫分離策略和權重調整一例
作業系統：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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15498/viewspace-2215742/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ProxySQL實現MySQL讀寫分離MySql
- ProxySQL實現Mysql讀寫分離 - 部署手冊MySql
- ProxySQL簡介原理及讀寫分離應用SQL
- MHA+ProxySQL實現讀寫分離高可用SQL
- MySQL中介軟體之ProxySQL(3)：初試讀寫分離MySql
- ProxySQL 配置詳解及讀寫分離(+GTID)等功能說明 (完整篇)SQL
- discuz 配置讀寫分離(主寫從讀)
- Centos8.3、proxysql2.0讀寫分離實戰記錄CentOSSQL
- ShardingSphere(七) 讀寫分離配置，實現分庫讀寫操作
- 使用ProxySQL實現MySQL Group Replication的故障轉移、讀寫分離（一）MySql
- Orchestrator+Proxysql 實現自動導換+應用透明讀寫分離SQL
- 配置\清除 MySQL 主從 讀寫分離MySql
- Linux下MySQL主從複製(GTID)+讀寫分離(ProxySQL)-實施筆記LinuxMySql筆記
- 【DB寶42】MySQL高可用架構MHA+ProxySQL實現讀寫分離和負載均衡MySql架構負載
- DM8配置讀寫分離叢集
- ProxySQL+MGR實現讀寫分離和主節點故障無感知切換 - 完整操作記錄SQL
- 搭建基於springmvc，ibatis的工程實現讀寫分離，配置分離SpringMVCBAT
- 資料庫的讀寫分離與負載均衡策略資料庫負載
- ShardingSphere-proxy +PostgreSQL實現讀寫分離（靜態策略）SQL
- 使用proxysql 1.4.14中介軟體實現mysql 5.7.26主從的讀寫分離MySql
- EF多租戶例項：演變為讀寫分離
- [Mysql]主從複製和讀寫分離MySql
- shardingjdbc + jpa 完成讀寫分離配置及資料分片JDBC
- Redis的讀寫分離Redis
- KunlunBase 讀寫分離方案
- Laravel讀寫分離原理Laravel
- MyCat分庫分表、讀寫分離
- 資料讀寫壓力大，讀寫分離
- DM8配置讀寫分離叢集V4.0
- 資料庫讀寫分離資料庫
- 讀寫分離 & 分庫分表 & 深度分頁
- 使用ShardingSphere-JDBC完成Mysql的分庫分表和讀寫分離JDBCMySql
- mysql優化之讀寫分離MySql優化
- 探究MySQL MGR的讀寫分離MySql
- MySQL 讀寫分離的好處MySql
- 【Cetus】Cetus-讀寫分離版
- StoneDB 讀寫分離實踐方案
- 位元組面試：什麼是讀寫分離？讀寫分離的底層如何實現？面試