This is never really a good position to be in, but there are ways of rebuilding these files into a clean slate.
Note, that we recommend using:
in the [mysqld] section of your /etc/my.cnf from install time. Changing it after databases already exist could break things and force you to use this guide.
If you want to change to use innodb_file_per_table, then you'd need to use this guide.
It will essentially dump all databases to sql files, drop all databases, stop mysqld, delete the ib* data files, and restart mysqld and restore the sql files.
Before anything else, ensure you have full .sql backups of all of your data. It's also recommended to have a copy of the mysql data directory, so you can copy it back if things go south.
CustomBuild 1.2 and 2.0 can make the plaintext sql files for you. Run the following, but only if your data itself is still valid
. Do not run this if you need to restore your backups to get non-corrupted data:
./build set mysql_backup yes
Then confirm that the sql files have been created here:
ls -la /usr/local/directadmin/custombuild/mysql_backupsIf you do not have any valid plain .sql file backups, do not proceed. This guide only works if if you have something to restore from.
: The innodb_force_recovery option
may let you get mysqld running, and possibly even repair it for you in a quick/easy manner.
Next, we'll shut off mysqld to make a data directory backup, which you'll want to do either way (corrupted or not).
perl -pi -e 's/mysqld=ON/mysqld=OFF/' /usr/local/directadmin/data/admin/services.status
then copy the directory. Use /home/mysql for Debian or FreeBSD.
cp -Rp mysql mysql.innodb.backup
Try this guide to attempt to surgically fix or remove the offending database:
Hopefully this will work for you, and you can stop without a full rebuilt.
If this fixes the problem, stop here, do not continue.
At this point, assuming you have safe backups of all of your data, you can move to the point of no return.
Start mysqld again, and drop all databases, except these three: mysql, performance_schema, information_schema
To do this, you can use /phpMyAdmin via Apache to make things easier and quicker.
Use the da_admin user/pass from:
Drop all, but those 3 databases.
Now that they're gone, we can clean up the ib* data. Stop mysqld again, delete the ib* files, and start mysqld. If you are changing to use the innodb_file_per_table option, make sure it's in your /etc/my.cnf at this point.
rm -f ib*
perl -pi -e 's/mysqld=OFF/mysqld=ON/' /usr/local/directadmin/data/admin/services.status
this will rebuild the ibdata1 and innodb log files, goodbye corruption.
Last step is to restore your sql data files. Because the mysql database was left, the users should all still be there (it's tables all use MyISAM, thus not affected by innodb corruption). Assumign your sql files are in the path above, then you should be able to run the following script:
chmod 755 restore_sql_files.sh
This will automatically skip the mysql and schema databases, and restore everything else.