MySQL: Changing the size of the log file

I was told that the new MySQL (5.6+) is flexible about this thing, that is very good. I know people have been fighting with it for a long time. Since the Ubuntu 14.04 is still having MySQL 5.5 as default, I would like to write this down as a reference.

Situation: The default innodb_log file size is small, as 5M, if it is not specified in the /etc/mysql/my.cnf. As soon as you start mysql server, ib_logfile0 and ib_logfile1 are created in the /var/lib/mysql folder with 5M size. Then you can not simply change it.

With the default small log file size, you can run into

Error code 1206: The number of locks exceeds the lock table size.

when doing some complicate query, or loaddata big file.

In an ideal word (as in the new MySQL(5.6+)), you just need to add innodb_log_file_size=128M in the my.cnf file, restart the mysql server, the software would load the new configuration and resize the log file.

But in reality (as MySQL 5.5-), if you modify the my.cnf and restart, your mysql service can't never start, and the log tells you that setting is not consistent with the existing log file size. Sure, we know that and that is exactly what we are trying to do: resizing the log file. But what should we do now?

So you have to change back to innodb_log_file_size to 5M in order to proceed. Add

innodb_fast_shutdown = 0

in config file for purging any pending transaction into database. So the my.cnf has this 2 lines:

innodb_fast_shutdown = 0innodb_log_file_size   =  5M

At this time, you should able to start mysql service:

service mysql start

Good. Then we stop it properly:

service mysql stop

At this time, because all pending transactions are purged from the log file, we can safely remove the ib_logfile*:

mv /var/lib/mysql/ib_logfile* /tmp

Now you can edit the my.cnf file toset the innodb_log_file_size into the size you want, and remove (comment out) the shutdown parameter:

#innodb_fast_shutdown = 0innodb_log_file_size   =  128M

Then restart the service:

service mysql start

The ib_logfiles will be created with new size now.