Have I made a mistake with my database setup?
Posted on 2012-08-16
I've got a few previous questions about expanding our web infrastructure. Finally decided to move our database(s - there are several even tho we call it "our database") from our local server to a hosted server, set up that stuff as a master, and then set up a new VPS I rented as a "slave" in mysql as well as our local server with our webpage as an additional "slave".
So the idea was that the slaves are used for simple searches and lookups, while the master is used for any inserts or updates. these then propagate down to the slaves at their leasure.
I was hoping this would spread the load and speed up the simple stuff that the web users do as well as form a expandable structure that we will be able to grow with for the next few years.
So I made dump of all our databases and copied them to the new hosted server and set it up with the key databases that need to be replicated mentioned in the my.cnf file. there were lots of steps, but basically, I dumped those databases from the hosted server, copied them to my test vps, imported them, set up a replication user, did some "start slave" - made sure the master was ID = 1 and slave ID = 2.
But when I view the webpages on the slave, (connecting to localhost) it's still allowing me to perform "write" functions to the database (like register new accounts). I was under the impression that in this kind of structure changes only propagate down from the master so the slaves are read-only and don't send changes back up to the master. so I expected not to be able to write to the database.
How can I know if what I've done is correct in setting up the relationship (I can't really have the webpage running on the master server to see if users I've created on the slave really exist)
And How can I see if it's really working without having to throw my real live local production server into the mix?
--At the moment all the PHP code still reads off "localhost" I got and tested a script to change the code to read and write off the master when the time is right. we'll then manually change everything we know is safe to read off localhost as we come across it in the weeks following the changeover.--
Is my plan sounding plausible, am I going down the right road?