MySQL 修改InnoDB重做日志文件的数量或大小

修改InnoDB重做日志文件的数量或大小
修改你的InnoDB重做日志文件的数量或大小，请执行以下步骤:
1.停止MySQL服务器，并确保它没有错误地关闭

mysql> show variables like ‘innodb_log_file%’;
±--------------------------±---------+
| Variable_name | Value |
±--------------------------±---------+
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
±--------------------------±---------+
2 rows in set (0.00 sec)
[root@localhost ~]# service mysqld stop
Shutting down MySQL… SUCCESS!
2.编辑my.cnf以更改日志文件配置。如果需要修改日志文件大小，请配置innodb_log_file_size。要增加日志文件的数量，可以配置

innodb_log_files_in_group。
[mysql@localhost mysql]$ vi my.cnf
…
innodb_log_file_size=100m
innodb_log_files_in_group=3
3.重新启动MySQL服务器
如果InnoDB检测到innodb_log_file_size大小与重做日志文件大小不同，它会写一个日志检查点，关闭并删除旧的日志文件，创建新的大小的志文件，并打开新的日志文件。

[root@localhost ~]# service mysqld start
Starting MySQL… SUCCESS!
日志文件显示如下:

[mysql@localhost mysql]$ tail -f mysql.err
2022-03-02T02:44:15.775252Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2022-03-02T02:44:15.775476Z 0 [Note] /mysqlsoft/mysql/bin/mysqld (mysqld 5.7.26-log) starting as process 613 …
2022-03-02T02:44:15.787224Z 0 [Note] InnoDB: PUNCH HOLE support available
2022-03-02T02:44:15.787311Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-03-02T02:44:15.787376Z 0 [Note] InnoDB: Uses event mutexes
2022-03-02T02:44:15.787395Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2022-03-02T02:44:15.787411Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-03-02T02:44:15.788175Z 0 [Note] InnoDB: Number of pools: 1
2022-03-02T02:44:15.788515Z 0 [Note] InnoDB: Using CPU crc32 instructions
2022-03-02T02:44:15.793577Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-03-02T02:44:15.812360Z 0 [Note] InnoDB: Completed initialization of buffer pool
2022-03-02T02:44:15.817437Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-03-02T02:44:15.862640Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2022-03-02T02:44:16.043532Z 0 [Warning] InnoDB: Resizing redo log from 23072 to 36400 pages, LSN=2494312
2022-03-02T02:44:16.169341Z 0 [Warning] InnoDB: Starting to delete and rewrite log files.
2022-03-02T02:44:16.200462Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100 MB
2022-03-02T02:44:16.200745Z 0 [Note] InnoDB: Progress in MB:
100
2022-03-02T02:44:26.280622Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 100 MB
2022-03-02T02:44:26.281005Z 0 [Note] InnoDB: Progress in MB:
100
2022-03-02T02:44:37.028157Z 0 [Note] InnoDB: Setting log file ./ib_logfile2 size to 100 MB
2022-03-02T02:44:37.029053Z 0 [Note] InnoDB: Progress in MB:
100
2022-03-02T02:44:47.532468Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2022-03-02T02:44:47.532675Z 0 [Warning] InnoDB: New log files created, LSN=2494312
2022-03-02T02:44:47.533502Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-03-02T02:44:47.533760Z 0 [Note] InnoDB: Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
2022-03-02T02:44:48.119249Z 0 [Note] InnoDB: File ‘./ibtmp1’ size is now 12 MB.
2022-03-02T02:44:48.121693Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2022-03-02T02:44:48.121740Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2022-03-02T02:44:48.122598Z 0 [Note] InnoDB: Waiting for purge to start
2022-03-02T02:44:48.172771Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 32356ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2022-03-02T02:44:48.186082Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2494303
2022-03-02T02:44:48.186431Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysqldata/mysql/ib_buffer_pool
2022-03-02T02:44:48.186776Z 0 [Note] Plugin ‘FEDERATED’ is disabled.
2022-03-02T02:44:48.366419Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2022-03-02T02:44:48.367248Z 0 [Warning] CA certificate ca.pem is self signed.
2022-03-02T02:44:48.369110Z 0 [Note] Server hostname (bind-address): ‘*’; port: 3306
2022-03-02T02:44:48.371083Z 0 [Note] IPv6 is available.
2022-03-02T02:44:48.371145Z 0 [Note] - ‘::’ resolves to ‘::’;
2022-03-02T02:44:48.371213Z 0 [Note] Server socket created on IP: ‘::’.
2022-03-02T02:44:48.430720Z 0 [Note] InnoDB: Buffer pool(s) load completed at 220302 10:44:48
2022-03-02T02:44:48.434777Z 0 [Note] Failed to start slave threads for channel ‘’
2022-03-02T02:44:48.452218Z 0 [Note] Event Scheduler: Loaded 0 events
2022-03-02T02:44:48.452566Z 0 [Note] /mysqlsoft/mysql/bin/mysqld: ready for connections.
Version: ‘5.7.26-log’ socket: ‘/mysqlsoft/mysql/mysql.sock’ port: 3306 Source distribution
其中以下部分显示了服务器重置日志文件的大小与数量

2022-03-02T02:44:16.043532Z 0 [Warning] InnoDB: Resizing redo log from 23072 to 36400 pages, LSN=2494312
2022-03-02T02:44:16.169341Z 0 [Warning] InnoDB: Starting to delete and rewrite log files.
2022-03-02T02:44:16.200462Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100 MB
2022-03-02T02:44:16.200745Z 0 [Note] InnoDB: Progress in MB:
100
2022-03-02T02:44:26.280622Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 100 MB
2022-03-02T02:44:26.281005Z 0 [Note] InnoDB: Progress in MB:
100
2022-03-02T02:44:37.028157Z 0 [Note] InnoDB: Setting log file ./ib_logfile2 size to 100 MB
2022-03-02T02:44:37.029053Z 0 [Note] InnoDB: Progress in MB:
100
2022-03-02T02:44:47.532468Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2022-03-02T02:44:47.532675Z 0 [Warning] InnoDB: New log files created, LSN=2494312
[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution
Copyright © 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show variables like ‘innodb_log_file%’;
±--------------------------±----------+
| Variable_name | Value |
±--------------------------±----------+
| innodb_log_file_size | 104857600 |
| innodb_log_files_in_group | 3 |
±--------------------------±----------+
2 rows in set (0.01 sec)