How to Resize MySQL InnoDB logs file in CentOS/RHEL

Log files are important disk-based resources which is managed by the InnoDB storage engine. MySQL creates two log files named ib_logfile0 and ib_logfile1 in the MySQL data directory. We define the size of log file in innodb_log_file_size system variable. If for any reason you need to change the size of InnoDB log files then follow below steps:

Step #1: Check InnoDB shutdown mode

First check the value of InnoDB shutdown mode. InnoDB shutdown mode can be configured one of three different values:

0 : InnoDB will clean up old and redundant data and perform insert buffer merge before shutting down.
1 : A fast shutdown which skips the above tasks. It’s also the default one.
2 : Performs a controlled “crash”.

# mysql> show variables like 'innodb_fast_shutdown%';

Output:

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 2     |
+----------------------+-------+
1 row in set (0.00 sec)

# mysql> SET GLOBAL innodb_fast_shutdown=1;

Step #2: Remove Old InnoDB log files

After confirming that innodb_fast_shutdown is not set to 2, stop the MySQL server and check mysql log files that it close down without errors.

# /etc/init.d/mysqld stop
# find /var/lib/mysql -type f -name "ib_logfile?" -exec mv {} {}_bkp \;

Step #3: Reconfigure InnoDB log files

Edit my.cnf to change the log file configuration. To change the log file size, nee to change the value of innodb_log_file_size.

# vim /etc/my.cnf

#innodb
innodb_log_file_size = 64M

Start the MySQL server again.

# /etc/init.d/mysqld start

Enjoy it!