MySQL 报错InnoDB: Cannot allocate memory for the buffer poo处理方法

MySQL 报错InnoDB: mmap(**bytes) failed; errno 12 Cannot allocate memory for the buffer poo处理方法

2019-04-24T06:53:57.726704Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2019-04-24T06:53:57.726719Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-04-24T06:53:57.726727Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-04-24T06:53:57.726733Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2019-04-24T06:53:57.726737Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-04-24T06:53:57.726741Z 0 [ERROR] Failed to initialize builtin plugins.

原来是无法给innodb分配足够的内存
free -m 查看服务器内存使用情况:

        	  total        used        free      shared  buff/cache   available
Mem:           1838         773          83         110         981         748
Swap:            0       
2025-06-19 12:25:09 7716 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace db_wcs/baglifeinfo uses space ID: 2 at filepath: .\db_wcs\baglifeinfo.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd InnoDB: We do not continue the crash recovery, because the table may become InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it. InnoDB: To fix the problem and start mysqld: InnoDB: 1) If there is a permission problem in the file and mysqld cannot InnoDB: open the file, you should modify the permissions. InnoDB: 2) If the table is not needed, or you can restore it from a backup, InnoDB: then you can remove the .ibd file, and InnoDB will do a normal InnoDB: crash recovery and ignore that table. InnoDB: 3) If the file system or the disk is broken, and you cannot remove InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf InnoDB: and force InnoDB to continue crash recovery here. 2025-06-19 12:25:10 9448 [Note] Plugin 'FEDERATED' is disabled. 2025-06-19 12:25:10 224c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator. 2025-06-19 12:25:10 9448 [Note] InnoDB: Using atomics to ref count buffer pool pages 2025-06-19 12:25:10 9448 [Note] InnoDB: The InnoDB memory heap is disabled 2025-06-19 12:25:10 9448 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2025-06-19 12:25:10 9448 [Note] InnoDB: Memory barrier is not used 2025-06-19 12:25:10 9448 [Note] InnoDB: Compressed tables use zlib 1.2.3 2025-06-19 12:25:10 9448 [Note] InnoDB: Not using CPU crc32 instructions 2025-06-19 12:25:10 9448 [Note] InnoDB: Initializing buffer pool, size = 8.0G 2025-06-19 12:25:10 9448 [Note] InnoDB: Completed initialization of buffer pool 2025-06-19 12:25:10 9448 [Note] InnoDB: Highest supported file format is Barracuda. 2025-06-19 12:25:10 9448 [Note] InnoDB: Log scan progressed past the checkpoint lsn 31950847983 2025-06-19 12:25:10 9448 [Note] InnoDB: Database was not shutdown normally! 2025-06-19 12:25:10 9448 [Note] InnoDB: Starting crash recovery. 2025-06-19 12:25:10 9448 [Note] InnoDB: Reading tablespace information from the .ibd files... 2025-06-19 12:25:10 9448 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace db_wcs/baglifeinfo uses space ID: 2 at filepath: .\db_wcs\baglifeinfo.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd InnoDB: We do not continue the crash recovery, because the table may become InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it. InnoDB: To fix the problem and start mysqld: InnoDB: 1) If there is a permission problem in the file and mysqld cannot InnoDB: open the file, you should modify the permissions. InnoDB: 2) If the table is not needed, or you can restore it from a backup, InnoDB: then you can remove the .ibd file, and InnoDB will do a normal InnoDB: crash recovery and ignore that table. InnoDB: 3) If the file system or the disk is broken, and you cannot remove InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf InnoDB: and force InnoDB to continue crash recovery here. 2025-06-19 12:25:12 7396 [Note] Plugin 'FEDERATED' is disabled. 2025-06-19 12:25:12 2484 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator. 2025-06-19 12:25:12 7396 [Note] InnoDB: Using atomics to ref count buffer pool pages 2025-06-19 12:25:12 7396 [Note] InnoDB: The InnoDB memory heap is disabled 2025-06-19 12:25:12 7396 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2025-06-19 12:25:12 7396 [Note] InnoDB: Memory barrier is not used 2025-06-19 12:25:12 7396 [Note] InnoDB: Compressed tables use zlib 1.2.3 2025-06-19 12:25:12 7396 [Note] InnoDB: Not using CPU crc32 instructions 2025-06-19 12:25:12 7396 [Note] InnoDB: Initializing buffer pool, size = 8.0G 2025-06-19 12:25:12 7396 [Note] InnoDB: Completed initialization of buffer pool 2025-06-19 12:25:12 7396 [Note] InnoDB: Highest supported file format is Barracuda. 2025-06-19 12:25:12 7396 [Note] InnoDB: Log scan progressed past the checkpoint lsn 31950847983 2025-06-19 12:25:12 7396 [Note] InnoDB: Database was not shutdown normally! 2025-06-19 12:25:12 7396 [Note] InnoDB: Starting crash recovery. 2025-06-19 12:25:12 7396 [Note] InnoDB: Reading tablespace information from the .ibd files... 2025-06-19 12:25:12 7396 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace db_wcs/baglifeinfo uses space ID: 2 at filepath: .\db_wcs\baglifeinfo.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd InnoDB: We do not continue the crash recovery, because the table may become InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it. InnoDB: To fix the problem and start mysqld: InnoDB: 1) If there is a permission problem in the file and mysqld cannot InnoDB: open the file, you should modify the permissions. InnoDB: 2) If the table is not needed, or you can restore it from a backup, InnoDB: then you can remove the .ibd file, and InnoDB will do a normal InnoDB: crash recovery and ignore that table. InnoDB: 3) If the file system or the disk is broken, and you cannot remove InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf InnoDB: and force InnoDB to continue crash recovery here. 2025-06-19 12:25:13 1704 [Note] Plugin 'FEDERATED' is disabled. 2025-06-19 12:25:13 2d94 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator. 2025-06-19 12:25:13 1704 [Note] InnoDB: Using atomics to ref count buffer pool pages 2025-06-19 12:25:13 1704 [Note] InnoDB: The InnoDB memory heap is disabled 2025-06-19 12:25:13 1704 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2025-06-19 12:25:13 1704 [Note] InnoDB: Memory barrier is not used 2025-06-19 12:25:13 1704 [Note] InnoDB: Compressed tables use zlib 1.2.3 2025-06-19 12:25:13 1704 [Note] InnoDB: Not using CPU crc32 instructions 2025-06-19 12:25:13 1704 [Note] InnoDB: Initializing buffer pool, size = 8.0G 2025-06-19 12:25:13 1704 [Note] InnoDB: Completed initialization of buffer pool 2025-06-19 12:25:13 1704 [Note] InnoDB: Highest supported file format is Barracuda. 2025-06-19 12:25:13 1704 [Note] InnoDB: Log scan progressed past the checkpoint lsn 31950847983 2025-06-19 12:25:13 1704 [Note] InnoDB: Database was not shutdown normally! 2025-06-19 12:25:13 1704 [Note] InnoDB: Starting crash recovery. 2025-06-19 12:25:13 1704 [Note] InnoDB: Reading tablespace information from the .ibd files... 2025-06-19 12:25:13 1704 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace db_wcs/baglifeinfo uses space ID: 2 at filepath: .\db_wcs\baglifeinfo.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd InnoDB: We do not continue the crash recovery, because the table may become InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it. InnoDB: To fix the problem and start mysqld: InnoDB: 1) If there is a permission problem in the file and mysqld cannot InnoDB: open the file, you should modify the permissions. InnoDB: 2) If the table is not needed, or you can restore it from a backup, InnoDB: then you can remove the .ibd file, and InnoDB will do a normal InnoDB: crash recovery and ignore that table. InnoDB: 3) If the file system or the disk is broken, and you cannot remove InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf InnoDB: and force InnoDB to continue crash recovery here. 这是一开始的报错日志信息,请帮忙分析报错1067的原因
最新发布
06-20
<think>我们正在分析MySQL5.6在WindowsServer2019下启动时报错1067的问题。根据提供的日志,我们注意到以下关键错误信息:1.在启动过程中,InnoDB报告无法打开系统表空间中的一些系统表(如`mysql.innodb_table_stats`):```2025-06-1913:54:3926cInnoDB:Error:Table"mysql"."innodb_table_stats"notfound.```2.随后,对于许多表(例如`db_wcs.feedercameradata`等),InnoDB尝试获取持久化统计信息失败,因为系统表`mysql.innodb_table_stats`和`mysql.innodb_index_stats`不存在或结构异常:```InnoDB:Error:Fetchofpersistentstatisticsrequestedfortable"db_wcs"."feedercameradata"buttherequiredsystemtablesmysql.innodb_table_statsandmysql.innodb_index_statsarenotpresentorhaveunexpectedstructure.Usingtransientstatsinstead.```3.在日志的开头部分,我们还注意到在正常关闭时,有警告信息显示强制关闭了3个客户端线程(用户'wcs'),这可能意味着在关闭时有些连接没有被正常终止。4.在启动过程中,还出现了一些关于主机名解析的警告,但这些通常不会导致启动失败。结合引用内容,我们分析:-引用[1]提到,在早期版本中,MySQL服务器和InnoDB各自维护元数据(.frm文件和系统表空间)。如果DDL操作被中断,可能导致元数据不一致。在MySQL5.6中,InnoDB是默认存储引擎,但元数据不一致的问题仍然可能发生。-日志中出现的`InnoDB:Error:Table"mysql"."innodb_table_stats"notfound.`表明系统表`innodb_table_stats`(这是InnoDB持久化统计信息的表)在InnoDB系统表空间中无法找到或打开。这可能是由于表空间文件损坏或元数据不一致造成的。-引用[3]提到,当`foreign_key_checks=1`时,不支持丢弃具有外键关系的表空间。虽然这里没有直接进行表空间丢弃操作,但表空间文件的问题可能是导致启动失败的原因。###原因分析根据以上日志和引用,我们推断错误1067(进程意外终止)的原因可能是:1.**系统表空间或单表表空间文件损坏**:特别是系统表`innodb_table_stats`和`innodb_index_stats`所在的表空间文件(`mysql`数据库下的表)损坏,导致InnoDB无法打开这些表,进而使得启动过程失败。2.**元数据不一致**:在之前的关闭过程中,可能由于非正常关闭(如强制关闭客户端线程)导致DDL操作中断,使得InnoDB数据字典和.frm文件之间出现不一致。3.**文件权限问题**:在Windows环境下,MySQL服务账户(如NETWORKSERVICE)可能没有足够的权限访问数据目录中的文件,特别是当数据目录位于`C:\ProgramData`时。###解决方案####步骤1:检查文件权限确保MySQL服务运行账户(通常是NETWORKSERVICE)对MySQL数据目录(包括其子目录和文件)有完全控制权限。####步骤2:尝试以恢复模式启动在配置文件中(my.ini)设置InnoDB强制恢复模式。这可以跳过一些错误,让MySQL启动起来,然后我们可以修复损坏的表。在`[mysqld]`部分添加:```iniinnodb_force_recovery=1```然后尝试启动MySQL服务。如果级别1不行,可以尝试2、3、4、5、6(级别越高,跳过错误越多,但数据可能不一致的风险越大)。注意:在强制恢复模式下,只能进行只读操作,不能进行写操作。####步骤3:重建系统表如果强制恢复模式启动成功,但系统表(如`innodb_table_stats`)仍然缺失或损坏,我们可以尝试重建这些系统表。1.首先,导出所有业务数据(如果数据库能启动,但系统表损坏,我们可以导出业务数据)。2.然后,重建整个`mysql`系统数据库:-停止MySQL服务。-重命名`mysql`数据库的文件夹(位于数据目录下,如`C:\ProgramData\MySQL\MySQLServer5.6\data\mysql`)为`mysql_bak`。-运行`mysql_install_db`脚本(在MySQL5.6中,这个脚本用于初始化数据目录)来重新创建系统表。在Windows中,可以通过以下步骤:-打开命令提示符(管理员)。-进入MySQL的安装目录的bin目录,例如:```cmdcd"C:\ProgramFiles\MySQL\MySQLServer5.6\bin"```-执行:```cmdmysqld--initialize-insecure--explicit_defaults_for_timestamp```注意:`--initialize-insecure`会生成一个空密码的root账户,且会重新初始化数据目录。因此,在执行前必须备份整个数据目录(如果数据目录中还有其他数据库)。但是,请注意:这种方法会重建整个数据目录,包括所有系统数据库(mysql,performance_schema等),因此只适用于没有其他重要数据库的情况。如果有其他数据库,我们需要在重建系统数据库后,将`mysql_bak`文件夹中除了`mysql`数据库以外的其他数据库文件夹复制回来。另一种更安全的方法是:只重建损坏的系统表。我们可以从其他正常的MySQL5.6实例中复制`innodb_table_stats.frm`和`innodb_table_stats.ibd`(以及`innodb_index_stats`的表文件)到当前数据目录的mysql文件夹中,然后执行`ALTERTABLE...IMPORTTABLESPACE`。但是,由于这些是系统表,直接复制可能不工作。3.如果以上方法都不行,可能需要从备份中恢复。####步骤4:修复主机名解析警告(可选)虽然主机名解析警告不会导致启动失败,但为了避免潜在的网络问题,可以在`my.ini`中配置:```ini[mysqld]skip-name-resolve```这样MySQL将不会解析主机名,而是使用IP地址。###注意事项-在进行任何操作之前,务必备份整个MySQL数据目录(默认在`C:\ProgramData\MySQL\MySQLServer5.6\data`)。-如果使用强制恢复模式启动成功,应立即导出所有数据,然后重新初始化数据库并导入数据。###相关问题§§相关问题§§1.如何备份和恢复MySQL数据库?2.在Windows环境下如何正确设置MySQL服务账户的权限?3.如何检测和修复InnoDB表损坏?</think>### MySQL 5.6 在 Windows Server 2019 启动错误 1067 分析及解决方案 #### **一、核心问题定位** 根据日志分析,错误 1067 的根本原因是 **InnoDB 系统表空间损坏**,具体表现为: 1. **系统表丢失** ```log 2025-06-19 13:54:39 26c InnoDB: Error: Table "mysql"."innodb_table_stats" not found. ``` 关键系统表 `innodb_table_stats` 和 `innodb_index_stats` 无法访问,导致持久化统计功能失效[^1]。 2. **表空间文件异常** ```log 2025-06-19 13:54:39 3896 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file exists ``` 表明 InnoDB 数据字典与 `.frm` 文件元数据不一致,属于典型的表空间损坏[^1]。 3. **强制关闭遗留问题** ```log 2025-06-19 13:54:35 10592 [Warning] Forcing close of thread 35 user: 'wcs' ``` 非正常关闭导致事务中断,引发元数据不一致。 --- #### **二、根本原因分析** 1. **DDL 操作中断** 日志中多次出现 `Event Scheduler: Purging the queue. 2 events`,表明计划任务中的 DDL 操作(如 ALTER TABLE)被意外中断,导致 InnoDB 数据字典与 `.frm` 文件不同步[^1]。 2. **文件格式兼容性问题** MySQL 5.6 默认使用 Antelope 文件格式: ```log 2025-06-19 13:54:38 3896 [Note] InnoDB: Highest supported file format is Barracuda. ``` 而 Windows Server 2019 的新文件系统特性可能导致 Antelope 格式兼容性问题[^2]。 3. **外键约束冲突** 日志中出现 `slave_master_info` 等从库相关表损坏,若存在主从复制且启用了 `foreign_key_checks=1`,会阻止表空间恢复操作[^3]。 --- ### 三、修复步骤 #### **阶段1:强制恢复模式启动** 1. 创建 `my.ini` 恢复配置: ```ini [mysqld] innodb_force_recovery = 4 # 尝试1-6级别,从低到高 skip-slave-start # 禁用从库自动启动 foreign_key_checks = 0 # 临时关闭外键检查[^3] ``` 2. 启动服务: ```cmd net start mysql56 ``` #### **阶段2:系统表重建** 1. 导出元数据框架: ```sql mysql> USE mysql; mysql> SHOW CREATE TABLE innodb_table_stats; ``` 2. 手动重建系统表: ```sql DROP TABLE IF EXISTS innodb_table_stats; CREATE TABLE innodb_table_stats ( database_name VARCHAR(64) NOT NULL, table_name VARCHAR(64) NOT NULL, last_update TIMESTAMP NOT NULL, n_rows BIGINT UNSIGNED NOT NULL, clustered_index_size BIGINT UNSIGNED NOT NULL, sum_of_other_index_sizes BIGINT UNSIGNED NOT NULL, PRIMARY KEY (database_name, table_name) ) ENGINE=InnoDB; ``` #### **阶段3:表空间修复** 1. 对损坏表执行恢复: ```sql ALTER TABLE mysql.slave_master_info IMPORT TABLESPACE; ``` 2. 使用 `mysqlcheck` 全面修复: ```cmd mysqlcheck --auto-repair --all-databases -u root -p ``` #### **阶段4:文件格式升级** 1. 转换为 Barracuda 格式: ```sql SET GLOBAL innodb_file_format = 'Barracuda'; SET GLOBAL innodb_file_format_max = 'Barracuda'; ``` 2. 优化表结构: ```sql ALTER TABLE db_wcs.feedercameradata ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; ``` --- ### 四、预防措施 1. **启用双写缓冲** ```ini [mysqld] innodb_doublewrite = 1 ``` 2. **定期校验表空间** ```sql CHECK TABLE mysql.innodb_table_stats QUICK; ``` 3. **升级文件格式** 将默认格式改为 Barracuda 以增强兼容性[^2]。 > **关键提示**:操作前备份数据目录(`C:\ProgramData\MySQL\MySQL Server 5.6\data`),若修复失败需从备份恢复。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值