• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

How to Resize MySQL Innodb Log Files Without Errors

June 10, 2014 by Andy Hayes 2 Comments

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 1.2.3.4
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

[sourcecode language=’sql’]
SHOW VARIABLES LIKE ‘innodb_fast_shutdown’
[/sourcecode]

Change to 1 to perform a fast shutdown

[sourcecode language=’sql’]
SET GLOBAL innodb_fast_shutdown=1;
[/sourcecode]

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.

example :-

log_error = /var/log/mysql/error.log

You want to see a nice clean shutdown

[sourcecode language=’sql’]
service mysql stop
[/sourcecode]

Check the log, it should show a good, error free shutdown…

[sourcecode language=’sql’]
tail -10 /var/log/mysql/error.log
[/sourcecode]

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
[sourcecode language=’sql’]
cd /var/lib/mysql
mv ib_logfile0 ib_logfile1 /home/ubuntu
[/sourcecode]

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.

[sourcecode language=’sql’]
innodb_log_file_size = 64M
[/sourcecode]

Start MySQL, it should start cleanly without error

[sourcecode language=’sql’]
service mysql start
[/sourcecode]

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

Finally

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.

Related Posts:

  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…
  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…

Filed Under: All Articles, MySQL Administration Tagged With: mysql, ubuntu

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Omar Butt says

    August 30, 2015 at 5:40 pm

    Hi Andy,
    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.

    Cheers,
    Omar

    Reply
  2. philnc says

    April 4, 2016 at 7:12 pm

    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.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©