Correct resizing of the innodb log (innodb_log_file_size)

During operation, innodb writes all changed data not immediately to the database files, but initially dumps everything into a binary log (option innodb_log_file). This allows you to increase the speed of work, because writing to a table file is more laborious than writing to a log file. In addition, logging allows you to write to the table file with consecutive pieces of data, serve mysql clients faster (received data, recorded it in a log, reported to the client that everything is OK)

If the server crashes, this log file allows you to roll back the damaged (incomplete) transactions. The larger the log file is, the more operations are stored in it, and the more time for innodb to view / analyze the correctness of the last shutdown.

The default binary log file innodb has a capacity of 5 MB:

  mysql -e "show variables like 'innodb_log_file_size'"
 + ---------------------- + --------- +
 |  Variable_name |  Value |
 + ---------------------- + --------- +
 |  innodb_log_file_size |  5242880 | 
 + ---------------------- + --------- + 

To change its volume, you must perform the following operations (from the root user):

1. Correctly stop the mysql server:

  # for Debian (Ubuntu)
 /etc/init.d/mysql stop 
 # for CentOS
 /etc/init.d/mysqld stop 

2. Modify / add a parameter in the configuration file (/etc/mysql/my.cnf - Debian (Ubuntu), /etc/my.cnf - CentOS):

  [mysqld]
 innodb_log_file_size = 64M 

3. It is important! Rename existing log files. Otherwise, when loading, innodb will report that the log file is damaged:

  mv / var / lib / mysql / ib_logfile0 / var / lib / mysql / ib_logfile0_old
 mv / var / lib / mysql / ib_logfile1 / var / lib / mysql / ib_logfile1_old 

4. Start the mysql server.

  # for Debian (Ubuntu)
 /etc/init.d/mysql start 
 # for CentOS
 /etc/init.d/mysqld start 

5. Check for errors in the mysql daemon log file:

  # tail -n 100 /var/log/mysqld.log # for CentOS 
  ... 
  130730 13:17:35 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: wait ...
 130730 13:17:35 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: wait ...
 130730 13:17:37 InnoDB: highest supported file format is Barracuda.
 InnoDB: ibdata files does not match the log sequence
 InnoDB: the log sequence number in the ib_logfiles!

As can be seen from the log, innodb has created new binary logs ib_logfile0 and ib_logfile1 of new volume.