Keeping a local MySQL DB in sync with a live DB (MySQL Replication?)

Question

Hi All,

I have a large production database at the moment (7GB+), much of the data I require to test my development branches needs to be up to date.

I would like to create a local DB so I can implement a CI server and have the development DB separate to the production DB, however with a DB of this size how do I ensure it's always in sync?

I have thought about replication, but what if the connection is down to my local server or I have changed the DB structure how will this effect the replication?

Thanks,

Gavin

Answer 1

Consider dumping the whole database to text files in form of sql statements (schema and actual data) and then putting them under some version control system that will track changes.

Answer 2

how do you plan to keep a development database in sync with production, considering that you may make table changes in development long before they get into production?

yeah that's part of the problem, I don't know if its even possible to do that given the schema could change quite a bit..

It will be very difficult to have any automatic synchronization that will not wipe out your or cause problems with your development changes.

So, I recommend that you restore a production backup manually and only when you need to. Set up a job to do the work, and just run it as necessary (don't put it on a schedule). Develop as necessary using the current data as long as you can. When you need a refresh, make sure you have implemented/saved to files all of your development changes, and then restore a backup to development. If you have make "releases" to production, then restore after that. You'll need to determine what works best. However, just make sure you don't lose any development changes when restoring!

If you need to have a local version that is up to date for support and debugging production problems, set up a "Support" database and use replication. Don't do develop work there, have a dedicated development database.