How to code PHP MySQL class for master and slave s

Question

I have never used a master/slave setup for my mysql databases so please forgive if I make no sense here.

I am curious, let's say I want to have a master DB and 3 slave DB's. Would I need to write my database classes to connect and add/update/delete entries to the master DB or is this automated somehow?

Also for my SELECT queries, would I need to code it to randomly select a random DB server?

Answer 1

you sound like you are wanting to improve performance/balance load

yes you need to do any destructive changes to the master database. the slaves can only be used for readonly. you would also need to be careful that you don't write to the master and read from the slave instantaneously, otherwise the data may not have been replicated to the slave yet. so any instantaneous reads would still need to come from the master.

i wouldn't suggest just randomly selecting a slave. you could do this by geographical region if they are spread out, or if you are running in a cluster you can use a proxy to do the load balancing for you..

here is some more info that may help

http://agiletesting.blogspot.com/2009/04/mysql-load-balancing-and-read-write.html

Answer 2

You should consider using mysqlnd_ms - PHP's replication and load balancing plugin.

I think this is better solution, especially for a production environment, since it's native to PHP and MySQL Proxy is still in Alpha release.

Useful links:

• https://blog.engineyard.com/2014/easy-read-write-splitting-php-mysqlnd
• http://pecl.php.net/package/mysqlnd_ms

Answer 3

What you want to use (and research) is MySQL Replication. This is handled completely independent of your code. You work with the database the same as if there were 1 or 100 servers.

Answer 4

The master/slave set up should be handled automatically by the MySQL server, so you should not need any special code for this configuration.