Compare structures of two databases?

For MySQL database you can compare view and tables (column name and column type) using this query:

SET @firstDatabaseName = '[first database name]';SET @secondDatabaseName = '[second database name]';SELECT * FROM    (SELECT      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType    FROM information_schema.columns cl,  information_schema.TABLES ss    WHERE      cl.TABLE_NAME = ss.TABLE_NAME AND      cl.TABLE_SCHEMA = @firstDatabaseName AND      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')    ORDER BY      cl.table_name ) AS t1LEFT JOIN                       (SELECT      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType    FROM information_schema.columns cl,  information_schema.TABLES ss    WHERE      cl.TABLE_NAME = ss.TABLE_NAME AND      cl.TABLE_SCHEMA = @secondDatabaseName AND      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')    ORDER BY      cl.table_name ) AS t2 ON t1.tableRowType = t2.tableRowTypeWHERE   t2.tableRowType IS NULL        UNION SELECT * FROM    (SELECT      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType    FROM information_schema.columns cl,  information_schema.TABLES ss    WHERE      cl.TABLE_NAME = ss.TABLE_NAME AND      cl.TABLE_SCHEMA = @firstDatabaseName AND      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')    ORDER BY      cl.table_name ) AS t1RIGHT JOIN                       (SELECT      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType    FROM information_schema.columns cl,  information_schema.TABLES ss    WHERE      cl.TABLE_NAME = ss.TABLE_NAME AND      cl.TABLE_SCHEMA = @secondDatabaseName AND      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')    ORDER BY      cl.table_name ) AS t2 ON t1.tableRowType = t2.tableRowTypeWHERE   t1.tableRowType IS NULL;

If you prefer using tool with UI you can also use this scripthttps://github.com/dlevsha/compalexwhich can compare tables, views, keys etc.

Compalex is a lightweight script to compare two database schemas. It supports MySQL, MS SQL Server and PostgreSQL.

Screenshot (compare tables)

You can use the command line:

mysqldump --skip-comments --skip-extended-insert -d --no-data -u root -p dbName1>file1.sqlmysqldump --skip-comments --skip-extended-insert -d --no-data -u root -p dbName2>file2.sqldiff file1.sql file2.sql

Red-Gate SQL Compare is a great tool that will do this for you. I have used this for years with great success. It has saved me thousands of hours of work.

They also have a tool that will compare data as well. The product I mentioned above compares schema.

www-red-gate.com