Mysql – whether a big (but not used) ibdata1 slow anything down

This looks oddly familiar.

I have seen this occur with one of my web hosting client's DB servers. There was a particular table that crashed mysqld every single time you accessed it, even with SHOW CREATE TABLE.

The problem stems from a corrupt data dictionary. There is really no way to correct it. You could attempt to alter the tablespace_id within the .ibd file but the headache stems from locating the tablespace_id list internal to ibdata1.

Even if you create a MyISAM table with the same name in the same database as the original InnoDB table, you cannot convert it to InnoDB because the tablespace_id is already associated with the table name. This, of course, is a corrupted state. It's like having a pidgeon hole in ibdata1 that you cannot patch up without some exploratory surgery.

You may have to mysqldump everything except the database that houses the corrupt table. You would then have to mysqldump every table in that database except the corrupt table. Remember, it is the data dictionary's view of the table that is screwed up, not necessarily the table's data.

The only sure way to clean everything up is to perform the mysqldumps as I just specified, shutdown mysql, rm -rf all DB folders except /var/lib/mytsql/mysql, delete ibdata1, delete ib_logfile0, delete ib_logfile1, startup mysql, reload all mysqldumps. See my StackOverflow post about cleaning up your InnoDB infrastructure.

Since you are not using innodb_file_per_table, any tables with this corrupt state of things within ibdata1 are lost as casualities of war. My condolences.

For future reference, click here to see an artistic conception of InnoDB and its Internals.

If you don't have backup.

I think the recommended way to recover that databases is start MySQL with innodb_force_recovery = 4 (or higher values) and dump the databases to a SQL.Then drop it and recover from backup. Instead of dropping the original database I prefer to create the new databases with another name or in another server and check the content first.

STEPS TO BE FOLLOWED

1. In mysqld section of my.cnf add a line innodb_force_recovery = 4 and then restart MySQL server using /etc/init.d/mysql restart.

2. Take backup.

3. Restore it on other server and verify the contents.

Please also have a look at Forcing InnoDB Recovery.

UPDATE : For your comment how to progress on that so that it would not happen again

You should not kill MySQL it is not a good practice that may cause MySQL server to crash,whenever you make any configuration changes you should proceed as follows.

1. Safely stop MySQL Server using /etc/init.d/mysql stop
2. Make changes
3. Restart MySQL Server