MySQL how to change innodb-log-file-size

Question

According to the mysql documentation (Docs), in order to change innodb-log-file-size in step #4 I need to delete the binary logs. I have some concerns and questions about this. My current value for innodb-log-file-size is 5MB. So I would assume my binary log files are 5MB each (max). When I look at the bin-log directory I have a bunch of file names like 'mysql-bin.000001', 'mysql-bin.000002', etc. I believe these are the binary log files, but they are all quite a bit larger than 5MB. There are 2 files (ib_logfile0, ib_logfile1) that are 5 MB. So my question is

1. Which of those files is my 'binary log'?
2. Which of those do I need to delete?

Thanks in advance

Answer 1

The InnoDB log is in ib_logfile0 and ib_logfile1. These are the files sized by innodb_log_file_size.

To resize the InnoDB logs, you first need to shut down mysqld cleanly. That will make sure that any changes in the log have already been flushed into your tablespaces. The clean shutdown is important, because if you don't do this step, you have a high chance of losing data.

After you have shut down mysqld cleanly, the ib_logfiles are superfluous. You must rm them to change their size.

As you restart mysqld, InnoDB notices that the files are missing, and creates new file at the new size according to the innodb_log_file_size variable in your my.cnf file. So make sure you edit that file before you restart, or else it'll just create new 5MB files.

MySQL 5.6 makes this process a little bit simpler. You don't need to rm the log files, but you do need to restart mysqld to make a new log file size take effect. The way it works in 5.6 is that if the size of these files is different from the config variable, MySQL automatically does another clean restart (to make sure the files don't contain any changes that are unflushed), and then InnoDB resizes the files upon the final startup.

The other files (mysql-bin.000001, etc.) are binary logs. These may grow up to max_binlog_size (which is 1GB by default), but the binary logs vary in size because new logs are created whenever you restart mysqld or execute FLUSH LOGS. Anyway, they have nothing to do with the InnoDB logs.

PS: You might like this article: How to calculate a good InnoDB log file size.