On Tuesday night, I had a database crash from which I recovered successfully (I run MySQL 5.0.54. The OS is Gentoo Linux with the 2.6.23 kernel.).
Once I recovered, I decided to tweak my my.cnf configuration file.
I modified then saved /etc/mysql/my.cnf. After, I stopped mysql without problem.
I moved to the directory where the data are kept (/big5/mysql) and deleted all the mysqld_bin.* files (This is where I made a mistake, I shouldn't have deleted them but I somehow panicked when I saw over a hundred 1.1 GB files). Each table has its own file in a subdirectory named "enginrecherche"( I have innodb_file_per_table in the my.cnf file).
I also removed the innodb log files as I was modifying innodb_log_file_size in my.cnf(following the procedure described here:
http://mysqldatabaseadministration.blogspot.com/2007/01/increase-innodblogfilesize-proper-way.html)
When I restarted mysql, /var/log/mysql/mysqld.err contained those lines:
080708 23:28:37 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=mysqld-bin' to avoid this problem.
080708 23:28:38 InnoDB: Log file /big5/mysql/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /big5/mysql/ib_logfile0 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
080708 23:28:46 InnoDB: Log file /big5/mysql/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /big5/mysql/ib_logfile1 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
080708 23:28:55 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...
080708 23:28:58 InnoDB: Error: page 7 log sequence number 58 1603808174
InnoDB: is in the future! Current system log sequence number 56 975172108.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB:
http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.
The last 6 lines repeated many times with various sequence numbers.
Finally there was these lines:
080708 23:28:58 InnoDB: Started; log sequence number 56 975172108
/usr/sbin/mysqld: File './mysqld-bin.000226' not found (Errcode: 2)
080708 23:28:58 [ERROR] Failed to open log (file './mysqld-bin.000226', errno 2)
080708 23:28:58 [ERROR] Could not open log file
080708 23:28:58 [ERROR] Can't init tc log
080708 23:28:58 [ERROR] Aborting
080708 23:28:58 InnoDB: Starting shutdown...
080708 23:29:00 InnoDB: Shutdown completed; log sequence number 56 975172108
080708 23:29:00 [Note] /usr/sbin/mysqld: Shutdown complete
At this point, I modified my.cnf to set innodb_force_recovery =1 and tried to restart mysql.
It didn't restart. I then increased innodb_force_recovery to 2 and it still didn't start. I repeated the procedure for innodb_force_recovery equal to 3, 4, 5, 6.
At this stage, I am almost ready to give up.
I don't really care about the data anymore, I just want to recover the structure of the database, the indexes, triggers and stored procedures. I never backed up this database as it is a sandbox for testing. I just want to recover the stored procedures, indexes and trigger we were working on when it crashed.
Thanks for your help!
Charles
Edited 1 time(s). Last edit at 07/09/2008 12:15PM by Charles Nadeau.