PDO SELECT from SLAVE and INSERT into MASTER

Question

is there any chance to set in PDO settings that SELECT's will be executed on SLAVE DB server and Insert & Update & DELETE will be executed on MASTER DB server, or I need to create PHP handler to do that?

Situation:

We have Master - Master replication for MySQL. We are going to add two new servers so it will be - Master/Slave - Master/Slave.

I want to create some handling for SELECT queries. I want execute SELECT queries on SLAVE instead of MASTER and all UPADTE&INSERT&DELETE queries will be executed on MASTER. Is this possible with some setting?

Thanks!

Answer 1

No, you can't configure PDO or any of PHP's database extensions to do this. That is simply because each PDO (or MySQLi, etc.) instance represents a single connection, to a single server.

So yes, you'll need a handler that is aware of multiple connections to do that. Some popular ORMs and other database-abstraction layers do provide such functionality.

Answer 2

I recommend not doing it even if you could. Replication is "asynchronous". That is, when you insert into the Master, there is no assurance that it will arrive at the Slave before you try to read it. Nor even any guarantee that it will arrive today!

If you user posts a comment on a blog, and then goes to a page that shows the comment, they will be annoyed if the comment does not show. They may assume that the comment was lost and then repost it. This causes you grief when users complain about double-posting.

This is called "critical read". This simple way to avoid the mess is to be careful about what you send to the Slaves -- namely nothing that would lead to "disappearing" posts.

There are various "proxy" packages that allow from the read-write split you describe; some try to avoid the "critical read", but I don't trust them.

A Galera Cluster (see PXC, MariaDB), does synchronous reads, so it can avoid the critical read problem. (There is, however, a setting you need to apply.)