8

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user633077
  • 231
  • 1
  • 2
  • 12

2 Answers2

13

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks Bill - I have read that article and I am all 'ready' to change the size. I just want to make sure I understand all pieces involved before changing this stuff. Is it critical for innodb_log_file_size to be the same on masters and slaves? – user633077 Nov 11 '13 at 17:17
  • No, there's no strict need for the log file size to be the same on master and slave. But they are handling the same rate of incoming changes, so it makes sense that they would be similar in size. – Bill Karwin Nov 11 '13 at 18:11
0

As per official document and it works for me for MySQL 5.7.30 To change the number or the size of your InnoDB redo log files, perform the following steps:

  • Stop the MySQL server and make sure that it shuts down without errors.
  • 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.
  • Start the MySQL server again.
  • If InnoDB detects that the innodb_log_file_size differs from the redo log file size, it writes a log checkpoint, closes and removes the old log files, creates new log files at the requested size, and opens the new log files.
ImranRazaKhan
  • 1,955
  • 5
  • 33
  • 74