Synchronizing databases between a local and remote development environment

Question

I've being working on a local project recently, and have been synchronising it with an online server.

The problem is, now it involves database functionality. What this means is that whenever I make a database change locally, I have to access the cPanel of the website, enter PHPMyAdmin, delete the whole database and copy in the SQL query I exported from my local version.

Isn't there an easier way to work with MySQL databases, using the local-remote design logic?

Answer 1

This is a one of the more challenging aspects of development as environments change frequently when you have more than one developer and potentially multiple branches and/or servers. However, it's hard to say how it might effect you as we know nothing of the exact circumstances.

One element of your current operation might be to remove the need to login to various interfaces and use scripts to achieve what you need - less manual; more automation... At its most basic, you could create a PHP script that:

• Backed up your production DB and saved it as a unique backup
• Applies your changes (or, if needed, an entire re-build)
• Allows you to revert to the backup if things go wrong

General options:

Updates not re-builds

There's no need to delete an entire DB and re-build in any environment unless all of the data changes (and it needs to). At its most basic, structural/schema changes are needed more than complete re-builds.

Replication

It's possible to synchronise MySQL DBs using master-master or master-slave replication, but sometimes this is overkill in some environments and may break functionality unless it's managed very carefully. If you made changes locally (in your development environment) they may break your remote site functionality...

Use framework tools

If you're using modern frameworks, many of them come with tools that facilitate DB updates and the ability to update (and potentially rollback), but often they aren't perfect as you might need to update specific data or tables that can't easily be rolled back.

My current setup

¬ multiple local developers
  ¬ multiple dev branches 
    ¬ development
      ¬ staging server 
          live

In our case, we use a custom-built script (and linked SQL files) that allow each developer to apply changes and promote changes to branches by running the following from the command-line:

php migrate.php server/branch update 

Or:

php migrate.php server/branch rollback

This could be simplified for a basic setup, but I can't see how without knowing more. By default, it would be worth asking this very question on dba.stackexchange.com too as you'll get exacting DB-focused responses.