why proxysql don't router to slave node?
43 views
Skip to first unread message
gaoqu...@gmail.com
May 15, 2017, 4:04:03 AM5/15/17
to proxysql
from message ,we known rule_id=3 has been used,but haven't router to slave node,why happend this?
mysql_query_rules:
insert into mysql_query_rules (rule_id,destination_hostgroup,active,match_digest,apply) values(1,600,1,'^select\s.*\sfor update',1);
insert into mysql_query_rules (rule_id,destination_hostgroup,active,match_digest,apply) values(2,600,1,'\/\*\smaster\s\*\/',1);
insert into mysql_query_rules (rule_id,destination_hostgroup,active,match_digest,apply) values(3,601,1,'^select',1);
mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+-------------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+-------------+
| 600 | 601 | replication |
+------------------+------------------+-------------+
mysql> select * from runtime_mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 600 | 10.110.18.90 | 3336 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 601 | 10.110.18.94 | 3336 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
mysql> select * from runtime_mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | mirror_flagOUT | mirror_hostgroup | error_msg | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------+---------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^select\s.*\sfor update | NULL | 0 | NULL | NULL | 600 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | \/\*\smaster\s\*\/ | NULL | 0 | NULL | NULL | 600 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL |
| 3 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^select | NULL | 0 | NULL | NULL | 601 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------+---------------+----------------------+---------+-----------------+------------
mysql> select * from runtime_mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| sysbench | *5952279FDFA4F00A8428ECA58DAE5D4091BE6F6E | 1 | 0 | 600 | sysbench | 0 | 1 | 0 | 0 | 1 | 10000 |
| sysbench | *5952279FDFA4F00A8428ECA58DAE5D4091BE6F6E | 1 | 0 | 600 | sysbench | 0 | 1 | 0 | 1 | 0 | 10000 |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from stats.stats_mysql_query_rules;
+---------+---------+
| rule_id | hits |
+---------+---------+
| 1 | 0 |
| 2 | 0 |
| 3 | 5583121 |
+---------+---------+
3 rows in set (0.00 sec)
mysql> select * from stats.stats_mysql_connection_pool;
+-----------+--------------+----------+--------+----------+----------+--------+---------+----------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+--------------+----------+--------+----------+----------+--------+---------+----------+-----------------+-----------------+------------+
| 600 | 10.110.18.90 | 3336 | ONLINE | 495 | 5 | 3033 | 0 | 86459047 | 4627606133 | 166205011552 | 22391 |
| 601 | 10.110.18.94 | 3336 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 154 |
+-----------+--------------+----------+--------+----------+----------+--------+---------+----------+-----------------+-----------------+------------+
2 rows in set (0.00 sec)
gaoqu...@gmail.com
May 15, 2017, 6:55:39 AM5/15/17
to proxysql
I found the reason,because --oltp-skip-trx=off,if turn it on,command should with --mysql-ignore-errors=1062.
在 2017年5月15日星期一 UTC+8下午4:04:03，gaoqu...@gmail.com写道：
在 2017年5月15日星期一 UTC+8下午4:04:03，gaoqu...@gmail.com写道：
Reply all
Reply to author
Forward
0 new messages