Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

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

  2. Verified there is plenty of memory

  3. Started in innodb_force_recovery = 4

  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 now: 0
120424 16:11:27  mysqld restarted
120424 16:11:28  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:11:28  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 12 661789262.
InnoDB: Doing recovery: scanned up to log sequence number 12 661789262
120424 16:11:28  InnoDB: Started; log sequence number 12 661789262
120424 16:11:28 [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 now: 0
120424 16:17:48  mysqld restarted
120424 16:17:49  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:17:49  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 12 662677386.
InnoDB: Doing recovery: scanned up to log sequence number 12 662677386
120424 16:17:49  InnoDB: Started; log sequence number 12 662677386
120424 16:17:49 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

And here is my 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
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Here is my SHOW ENGINE output:

+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | 
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | 
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | 
| BerkeleyDB | YES     | Supports transactions and page-level locking                   | 
| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) | 
| EXAMPLE    | NO      | Example storage engine                                         | 
| ARCHIVE    | NO      | Archive storage engine                                         | 
| CSV        | NO      | CSV storage engine                                             | 
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 | 
| FEDERATED  | NO      | Federated MySQL storage engine                                 | 
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | 
| ISAM       | NO      | Obsolete storage engine                                        | 
+------------+---------+----------------------------------------------------------------+
share|improve this question

migrated from stackoverflow.com Apr 25 '12 at 17:54

This question came from our site for professional and enthusiast programmers.

    
Question: Did you erase the innodb log files before trying to start up mysql? –  RolandoMySQLDBA Apr 25 '12 at 18:06
    
Yes, I renamed logfiles before increasing the size –  joncon Apr 25 '12 at 19:03

1 Answer 1

up vote 5 down vote accepted

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
      1. Empty Undo Space
      2. Empty Rollback Segments
      3. Empty Data Dictionary
      4. 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 !!!

share|improve this answer
    
Thank you so much. It looks like this did the trick. –  joncon Apr 25 '12 at 21:55
    
Brilliant advice, thank you so much. –  Martin Lyne Dec 7 '13 at 14:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.