If you have more then a single MySQL database for your production environment, e.g. a development and test environment, or a MySQL replication topology, ensuring your schema’s are in sync can be task that requires some time if not managed correctly.
There is a tool I do use for MySQL environments called Schema Sync – a MySQL Schema Versioning and Migration Utility. There are many reasons why schema’s get out of sync. Developers may not ensure their changes are reflected in any software to be deployed, and when not tested you could end up with broken functionality. A DBA might try some different index strategies on a slave, but not the master, and never implement or revert.
While some people want the quick and hidden just sync version akin to Rails, I really like this product as it produces proper patch and revert SQL scripts. As a DBA I really want to know what’s going to be applied to my schema, I want to be able to review it before it’s just magically applied. The revert process is also ideal, in time critical situations when you may need to rollback a change, either it’s taking to long to deploy.
This is definitely a tool to use over time, especially if you have multiple developers, DBA’s and more then one MySQL environment that should be the same.
It’s a cool program. Especially for the ones who love the command line as much as I do.
However, at the moment it doesn’t deal with partitioning yet. For that, the only one that I found that works across multiple operating systems and supports partitions is MySQL Workbench.
Giuseppe
This may be very interesting, I’ve been looking for something which does this properly for some time.
We try hard (really hard) to keep the schema in sync, writing migrations and rollbacks for every change and keeping our many dev and testing environments in sync with our (few) production environments.
In practice this doesn’t always work (usually because of people failing to follow the process, rather than bugs in the process), and they eventually drift out of sync. Something which can easily bring it back could simplify things significantly.
As far as renames are concerned, there should be some way I can attach metadata to a column/table to say “This used to be called:x” and it could pick it up and rename it. Maybe I can enhance it.
Hi there!
Some months ago I searched tool for versioning MySQL schema. I found many useful tools, like Doctrine migration, RoR migration, some tools writen in Java and Python.
But no one of them was satisfied my requirements.
My requirements:
1. No requirements , exclude PHP and MySQL
2. No schema configuration files, like schema.yml in Doctrine
3. Able to read current schema from connection and create new migration script, than represent identical schema in other installations of application.
I started to write my migration tool, and today I have beta version.
Please, try it, if you have an interest in this topic.
Please send me future requests and bugreports.
Source code: bitbucket.org/idler/mmp/src
Overview in English: bitbucket.org/idler/mmp/wiki/Home
Overview in Russian: http://www.antonoff.info/development/mysql-migration-with-php-project