修改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 2*3072 to 3*6400 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 2*3072 to 3*6400 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 (c) 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)