Free and easy schema diff and patch

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:

  1. 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:


    2. mysqldump --no-data -h dev_server --all-databases > final.sql
    You may want to specify which databases instead of --all-databases.
  2. Open MySQL Workbench.

  3. Right-click on the “mydb” schema and delete it.

  4. File->Import->Reverse Engineer MySQL Create script and choose the final.sql script

  5. Then, “forward engineer” using the current schema. Export the schema you want to change (in our example, the production schema):

    6. mysqldump --no-data -h prod_server --all-databases > current.sql
  6. File->Export->Forward Engineer SQL ALTER Script and choose the currentschema.sql script

  7. View the alter script and/or save to a file. You can also copy and paste the information.

  8. 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.

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:

  1. 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:


    2. mysqldump --no-data -h dev_server --all-databases > final.sql
    You may want to specify which databases instead of --all-databases.
  2. Open MySQL Workbench.

  3. Right-click on the “mydb” schema and delete it.

  4. File->Import->Reverse Engineer MySQL Create script and choose the final.sql script

  5. Then, “forward engineer” using the current schema. Export the schema you want to change (in our example, the production schema):

    6. mysqldump --no-data -h prod_server --all-databases > current.sql
  6. File->Export->Forward Engineer SQL ALTER Script and choose the currentschema.sql script

  7. View the alter script and/or save to a file. You can also copy and paste the information.

  8. 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.