Mysql Compare Two Database Tables and Fields

Question

• I have to compare two database tables and fields ( Not their DATA) .
• Say Live DB and Development DB. In live DB there are 200 tables and in development DB there are 218 Tables With some new column fields added in old tables too.
• During the final stage i have to update the Live DB by adding the new columns and tables taken from the development DB .
• I should not loss and data in the old DB.
• I tried many ways to do this but it is taking a very long time for me to finish. One of the tool i used is mysql workbench.

Is there any queries to perform this using information schema ?

To make the things easier i imported the development DB from development server and uploaded in the live db with different name . Now both the DB are in the same server but with diff name. E.g. sitedb , sitedevdb

Answer 1

Whenever you update the development DB with changes that will eventually be put live, you should write a .sql script that will repeat the changes on the live database.

Otherwise this operation is super-tricky.. you may have to manually figure out the added columns yourself, or you may be able to use each INFORMATION_SCHEMA.COLUMNS to compare the old tables.

For the new tables SHOW CREATE **table_name** is really useful.