0

I know how to setup a master->slave replication but I am a bit confused on how I would do read/write splitting between the two MySQL servers with a PHP application.

When I have MySQL replication working and PHP connects to the master, does it read and write from just the master only or both master and slave? If not, how would I achieve this (via PHP/MySQL) or some other means?

I had always thought that MySQL replication had some built-in functionality that reads data from the slave for a bit of load-balancing to occur but now I think MySQL replication is just to copy data over to a slave for fail-over purposes only. I would like to achieve load-balancing but I still do not know exactly how MySQL replication works as far as reading data from slaves and writing data to master.

user3186337
  • 85
  • 1
  • 3
  • 10

1 Answers1

0

The short answer is no.

The long answer is - MySQL replication really does what the name suggests. It replicates data (replaying log on another machine). As opposed to, for example, Elasticsearch it does not care about reading.

The replication support would have to be implemented in driver. And since PHP MySQL/MySQLi/PDO driver does not (as far as i know) have any replication support, there are two options left.

Either use proxy as suggested by @MichaelHampton in comments, or implement the split yourself. It is not that difficult if the only thing you're concerned about is read/write splitting. Handling failover, and or watching replication lag, to stop reading from delayed slave might get a bit more tricky, but still no rocket science.

Fox
  • 3,977
  • 18
  • 23