Create the alter table syntaxes between your dev and staging/production databases

« Back to overview

Create ALTER TABLE syntaxes between two MySQL databases

If you have modifies your mysql database schema during development, you might have lost track of the ALTER TABLE statements to create your migration to staging or production.

Mysql provides a mysqldbcompare tool to compare the two databases. Below is an example. For this to work, you have to have two database servers (one to compare the other to) - you can run these in two different virtual box machines for example.

To compare the database schemas on the two database servers:

(note that this NOT do CREATE TABLE statements)

mysqldbcompare --server1=username:password@dbhost1 --server2=username:password@dbhost1  --difftype=sql --run-all-tests --skip-data-check  --skip-row-count --skip-table-options --compact DBNAME

Installing mysqldbcompare

mysqldbcompare is part of the


packages. On CentOs, you might want to take care you have python 3 installed, and also you might have to take additional steps if you have the wrong


installed. This is what worked for us in one variant or the other:

yum remove mysql-connector-python
yum install mysql-connector-python.noarch
yum install mysql-utilities