InnoDB: which exceeds the log group capacity

有时你会遇到类似下面的错误：

120414  2:20:34  InnoDB: ERROR: the age of the last checkpoint is 241588252,InnoDB: which exceeds the log group capacity 241588224.InnoDB: If you are using big BLOB or TEXT rows, you must set the   InnoDB: combined size of log files at least 10 times bigger than theInnoDB: largest such row.

这个错误的原因：

 系统中存在较大的事物，但是innodb_log_file_size设置太小。

解决方法：

  加大redo日志大小。

01) 修改my.cnf

这个需要根据实际情况调整。下面给个例子

[mysqld]innodb_log_buffer_size          = 32Minnodb_buffer_pool_size         = 3Ginnodb_log_file_size            = 768M

注意：innodb_log_file_size在5.5时默认是5M,在5.7时默认是48M。并且必须大于4M，小于innodb_buffer_pool_size/N(这个N为一个日志组中的日志数)。

并且innodb_log_file_size * innodb_log_files_in_group不能大于512G

这里的目的是要加大，innodb_log_file_size，但是也必须考虑innodb_buffer_pool_size对其的影响。

02)设置innodb_fast_shutdown

mysql> SET GLOBAL innodb_fast_shutdown = 0;

在关闭数据量前将innodb_fast_shutdown设置为0是为了完全干净的关闭数据库，具体可以看下面关于innodb_fast_shutdown的解释。

03) 关闭数据库 

   service mysql stop

04)删除当前的redo文件（ib_logfile*）
05)启动数据库 

service mysql start

5.7官方文档：

innodb_log_file_size

Command-Line Format--innodb_log_file_size=#System VariableNameinnodb_log_file_sizeVariable ScopeGlobalDynamic VariableNoPermitted Values (<= 5.7.10)TypeintegerDefault50331648Min Value1048576Max Value512GB / innodb_log_files_in_groupPermitted Values (>= 5.7.11)TypeintegerDefault50331648Min Value4194304Max Value512GB / innodb_log_files_in_group

The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, would allow you to approach the limit but not exceed it. The default value is 48MB. Sensible values range from 4MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration. For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

 innodb_fast_shutdown

Command-Line Format--innodb_fast_shutdown[=#]System VariableNameinnodb_fast_shutdownVariable ScopeGlobalDynamic VariableYesPermitted ValuesTypeintegerDefault1Valid Values012

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.