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

Arth · Accepted Answer · 2014-08-05 10:05:34Z

2

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.

answered Aug 5, 2014 at 10:05

Arth

13.1k5 gold badges43 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raaga Over a year ago

I am trying to do this with PHP coding.

Arth Over a year ago

@Raaga, unless you are building some mad DB versioning system, this is hopefully gonna be a one-off. I'd probably end up using a mixture of SQL snippets, manual investigation and in extreme cases possibly PHP to fetch the information I need to build a script. Much easier to build the change script as you go along however.

Collectives™ on Stack Overflow

Mysql Compare Two Database Tables and Fields

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related