MySQL Innodb corruption

I recently inherited a Drupal site that has an Innodb corruption. MySQL will run for sometime--usually about 24-hours but will eventually need to be manually restarted(see log output below). I have tried/verified the following:

-
Renamed existing logfiles, then increased the size of the logfiles to 64M and now up to 256M.

-
Verified there is plenty of memory

-
Started in innodb_force_recovery = 4

-
Verified that every table is ok with CHECK TABLE

Despite all these efforts the problem persists. I don't have a stable backup to recover from. I have read that I may need to do the following:

• backup the datadir

• uninstall MySQL

• reinstall

but I don't understand what this would fix since all the tables pass the CHECK TABLE query.

Here is a sample of my log files. This activity goes back a year:

```
Number of processes running now: 0
120424 16:05:25 mysqld restarted
120424 16:05:58 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
120424 16:06:14 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 12 657040373.
InnoDB: Doing recovery: scanned up to log sequence number 12 657234827
120424 16:06:14 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
120424 16:06:14 InnoDB: Started; log sequence number 12 657234827
120424 16:06:15 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
Number of processes running

Since the InnoDB Storage Engine looks operational at the time mysql is restated, you should take the time to cleanup the InnoDB infrastructure

• mysqldump the entire database instance

• reconfigure innodb from scratch

• Start over with a fresh ibdata1 file

• Empty Undo Space

• Empty Rollback Segments

• Empty Data Dictionary

• Empty List of Tablespace IDs

• Store Each InnoDB table in its own tablespace

• Configure Buffer Pool to be 4 times the Log File Size

• Perform Better Flushing of the Buffer Pool

• reload mysql data

Add this to my.cnf

[mysqld]   
datadir=/var/lib/mysql   
socket=/var/lib/mysql/mysql.sock   
user=mysql   
#innodb_force_recovery = 4    
# Default to using old password format for compatibility with mysql 3.x   
# clients (those using the mysqlclient10 compatibility package).   
old_passwords=1   
innodb_log_file_size=256M   
innodb_file_per_table
innodb_buffer_pool_size=1G
innodb_flush_method=O_DIRECT
[mysqld_safe]   
log-error=/var/log/mysqld.log   
pid-file=/var/run/mysqld/mysqld.pid

Run these commands to install mysql in a new folder

service mysql restart --skip-networking --skip-grant-tables
mysqldump --all-databases --routines --triggers > /root/MySQLData.sql
service mysql stop
mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_old/mysql/* /var/lib/mysql/mysql/.
chown -R mysql:mysql /var/lib/mysql/mysql/.
service mysql start --skip-networking --skip-grant-tables
mysql < /root/MySQLData.sql
service mysql restart

Give it a Try !!!

StackExchange Database Administrators Q#17040, answer score: 8