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?


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.

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.