Getting a 2nd Remote MySQL Query : Replication Help

Databases
1

My remote mysql server is holding up the show, causing me to need a second one.

I was told the setup is going to be like this:

http://dev.mysql.com/doc/refman/5.5/en/replication.html

The idea behind the replication is:

“1st machine can do the writing and reading while the 2nd machine does purely reading.” OR “1st machine can be pure writing and 2nd machine reading.”

With this setup I am going to need a separate mysql connection resource depending on whether I’m running a SELECT or anything else.

Is there an easy way to do this, or do I just need to update ALL my queries across my site (think 100+ PHP pages with about 6 queries each) one by one?

Any help/feedback GREATLY appreciated.

Cheers!
Ryan

2

You will need a master - slave replication for MySQL (master = write and replicate it to slave, slave = read) and unfortunately there’s no way of detecting read queries purely in MySQL. You will need to rewrite your PHP application and use different connection for read queries and different settings for write.