CODEWITHSUNDEEP
mysqldatabase-comparison
shantanuo
shantanuo

Reputation: 32346

Compare 2 database

I have 2 identical databases. abc15 and abc18. But one of the database has one extra table and I need to find that. I thought the following query should return it, but is it not showing the record that I expect.

select * from information_schema.tables as a
    left join information_schema.tables as b
        on a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME
    where a.TABLE_SCHEMA = 'abc15' AND b.TABLE_SCHEMA='abc18' and
        b.TABLE_NAME IS NULL

Upvotes: 2

Views: 150

Answers (2)

amphetamachine
amphetamachine

Reputation: 30613

$ mysqldumpslow --database abc15 >/tmp/a
$ mysqldumpslow --database abc18 >/tmp/b
$ diff /tmp/a /tmp/b

Upvotes: 1

LesterDove
LesterDove

Reputation: 3044

What if database b has the extra table? Try a full outer join and an extra constraint at the end (OR a.TABLE_NAME IS NULL)

Upvotes: 3

Related Questions