Mysql – Table corruption: How to perform Innodb Checksum checks in MySQL 5.5 for Windows

innodbMySQLmysql-5.5recoverywindows

Having a corrupted Mysql 5.5.31 (Windows) database, my question relates to the the top solution provided in How do you identify InnoDB table corruption? , more precisely to the following script that is supposed to tell you which tables are corrupted:

#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
  innochecksum -v $i
done

In fact I have two questions:
1) Where do you execute such a script? I thought the scripting shell in MySQL Workbench would to the job by saving this snippet as a Python file and then executing it – however it reports invalid syntax already in the "for …" line.
2) According to http://dev.mysql.com/doc/refman/5.5/en/innochecksum.html innochecksum is a utility provided by MySQL/Oracle. However, I do not seem to find it in the bin or other folders of my MySQL installation. How do I obtain it?

UPDATE: As I did not trust my own MySQL installation, I downloaded the zip files for both 32 and 64 bit versions of 5.5.31 but can confirm that a innochecksum file is not included.

Thanks.

Best Answer

ANSWER #1

This feels clumsy doing this in Windows but here it goes. If you are able to login to mysql, then run this script

set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set SQLSTMT=SELECT CONCAT('REPAIR TABLE ',table_schema,'.',table_name,';')
set SQLSTMT=%SQLSTMT% FROM information_schema.tables WHERE engine='InnoDB'
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
echo %SQLSTMT%
mysql %MYSQL_CONN% -ANe"%SQLSTMT%" > C:\windows\InnoDBCheck.sql

After you run this script, add this to my.ini

[mysqld]
init-file=C:\windows\InnoDBCheck.sql

Then, restart mysql

net stop mysql
met start mysql

during the startup, just after crash recovery, the

Forgive me for such a crude solution since I have very little dealing with MySQL for Windows (that's not sarcasm, I am serious).

ANSWER #2

Here is something more serious

I just checked my MySQL 5.6.10 no-install ZIP file. It has innochecksum.

C:\>dir \MySQL_5.6.10\bin\i*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.6.10\bin

01/22/2013  07:05 PM         4,065,792 innochecksum.exe
01/22/2013  07:05 PM         3,706,880 innochecksum.pdb
               2 File(s)      7,772,672 bytes
               0 Dir(s)  160,596,770,816 bytes free

C:\>

Download that ZIP and try running that .

You could then collect all the .ibd files with something like this:

cd "C:\Program Files\MySQL\MySQL 5.5\data"
dir *.ibd /s/b > C:\ibdfiles.bat

You can then edit C:\ibdfiles.bat and prepend innochecksum against every filename.

Sorry this is not a full answer, but at least you can a hold of innochecksum.exe

Related Question