TLDR；Proxysql没有向或复制副本发送任何数据。master运行得很好，复制品都被赶上了，但它们不提供流量。
概述：主要问题是没有流量流向副本：
- 我们有4个mysql集群。每个都有一个主副本和两个副本
- 所有的复制副本都赶上了master，并且复制良好
- 我们现在使用…Drupal(叹气)，所以每个集群有多个数据库。所有这些都有自己的proxysql用户和默认主机组
- Proxysql在与副本的连接错误时显示null。它看起来可以连接
- 但所有的流量都撞到了主人。它为所有select、update和delete语句提供服务
- 我们将主控和副本设置在它们自己的写和读组中(而不是在同一主机组中，因为我们更喜欢手动指定哪些是主控，哪些是副本)
问题：我们如何让数据进入复制副本而不仅仅是主副本
配置：
mysql_users=
(
{ username = "company1", password = "aaaaaabbbbbbccccccdddddddfffffff", default_hostgroup = 10, max_connections = 200, active = 1, transaction_persistent = 0 },
{ username = "company2", password = "aaaaaabbbbbbccccccdddddddggggggg", default_hostgroup = 10, max_connections = 200, active = 1, transaction_persistent = 0 },
{ username = "company3", password = "aaaaaabbbbbbccccccdddddddhhhhhhh", default_hostgroup = 20, max_connections = 200, active = 1, transaction_persistent = 0 },
{ username = "company4", password = "aaaaaabbbbbbccccccdddddddiiiiiii", default_hostgroup = 20, max_connections = 200, active = 1, transaction_persistent = 0 }
.... and lots more.
)
mysql_servers =
(
# Cluster 1 (Master|Write Hostgroup)
{ address = "10.0.0.1", port = 3306, hostgroup = 10, status = "ONLINE", weight = 1000, compression = 0, max_replication_lag = 2, max_connections = 300, max_latency_ms = 2000, comment="Cluster 1 (Master DB-1)" },
# Cluster 1 (Slave|Read Hostgroup)
{ address = "10.0.0.2", port = 3306, hostgroup = 11, status = "ONLINE", weight = 1000, compression = 0, max_replication_lag = 2, max_connections = 300, max_latency_ms = 2000, comment="Cluster 1 (Slave DB-2)" },
{ address = "10.0.0.3", port = 3306, hostgroup = 11, status = "ONLINE", weight = 1000, compression = 0, max_replication_lag = 2, max_connections = 300, max_latency_ms = 2000, comment="Cluster 1 (Slave DB-3)" },
# Cluster 2 (Master|Write Hostgroup)
{ address = "10.0.0.4", port = 3306, hostgroup = 20, status = "ONLINE", weight = 1000, compression = 0, max_replication_lag = 2, max_connections = 300, max_latency_ms = 2000, comment="Cluster 2 (Master DB-4)" },
# Cluster 2 (Slave|Read Hostgroup)
{ address = "10.0.0.5", port = 3306, hostgroup = 21, status = "ONLINE", weight = 1000, compression = 0, max_replication_lag = 2, max_connections = 300, max_latency_ms = 2000, comment="Cluster 2 (Slave DB-5)" },
{ address = "10.0.0.6", port = 3306, hostgroup = 21, status = "ONLINE", weight = 1000, compression = 0, max_replication_lag = 2, max_connections = 300, max_latency_ms = 2000, comment="Cluster 2 (Slave DB-6)" },
)
mysql_replication_hostgroups=
(
# Cluster 1
{ writer_hostgroup=10, reader_hostgroup=11, comment="Cluster 1 Master / Slave 1 " },
# Cluster 2
{ writer_hostgroup=20, reader_hostgroup=21, comment="Cluster 2 Master / Slave 1" },
)
mysql_query_rules=
(
{ rule_id = 1, active = 0, match_digest = ".", log = 1, apply = 0 },
{ rule_id = 2, active = 1, match_digest = "^SELECTsname,stypesfromssystemsWHEREsstatus.*", cache_ttl = 3600000, flagOUT = 2, apply = 1 },
{ rule_id = 3, active = 1, match_digest = "SELECTsDISTINCTsregistry.namesASsname,sregistry.filenamesASsfilenamesFROMsregistry.*", cache_ttl = 3600000, flagOUT = 2, apply = 1 },
{ rule_id = 4, active = 1, match_digest = "SELECTs.*sFROMsmenu_routersWHEREspathsIN.*", cache_ttl = 3600000, flagOUT = 2, apply = 1 },
{ rule_id = 5, active = 1, match_digest = "SELECTsbase.vidsASsvid,sbase.namesASsname,sbase.machine_namesASsmachine_name,sbase.descriptionsASsdescription,sbase.hierarchysASshierarchy,sbase.modulesASsmodule,sbase.weightsASsweightsFROMstaxonomy_vocabularysbasesWHERE", cache_ttl = 300000, flagOUT = 2, apply = 1 },
{ rule_id = 6, active = 1, match_digest = "^SELECTsnid,sdatasFROMscompany_sync_nodes", flagOUT = 2, apply = 1 },
{ rule_id = 7, active = 1, match_digest = "^SELECTsclient_name_displaysFROMsiss_dispatch_clients", flagOUT = 2, apply = 1 },
{ rule_id = 8, active = 1, match_digest = "SELECTsf.fidsASsfidsFROMsfile_managedsfsWHEREs(f.statuss=s.*)sANDs(f.urisLIKEs.*sESCAPEs.*)sANDs(f.urisNOTsLIKEs.*sESCAPEs.*)sANDs(f.filemimesNOTsLIKEs.*sESCAPEs.*sORDERsBYsf.fidsDESCsLIMITs.*sOFFSETs.*", flagOUT = 2, apply = 1 },
{ rule_id = 9, active = 1, digest = "0x6C21FFE9B05A6873", flagOUT = 2, apply = 1 },
{ rule_id = 10, active = 1, digest = "0x6C21FFE9B05A6873", flagOUT = 2, apply = 1 },
{ rule_id = 11, active = 1, digest = "0x7E8E89B6752B147F", flagOUT = 2, apply = 1 },
{ rule_id = 12, active = 1, digest = "0xF41E2E690383C416", flagOUT = 2, apply = 1 },
{ rule_id = 13, active = 1, match_pattern = ".*ProxySQLSendToSlave.*", flagOUT = 2, apply = 1 },
{ rule_id = 99, active = 1, match_digest = ".", flagOUT = 1, apply = 1 }
)
mysql_variables=
{
threads=4
max_connections=2048
connection_max_age_ms=1200000
max_transaction_idle_time=1200000
monitor_replication_lag_count=3
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="myusername"
monitor_password="mypassword"
monitor_history=600000
monitor_connect_interval=30000
monitor_slave_lag_when_null=60
monitor_replication_lag_interval=30000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
monitor_writer_is_also_reader=false
eventslog_filename="queries.log"
log_unhealthy_connections="false"
query_cache_size_MB=2000
}
答案是，如果最后一个mysql_query_rules规则的apply为1，则不会应用mysql_query_rules_fast_routing规则。您需要将其保留为apply=0，然后将应用快速查询规则。