Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I did a mysql DBname < dumpFile.sql and like a real jerk, I didnt background the process, which eventually my ssh session died with broken pipe. How do I, using the dump file, verify if the import completed on the mysql DB? RHEL 6, mysql 5.1.6.

share|improve this question

4 Answers 4

Check the last insert of the dump file (it's text, tail it) and see whether that data has made it to your database.

share|improve this answer

One of:

  • Do a dump from the database and diff the old and new

  • Drop the DB and re-import the data

share|improve this answer
    
I would suggest reimport. Also, diff probably won't work if the dump is large. md5sum is better for comparison for identity, but that needs exactly same dump settings. –  Fiisch Apr 25 at 17:40

Just do a mysqldump of the new database, and diff it against the old database.

share|improve this answer
    
Its several Gigs, for what it's worth. –  usedTobeaMember Apr 25 at 17:45
1  
Then it may be best to just drop the db and reimport. This time use screen (gnu.org/software/screen) so if your SSH session dies with a broken pipe, your import will continue and you can just reattach the screen session. –  Kelly John Rose Apr 25 at 18:05

Assuming your MySQL versions on both servers match (or are very close to matching)...

Step 1

From the MySQL prompt run CHECKSUM TABLE table_name; and see if the checksum matches in both places.

Note that this will take a little time to run and tie up your table. However, even with several GBs of data it should be less than a minute or so... unless your box config is somehow wonky.

http://dev.mysql.com/doc/refman/5.1/en/checksum-table.html

Step 2

Because there is a slight chance of checksum collision on non-matching tables and also for posterity, this is your backup step.

Run (after changing your_database_name and your_table_name) ...

SELECT CONCAT(table_schema, '.', table_name) AS 'database.table',
     FORMAT(table_rows, 0) AS table_rows,
     FORMAT(data_length, 0) AS data_length,
     FORMAT(index_length, 0) AS index_length
FROM information_schema.TABLES
WHERE table_schema = 'your_database_name'
AND table_name = 'your_table_name';


If everything matches, you're good to go! If not, you'll have to dig deeper.

A mismatch on index size only might not be an issue because an import generally (depending on settings) creates fresh indexes on import without fragmentation. However, if the indexes do mismatch and you want to try to sync them before proceeding, run OPTIMIZE TABLE against the source table and check again to see if that solves it.

There is an edge case for index size I haven't quite sorted out, just FYI... Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

Conclusion

This works for me in 99% of cases without an issue. It should be noted I use MyISAM mostly (for various reasons), but this should be fine for InnoDB as well. Hope it helps!


This is the answer you were looking for... (waves Jedi hand)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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