Proxysql不向多db集群中的MySQL副本/从服务器发送数据

Question

Proxysql没有向副本发送任何数据。主人工作得很好，所有的复制品都被追上了，但它们并不能为交通服务。

概述:主要问题是副本没有流量：

• 我们有4个mysql集群。每个副本都有一个主副本和两个副本。
• 复制的副本都被捕获并复制得很好。
• 我们在……目前，Drupal (叹息)，因此每个集群有多个数据库。使用它们自己的proxysql用户和默认主机，group.
• Proxysql在指向副本的连接错误上显示为null。看起来它可以连接，

，

• ，但是所有的流量都会命中主机。它为所有的选择、更新和删除statement.
• We提供服务，我们的主程序和副本都在它们自己的写和读组中设置(而不是在相同的主机组中，因为我们更希望手动指定哪个是主服务器，哪些是副本)。

问题:我们如何使数据访问我们的副本而不仅仅是主程序？

图：

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 = "^SELECT\sname,\stype\sfrom\ssystem\sWHERE\sstatus.*", cache_ttl = 3600000, flagOUT = 2, apply = 1 },
  { rule_id = 3, active = 1, match_digest = "SELECT\sDISTINCT\sregistry.name\sAS\sname,\sregistry.filename\sAS\sfilename\sFROM\sregistry.*", cache_ttl = 3600000, flagOUT = 2, apply = 1 },
  { rule_id = 4, active = 1, match_digest = "SELECT\s.*\sFROM\smenu_router\sWHERE\spath\sIN.*", cache_ttl = 3600000, flagOUT = 2, apply = 1 },
  { rule_id = 5, active = 1, match_digest = "SELECT\sbase.vid\sAS\svid,\sbase.name\sAS\sname,\sbase.machine_name\sAS\smachine_name,\sbase.description\sAS\sdescription,\sbase.hierarchy\sAS\shierarchy,\sbase.module\sAS\smodule,\sbase.weight\sAS\sweight\sFROM\staxonomy_vocabulary\sbase\sWHERE", cache_ttl = 300000, flagOUT = 2, apply = 1 },
  { rule_id = 6, active = 1, match_digest = "^SELECT\snid,\sdata\sFROM\scompany_sync_nodes", flagOUT = 2, apply = 1 },
  { rule_id = 7, active = 1, match_digest = "^SELECT\sclient_name_display\sFROM\siss_dispatch_clients", flagOUT = 2, apply = 1 },
  { rule_id = 8, active = 1, match_digest = "SELECT\sf.fid\sAS\sfid\sFROM\sfile_managed\sf\sWHERE\s(f.status\s=\s.*)\sAND\s(f.uri\sLIKE\s.*\sESCAPE\s.*)\sAND\s(f.uri\sNOT\sLIKE\s.*\sESCAPE\s.*)\sAND\s(f.filemime\sNOT\sLIKE\s.*\sESCAPE\s.*\sORDER\sBY\sf.fid\sDESC\sLIMIT\s.*\sOFFSET\s.*", 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
}

Answer 1

答案是，如果最后一个mysql_query_rules_fast_routing规则的应用为1，则不会应用mysql_query_rules规则。您需要将其保留为apply=0，然后应用快速查询规则。