Issue after moving the ib_logfile1 and ib_logfile0 files

Question

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.

Answer 1

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.

Answer 2

The steps you outlined are correct.

I have written about this before

• Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?
• May 30, 2012 : mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

Doing innodb_fast_shutdown = 1 is incomplete because ibdata1 still have other transactional tidbits that will cleanup during the Crash Recovery phase of the next restart of mysqld. Some data changes may not make it.

Doing innodb_fast_shutdown = 0 is a complete flush of all InnoDB transactional changes in the Log Files and in ibdata1 (See Pictorial Representation).

Therefore, please do not change anything you have stated.

Answer 3

There is a small problem in your approach, if I understood correctly, you changed the log file size, and restarted MySQL with innodb_fast_shutdown = 0. The problem here is that you still have some transaction on log files that aren't in the ibdata. What you need to do is tell MySQL to write all changes from log files to ibdata before move the log files, see bellow

1. Change innodb_fast_shutdown to 0
2. Restart MySQL - it will make MySQL start with innodb_fast_shutdown = 0
3. Stop MySQL - it will force MySQL to write all changes remaining on log files to ibdata
4. Change the value for innodb_log_file_size and return innodb_fast_shutdown = 1
5. Move old log files to some place else
6. Start MySQL

Following these steps you will make sure all remaining changes will be wrote to ibdata before create the new log files.