'In Proxysql Insert,Update are not happening on Master

I have configured Proxysql with master slave. I have only one slave configured and on. Instead of happening update,insert on master, It is happening on slave server. My proxysql server and slave server are same.

The query rules I have given is :

INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 1, 0); 
INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*FOR UPDATE', 0, 1);

hostgroup id 0 is for update and 1 is for select.

some result for reference is: 

Admin>SELECT hostgroup_id,hostname,port,status,weight FROM runtime_mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 0            | 127.0.0.1 | 3306 | ONLINE | 1          |
| 1            | 127.0.0.1 | 3306 | ONLINE | 1          |
| 1            | 127.0.0.2 | 3306 | ONLINE | 1000       |
| 0            | 127.0.0.2 | 3306 | ONLINE | 1000       |
+--------------+---------------+------+--------+--------+

Help me so I can forward my update,insert query to master server which is 127.0.0.1 and select query to slave which is 127.0.0.2

proxysql


Solution 1:[1]

In your INSERT INTO mysql_query_rules you set the apply value to 0 on the first rule. You can probably set it to 1 to stop processing any further if there is a match.

It looks like your servers are duplicated?

You might also want to check the default_hostgroup value on your proxysql users.

Solution 2:[2]

It looks to me like your setup is incomplete and incorrect. I believe of the 4 lines you have in the mysql_servers, you should only have 2 or 3, depending on whether you want the master to ALSO handle selects. The 4th line needs to be dropped. That would make the slave part of the write group. The second line is optional for allowing queries on the master.

You also need to add rules to route queries into the mysql_query_rules table. That's where you define which hostgroup should get which type of query. See https://proxysql.com/documentation/ProxySQL-Configuration/ for more guidance.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Tyler Hains

Related Questions