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.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
What would get deleted if each log file would be for example 1 GiB in size? I assume that a couple of GiB could hold a lot of “recent changes (inserts/updates/deletes)”? –  Rafael Sep 17 at 11:50
1  
@Rafael if the server was stopped gracefully with innodb_fast_shutdown set to 0, and apparently 1, the data in the logs should have already been committed to ibdata1 file and *.ibd files, so there should be no data loss. –  Michael - sqlbot Sep 18 at 1:55

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.