How are you testing daily or weekly for MySQL InnoDB data file corruption?

InnoDB needs careful and astute planning, but the info you need can be quickly obtained.

First thing you need is to enable the option innodb_file_per_table. This allows each innodb table to have its own external tablespace file to house just the data and index pages for that table only. That way you can shrink/defrag the table using OPTIMIZE TABLE. If you do not use innodb_file_per_table, all innodb tables will reside in /var/lib/mysql/ibdata1 and will always grow and never shrink. If you already have a ton of innodb data trapped in /var/lib/mysql/ibdata1, use the info in these articles I wrote:

http://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-co...

http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage...

The second thing you need is to size up the innodb_buffer_pool_size.

Once your data is loaded, run this query:

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw3,0,pw)))+0.49999),SUBSTR(' KMG',IF(pw3,0,pw))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE engine='InnoDB') A,(SELECT 3 pw) B;

This will show the recommended size to set the innodb_buffer_pool_size in /etc/my.cnf. If the reported number exceeds the installed RAM on your DB server, the set it to 75%-80% (I prefer 75%) of installed RAM. Thus, if you have a 16GB DB Server, set innodb_buffer_pool_size to 12G.

The third thing is to set the innodb_log_file_size. It should be 25% of the innodb_buffer_pool_size. However, InnoDB places a limitation (I have seen it in the source code) of InnoDB Log Files, no matter how many there are, must be 3,0,pw)))+0.49999),SUBSTR(' KMG',IF(pw3,0,pw))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE engine='InnoDB') A,(SELECT 3 pw) B;

If innodb_buffer_pool_size recommended is bigger than before, you can apply the info from the StackExchange articles mentioned before to resize everything, but this should be a monthly (or even annual) event.

You could also run the recommended buffer pool query and compare it to the combined size of all .ibd files in the /var/lib/mysql folder. If the sum of the .ibd files is 1.25 times innodb_buffer_pool_size, you have 20% fragmentation. You should then run OPTIMIZE TABLE against all InnoDB tables. If you know which tables are growing out of control, you could check each individual innodb table as follows:

Example: To check the table mydb.mytable

Run this query

SELECT data_length+index_length tblsize FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable';

Now in the Linux OS do the following
cd /var/lib/mysql/mydb
ls -l mytable.ibd | awk '{print $5}'

If the size of the table from the OS is 1.25 times the size of the table from the information_schema, you have 20% file fragmentation. Just run OPTIMIZE TABLE mydb.mytable;

Plan to do this kind of defragmentation monthly if the DB is heavily trafficked.

These suggestions form the basis for settng up and maintaining InnoDB.

Now you can schedule a monthly CHECK TABLE program.

To formulate a dynamic CHECK TABLE script run this in the Linux OS:

mysql -h -u -p --skip-column-names -A -e"SELECT CONCAT('CHECK TABLE ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine='InnoDB'" CheckInnoDBTables.sql

Then, run the script

mysql -h -u -p --skip-column-names -A CheckInnoDBTablesResult.txt &

Don't test at all. Simply don't use MySQL InnoDB.