0

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

Julian
  • 886
  • 10
  • 21
Gopal
  • 1
  • 3

2 Answers2

0

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.

bounav
  • 4,886
  • 4
  • 28
  • 33
  • In the `proxysql` documentation they always seem to specify the primary key when they insert servers or rules. This makes it easier to edit or delete rules or server at a later point. – bounav Feb 26 '20 at 17:09
0

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.

Tyler Hains
  • 65
  • 1
  • 2