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.