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 188.8.131.52 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
SHOW VARIABLES LIKE ‘innodb_fast_shutdown’
Change to 1 to perform a fast shutdown
SET GLOBAL innodb_fast_shutdown=1;
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.
log_error = /var/log/mysql/error.log
You want to see a nice clean shutdown
service mysql stop
Check the log, it should show a good, error free shutdown…
tail -10 /var/log/mysql/error.log
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
mv ib_logfile0 ib_logfile1 /home/ubuntu
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.
innodb_log_file_size = 64M
Start MySQL, it should start cleanly without error
service mysql start
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
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.
Omar Butt says
I have followed your fantastic instructions and dozen’s more on the internet step by step but inspite of changing the log file size to 256M, every time in the end I restart the server it still defaults to 5MB log file. Could there be another location other than /etc/my.cnf where I also need to change it.
Check alternate for additional files in places like /etc/my.cnf.d. One way to find them is to go to / and do a find . -name “my.cnf” -type f -print.