Need Insight - What is the best practice for syncing up a production database that will be used on a live site? CakePHP, Mysql

Question

I have a site set up using CakePHP and MySQL and I want to work on a test database without disrupting my live site in case something goes wrong.

I have another busy site, but my test site runs off the live database which can be occasionally nerve wracking.

What do I do if I change a table name in the test db and I want it changed in the live database? Or if I remove a record from the test database. Is there a way to diff the changes? How do I even merge those changes?

How does this interfere with live user edits and things of that nature?

Hopefully some of you working devs can share some insight!

Answer 1

As I said in the comment, there are too many questions at once here IMO.

However, as for this question:

What do I do if I change a table name in the test db and I want it changed in the live database

this is comparably easy to do manually: Any mySQL client will show you the exact SQL query that was made to change a table or a record. You would keep track of every change, and build "changesets" from those queries, i.e. just series of queries that you then run on your live database, for example after putting the site into maintenance mode for a moment.

This is enough in many, many small to mid-size use cases.

To get answers on more sophisticated topics like database replication, clustering and such, I think you will need to refine your answer.