So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:

mysql> show variables like 'innodb_buffer%';
| Variable_name           | Value     |
| innodb_buffer_pool_size | 134217728 |
1 row in set (0.00 sec)

When I went to change the innodb_log_file_size value (example my.cnf on mysql's innodb configuration page comments to change the log file size to 25% of the buffer size. So now my my.cnf looks like this:

# innodb
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M

When I restart the server, I get this error:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 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 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

So my question: Is it safe to delete the old log_files, or is there another method to change the innodb_log_file_size variable?

  • 1
    Just comment the innodb_log_file_size in my.ini ..... – user3988 Oct 20 '11 at 13:24
  • 5
    hmm, why would I want to comment it out to use the default value when I'm trying to change it from the default value? – Derek Downey Oct 20 '11 at 13:32
  • Yes by commenting the innodb_log_file_size line its works.. Thanks. – muhammad umar farooq frank Oct 4 '15 at 16:46
  • 2
    @muhammadumarfarooqfrank Of course it works -- because you're not changing the value of the variable anymore, therefore making the whole point moot. I wish there was a way to downvote comments. – dr01 Oct 26 '16 at 7:54

Yes it is safe to delete the log file once mysqld has been shutdown

In light of this, just perform the following steps:

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop
mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start

Starting up mysqld will recreate ib_logfile0 and ib_logfile1

Give it a Try !!!

UPDATE 2011-10-20 16:40 EDT

It cleanly page out all data in the InnoDB Buffer Pool prior to redoing the Log Files, you should set this option about 1 hour before shutdown:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5+) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. To keep to this option just add it to /etc/my.cnf:

innodb_max_dirty_pages_pct = 0

UPDATE 2013-04-19 16:16 EDT

I updated my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.

  • 1
    Nice answer and the update is great as well. My only suggestion would be to COPY the ib_logfiles to another location in case something goes wrong. This will help you get an idea for how to size the files : mysqlperformanceblog.com/2011/07/09/… – Justin Noel Nov 21 '12 at 19:00
  • 5
    Worked for me as well, BUT: linux console UI can be misleading - mysqld startup takes a lot of time if you set a big log file size (several hundred MB or more). Console UI is showing you dots and then shows "failed!", but in fact MySQL is still starting up. Wait and keep reading the log file (or monitor the log file with "tail -f [log-file]") until you see "mysqld: ready for connections." and both log files allocated on disk. – f055 Dec 6 '12 at 13:55
  • 2
    WARNING!! step 3 didn't work for me and my heart nearly stopped when I saw mysql loading up without InnoDB, Had to stop mysql and remove them manually and start MySQL again. two pieces of advice : 1. backup your already existing log files , 2. delete the files manually – Peeyush Kushwaha Jun 18 '13 at 11:25
  • 2
    Peeyush is correct. Even the mysql documentation recommends backing up your logfiles in case something goes wrong – Greg Jan 13 '15 at 20:53
  • 1
    @Greg this is why I use SET GLOBAL innodb_fast_shutdown = 0;. When MySQL shuts down, everything transactional is flushed from all moving parts, including the redo logs (ib_logfile0 and ib_logfile1). One can keep them. I have yet to encounter issues with completely flushed logs. – RolandoMySQLDBA Jan 13 '15 at 21:21

I would instead recommend the official method, which I reproduce here for convenience:

To change the number or the size of InnoDB log files in MySQL 5.6.7 or earlier, use the following instructions. The procedure to use depends on the value of innodb_fast_shutdown, which determines whether or not to bring the system tablespace fully up-to-date before a shutdown operation:

  • If innodb_fast_shutdown is not set to 2: Stop the MySQL server and make sure that it shuts down without errors, to ensure that there is no information for outstanding transactions in the redo log. Copy the old redo log files to a safe place, in case something went wrong during the shutdown and you need them to recover the tablespace. Delete the old log files from the log file directory, edit my.cnf to change the log file configuration, and start the MySQL server again. mysqld sees that no InnoDB log files exist at startup and creates new ones.

  • If innodb_fast_shutdown is set to 2: Set innodb_fast_shutdown to 1:

mysql> SET GLOBAL innodb_fast_shutdown = 1;

Then follow the instructions in the previous item.

As of MySQL 5.6.8, the innodb_fast_shutdown setting is no longer relevant when changing the number or the size of InnoDB log files. Additionally, you are no longer required remove old log files, although you may still want to copy the old log files to a safe place, as a backup. To change the number or size of InnoDB log files, perform the following steps:

  1. Stop the MySQL server and make sure that it shuts down without errors.

  2. Edit my.cnf to change the log file configuration. To change the log file size, configure innodb_log_file_size. To increase the number of log files, configure innodb_log_files_in_group.

  3. Start the MySQL server again.

If InnoDB detects that the innodb_log_file_size differs from the redo log file size, it will write a log checkpoint, close and remove the old log files, create new log files at the requested size, and open the new log files.

  • This is a good answer as an update to this question. +1 !!! – RolandoMySQLDBA Jun 18 '13 at 15:23
  • 2
    This is not an a "update". These manual pages have long existed. I always recommend first-hand information from the manual (one of the best manuals out there) rather than reinventing the wheel and duplicating information (which is something we DBA's hate the most). – RandomSeed Jun 22 '13 at 9:17
  • This is the preferred method as of MySQL 5.6. If you happen to still be running on a version prior to 5.6, this won't work. – Derek Downey Oct 25 '16 at 14:29

innodb_buffer_pool_size -- simply change my.cnf (my.ini) and restart mysqld.

innodb_log_file_size is less critical. Don't change it unless there is a reason to. Roland provided the steps, but one aspect worries me... I do not know if the first two steps are important; it seems like they could be:

  1. set innodb_fast_shutdown = OFF
  2. restart mysql
  3. stop mysql
  4. remove the logfiles
  5. start mysql

The log files keep track of unfinished business; "innodb_fast_shutdown" says to deal with that stuff after restarting. So removing the files may lose info?

New versions have improved things: (more discussion in Comments)

  • 5.6 Allows for innodb_log_file_size > 4GB
  • 5.6 innodb_log_file_size can be changed without first removing iblog*
  • 5.7 allows for dynamically resizing innodb_buffer_pool_size

Should I change log_file_size?

Use GLOBAL STATUS to compute the number of minutes before the log cycles.

Uptime / 60 * innodb_log_file_size / Innodb_os_log_written`

If it is much less than 60 (minutes), then it might help to increase log_file_size. If it is much more, then the log files are wasting disk space. That "1 hour" is rather arbitrary, so if you are close to it, do not bother changing the log_file_size.

Leave innodb_log_files_in_group at the default of 2.

  • +1 your concern seems to be supported by the docs – Jack Douglas Oct 20 '11 at 14:13
  • I took a look at this answer and I like the first line. I usually had clients bring mysql down and up with --skip-networking as a precaution to get those last-minute changes out the way. Your first line (set innodb_fast_shutdown = OFF) eliminates that. +1 !!! – RolandoMySQLDBA Nov 30 '12 at 22:27
  • 1
    Thanks for the upvotes. New readers may not need this. In 5.6.8, innodb_log_file_size was enhanced to allow changing it without removing the iblog files. – Rick James Aug 30 '15 at 0:44
  • Dis you mean "more critical", not "less critical"? – Igor Mar 21 '16 at 19:31
  • @Igor - No. If you have log_file_size much too small, there will be extra I/O thrashing through it. I rarely see that. If you have it much too big, you are just wasting disk space. The goal in setting it is to cycle through in one hour. But 10 minutes versus 10 hours -- neither matters much. more... – Rick James Mar 21 '16 at 20:05

When you log on to mysql type those commands:

pager grep seq;
show engine innodb status \G select sleep(60); show engine innodb status \G

You will get two numbers. First you get one and then wait for a minute. You will get another.

Say the first one is 3.456.718.123 and the second one is 4.098.873.134

Now (4.098.873.134-3.856.718.123)*60/1024/1024

The result is = 13.856 MB

You have two log files. So divive it by two and you will get a number near 7.000 MB. Just to be sure, set your log file size 8GB

  • 1
    It's not obvious (to me at least) that this actually answers the question. This seems to be a suggestion for an alternate size for the log file, not how to safely change the size of the log file. – RDFozz Dec 29 '17 at 17:54
  • 1
    @RDFozz you are right. This does not answer how to change the log file size. This question answers how to go about figuring out the number to set the innodb_log_file_size. I already answered such a question five years ago (See the subheading Log File Size in dba.stackexchange.com/questions/23189/…) – RolandoMySQLDBA Dec 29 '17 at 18:02
  • I only wanted to help :/ I know its not the exact answer though. – Linux Newbie Dec 29 '17 at 20:23

chown mysql:mysql -R /etc/mysql /var/lib/mysql && cd /var/lib/mysql && rm -f ib_logfile* && service mysql restart || service mysql restart

Try it, guaranteed to be working [tested on Debian 6]

  • 2
    This does not guarantee a clean shutdown. It might work in the average case on a server with only light load, but it is not recommended if you care about your database integrity. – Emil Vikström Feb 23 '15 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.