MySQL – Issue After Moving ib_logfile1 and ib_logfile0 Files

innodbMySQLmysql-5.5

I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes. So I moved these files to different location and restarted the MySQL server. It worked. Before shutting down the MySQL, server I also made innodb_fast_shutdown = 0.

But I have a doubt, after MySQL shutdown can we expect mysql is an consistent state and all the data that were in the ib_logfile0 and ib_logfile1 have been written to data files. So that I can delete these files or should I do something else also.

And if the answer is MySQL will be in consistent state than why these files contain some data even after graceful shutdown and start up with innodb_fast_shutdown = 0.

[EDIT details] giving the steps i have done

Steps i followed:

  1. SET GLOBAL innodb_fast_shutdown = 0
  2. /etc/init.d/mysqld stop
  3. rm -f /var/lib/mysql/ib_logfile0
  4. rm -f /var/lib/mysql/ib_logfile1
  5. /etc/init.d/mysqld start

Is the above step is fine? if yes then how come after the clean shutdown and start up the log files contains some data. Ideally it should be empty.

Best Answer

How are you coming to the conclusion that "these files contain some data even after graceful shutdown"?

Even if it's been fully flushed and uncommitted transactions cleared, etc., by using innodb_fast_shutdown = 0, the "flushing" probably means that the file is "empty" as far as MySQL is concerned, but it won't physically be full of zeroes.

If you start MySQL up again, it will probably zip through the files to the latest Log Sequence Number and realise that there's no more work to be done (someone familiar with the source code that handles the innodb logs would be able to confirm/deny this).

Your steps should be absolutely fine, but I'd take Baron Schwartz's suggestion and move the ib_logfiles somewhere instead of deleting them, just in case it turns out that the server doesn't shut down cleanly for some reason.

Related Question