如何修改 5.7

• 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 file.

上面这段话取自mysql5.7官方手册。

• 确保干净的关闭数据库
• 修改my.cnf 文件innodb_log_file_size，innodb_log_files_in_group
• 启动数据库

调整redo log 大小官方 guidelines

• Make your redo log files big, even as big as the buffer pool. When InnoDB has written the redo log files full, it must write the modified contents of the buffer pool to disk in a checkpoint. Small redo log files cause many unnecessary disk writes. Although historically big redo log files caused lengthy recovery times, recovery is now much faster and you can confidently use large redo log files.
• Consider increasing the size of the log buffer. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. Log buffer size is configured using the innodb_log_buffer_size configuration option.

这是官方给出的两条guidelines，大意是redo log越大越好，甚至可以配置到和buffer pool一样的大小。过小的redo log会带来许多不必要磁盘写入。事实上来看，确实redo log files大有许多的好处，带来的坏处就是系统一旦崩溃，恢复的时间比较长。同时考虑调整innodb_log_buffer_size参数来支持处理大事物的能力。

官方的说法比较模糊，现实中也不可能调整redo log files至 buff pool的大小，我们实际上需要更为精确的计算方法。

具体的计算方法

选取业务高峰期计算产生的日志大小，redo log 的大小最少应该承载一个小时的业务日志。

计算lsn（ log sequence number）

mysql> pager grep sequence;
PAGER set to 'grep sequence'
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G;
Log sequence number 2133421300
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 2160035601
1 row in set (0.00 sec)

mysql> select (2160035601-2133421300)/1024/1024 as MB_per_min;
+-------------+
| MB_per_min  |
+-------------+
| 25.38137531 |
+-------------+
1 row in set (0.00 sec)

一个小时的日志量是1500M，默认innodb_log_files_in_group =2，则一个innodb_log_file_size =750M。我们一般取2的整数倍，所以
innodb_log_files_in_group =2
innodb_log_file_size =1024M

对于写入很频繁的数据库，设置比较大redo log file 是一个比较明智的措施。

网上有很多说法可以去计算 Innodb_os_log_written（ Innodb_os_log_written is the number of bytes written to the redo logs）的大小，实际上 Innodb_os_log_written一般都是lsn的几倍，建议还是使用lsn计算，具体信息可以参考链接：
link

有个有趣的现象innodb_buffer_pool_instances这个值比较大时，实际上会产生比较多的log，这时可以调高logsize大小。

参考链接：
link