How to upgrade MySQL database schema?

Question

How to upgrade a production DB schema from a dev DB schema automatically, via command line? The dev version has changes to the schema that need to be made in the production schema, but I cannot lose the data in production.

Answer 1

Schema Migrations

Most modern projects use a tool to track each individual change to the database, and associate some version number with the change. The database must also have some table to store its current version. That way the tool can query the current version and figure out which (if any) changes to apply.

There are several free tools to do this, like:

• Liquibase
• Flyway
• Rails Migrations
• Doctrine Migrations
• SQLAlchemy Migrate

All of these require that you write meticulous code files for each change as you develop. It would be hard to reverse-engineer a project if you haven't been following the process of creating schema change code all along.

There are tools like mysqldbcompare that can help you generate the minimal ALTER TABLE statements to upgrade your production database.

There is also a newer free tool called Shift (I work with the engineer who created it), which helps to automate the process of upgrading your database. It even provides a nice web interface for entering your schema changes, running them as online changes, and monitoring their progress. But it requires quite a lot of experience to use this tool, I wouldn't recommend it for a beginner.

Answer 2

You can also use MySQL WorkBench: https://dev.mysql.com/downloads/workbench/ if you want an easy GUI, and cross OS compatible ;)