innochecksum prints checksums for InnoDB files. This tool reads an InnoDB tablespace file, calculates the checksum for each page, compares the calculated checksum to the stored checksum, and reports mismatches, which indicate damaged pages.
It was originally developed to speed up verifying the integrity of tablespace files after power outages but can also be used after file copies. Because checksum mismatches cause InnoDB to deliberately shut down a running server, it may be preferable to use this tool rather than waiting for an in-production server to encounter the damaged pages.
Below is a script you can use to get corrupted tables. Replace the directory path where your data files are located. (Stop MySQL/MariaDB service first, before running it)
INNOCKSM_LOG=/mysql/backup/innochecksum_`date +%Y%m%d_%H%M%S`.log
for DB in `ls -1vd /mysql/data/*/ | grep -wv '/mysql/data/mysql/\|/mysql/data/performance_schema/\|/mysql/data/lost+found/'`
do
for IBD in `ls -1v $DB | grep .ibd`
do
innochecksum ${DB}${IBD}
if [ $? -ne 0 ]; then
echo ${DB}${IBD} >> $INNOCKSM_LOG
innochecksum ${DB}${IBD} >> $INNOCKSM_LOG 2>&1
fi
done
done
Cheers!
Leave a Reply