We help IT Professionals succeed at work.
Get Started

Have I made a mistake with my database setup?

StevenHook asked
Last Modified: 2013-03-10
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?

Watch Question
This problem has been solved!
Unlock 7 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE