I'm using MySQL 5.6. I have a production database and a development database.

As I'm developing the application, I sometimes need to add new columns/tables to the database. Is it possible, when I'm ready to update the production database, to take the dev. schema and "lay it over" the production schema, but let the production server keep its data?

Currently, I'm just using mysqldump and dumping out the whole database, and then copying it over the top of the other. It's working at the moment because the app isn't entirely in production mode, yet, but it's not gonna work forever.

  • Why the whole database and not just those tables that have changed? – Vérace Sep 11 '14 at 23:00
  • Well, that's absolutely a possibility as well. Mainly, I was wondering if there were a tool or a method to basically diff the two schemas, and them merge their changes while not altering the data. – Nathan Lutterman Sep 11 '14 at 23:01
  • Look into the two tools mentioned here - both get favourable reviews by "big hitters" on this list. – Vérace Sep 11 '14 at 23:04

To do this in any automated manner, you need to change your development model. Instead of just keeping the dev database as the current state and using a dump of its structure to build new databases, you should instead create scripts that transform one database version to the next - adding/dropping/altering objects as needed, and transforming data as required by those changes (new columns may need initial values, moving data to a new structure before dropping and old one, etc.). These scripts should be kept in good source control of course.

This way you can take your production database (at, say, version 2.5) and to get it up to the current development release (say, 2.8) apply the upgrade scripts for 2.5->2.6, 2.6->2.7, 2.7->2.8. Of course you can test this process in a test environment first as it should be repeatable (reset test environment to match current production, upgrade, test the application, fix upgrade scripts if something fails, repeat until everything is OK).

At some point this process will become inefficient for creating brand new databases for other instances of the application (i.e. building from the very first version onwards may become unwieldy after time) so you'll want to refactor the process slightly, picking a new starting point and building from there each time.

There are tools to help with the more manual process that you describe, but you will be much better off in the long run not arranging your release process around less automatable actions for managing schema changes. Setting up the process may seem like hassle, but it is a lot easier to do at this stage than after you have real production environments to worry about.

| improve this answer | |
  • Absolutely. There is simply no safe way to "diff" the schemata of two servers and craft the DDL to make one identical to the other, if there is data in the tables. I designed my own tool for this, and it ends up "commenting out" many of the statements it generates, since there isn't a conclusive way to determine that they are safe. Column drop/add or column rename? Indistinguishable. – Michael - sqlbot Sep 12 '14 at 12:25

In order to synchronize(check database, object and data) the dev database with the production env you can take a look into the MySQL Utilities on these tools:

Currently I'm working frequently with the first one obtaining good results and saving time... keep in mind that you have to check the result before apply them to the destination database(there are some little bugs in some scenario with unique keys on multiple columns and other)

You can start using these tools with two test database in order to become familiar.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.