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
mysql-utilities
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
mysql-connector-python
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