Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Without using a script, Is there a way to get mysql to check all the tables at once to make sure they're not corrupted?

share|improve this question
    
You may want to see: dba.stackexchange.com/questions/6191/… –  randy melder Aug 2 '12 at 16:08

1 Answer 1

innodb is very very very unlikely to have any corruption caused by the storage engine. (corruption caused by applications isn't something mysql can help with).

Upon each start up it will check it's log files and automatically recover any from any partially completed transactions in the event of a crash.

That being said it is still very possible for a replication slave to become out of synch. For that reason, or even if you just want to be uber paranoid you can use the tool pt-table-checksum

This will sometimes intelligently checksum your live tables in chunks so performing checks doesn't require downtime (either through explicitly taking the server down or having tables locked for a checksum table; query). These numbers as a one off don't really tell you much. As with any checksum you need something to compare it against to have any value. If you run periodic checksums you can compare against previous values to alert you of unexpected changes.

Now, obvious you will be making changes in a database so this isn't a catchall. This could be a good method for checking archival tables were data is not updated once it is written.

Note: I said it "sometimes intelligently" does checks in chunks. You might run into issues if you only have a PK/unique key that is multi column.

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.