Rotating the Innodb Log File

This is a quick way I rotate the InnoDB log file. I will rotate it if the time between ib_logfile0 and ib_logfile1 are basically the same or within minutes of each other. There are other ways to determine what size to make it, like a percentage of your tablespace memory, but I prefer to see how often MySQL is actually rotating it.

The InnoDB log files are named ib_logfile0 and ib_logfile1. They store temporary information about transactions. You can find them in your data directory. For example:

ls -la /var/lib/mysql/ib_logfile*
-rwxrwx--- 1 mysql mysql 5242880 Jul 10 08:30 /var/lib/mysql/ib_logfile0
-rwxrwx--- 1 mysql mysql 5242880 Jul 10 08:30 /var/lib/mysql/ib_logfile1

Edit your MySQL configuration file:

# vi /etc/my.cnf /* Or wherever your MySQL configuration file location is */

Edit or add this line. I increased it from the default of 5MB to 32 MB, with a little timestamped comment about what I did:

innodb_log_file_size = 32M # Increased from 5MB to 32MB on 7/10/2013

Identify your MySQL start script. Let's assume /etc/init.d/mysql.server

Identify your MySQL data directory. Assume /var/lib/mysql

On the Linux command line, run these commands (Make sure to replace with your data directory and your MySQL start script locations):

# cd /[YourDataDirectory]
# /etc/init.d/[YourStartScript] stop; mv ib_logfile0 ib_logfile0.bk; mv ib_logfile1 ib_logfile1.bk; /etc/init.d/[YourStartScript] start;

Here are some additional references, especially if you run into problems:
How To Change Innodb Log File Size Safely
How To Calculate A Good Innodb Log File Size


New Comment