How to use mysql to migrate database changes from local databases?

Question

I am working on a project with a few people and we have got the point where we all have different local versions of our website database and wish to get these changes to the server, after searching around I found something I liked but have a few questions on my approach to keeping our local databases in sync...

So, as we are all using phpMyAdmin, when we want to push changes we would export our local databases with phpMyAdmin and check our .sql files from phpMyAdmin into our git repo under a folder called schema... I already have some git web hooks setup, so I was basically thinking of running the command before inside of my deploy script as whenever we push changes to our dev branch or master on git hub a deploy script runs on the server...

Now, the idea is it would use the exported .sql file from the schema folder and import the new database schema, is this approach ok? What would happen if a column was removed or a length was changed in the structure of the database, would this cause errors? I followed this way because I did not want anything too complicated...

mysql -u <username> -p<PlainPassword> <databasename> < <filename.sql>

or is there a better way to do this? My tech stack is LAMP and I am using a local LAMP server to develop.

Answer 1

Database Migration tools

There are tools to help you manage the process of updating the structure of your database as your app evolves. ”Database migration" tools seems to be the emerging technical term for this, though imprecise/ambiguous.

Examples include Flyway (my favorite) and Liquibase. By the way, Flyway is Java-based but can be used by non-Java programmers via command-line utilities that wrap the Java classes.

The idea is that you must be disciplined in making no ad-hoc changes at all. Every change to the database structure must be written in a script to be formally managed by the migration tool.

You can run the tool wherever makes sense to you. If you have a single monolithic app, you could run the migration tool every time the app launches. Or you could call the tool from the command-line, or from a hook in your build tool or deployment tool.

The tool automatically detects which scripts have been applied already and which need to be applied. So you are accumulating scripts over time, never deleting them. Great for testing too, as you can recreate the database and take it to any point in the history of your project.

The scripts may be simply SQL code, as is the case with Flyway, plus optional Java classes you can write for complicated cases beyond the capability of SQL. Liquibase uses XML-based scripts.

These tools are best used from the start of a project but you can start with a 'baseline'. Personally, I suggest rebuilding your database from scratch using the tool to recreate the database entirely from your new organized scripts.

Consistent use of a database migration tool eliminates your questions. For example, ”What would happen if a column was removed or a length was changed in the structure of the database, would this cause errors?“, your migration scripts would alter the table to drop or modify the column. You know the column exists because you know the migration tool ran earlier scripts that already added the column.