1

I am trying to compare total number of records for a particular table in two databases. They are from the same database, but both the database are running in two different servers.

Table structure is same on both the tables , but no of records varies on both the tables.

I want to find out the missing records from both these tables.

mysql> select count(*) from dlfilerank;
+----------+
| count(*) |
+----------+
|    38259 |
+----------+
1 row in set (0.02 sec)


mysql> select count(*) from dlfilerank;
+----------+
| count(*) |
+----------+
|    38359 |
+----------+
1 row in set (0.02 sec)
3

So, dump both tables into two files and then see the difference with diff:

 mysql -h host1 -NB -e "SELECT * FROM db.table ORDER BY id" > t1
 mysql -h host2 -NB -e "SELECT * FROM db.table ORDER BY id" > t2

 diff -u t1 t2
| improve this answer | |
0

Assuming id is a unique field in the tables... To find what is in db1.tbl but not in db2.tbl:

SELECT db1.tbl.*
FROM db1.tbl
LEFT JOIN db2.tbl USING(id)
WHERE db2.tbl.id IS NULL;

Swap db names to check the other direction.

| improve this answer | |
-2
SELECT TABLE_NAME 
FROM information_schema.tables 
WHERE table_schema = 'db1' 
AND `TABLE_NAME` NOT IN  (
    SELECT TABLE_NAME 
    FROM information_schema.tables 
    WHERE table_schema = 'db2');
| improve this answer | |
  • 1
    This compares metadata, not data. So it doesn't answer the question. – Colin 't Hart Nov 20 '19 at 9:34
  • 1
    As @Colin'tHart points out, this does not answer the question. I wouldn't worry about it too much - I've done the same thing frequently enough - you only have to look at this morning! Just keep trying and welcome to the forum! :-) – Vérace Nov 20 '19 at 11:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.