We are using MySQL clusters with proxySQL with the following set up:
- Server 1 is the main server hosting the main dB, located in Europe
- Server 2 is a server in Asia used to serve local users, this server contains a read only version of the DB that is updated by server 1 when changes are made.
Here is the issue: A user on server 2 triggers an SQL UPDATE, UPDATE customers SET name= 'New Name' WHERE id = 123. (this update is sent to server 1 which then update server 2)
In the same code block if we select the record (SELECT name FROM customers WHERE id =123), this query is sent to the local server and the result will be "Old Name" unless we pause the code for 1 second (to test).
One solution we thought about is to change the connection (PHP) and go directly to server 1 for the remainer of the code block but this defeats the purpose a little and is far from ideal (quiet large code base to update).
Are there solutions involving proxySQL maybe?
To prevent query route to slave with high replication lag you can set threshold maximum replication lag in table mysql_server column max_replication_lag. ref : max_replication_lag
max_replication_lag – if greater than 0, ProxySQL will regularly monitor replication lag and if it goes beyond the configured threshold it will temporary shun the host until replication catches up.
you can also create query rules to split query read and write. ref : ProxySQL Split Read/Write
Important: proxysql-gtid-causal-reads this might mostly solve your problem, if you are using Mysql version 5.7.5 or newer.