Shulou(Shulou.com)06/01 Report--

Operating system: CentOS release 6.9

Main library: 192.168.140.51

From library 1: 192.168.140.52 read_only=on

From Library 2: 192.168.16.150 read_only=on

Proxysql middleware: 192.168.140.52

Configure the read-write separation policy, which requires the master database to write and two slaves to read. All SQL statements starting with select are assigned to the read group numbered 20, and select for update or update operations are assigned to the write group numbered 10:

Mysql > insert into mysql_query_rules (active,match_pattern,destination_hostgroup,apply) values (1meme'^ 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 (1meme'^ 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 (Note: 6033 is the external access port number of middleware)

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 breadwinner dsf'

Query OK, 60000 rows affected (1.80 sec)

Rows matched: 60000 Changed: 60000 Warnings: 0

Read-write separation test:

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 baggage? | 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 |

| | 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 |

| | 10 | information_schema | dsf | 0xA4D993880D70C4D9 | show slave hosts | 1 | 1539065258 | 1539065258 | 6351 | 6351 | 6351 |

| | 20 | test | dsf | 0x3765930C7143F468 | select * from T1 | 4 | 1539067272 | 1539068207 | 587 | 2876 |

| | 10 | test | dsf | 0x02033E45904D3DF0 | show databases | 3 | 1539067262 | 1539067399 | 8,745 | 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)

Adjust the read-write separation weight:

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.