Question :
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.
Answer :
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.
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.