CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110093

Finding the difference in two MySQL tables that should be identical

I have two MySQL databases: one for testing, and one for production. They are on different servers. These tables should be identical, however one table has one more row than the other. How would I find this row?

Upvotes: 0

Views: 396

Answers (5)

Derek Organ
Derek Organ

Reputation: 8473

One way is export each table to an sql file and use a diff program to find the differences.

Pretty Diff (web) - http://prettydiff.com/

WinMerge (win) - http://winmerge.org/

FileMerge (OS X) - http://en.wikipedia.org/wiki/Apple_Developer_Tools#FileMerge

Upvotes: 3

Marco
Marco

Reputation: 57573

You could try:

  1. Export second server table in a SQL file (using MySqlDump)
  2. Edit file changing table name
  3. Import SQL file into first server

Then run

SELECT t1.* FROM table1 t1
LEFT JOIN table2 t2
  ON t1.id = t2.id
WHERE t2.id IS NULL

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562260

pt-table-sync --print h=server1,D=dbname,t=tablename h=server2

See http://www.percona.com/doc/percona-toolkit/2.0/pt-table-sync.html

Upvotes: 1

gpeche
gpeche

Reputation: 22504

A trick I have done sometimes (you need access to both tables):

  1. Open Access or similar
  2. Link table from server A
  3. Link table from server B
  4. Calculate difference in Access

Obviously, you have to be careful if the tables are large.

Upvotes: 0

eiu165
eiu165

Reputation: 6261

we use http://www.red-gate.com/products/sql-development/sql-compare/

Upvotes: 0

Related Questions