Verify DB dump integrity

mstester

Hi all,

Hoping someone maybe able to shed some light on the following:

After I do a dump of a mysql database is there a way to verify that all the data has been writting to the textfile (.SQL) correctly?

The last time I done a dump some data seemed to be missing and I want to avoid that this time.

Thanks in advance!

samhail

you could load the dumped sql file into another dbase and do a quick check on the new data, or a size check or a hash check of some sort

mstester

Hi Samhail

"you could load the dumped sql file into another dbase and do a quick check on the new data"

Thanks for the response but the above is how I found out some data was missing / corrupted.

Could you provide me with some more info on how to do the below please?
"or a size check or a hash check of some sort"

Thanks again

samhail

i was referring to the reloaded database in checking the size.
If your dump of the data is failing then that might be what you should be looking at if you know the SQL file is incomplete ?

ressem

I don't know of an easy way to test the mysql dump with reloading it again on a mysql server.

There are a number of ways of creating checksum on tables.
Are your database tables stored as Myisam, innodb or other.

There is the mysql provided Checksum table <tablename> or 3rd party perl scripts like http://www.xaprb.com/blog/2007/02/26/introducing-mysql-table-checksum/

What command syntax are you using to carry out the backup?

JimmyCrackCorn!

Restore it to another Db and use a database comparison tool.

They do exist.

I just cant recommend any as they cost money and id probably just count the rows in each table and use the system table to do sanity checks.

Sorry my MSSQL is better than my mysql.