How to transform a table schema to another one keeping data

Question

I have a web application that is installed for several customers' machinges. While updating the app I need to propagate the last version of tables' schema to production servers.

There are a lot of migration tools, but most of them require a lot of effort or are more complicated than what I need. I need a solution as simple as what described below to streamline the upgrading phase .

I need a tool that receive the tables' schema (generated by mysqldump --no-create-info) of development and production machines and generates the alter table queries needed to run on the production servers.

+------------------------+                                             
|                        +--------------+                              
|Development Machine     |              |                              
|create tables statements|     +--------v-------+                      
+------------------------+     |                |     +----------------+
                               | Automation Tool+----->Alter Statements|
                               |                |     +----------------+
                               +---------^------+                      
+------------------------+               |                             
|Production Machine      |               |                             
|create table statements +---------------+                             
+------------------------+

score 1 · Answer 1 · answered Jan 10 '15 at 10:32

My method would be to manually add the updated columns to the live user table using your preferred mysql access method (command line, phpmyadmin, SequelPro etc...) and then write an import script in a server side language to port across any necessary data from your development user table to the live table.

Also make sure to back up your live DB before you being this work.

If you wanted to make it more automated in the future, I would recommend looking at something like Liquibase (http://www.liquibase.org/) for version control of your database, and using something like Jenkins CI (http://jenkins-ci.org/) to handle your migration.

user3170450 · Answer 2 · 2015-02-14T13:01:32.263

You can use mysql workbench for this. You give the schema to it and give the credentials for the live database and it will compare and generate the alter statements.

You can go to Database --> Synchronize With Any Source and then UI is self explanatory to generate alter statements.

Go to this link to get a feel Database Synchronization and find Synchronize With Any Source.

We have a project where we generate SQL based on an Excel sheet (whenever something change) and always give that new SQL to mysql workbench to generate the alter statements by comparing to the productions database.

score 1 · Answer 3 · answered Mar 31 '15 at 12:34

I've tried Database Comparer v 6.2 Standalone Utility which is awesome. However it is not smart enough to detect column rename actions and simply drop and create a renamed column. The desired program, should ask user to choose between rename or drop/create actions.

score 0 · Answer 4 · edited May 23 '17 at 11:52

It sounds like you are looking for a tool similar to mysql-schema-diff which is part of the Ubuntu package (libmysql-diff-perl).

A similar tool is mysqldiff which is part of the MySQL Utilities. But, it just compares given objects, so you cannot compare all the objects in the database at once, and you need to know which tables were modified. Plus, it cannot work from database dumps.

There are some other diff tools listed here and here.

Some manual tweaking might be necessary anyway. For example, detecting that a column was renamed is not easy. But, if you want to keep the data, you need to use alter change instead of alter drop followed by alter add, and an automatic tool might not be able to recognize it.

How to transform a table schema to another one keeping data

4 Answers4