What is a good program to monitor mysql tables for corruption, one that would email me as soon as there is a corrupted table? I'm not looking for mysql workbench or admin to manage the database. But one that will actively monitor the tables and email reports.
3 Answers
Corruption is tough to look for, but there are some methods.
Maatkit has some amazing tools for this. mk-table-checksum in particular is useful:
http://www.maatkit.org/doc/mk-table-checksum.html
You can restore from a backup onto another machine, and compare all the rows that you think should be the same, or just eyeball the diffs to make sure they all make sense. However, there isn't any strong checksumming built into the row formats, so if you aren't doing your own data checksums, you're at the mercy of bitrot on your disks nad weird memory corruption errors. :-P
A simple crontab with mysql check command can do the trick !
mysql check table <table_name>;
MONyog - MySQL Monitor is very handy tool to monitor your MySQL database this tool allows you to monitor custom query output as well and if result is not valid one you can get email-alerts for those. I am consistently monitoring table corruption queries like
CHECK TABLE ; and ANALYZE TABLE ;
To monitor status of my tables.
For more information on how to monitor custom queries you can refer:
http://blog.webyog.com/2011/12/19/how-to-add-a-simple-custom-object-in-monyog/