Compare the structure of two MySQL Databases

Question

I have two MySQL databases that are very similar to each other. How can I find out the differences in tables, and the differences in columns in each table?

• the databases are in different schema.
• It's only the structure I want to compare, not the data.

Answer 1

Using INFORMATION_SCHEMA.COLUMNS, here is my proposed query

SELECT B.* FROM
(
 SELECT DISTINCT table_name FROM
 (
 SELECT table_name,column_name,ordinal_position,data_type,column_type,COUNT(1) match_count
 FROM information_schema.columns WHERE table_schema IN ('db1','db2')
 GROUP BY table_name,column_name,ordinal_position,data_type,column_type
 HAVING COUNT(1) = 1
 ) AA
) A INNER JOIN
(
 SELECT table_schema,table_name,column_name,ordinal_position,data_type,column_type
 FROM information_schema.columns WHERE table_schema IN ('db1','db2')
) B;
USING (table_name)
ORDER BY B.table_name,B,table_schema;

The output will be each the columns differences. You will see differences by data type, column type, and/or column position. You should quickly see if a table only appears in one one database and not another.

• Walter Mitty: Nice. I once detected a difference between two databases with about 500 columns in them where one column was variously defined as Int or Float. This pinpointed an elusive problem that had been bugging the administrators for a long time.