MySQL Forums
Forum List  »  Data Recovery

Recovering an INNODB database without mysqld_bin.* files
Posted by: Charles Nadeau
Date: July 09, 2008 12:13PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Recovering an INNODB database without mysqld_bin.* files
7877
July 09, 2008 12:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.