Mysql – What happens if the thesql database’s innodb log files are lost

backupinnodbMySQLrecovery

What I did was

/etc/init.d/mysql stop

then removed the files: ib_logfile0, ib_logfile1

then modified my.cnf file, variable: innodb_log_file_size

and then:

/etc/init.d/mysql start

and allowed the files to be recreated

I later discovered that the global variable innodb_fast_shutdown is set to "1"

The question is, how much data was lost ?

Note: I still have the old files ib_logfile0, ib_logfile1, not deleted yet.

And the website relying on the database appears to be working.

Best Answer

First things first: kudos for not actually deleting the files -- moving files out of the way is always a better idea than just deleting them -- but don't try to put the old log files back.

It's possible that you might be able to analyze them but you can't put them back into service on this server, because the log sequence number (LSN) in the log files won't match the one stored in ibdata. 

InnoDB:                          WARNING!
InnoDB: The log sequence number in ibdata files is higher
InnoDB: than the log sequence number in the ib_logfiles! Are you sure
InnoDB: you are using the right ib_logfiles to start up the database?

It would most likely then begin crash recovery and could potentially start "repairing" things that were not broken... so, I definitely would not try this.

The question is, how much data was lost?

If any data was lost, it would have been recent changes (inserts/updates/deletes); however, quite contrary to what I've always thought, it may actually be safe to do what you did with innodb_fast_shutdown set to 1 (but not 2).

http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html

Those instructions imply that as long as it's not set to '2' you'd be fine, and if it were set to '2,' they say to set it to '1' -- as opposed to '0' as I would have expected... so it looks like either you're good to go, or there's a fairly serious flaw in the documentation.

Related Question