How to Resize MySQL Innodb Log Files Without Errors

Upon installing MySQL, the default innodb log file size is 5MB. This might be fine for a lot of servers but should you want to resize it, it is not simply a case of changing innodb_log_file_size to the new size and restarting MySQL.

MySQL will not start and produce error log output similar to the following:

140610 20:09:14 InnoDB: The InnoDB memory heap is disabled
140610 20:09:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140610 20:09:14 InnoDB: Compressed tables use zlib 1.2.3.4
140610 20:09:14 InnoDB: Initializing buffer pool, size = 128.0M
140610 20:09:14 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
140610 20:09:14 [ERROR] Plugin 'InnoDB' init function returned error.
140610 20:09:14 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140610 20:09:14 [ERROR] Unknown/unsupported storage engine: InnoDB
140610 20:09:14 [ERROR] Aborting
140610 20:09:14 [Note] /usr/sbin/mysqld: Shutdown complete

MySQL will not start because the configured value in my.cnf is not the same as the current log file size.

How to safely adjust Innodb log file size

(Example demonstrated on a Ubuntu 12.04LTS instance)

1/ Make sure that innodb_fast_shutdown is NOT configured as 2

You may suffer data loss if this is the case. Change to either 0 or 1. Setting as 0 will take longer to shutdown the server so 1 is preferred. Check out more information on this at this link

[sourcecode language=’sql’]
SHOW VARIABLES LIKE ‘innodb_fast_shutdown’
[/sourcecode]

Change to 1 to perform a fast shutdown

[sourcecode language=’sql’]
SET GLOBAL innodb_fast_shutdown=1;
[/sourcecode]

2/ Safely stop the MySQL service checking for issues

Stop MySQL and check the log file as defined in my.cnf  by the variable “log_error” for any issues.

example :-

log_error = /var/log/mysql/error.log

You want to see a nice clean shutdown

[sourcecode language=’sql’]
service mysql stop
[/sourcecode]

Check the log, it should show a good, error free shutdown…

[sourcecode language=’sql’]
tail -10 /var/log/mysql/error.log
[/sourcecode]

140610 20:19:44 [Note] /usr/sbin/mysqld: Normal shutdown
140610 20:19:44 [Note] Event Scheduler: Purging the queue. 0 events
140610 20:19:44  InnoDB: Starting shutdown...
140610 20:19:44  InnoDB: Shutdown completed; log sequence number 1595685
140610 20:19:44 [Note] /usr/sbin/mysqld: Shutdown complete

3/ Move the existing log files out of your MySQL “datadir” folder

You need to move these, don’t delete them. The innodb log files are found in your “datadir” folder as defined in my.cnf

They are ib_logfile0, ib_logfile1 etc
[sourcecode language=’sql’]
cd /var/lib/mysql
mv ib_logfile0 ib_logfile1 /home/ubuntu
[/sourcecode]

4/ Change innodb_log_file_size variable in my.cnf and start MySQL

Set the size that you need for your server, I’m setting to 64M in this case.

[sourcecode language=’sql’]
innodb_log_file_size = 64M
[/sourcecode]

Start MySQL, it should start cleanly without error

[sourcecode language=’sql’]
service mysql start
[/sourcecode]

5/ Now check your servers log file again

It should look similar to the below where the log will show MySQL creating new log files

140610 20:51:17  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait...
140610 20:51:21  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
140610 20:51:24 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140610 20:51:24  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...
140610 20:51:24  InnoDB: Waiting for the background threads to start
140610 20:51:25 InnoDB: 5.5.37 started; log sequence number 1596428

Finally

Check that you can query your innodb tables and then it should be safe to remove the old log files which you backed up earlier.