ProxySQL official file translation

20_Mirroring

Note: during the writing time of the article from April 201904 to may 201905, subsequent official updates in github were not written

~
~
Mirroring
be careful:
1) Its rules can be changed at any time;
2) It does not support prepare statements;

1, Extensions to MySQL query rules

Modify MySQL query rules table and add 2 columns:
1) mirror_flagOUT
2) mirror_hostgroup

Therefore, the new definition of MySQL query rules table becomes:

Admin> show create table mysql_query_rules\G
*************************** 1. row ***************************
       table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    schemaname VARCHAR,
    flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535), digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagOUT INT CHECK (flagOUT >= 0), 
    replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
    cache_timeout INT CHECK(cache_timeout >= 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED CHECK (timeout >= 0),
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED CHECK (delay >=0),
    next_query_flagIN INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    OK_msg VARCHAR,
    sticky_conn INT CHECK (sticky_conn IN (0,1)),
    multiplex INT CHECK (multiplex IN (0,1,2)),
    gtid_from_hostgroup INT UNSIGNED,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    comment VARCHAR)
1 row in set (0.00 sec)

2, Function Implementation overview

When mirror ou flagout or mirror ou hostgroup is set for matching query rules, the mirror real-time query function is automatically enabled.

Please note that:
If mirror ou flagout or mirror ou hostgroup is set in the rule In addition, replace pattern is set: that is, if the text content (SQL statement) of the original query has been rewritten (that is, the query rule has set replace pattern to rewrite / replace the original SQL), the mirror query function will be enabled for the final executed query (the modified statement), and the mirror query will be enabled for the original SQL: that is, if the query (SQL statement) is based on digest, match Gu Digest Or match pattern is matched to the query rule, but it is found that the query rule sets replace pattern to rewrite the matched query SQL. Then, the mirror logic will be applied to the rewritten query (SQL). Although the mirrored query (i.e. the original SQL statement) can be rewritten or modified, the mirror query function will be enabled as long as mirror_flagOUT or mirror_hostgroup is set. Details are at the back.

If the source query (SQL) matches more than one query rule, it is possible to change mirror ou flagout or mirror ou hostgroup more than once.

The image logic is as follows:
1) A new mysql session will be created if mirror'flagout'or'mirror'hostgroup'is set when processing the source query.
2) The new mysql session will get all the same properties of the original mysql session: the same credentials, library name, default host group, etc. (Note: charset is not currently copied)
3) If the mirror ou hostgroup is set in the original session, the new session changes its default host group to mirror ou hostgroup.
4) If mirror ou flagout is not set, the new session performs the original query against the defined mirror ou hostgroup.
5) If mirror'flagout 'is set in the original session, the new MySQL session will try to find the query rule whose FlagIN value is equal to it in mysql'query'rules according to the value of mirror'flagout in the original session (that is, the rule to find FlagIN = mirror'flagout); The mirror query request is then sent to this rule for processing: This allows you to modify the query, such as rewrite the query, or change the hostgroup again.
(refer to the following < 7. Advanced example: using image test query rewrite > content)

3, Case 1: select the same primary unit for original query and image query [mirror selections to same host group]

In this very simple example, we will send all the SELECT statements to hostgroup10, including the original and mirror statements.

1. Set query rules

1) Set query rules

Point the original query and image query to the same primary unit:

Admin> SELECT rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply FROM mysql_query_rules ;
+---------+--------+---------------+-----------------------+------------------+-------+
| rule_id | active | match_pattern | destination_hostgroup | mirror_hostgroup | apply |
+---------+--------+---------------+-----------------------+------------------+-------+
| 5       | 1      | NULL          | NULL                  | NULL             | 1     |
+---------+--------+---------------+-----------------------+------------------+-------+
1 row in set (0.00 sec)

Admin> INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply) VALUES (6,1,'^SELECT',10,10,1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply FROM mysql_query_rules ;
+---------+--------+---------------+-----------------------+------------------+-------+
| rule_id | active | match_pattern | destination_hostgroup | mirror_hostgroup | apply |
+---------+--------+---------------+-----------------------+------------------+-------+
| 5       | 1      | NULL          | NULL                  | NULL             | 1     |
| 6       | 1      | ^SELECT       | 10                    | 10               | 1     |
+---------+--------+---------------+-----------------------+------------------+-------+
2 rows in set (0.00 sec)

2) Load configuration to RUNTIME layer

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

Admin> SELECT rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply FROM runtime_mysql_query_rules ;
+---------+--------+---------------+-----------------------+------------------+-------+
| rule_id | active | match_pattern | destination_hostgroup | mirror_hostgroup | apply |
+---------+--------+---------------+-----------------------+------------------+-------+
| 5       | 1      | NULL          | NULL                  | NULL             | 1     |
| 6       | 1      | ^SELECT       | 10                    | 10               | 1     |
+---------+--------+---------------+-----------------------+------------------+-------+
2 rows in set (0.00 sec)

2. Connect to ProxySQL to execute query

From the mysql session, we will run some queries:

1) Connect to ProxySQL with business account

# mysql -h 188.188.0.71 -P 6033 -umsandbox -p

mysql> use sbtest;

mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
+------------------+
5 rows in set (0.00 sec)

2) The ProxySQL management end clears and views the query summary statistics currently executing SQL:

Admin> SELECT hostgroup,count_star,schemaname,digest_text FROM stats_mysql_query_digest_reset WHERE schemaname='sbtest' ORDER BY digest;
+-----------+------------+------------+--------------------------------+
| hostgroup | count_star | schemaname | digest_text                    |
+-----------+------------+------------+--------------------------------+
| 10        | 1          | sbtest     | show databases                 |
| 10        | 1          | sbtest     | show tables                    |
+-----------+------------+------------+--------------------------------+
2 rows in set (0.00 sec)

3) Business account execution query

mysql> SELECT id FROM sbtest1 LIMIT 3;
+------+
| id   |
+------+
| 1891 |
| 1511 |
| 8032 |
+------+
3 rows in set (0.00 sec)

4) ProxySQL management end, view the query summary statistics of the current SQL execution:

Admin> SELECT hostgroup,count_star,schemaname,digest_text FROM stats_mysql_query_digest WHERE schemaname='sbtest' ORDER BY digest;
+-----------+------------+------------+--------------------------------+
| hostgroup | count_star | schemaname | digest_text                    |
+-----------+------------+------------+--------------------------------+
| 10        | 2          | sbtest     | SELECT id FROM sbtest1 LIMIT ? |
+-----------+------------+------------+--------------------------------+
1 row in set (0.00 sec)

We can see that the SELECT statement has been executed twice!!

5) As an additional test, we rerun the same query:

mysql> SELECT id FROM sbtest1 LIMIT 3;
+------+
| id   |
+------+
| 1891 |
| 1511 |
| 8032 |
+------+
3 rows in set (0.00 sec

6) ProxySQL management end, view the query summary statistics of the current SQL execution:

Admin> SELECT hostgroup,count_star,schemaname,digest_text FROM stats_mysql_query_digest WHERE schemaname='sbtest' ORDER BY digest;
+-----------+------------+------------+--------------------------------+
| hostgroup | count_star | schemaname | digest_text                    |
+-----------+------------+------------+--------------------------------+
| 10        | 4          | sbtest     | SELECT id FROM sbtest1 LIMIT ? |
+-----------+------------+------------+--------------------------------+
1 row in set (0.01 sec)

count_star is twice as many times as we execute the query because it is mirrored. It is worth noting that ProxySQL collects metrics for both the original and mirror queries.

4, Case 2: select different host groups for the original query and image query [mirror select to different host group]

In this example, we will reconfigure proxysql to send all the SELECT statements to hostgroup10, but to perform a mirror query on hostgroup20:

1. Set query rules

1) Set query rules

Admin> DELETE FROM mysql_query_rules;
Query OK, 2 rows affected (0.00 sec)

Admin> INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply) VALUES (5,1,'^SELECT',10,20,1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply FROM mysql_query_rules ;
+---------+--------+---------------+-----------------------+------------------+-------+
| rule_id | active | match_pattern | destination_hostgroup | mirror_hostgroup | apply |
+---------+--------+---------------+-----------------------+------------------+-------+
| 5       | 1      | ^SELECT       | 10                    | 20               | 1     |
+---------+--------+---------------+-----------------------+------------------+-------+
1 row in set (0.00 sec)

2) Load configuration to RUNTIME layer

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

2. Connect to ProxySQL to execute query

1) Connect to ProxySQL with business account

# mysql -h 188.188.0.71 -P 6033 -umsandbox -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sbtest             |
+--------------------+
2 rows in set (0.00 sec)

mysql> use sbtest;

mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
+------------------+
5 rows in set (0.00 sec)

2) The ProxySQL management end clears stats > MySQL > query > digest to get new statistics:

Admin> SELECT hostgroup,count_star,schemaname,digest_text FROM stats_mysql_query_digest_reset WHERE schemaname='sbtest' ORDER BY digest;
+-----------+------------+------------+----------------+
| hostgroup | count_star | schemaname | digest_text    |
+-----------+------------+------------+----------------+
| 10        | 1          | sbtest     | show databases |
| 10        | 1          | sbtest     | show tabels    |
| 10        | 2          | sbtest     | show tables    |
+-----------+------------+------------+----------------+
3 rows in set (0.00 sec)

Admin> SELECT hostgroup,count_star,schemaname,digest_text FROM stats_mysql_query_digest WHERE schemaname='sbtest' ORDER BY digest;
Empty set (0.00 sec)

3) Business account execution query

From the mysql client, we can now run some queries (for simplicity, we run the same):

mysql> SELECT id FROM sbtest1 LIMIT 3;
+------+
| id   |
+------+
| 1891 |
| 1511 |
| 8032 |
+------+
3 rows in set (0.00 sec)

4) ProxySQL management end, view the query summary statistics of the current SQL execution:

Admin> SELECT hostgroup,count_star,schemaname,digest_text FROM stats_mysql_query_digest WHERE schemaname='sbtest' ORDER BY digest;
+-----------+------------+------------+--------------------------------+
| hostgroup | count_star | schemaname | digest_text                    |
+-----------+------------+------------+--------------------------------+
| 20        | 1          | sbtest     | SELECT id FROM sbtest1 LIMIT ? |
| 10        | 1          | sbtest     | SELECT id FROM sbtest1 LIMIT ? |
+-----------+------------+------------+--------------------------------+
2 rows in set (0.00 sec)

You can see that ProxySQL sends the same query to hostgroup10 and hostgroup20!

5, Case 3: rewrite both source query and mirror

In this example, we will rewrite the original query and mirror it: for simplicity, we will rewrite the operation on table sbtest[0-9] + to the operation on table sbtest3:

1. Set query rules

1) Set query rules

Admin> DELETE FROM mysql_query_rules;
Query OK, 1 row affected (0.00 sec)

Admin> INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,replace_pattern,mirror_hostgroup,apply) VALUES (5,1,'sbtest[0-9]+',10,'sbtest3',20,1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT rule_id,active,match_pattern,destination_hostgroup,replace_pattern,mirror_hostgroup,apply FROM mysql_query_rules ;
+---------+--------+---------------+-----------------------+-----------------+------------------+-------+
| rule_id | active | match_pattern | destination_hostgroup | replace_pattern | mirror_hostgroup | apply |
+---------+--------+---------------+-----------------------+-----------------+------------------+-------+
| 5       | 1      | sbtest[0-9]+  | 10                    | sbtest3         | 20               | 1     |
+---------+--------+---------------+-----------------------+-----------------+------------------+-------+
1 row in set (0.00 sec)

2) Load configuration to RUNTIME layer

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

2. Connect to ProxySQL to execute query

1) Connect to ProxySQL with business account

# mysql -h 188.188.0.71 -P 6033 -umsandbox -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sbtest             |
+--------------------+
2 rows in set (0.00 sec)

mysql> use sbtest;

mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
+------------------+
5 rows in set (0.00 sec)

2) The ProxySQL management end clears stats > MySQL > query > digest to get new statistics:

Admin> SELECT hostgroup,count_star,schemaname,digest_text FROM stats_mysql_query_digest_reset WHERE schemaname='sbtest' ORDER BY digest;
+-----------+------------+------------+----------------+
| hostgroup | count_star | schemaname | digest_text    |
+-----------+------------+------------+----------------+
| 10        | 1          | sbtest     | show databases |
| 10        | 2          | sbtest     | show tables    |
+-----------+------------+------------+----------------+
2 rows in set (0.00 sec)

Admin> SELECT hostgroup,count_star,schemaname,digest_text FROM stats_mysql_query_digest WHERE schemaname='sbtest' ORDER BY digest;
Empty set (0.01 sec)

3) Business account execution query

From the mysql client, we can run common queries:

mysql> SELECT id FROM sbtest1 LIMIT 3;
+------+
| id   |
+------+
| 1149 |
| 9825 |
| 5704 |
+------+
3 rows in set (0.00 sec)

As expected, the output of this query is different from the previous one because the original query is now rewritten. The table name is to query that sbtest1 is actually rewritten to sbtest3; it can be verified by the following content.

4) ProxySQL management end, view the query summary statistics of the current SQL execution:

Admin> SELECT hostgroup,count_star,schemaname,digest_text FROM stats_mysql_query_digest WHERE schemaname='sbtest' ORDER BY digest;
+-----------+------------+------------+--------------------------------+
| hostgroup | count_star | schemaname | digest_text                    |
+-----------+------------+------------+--------------------------------+
| 20        | 1          | sbtest     | SELECT id FROM sbtest3 LIMIT ? |
| 10        | 1          | sbtest     | SELECT id FROM sbtest3 LIMIT ? |
+-----------+------------+------------+--------------------------------+
2 rows in set (0.01 sec)

As expected, the modified query is executed on two host groups, and the table name sbtest1 in the original SQL is rewritten as sbtest3.

6, rewrite mirror query only

In this example, we will override only the mirror query.
This is useful, for example, if we want to see the performance of rewriting queries, or if the new index will improve performance.
In this example, we will compare the performance of the same query with and without indexes. Of course, we will also send queries to the same host group.

1. Set query rules

1) Set query rules

Create the following rule (rule Ou id = 5):
1) Match FROM sbtest1;
2) Set destination_hostgroup = 20;
3) Set mirror ou flagout = 100;
4) Do not set mirror_hostgroup;

Admin> DELETE FROM mysql_query_rules;
Query OK, 1 row affected (0.00 sec)

Admin> INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_flagOUT,apply) VALUES (5,1,'FROM sbtest1 ',20,100,1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT rule_id,active,match_pattern,destination_hostgroup,mirror_flagOUT,mirror_hostgroup,apply FROM mysql_query_rules ;
+---------+--------+---------------+-----------------------+----------------+------------------+-------+
| rule_id | active | match_pattern | destination_hostgroup | mirror_flagOUT | mirror_hostgroup | apply |
+---------+--------+---------------+-----------------------+----------------+------------------+-------+
| 5       | 1      | FROM sbtest1  | 20                    | 100            | NULL             | 1     |
+---------+--------+---------------+-----------------------+----------------+------------------+-------+
1 row in set (0.00 sec)

A new session will be created to run the same query because mirror'flagout is set. However, because mirror_hostgroup is not set, the query will be sent to its default host group according to the default host group set by the current user in mysql_users. Instead, if we want to send a mirror query to the same host group as the original. We can set the mirror_hostgroup in the rule_id = 5, or create
A new rule. Here, select the latter, and we will create a new rule to match the rewrite query:

Admin> INSERT INTO mysql_query_rules (rule_id,active,flagIN,match_pattern,destination_hostgroup,replace_pattern,apply) VALUES (10,1,100,'FROM sbtest1 ',20,'FROM sbtest1 IGNORE INDEX(k_1) ',1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT rule_id,active,flagIN,match_pattern,replace_pattern,destination_hostgroup,mirror_flagOUT,mirror_hostgroup,apply FROM mysql_query_rules ;
+---------+--------+--------+---------------+---------------------------------+-----------------------+----------------+------------------+-------+
| rule_id | active | flagIN | match_pattern | replace_pattern                 | destination_hostgroup | mirror_flagOUT | mirror_hostgroup | apply |
+---------+--------+--------+---------------+---------------------------------+-----------------------+----------------+------------------+-------+
| 5       | 1      | 0      | FROM sbtest1  | NULL                            | 20                    | 100            | NULL             | 1     |
| 10      | 1      | 100    | FROM sbtest1  | FROM sbtest1 IGNORE INDEX(k_1)  | 20                    | NULL           | NULL             | 1     |
+---------+--------+--------+---------------+---------------------------------+-----------------------+----------------+------------------+-------+
2 rows in set (0.00 sec)

Or view all information about the rule:

Admin> SELECT * FROM mysql_query_rules \G 
*************************** 1. row ***************************
              rule_id: 5
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: FROM sbtest1 
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 20
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: 100
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 10
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 100
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: FROM sbtest1 
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: FROM sbtest1 IGNORE INDEX(k_1) 
destination_hostgroup: 20
            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: NULL
2 rows in set (0.00 sec)

It should be noted that in the rule with rule UU id = 10, the mirror query will match the rule [because mirror UU flagout (Rule 5) = flagin (rule 100 = Rule 10)]. At this time, we need to set the destination UU hostgroup instead of the mirror UU hostgroup: only the mirror UU hostgroup should be set for the original query, so that the mirror query request can be sent to the specified location immediately without needing to Other additional rules in MySQL query rules are involved.

2) Load configuration to RUNTIME layer

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

Admin> SELECT rule_id,active,flagIN,match_pattern,replace_pattern,destination_hostgroup,mirror_flagOUT,mirror_hostgroup,apply FROM runtime_mysql_query_rules ;
+---------+--------+--------+---------------+---------------------------------+-----------------------+----------------+------------------+-------+
| rule_id | active | flagIN | match_pattern | replace_pattern                 | destination_hostgroup | mirror_flagOUT | mirror_hostgroup | apply |
+---------+--------+--------+---------------+---------------------------------+-----------------------+----------------+------------------+-------+
| 5       | 1      | 0      | FROM sbtest1  | NULL                            | 20                    | 100            | NULL             | 1     |
| 10      | 1      | 100    | FROM sbtest1  | FROM sbtest1 IGNORE INDEX(k_1)  | 20                    | NULL           | NULL             | 1     |
+---------+--------+--------+---------------+---------------------------------+-----------------------+----------------+------------------+-------+
2 rows in set (0.00 sec)

Configuration has taken effect!

2. Business account execution query

1) Connect to ProxySQL with business account

# mysql -h 188.188.0.71 -P 6033 -umsandbox -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sbtest             |
+--------------------+
2 rows in set (0.00 sec)

mysql> use sbtest;

mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
+------------------+
5 rows in set (0.00 sec)

2) The ProxySQL management end clears stats > MySQL > query > digest to get new statistics:

Admin> SELECT COUNT(*) FROM stats_mysql_query_digest_reset;
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

3) Business account execution query

From the mysql client, we can run common queries:

mysql> SELECT id FROM sbtest1 ORDER BY k DESC LIMIT 3;
+------+
| id   |
+------+
| 5076 |
| 5121 |
| 8573 |
+------+
3 rows in set (0.00 sec)

mysql> SELECT id,k FROM sbtest1 ORDER BY k DESC LIMIT 3;
+------+------+
| id   | k    |
+------+------+
| 5076 | 7672 |
| 5121 | 7655 |
| 8573 | 7565 |
+------+------+
3 rows in set (0.01 sec)

4) ProxySQL management end, view the query summary statistics of the current SQL execution:

Admin> SELECT hostgroup,count_star,sum_time,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+-----------+------------+----------+--------------------------------------------------------------------+
| hostgroup | count_star | sum_time | digest_text                                                        |
+-----------+------------+----------+--------------------------------------------------------------------+
| 20        | 1          | 3997     | SELECT id,k FROM sbtest1 IGNORE INDEX(k_1) ORDER BY k DESC LIMIT ? |
| 20        | 1          | 3997     | SELECT id FROM sbtest1 IGNORE INDEX(k_1) ORDER BY k DESC LIMIT ?   |
| 20        | 1          | 615      | SELECT id FROM sbtest1 ORDER BY k DESC LIMIT ?                     |
| 20        | 1          | 342      | SELECT id,k FROM sbtest1 ORDER BY k DESC LIMIT ?                   |
+-----------+------------+----------+--------------------------------------------------------------------+
4 rows in set (0.01 sec)

The results of the stats MySQL query digest table show that:
1) The original query is mirrored;
2) The original query was not rewritten (but mirrored);
3) The image query is rewritten (with the IGNORE statement in the rule);
4) The image query speed is much slower because the index is ignored;

7, Advanced example: use mirroring to test query rewrite

When dealing with images, I was asked a completely different question about Query Rewriting: how to know whether a given regular expression matches a given query and verify whether the rewriting pattern is correct?
More specifically, the problem is to understand whether the rewriting is correct without affecting real-time traffic. Although the image was not originally designed for this purpose, it can answer this question.

In this case, we will write a rule to match all the SELECT, mirror them, and try to rewrite them.

1. Set query rules

1) Set query rules

Admin> DELETE FROM mysql_query_rules;
Query OK, 2 rows affected (0.00 sec)

Admin> INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_flagOUT,apply) VALUES (5,1,'^SELECT ',20,100,1);
Query OK, 1 row affected (0.00 sec)

Admin> INSERT INTO mysql_query_rules (rule_id,active,flagIN,match_pattern,destination_hostgroup,replace_pattern,apply) VALUES 
(10,1,100,'^SELECT DISTINCT c FROM sbtest([0-9]{1,2}) WHERE id BETWEEN ([0-9]+) AND ([0-9]+)\+([0-9]+) ORDER BY c$',20,'SELECT DISTINCT c FROM sbtest\1 WHERE id = \3 \+ \4 ORDER BY c',1);
Query OK, 1 row affected (0.00 sec)

To view the setup results:

Admin> SELECT rule_id,active,flagIN,match_pattern,destination_hostgroup,replace_pattern,mirror_flagOUT,apply FROM mysql_query_rules \G
*************************** 1. row ***************************
              rule_id: 5
               active: 1
               flagIN: 0
        match_pattern: ^SELECT 
destination_hostgroup: 20
      replace_pattern: NULL
       mirror_flagOUT: 100
                apply: 1
*************************** 2. row ***************************
              rule_id: 10
               active: 1
               flagIN: 100
        match_pattern: ^SELECT DISTINCT c FROM sbtest([0-9]{1,2}) WHERE id BETWEEN ([0-9]+) AND ([0-9]+)\+([0-9]+) ORDER BY c$
destination_hostgroup: 20
      replace_pattern: SELECT DISTINCT c FROM sbtest\1 WHERE id = \3 \+ \4 ORDER BY c
       mirror_flagOUT: NULL
                apply: 1
2 rows in set (0.00 sec)

2) Load configuration to RUNTIME layer

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

Admin> SELECT rule_id,active,flagIN,match_pattern,destination_hostgroup,replace_pattern,mirror_flagOUT,apply FROM runtime_mysql_query_rules \G
*************************** 1. row ***************************
              rule_id: 5
               active: 1
               flagIN: 0
        match_pattern: ^SELECT 
destination_hostgroup: 20
      replace_pattern: NULL
       mirror_flagOUT: 100
                apply: 1
*************************** 2. row ***************************
              rule_id: 10
               active: 1
               flagIN: 100
        match_pattern: ^SELECT DISTINCT c FROM sbtest([0-9]{1,2}) WHERE id BETWEEN ([0-9]+) AND ([0-9]+)\+([0-9]+) ORDER BY c$
destination_hostgroup: 20
      replace_pattern: SELECT DISTINCT c FROM sbtest\1 WHERE id = \3 \+ \4 ORDER BY c
       mirror_flagOUT: NULL
                apply: 1
2 rows in set (0.00 sec)

The above regular expression is very complex, which is why the mirror query function is useful. It does not directly rewrite the real-time traffic.

2. Business account execution query

1) Connect to ProxySQL with business account

# mysql -h 188.188.0.71 -P 6033 -umsandbox -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sbtest             |
+--------------------+
2 rows in set (0.00 sec)

mysql> use sbtest;

mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
+------------------+
5 rows in set (0.00 sec)

2) The ProxySQL management end clears stats > MySQL > query > digest to get new statistics:

Admin> SELECT COUNT(*) FROM stats_mysql_query_digest_reset;
+----------+
| COUNT(*) |
+----------+
| 10       |
+----------+
1 row in set (0.00 sec)

3) Business account execution query

From the mysql client, we can run common queries:

mysql> SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 10 AND 10+2 ORDER BY c;
+-------------------------------------------------------------------------------------------------------------------------+
| c                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------+
| 06208928544-69213163800-95083408911-83949560459-26629535077-58798231143-58688386449-59141897529-07315042085-86003451120 |
| 68305043604-07392484646-78480928447-88155597080-08908465928-35357008626-44894171482-13904841657-13998032237-49278517178 |
| 84225420767-95119807827-48689909948-04145663437-29723649568-88238910120-61256632514-12324871715-71270848294-09484980067 |
+-------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

The query ran successfully. As mentioned above, we did not modify the original traffic.
What about stats > MySQL > query > digest?

4) ProxySQL management end, view the query summary statistics of the current SQL execution:

Admin> select hostgroup,count_star,sum_time,digest_text from stats_mysql_query_digest ORDER BY digest_text;
+-----------+------------+----------+----------------------------------------------------------------------+
| hostgroup | count_star | sum_time | digest_text                                                          |
+-----------+------------+----------+----------------------------------------------------------------------+
| 20        | 2          | 1493     | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
+-----------+------------+----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

As you can see, the original query was executed twice, so some of the content did not work properly. We can notice that both queries are sent to hostgroup20: we should believe that rule Ou id = 10 is a match, but the query is not rewritten.
Let's verify that it matches:

Admin> SELECT * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 5       | 2    |  -->One of them was show tables Triggered.
| 10      | 1    |
+---------+------+
2 rows in set (0.00 sec)

According to the rule hit information in stats MySQL query rules, we can see that the rule with rule id = 10 matches.
So why not rewrite the query?

5) Problem fix

After re examining the replace pattern content of rule id = 10:

SELECT DISTINCT c FROM sbtest\1 WHERE id = \3 \+ \4 ORDER BY c

Found that there should be no escape before the + sign. Update query Rule 10:

Admin> UPDATE mysql_query_rules SET replace_pattern='SELECT DISTINCT c FROM sbtest\1 WHERE id = \3 + \4 ORDER BY c' WHERE rule_id=10;
Query OK, 1 row affected (0.00 sec)

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

Admin> SELECT rule_id,active,flagIN,match_pattern,destination_hostgroup,replace_pattern,mirror_flagOUT,apply FROM runtime_mysql_query_rules WHERE rule_id=10 \G
*************************** 1. row ***************************
              rule_id: 10
               active: 1
               flagIN: 100
        match_pattern: ^SELECT DISTINCT c FROM sbtest([0-9]{1,2}) WHERE id BETWEEN ([0-9]+) AND ([0-9]+)\+([0-9]+) ORDER BY c$
destination_hostgroup: 20
      replace_pattern: SELECT DISTINCT c FROM sbtest\1 WHERE id = \3 + \4 ORDER BY c
       mirror_flagOUT: NULL
                apply: 1
1 row in set (0.00 sec)

Modification has taken effect!!

6) Verification results

The ProxySQL management end clears stats > MySQL > query > digest to get new statistics:

Admin> SELECT COUNT(*) FROM stats_mysql_query_digest_reset;
+----------+
| COUNT(*) |
+----------+
| 5        |
+----------+
1 row in set (0.00 sec)

Execute the original SQL again on the client:

mysql> SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 10 AND 10+2 ORDER BY c;
+-------------------------------------------------------------------------------------------------------------------------+
| c                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------+
| 06208928544-69213163800-95083408911-83949560459-26629535077-58798231143-58688386449-59141897529-07315042085-86003451120 |
| 68305043604-07392484646-78480928447-88155597080-08908465928-35357008626-44894171482-13904841657-13998032237-49278517178 |
| 84225420767-95119807827-48689909948-04145663437-29723649568-88238910120-61256632514-12324871715-71270848294-09484980067 |
+-------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Now let's verify that the query is rewritten correctly:

Admin>  select hostgroup,count_star,sum_time,digest_text from stats_mysql_query_digest ORDER BY digest_text;
+-----------+------------+----------+----------------------------------------------------------------------+
| hostgroup | count_star | sum_time | digest_text                                                          |
+-----------+------------+----------+----------------------------------------------------------------------+
| 20        | 1          | 414      | SELECT DISTINCT c FROM sbtest1 WHERE id = ? + ? ORDER BY c           |
| 20        | 1          | 661      | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
+-----------+------------+----------+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

As you can see here, the query is rewritten correctly and executed!

8, Advanced example: use mirroring and firewall to test query rewrite

The previous example / exercise is a little ahead of time: so, can you rewrite the query without executing it? The answer is: Yes!
To do this, we will set up error_msg for the mirror query: so that ProxySQL will process the mirror query, but it will filter it without sending it to any mysql server.
As mentioned at the beginning, you can modify the image query, and the firewall is an example of modifying the image query.

For example:
Continue with the previous case.

1) Modify query rules

Add error? MSG information for Rule 10.

Admin> UPDATE mysql_query_rules SET error_msg="random error, blah blah" WHERE rule_id=10;
Query OK, 1 row affected (0.00 sec)

2) Load configuration to RUNTIME layer

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

To view the modification results:

Admin> SELECT rule_id,active,flagIN,match_pattern,destination_hostgroup,replace_pattern,mirror_flagOUT,apply,error_msg FROM runtime_mysql_query_rules WHERE rule_id=10 \G
*************************** 1. row ***************************
              rule_id: 10
               active: 1
               flagIN: 100
        match_pattern: ^SELECT DISTINCT c FROM sbtest([0-9]{1,2}) WHERE id BETWEEN ([0-9]+) AND ([0-9]+)\+([0-9]+) ORDER BY c$
destination_hostgroup: 20
      replace_pattern: SELECT DISTINCT c FROM sbtest\1 WHERE id = \3 + \4 ORDER BY c
       mirror_flagOUT: NULL
                apply: 1
            error_msg: random error, blah blah
1 row in set (0.00 sec)

As you can see, the modification has taken effect!!

3) The ProxySQL management end clears and views the query summary statistics currently executing SQL:

Admin> SELECT COUNT(*) FROM stats_mysql_query_digest_reset;
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

4) Business account execution query

Rerun the query on the mysql client:

mysql> SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 10 AND 10+2 ORDER BY c;
+-------------------------------------------------------------------------------------------------------------------------+
| c                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------+
| 06208928544-69213163800-95083408911-83949560459-26629535077-58798231143-58688386449-59141897529-07315042085-86003451120 |
| 68305043604-07392484646-78480928447-88155597080-08908465928-35357008626-44894171482-13904841657-13998032237-49278517178 |
| 84225420767-95119807827-48689909948-04145663437-29723649568-88238910120-61256632514-12324871715-71270848294-09484980067 |
+-------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

5) ProxySQL management end, view the query summary statistics of the current SQL execution:

Admin> select hostgroup,count_star,sum_time,digest_text from stats_mysql_query_digest ORDER BY digest_text;
+-----------+------------+----------+----------------------------------------------------------------------+
| hostgroup | count_star | sum_time | digest_text                                                          |
+-----------+------------+----------+----------------------------------------------------------------------+
| 10        | 1          | 0        | SELECT DISTINCT c FROM sbtest1 WHERE id = ? + ? ORDER BY c           |
| 20        | 1          | 730      | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
+-----------+------------+----------+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

Admin> SELECT * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 5       | 1    |
| 10      | 1    |
+---------+------+
2 rows in set (0.01 sec)

Great!! we've seen that the query has been rewritten, but it's not actually sent anywhere:
1) sum_time = 0 because the response is immediate;
2) hostgroup=10. In Rule 10, destination_hostgroup=20 is set, but the default host group 10 of business account is displayed here. If the default host group is not set for business account, it is displayed as 0. This display is inconsistent with the setting and is not sent to any place on the surface (you can open the general_log of default host group MySQL to verify if you don't believe it)

complete!

Tags: MySQL SQL Session Database

Posted on Tue, 28 Apr 2020 04:27:53 -0700 by Hebbs