MySQL query distribution

Question

MySQL

Hello!
The situation is this. There are two servers with MySQL, replication is configured, one server is the master, the other is the slave, respectively. How can I make requests to change the database (INSERT, UPDATE, DELETE, etc.) go to the master, and read requests (SELECT) to the slave. Maybe there is some setting in mysql or a third party solution?

The project itself is made in PHP and we did not think over this issue in advance. Queries are called through regular mysql_query.
Thanks in advance!

Answer 1

The standard solution is to have stupidly 2 different connections.
One goes to the master, the other to the slave. Change to first, read from second.
This is the most natural that can be.
well, or www.linuxvirtualserver.org/ , but that's a bit about something else.

Answer 2

There is such a thing as MySQL proxy, which can transparently for the client scatter connections to the master and slave (naturally with restrictions) read here - forge.mysql.com/wiki/MySQL_Proxy_RW_Splitting
sorry that it's not production ready yet.
for good - rewrite all calls to the database through your class (in the simplest case, just search-replace according to the source code) and analyze in it - reading or writing is in progress.

Answer 3

About hatzner and servers. Consider the option to take www.hetzner.de/hosting/produkte_rootserver/ex5 , of course, splurge on the setup, but 24GB of memory instead of 8GB decides in the case of a muscle rather heavily due to the cache, especially if you have a lot of reading.
Moreover, sometimes it makes sense to hang the slave on the server, where all the tables are on the ram disk. For a slave, frames are not a hindrance, but for speed, reading from the RAM is not for you reading from a household screw, even at 7200.

Answer 4

If you are looking for HA, then there are several options. And there is definitely no good among them - you need to look at the circumstances. Again, a lot depends on what kind of reliability you need.
If you need very very reliable and the slightest loss of data is unacceptable, then you will have to pay with performance in any case. Normal, asynchronous replication will not suit you, since there will be no 100% certainty that the data has managed to go to the slave.
You can use DRDB + Heartbeat
Reliable, but a write penalty. And the network between servers should be good.
For FreeBSD, you can build something similar using replication at the ZFS level.
In 5.5, replication appeared with confirmation of data writing, at least to one of the slaves.
If short-term downtime and a small loss of data are acceptable (attention, I do not suggest spitting on user data! But banking and finance are one thing, losing two or three votes for an avatar is another ...), then asynchronous replication is quite a good solution.
However, something (for example, nagios) should monitor SHOW SLAVE STATUS in order to notice in time if replication has failed.
You can switch to a slave either at the level of your application's config. Or using a virtual IP , which, in the event of the death of the master, is quickly transferred to a live slave.
But using a spare database for balancing queries is not always a good idea. If the master falls, then requests that were previously processed by two servers will have to be processed by one slave. The code should be ready, cut off a number of functions in this situation.
This is all my IMHO...

Answer 5

2korvindest, the issue of replication is not very familiar to me, but I know that many people work on this principle. Write to master, read from slave. It seems to me that the process of replication from server to server is instantaneous, if this is not some kind of heavy request, of course.
The thing is, I don't see any other decent solution for scaling mysql. If someone tells me something better, I will only be happy.

Answer 6

and tell us the characteristics of the server, since you do not have enough one box

Answer 7

At the initial stage, there was no point in spending extra money on an unreasonably powerful server. By the way, the first server began to show a serious load only six months later. At peak moments, Load Average reached 7.0 and this was with two cores, although there were no brakes on the site itself.