How to distribute queries to MySQL across servers?

Question

MySQL

There is MySQL Master + Slave
Now about 1.5 - 2 thousand requests per second go to the database server. I want to scatter them on master and slave. What can be used for automatic distribution? It is desirable through 1 connection with a DB.

Answer 1

What means through one connection with a db?
I hope you have some kind of your own binding over mysqli functions?
You can make simple logic - if the query contains the words insert/delete/update/replace/... - go to the master, otherwise to the slave(s). In scripts that implement transactions, we immediately go to master forcibly.
In principle, you can define this with handles at the beginning of each controller (controller function) - but it seems to me that the flexible version with two connections is generally better.
The only nuance that (hypothetically) can be - if you have asynchronous replication, there is a chance that the user, for example, writes a comment, refreshes the page - and does not see it, because the slave is lagging behind.
You can solve this in different ways, it seems to me that it is more correct - on the side of the frontend.

Answer 2

mysqlproxy
haproxy

Answer 3

Try MySQL Proxy

Answer 4

A lot of good things are written about haproxy, thanks, I'll take a closer look.

Answer 5

haproxy is not suitable if you are not going to modify the application (that is, inside it describe the logic of where to go with a select and where with an insert).
MysqlProxy will do. But it has problems with encodings other than utf8.