If you need to change MySQL’s innodb_log_file_size parameter (see How to calculate a good InnoDB log file size), you can’t just change the parameter in the my.cnf file and restart the server. If you do, InnoDB will refuse to start because the existing log files don’t match the configured size.
Changing the innodb_log_file_size safely
You need to shut the server down cleanly and normally and move away (don’t delete) the log files, which are named ib_logfile0, ib_logfile1, and so on. Check the error log to ensure there was no problem shutting down. Then restart the server and watch the error log output carefully. You should see InnoDB print messages saying that the log files don’t exist. It will create new ones and then start. At this point, you can verify that InnoDB is working, and then you can delete the old log files.
The typical error message you’ll see in the client when InnoDB has refused to start due to log file size mismatch looks like this:
1 | ERROR 1033 (HY000): Incorrect information in file... |
That really means that InnoDB hasn’t started, and MySQL is trying to access a table with the InnoDB storage engine, which is failing, of course.
More Resources
Posts
- How to Choose the MySQL innodb_log_file_size
- InnoDB performance optimization basics (redux)
- Here’s my favorite secret MySQL 5.6 feature. What’s yours?
- What is a big innodb_log_file_size?
- Measuring the amount of writes in InnoDB redo logs
Good to remenber !
Official documentation about that : http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html
Just be careful about the innodb_fast_shutdown parameter.
As not every reader may be aware of it, you should probably mention explicitly that you need to disable innodb_fast_shutdown if it was enabled.
Ah, beaten to it already… no comment delete button!
Thanks for the article, ran into the same issue today.
http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html says innodb_fast_shutdown should be set to 1.
Shouldn’t the innodb_fast_shutdown value set to 0 be the one that’s advised before changing innodb logs ?
Since it does full insert buffer merge before shutting down it would be safer way to change innodb_log_file_size and/or innodb_log_files_in_group, am I wrong ?
It’s fine to set it to 1. A clean shutdown is performed. You just don’t want it set to 2, because then there’s basically a crash-only shutdown, and you will need the logs to start up again. You don’t need the logs to start up with it set to 1.
I am running MySQL 5.1 on Debian Squeeze 6.0.
And I can tell you that changing innodb_log_file_size does NOT work for good.
Changed it to 32M yesterday for Zarafa MySQL tuning.
I did not get this expected startup message:
Nov 8 19:45:00 mysqld: 111108 19:45:00 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
Nov 8 19:45:00 mysqld: InnoDB: Setting log file ./ib_logfile0 size to 32 MB
But after deleting ib_logfile0 + ib_logfile1 (after complains about that the sizes do not match) I get this error message and mysqld does NOT startup anymore:
Nov 8 19:19:58 mysqld: 111108 19:19:58 InnoDB: Error: page 23008 log sequence number 0 3522050451
Nov 8 19:19:58 mysqld: InnoDB: is in the future! Current system log sequence number 0 3177053196.
Parameter ‘innodb_fast_shutdown’ has NOT been set to 2, so the default was 1 (0 neither set).
Even trying to set it to 0 couldn’t fix / recreate the innodb logfiles (it did, but they never matched on startup, even when both timestamps where identical). Of course I was able to start the server with “innodb_force_recovery=5” and probably (will not test it today anymore) would be able to create a mysqldump from the datafile. But as I understand it, there is absolutely NO guarantee that this will work in every scenario!
As this is a Debian hosting server migration I started over the weekend, cheers god, mysqldump for the Zarafa database (on 7.0.2 now) has been used before getting to tuning.
So as a final note (my personal view on this topic): Never change innodb_log_file_size in a running production system with no backups at all and rely on, that the logfiles will be easily re-created!
Cheers
Thomas
I don’t seem to be able to able to change the logfile size in a 5.0.95 MySQL database running on RHEL 5.
I add the following line to /etc/my.conf:
innodb_log_file_size = 104857600 #also tried innodb_log_file_size = 64MB
Then:
/sbin/service mysqld stop
Then rename the logfiles:
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
Then:
/sbin/service mysqld start
The newly created logfiles are still 5mb.
I don’t get what i’m doing wrong. I’ve posted this in a few other forums (i think that’s frowned up) but will update with any resolution. I can’t figure out what i’m overlooking. this seems dead simple.
thanks for any help.
chris
It’s quite likely that something simple is happening, like you’re updating the wrong .cnf file. Check the error log for more clues.
Hi Chris,
try this:
innodb_log_file_size = 64M (without the “B” in “MB” string)
This works on my site on a Debian Squeeze system where I have to edit: /etc/mysql/my.cnf
Also make sure that innodb_buffer_pool_size is for performance reasons set to x4 of log file size.
On my system I set to 128M, which does not 100% accurately reflect log file size x 4 factor.
But I can change that options at any time to 256M when I need more “power”.
Not sure what the MySQLDB performance cracks like Baron recommend you to be on the safe / perfect side.
innodb_log_file_size is always a fixed parameter and it is quite difficult to extend at a later time with no trouble if you are not going to choose 64M or more (but e.g instead of 32M x 4 = 128M innodb_buffer_pool_size).
According to my previous posts in this blog a dynamic innodb log file RECREATE did NOT seem to work if you are going to delete the log files later (or even do not move them).
The end result was: I could only use mysqldump to import all the data into a clean/fresh innodb system!
Good luck!
Thomas
Thanks for the suggestion. I don’t see anything suspicious in the /var/log/mysqld.log file which i believe is the error log you are talking about?
120502 11:50:41 [Note] /usr/libexec/mysqld: Shutdown complete
120502 11:50:41 mysqld ended
120502 11:51:16 mysqld started
120502 11:51:16 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait…
120502 11:51:16 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120502 11:51:16 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…
120502 11:51:16 InnoDB: Started; log sequence number 119 647537164
1120502 11:51:17 [Note] /usr/libexec/mysqld: ready for connections.
Version: ‘5.0.95’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Source distribution
Also, I don’t see how MySQL could be using any other conf file other than /etc/my.cnf
[root@tstlx1 mysql]# locate my.cnf
/etc/my.cnf
/etc/my.cnf.old
/etc/my.cnf.rpmnew
[root@tstlx1 mysql]# cat /etc/my.cnf | grep log_file
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
[root@tstlx1 mysql]#
It may not even be using that my.cnf file. Or you may have the options in the wrong section of the file.
huh. something appears to be jacked up with the current my.cnf file.
I renamed it and copied /usr/share/doc/mysql-server-5.0.95/my-medium.cnf to /etc/my.cnf
After doing that and following the procedure i originally posted, the log files were created with the size i specified.
I wonder how many other configuration values have been ignored.
Thanks for your help Baron.
…and thank you Thomas Heiss!
i follow up this post and try to change the innodb setting in my local machine.
1) shutdown mysql
2) change the following parameters in my.ini
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 8M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_fast_shutdown = 1
3) rename the 2 log files (ib_logfile0 and ib_logfile1)
4) restart mysql
I can see the new log file create as 64M now. but i saw some error in mysql_error.log and i need advise whether i can ignore it.
130319 0:10:24 InnoDB: Log file C:\xampp\mysql\data\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file C:\xampp\mysql\data\ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait…
130319 0:10:25 InnoDB: Log file C:\xampp\mysql\data\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file C:\xampp\mysql\data\ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130319 0:10:26 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…
130319 0:10:26 InnoDB: Started; log sequence number 0 65548
130319 0:10:27 [Note] Event Scheduler: Loaded 0 events
130319 0:10:27 [Note] mysql\bin\mysqld.exe: ready for connections.
Version: ‘5.1.33-community’ socket: ” port: 3306 MySQL Community Server (GPL)
Thanks.
I followed the article, mysql is working fine now, but i dont see any innodb tables in phpmyadmin, when “check table” it says table does not exist
@Question: Did your innodb engine startup correctly? Seems to me that you can see any innodb dbs because of this.
I use following paremetes, but the size of four log files never bigger than 5 mb
innodb_log_files_in_group = 4
innodb_log_buffer_size = 256M
any ideas why ?
Hi Sergey, this is an old post (well over 2 years old). A much better place to ask this (and other) questions is our Percona discussion forums: https://www.percona.com/forums/ I look forward to seeing you join our MySQL (and related) conversations there as a member of the Percona community.
It would have been nice if you mention the location of the log files! That’s kind of important. For me it was /var/lib/mysql