Changing InnoDB Log File Size

If MySQL error log is showing this error:


InnoDB: ERROR: the age of the last checkpoint is 9433957,

InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

This means that the innodblog is smaller than some of the executed queries and it is unable to log them.

That is not critical if you are not using transactions, but it is if so.  Here is the procedure to change the InnoDB log file size.

1. Stop MySQL

/etc/init.d/mysql stop

2. Check that MySQL is not running

ps -fea | grep myql

3. Go to the data directory

cd data

4. Check the files

ls -la

5. Move the InnoDB log files to a safe location (If MySQL does not start we will need to copy then back)

mv ib_logfile0 /tmp/ib_logfile0.old

mv ib_logfile1 /tmp/ib_logfile1.old

4. Verify that the ib_logfiles are not in the /mysql/data directory

ls -la

5. Edit /etc/my.cnf

vi /etc/my.cnf

6. Add the line

innodb_log_file_size=50M (Set the File Size, in this case 50M)

7. Start MySQL

/etc/init.d/mysql start

Starting MySQL.....                                        [  OK  ]

8. Verify that MySQL is runing

ps -fea | grep mysql

9. Verify that the InnoDB log files were created and their sizes

ls -la /mysql/data

10. Verify that you can login to MySQL

mysql -u -p -A 

11 Delete the old InnoDB log files

rm /tmp/ib_logfile*