proxySQL with SemiSync

配置读写分离

• 先查看一下规则表的表结构

https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_query_rules
mysql> 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,    --规则id
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,  --查询处理模块将仅考虑active = 1的规则，并且仅将active规则加载到运行时。
    username VARCHAR,                                       --匹配用户名的过滤条件。 如果为非NULL，则仅当使用正确的用户名建立连接时，查询才会匹配。
    schemaname VARCHAR,                                     --符合标准名称的过滤条件。 如果为非NULL，则仅当连接使用schemaname作为默认架构时查询才匹配
    flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,      --flagIN，flagOUT，应用-这些使我们能够创建一个"规则链"，一个接一个地应用。 输入标志值设置为0，并且仅在开始时考虑flagIN = 0的规则。 当为特定查询找到匹配规则时，将评估flagOUT，如果NOT NULL，则将在flagOUT中使用指定的标志来标记查询。 如果flagOUT与flagIN不同，则查询将退出当前链，并输入具有flagIN作为新输入标志的新规则链。 如果flagOUT与flagIN匹配，则将针对带有该flagIN的第一条规则再次重新评估查询。 直到不再有匹配的规则，或者将apply设置为1时，这才发生（这意味着这是最后一个要应用的规则）
    client_addr VARCHAR,                                    --match traffic from a specific source，匹配特定来源。
    proxy_addr VARCHAR,                                     --match incoming traffic on a specific local IP，匹配特定本地IP的入口。
    proxy_port INT CHECK (proxy_port >= 0 AND proxy_port = 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,                 --将匹配的查询路由到该主机组。 除非存在已启动的事务，并且已登录的用户将transaction_persistent标志设置为1（请参见mysql_users表），否则将发生这种情况。
    cache_ttl INT CHECK(cache_ttl > 0),                     --the number of milliseconds for which to cache the result of the query. Note: in ProxySQL 1.1 cache_ttl was in seconds
    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),              --执行匹配或重写查询的最大超时（以毫秒为单位）。 如果查询的运行时间超过特定阈值，则会自动终止该查询。 如果未指定超时，则应用全局变量mysql-default_query_timeout。
    retries INT CHECK (retries>=0 AND retries =0),                   --延迟查询执行的毫秒数。是一种限制机制和QoS，允许优先处理某些查询而不是其他查询。 该值被添加到适用于所有查询的mysql-default_query_delay全局变量中。 未来版本的ProxySQL将提供更高级的限制机制。
    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)),             --如果为0，将禁用多路复用。 如果为1，则在没有其他条件阻止这种情况（例如用户变量或事务）的情况下，可以重新启用Multiplex。 如果为2，则仅对当前查询不禁用多路复用。 请参见Wiki。默认为NULL，因此不修改多路复用策略。
    gtid_from_hostgroup INT UNSIGNED,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,  --当设置为1时，在匹配并处理此规则后将不再评估其他查询（注意：此后将不再评估mysql_query_rules_fast_routing规则）
    comment VARCHAR)
1 row in set (0.00 sec)

• 根据sql匹配正则表达式，并加载到runtime

mysql> insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^SELECT',101,1);
Query OK, 1 row affected (0.00 sec)

mysql> select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_pattern        | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 100                   | 1     |
| 2       | 1      | ^SELECT              | 101                   | 1     |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)

mysql> load mysql query rules to run;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.04 sec)

• 使用sysbench运行事务，并查看stats.mysql_query_digest

[14:31:03] root@ms84:~ # sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.188.84 --mysql-port=6033 --mysql-user=proxy --mysql-password=proxy --db-driver=mysql --mysql-db=kk --table-size=5000  run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

mysql> select * from stats.stats_mysql_query_digest_reset;
Empty set (0.00 sec)

mysql> select hostgroup, digest, digest_text, count_star, first_seen, last_seen from stats.stats_mysql_query_digest;
+-----------+--------------------+--------------------------------------------------------------------+------------+------------+------------+
| hostgroup | digest             | digest_text                                                        | count_star | first_seen | last_seen  |
+-----------+--------------------+--------------------------------------------------------------------+------------+------------+------------+
| 100       | 0xE52A0A0210634DAC | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)            | 409        | 1589870243 | 1589870250 |
| 100       | 0xFB239BC95A23CA36 | UPDATE sbtest1 SET c=? WHERE id=?                                  | 409        | 1589870243 | 1589870250 |
| 100       | 0xC198E52BCCB481C7 | UPDATE sbtest1 SET k=k+? WHERE id=?                                | 409        | 1589870243 | 1589870250 |
| 101       | 0xC19480748AE79B4B | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | 409        | 1589870243 | 1589870250 |
| 100       | 0xE365BEB555319B9E | DELETE FROM sbtest1 WHERE id=?                                     | 409        | 1589870243 | 1589870250 |
| 101       | 0xAC80A5EA0101522E | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c          | 409        | 1589870243 | 1589870250 |
| 101       | 0xDBF868B2AA296BC5 | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                | 409        | 1589870243 | 1589870250 |
| 101       | 0x290B92FD743826DA | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                     | 409        | 1589870243 | 1589870250 |
| 101       | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=?                                   | 4087       | 1589870243 | 1589870250 |
| 100       | 0x695FBF255DBEB0DD | COMMIT                                                             | 409        | 1589870243 | 1589870250 |
| 100       | 0xFAD1519E4760CBDE | BEGIN                                                              | 410        | 1589870243 | 1589870250 |
+-----------+--------------------+--------------------------------------------------------------------+------------+------------+------------+
11 rows in set (0.01 sec)

• 针对特定SQL进行读写分离规则（生产环境强烈建议使用此模式）
  因为生产环境业务情况较为固定，SQL类别总体上有一个固定范围。根据业务情况，并不是将所有读写进行分离就是最佳方案，很多时候只许将特定的一些SQL集路由到slave上进行读，而大部分业务还保留在主库。
  这时便用上了基于digest进行读写分离的规则。一般将特别大事务量的查询，或特别频繁的查询路由到slave上。
  不过digest是完全正则匹配， 如果出现大小写、多空格等情况，生成的digest是不同的，无法利用上规则 比如，上一个实验里，0xBF001A0C13781C1D 这个sql运行了4087次，其它sql都远低于该值。那么我们就为这个sql指定规则。

mysql> insert into mysql_query_rules(rule_id,active,digest,destination_hostgroup,apply) values(3,1,'0xBF001A0C13781C1D',101,1);
Query OK, 1 row affected (0.00 sec)

mysql> update mysql_query_rules set active=0 , apply=0 where rule_id in (1,2);
Query OK, 2 rows affected (0.00 sec)

mysql> select rule_id,active,digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+--------------------+-----------------------+-------+
| rule_id | active | digest             | destination_hostgroup | apply |
+---------+--------+--------------------+-----------------------+-------+
| 1       | 0      | NULL               | 100                   | 0     |
| 2       | 0      | NULL               | 101                   | 0     |
| 3       | 1      | 0xBF001A0C13781C1D | 101                   | 1     |
+---------+--------+--------------------+-----------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from stats.stats_mysql_query_digest_reset;
Empty set (0.00 sec)

mysql> load mysql query rules to run;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.04 sec)

• 使用sysbench运行事务，并查看stats.mysql_query_digest
  可以看到，0xBF001A0C13781C1D已经被路由到slave，其它事务不受影响，依然在master进行。

[14:58:57] root@ms84:~ # sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=192.168.188.84 --mysql-port=6033 --mysql-user=proxy --mysql-password=proxy --db-driver=mysql --mysql-db=kk --table-size=5000  run

mysql> select hostgroup, digest, digest_text, count_star, first_seen, last_seen from stats.stats_mysql_query_digest;
+-----------+--------------------+--------------------------------------------------------------------+------------+------------+------------+
| hostgroup | digest             | digest_text                                                        | count_star | first_seen | last_seen  |
+-----------+--------------------+--------------------------------------------------------------------+------------+------------+------------+
| 100       | 0xE52A0A0210634DAC | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)            | 653        | 1589871539 | 1589871549 |
| 100       | 0xE365BEB555319B9E | DELETE FROM sbtest1 WHERE id=?                                     | 653        | 1589871539 | 1589871549 |
| 100       | 0xFB239BC95A23CA36 | UPDATE sbtest1 SET c=? WHERE id=?                                  | 653        | 1589871539 | 1589871549 |
| 100       | 0xC198E52BCCB481C7 | UPDATE sbtest1 SET k=k+? WHERE id=?                                | 653        | 1589871539 | 1589871549 |
| 100       | 0xC19480748AE79B4B | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | 653        | 1589871539 | 1589871549 |
| 100       | 0xDBF868B2AA296BC5 | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                | 653        | 1589871539 | 1589871549 |
| 100       | 0x290B92FD743826DA | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                     | 653        | 1589871539 | 1589871549 |
| 101       | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=?                                   | 6521       | 1589871539 | 1589871549 |
| 100       | 0x695FBF255DBEB0DD | COMMIT                                                             | 653        | 1589871539 | 1589871549 |
| 100       | 0xAC80A5EA0101522E | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c          | 653        | 1589871539 | 1589871549 |
| 100       | 0xFAD1519E4760CBDE | BEGIN                                                              | 653        | 1589871539 | 1589871549 |
+-----------+--------------------+--------------------------------------------------------------------+------------+------------+------------+
11 rows in set (0.01 sec)

Original: https://www.cnblogs.com/konggg/p/13571598.html
Author: 孔个个
Title: proxySQL with SemiSync