The easiest way to see the differences between two schemas on a non-Windows machine is to run:
mysqldump -h server1 --no-data --all-databases >
file1.sql
mysqldump -h server2 --no-data --all-databases >
file2.sql
diff file1.sql file2.sql
However, this will show also trivial differences, such as the
value of AUTO_INCREMENT. It also does not give you a
way to patch one schema to be like another.
We frequently are asked to “do a schema diff and create a script that will ‘patch’ one server.” Usually this is done to take a development or test schema and move it to production for a release.
We like to use the best tool for the job, and while diff is good, I like to use MySQL workbench. The OSS (Community) edition provides all the functionality we need for creating a schema diff and patch. MySQL workbench does both reverse engineering and “forward engineer” alter script for free in the OSS/Community version.
Step by step, here’s how to do it:
- First, create a script with the database schema that you want
to be the final result. For example, if you want to take an
existing production schema and change it to be like the
development schema, then your “final result” is the development
schema:
mysqldump --no-data -h dev_server --all-databases >
final.sql
You may want to specify which databases instead of
--all-databases.
Open MySQL Workbench.
Right-click on the “mydb” schema and delete it.
File->Import->Reverse Engineer MySQL Create script and
choose the final.sql script
Then, “forward engineer” using the current schema. Export the
schema you want to change (in our example, the production
schema):
mysqldump --no-data -h prod_server --all-databases >
current.sql
File->Export->Forward Engineer SQL ALTER Script and choose
the currentschema.sql script
Note that depending on your setup, there may be some “noise” — if fields are in a different order, or if indexes have different names, they will have patches to make the schemas exactly the same. Index names usually do not matter, and for some organizations field order matters, for others it does not. In general it is good to have the same field orders, but if it is a lot of work to change that (for example, ALTERing a table with 2 million rows) then it may not be necessary. I strongly advise not to have an environment where order matters.