InnoDB数据字典操作进行故障排除

本文记录了一次因停电导致MySQL服务启动失败的问题及解决过程。通过更改数据存储位置并利用dbsake和.ibd文件成功恢复了丢失的数据表。

InnoDB数据字典操作进行故障排除

InnoDB数据字典操作进行故障排除

  1. 事情是这样的,因为昨天晚上停电,导致服务器全部关机,早上一到公司就把所有服务启动,当我启动爬虫任务的时候,发现采集提示数据库入库失败,mysql
    提示连接失败。我一看这提示应该是mysql服务没启动,我赶忙进入宝塔看看MySQL什么情况,果然是关闭状态,马上重启却重启失败,我看了看错误日志,好像是缺少了启动需要文件,我突然记起前天我手动改了配置文件数据存储位置,而没有使用宝塔提供的数据迁移功能

在这里插入图片描述

  1. 可能手动修改会造成问题,但当时没有问题,我想切换回原来目录应该就可以启动了,说干就干,使用迁移功能切换回原来储存目录,果然能行,服务启动了。
    但是第二个问题来,我切换回来后启动采集服务,mysql提示采集数据库表不存在,我马上打开数据库发现所有采集数据表都打不开提示表不存在,查看mysql错误日志提示:Cannot
    open table bk_esf_xq/bk_esf_copy from the internal data dictionary
    of InnoDB though the .frm file for the table exists. See
    http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
    for how you can resolve the problem.

  2. 它说无法从InnoDB的内部数据字典中打开表bk_esf_xq/bk_esf_copy,尽管表存在.frm文件,
    .frm 是mysql数据表结构文件.
    如果你没有数表结构备份可以使用dbsake提取.frm文件表结构,这是偶然发现的一个工具,文档中它这样介绍自己:dbsake - a (s)wiss-(a)rmy-(k)nif(e) for MySQL
    作者一定是一个对 mysql 很有心得的人,工具从下载,安装到使用,简单,利落。

$ curl -s get.dbsake.net > dbsake

$ chmod u+x dbsake

$ ./dbsake frmdump [frm-file-path]
  1. MySQL提供了解决问题的连接http://dev.mysql.com/doc/refma
2025-10-28T20:39:45.659482+08:00 15 [ERROR] InnoDB: Operating system error number 32 in a file operation. 2025-10-28T20:39:45.660451+08:00 15 [ERROR] InnoDB: The error means that another program is using InnoDB's files. This might be a backup or antivirus software or another instance of MySQL. Please close it to get rid of this error. 2025-10-28 20:39:55 0x740c InnoDB: Assertion failure in thread 29708 in file handler0alter.cc line 7404 InnoDB: Failing assertion: error == DB_SUCCESS InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 2025-10-28T20:39:55.663376+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9553ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.) 12:39:55 UTC - mysqld got exception 0x80000003 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. key_buffer_size=536870912 read_buffer_size=131072 max_used_connections=51 max_threads=600 thread_count=50 connection_count=50 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 761977 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x26ebec94ce0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... 7ff754c49812 mysqld.exe!my_sigabrt_handler()[my_thr_init.c:449] 7ff754fee349 mysqld.exe!raise()[winsig.c:587] 7ff754fed240 mysqld.exe!abort()[abort.c:82] 7ff754d49b08 mysqld.exe!ut_dbg_assertion_failed()[ut0dbg.cc:67] 7ff754e2f72f mysqld.exe!ha_innobase::commit_inplace_alter_table()[handler0alter.cc:8275] 7ff7545c1443 mysqld.exe!mysql_inplace_alter_table()[sql_table.cc:7449] 7ff7545bf17a mysqld.exe!mysql_alter_table()[sql_table.cc:9538] 7ff7546e565e mysqld.exe!Sql_cmd_alter_table::execute()[sql_alter.cc:316] 7ff754546aa5 mysqld.exe!mysql_execute_command()[sql_parse.cc:3521] 7ff75454938a mysqld.exe!mysql_parse()[sql_parse.cc:5525] 7ff75454239d mysqld.exe!dispatch_command()[sql_parse.cc:1432] 7ff7545433aa mysqld.exe!do_command()[sql_parse.cc:999] 7ff7545081e4 mysqld.exe!handle_connection()[connection_handler_per_thread.cc:301] 7ff754f2cd92 mysqld.exe!pfs_spawn_thread()[pfs.cc:2191] 7ff754c4967b mysqld.exe!win_thread_start()[my_thread.c:38] 7ff754feddbf mysqld.exe!_callthreadstartex()[threadex.c:376] 7ff754fee00a mysqld.exe!_threadstartex()[threadex.c:354] 7ffaba1f84d4 KERNEL32.DLL!BaseThreadInitThunk() 7ffabc6ee871 ntdll.dll!RtlUserThreadStart() Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (26ebece8580): ALTER TABLE formmain_0495 DROP COLUMN field0028 Connection ID (thread ID): 15 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 2025-10-28T20:43:23.759825+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2025-10-28T20:43:23.759825+08:00 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. 2025-10-28T20:43:23.760771+08:00 0 [Note] D:\newmysql\mysql-5.7.12-winx64\bin\mysqld (mysqld 5.7.12-log) starting as process 39684 ... 2025-10-28T20:43:23.818393+08:00 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2025-10-28T20:43:23.819370+08:00 0 [Note] InnoDB: Uses event mutexes 2025-10-28T20:43:23.819370+08:00 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier 2025-10-28T20:43:23.820343+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 2025-10-28T20:43:23.821322+08:00 0 [Note] InnoDB: Number of pools: 1 2025-10-28T20:43:23.822294+08:00 0 [Note] InnoDB: Not using CPU crc32 instructions 2025-10-28T20:43:23.866243+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 8G, instances = 8, chunk size = 128M 2025-10-28T20:43:24.265683+08:00 0 [Note] InnoDB: Completed initialization of buffer pool 2025-10-28T20:43:24.414092+08:00 0 [Note] InnoDB: Highest supported file format is Barracuda. 2025-10-28T20:43:24.508818+08:00 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 933908534 2025-10-28T20:43:24.633818+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 933994745 2025-10-28T20:43:24.639683+08:00 0 [Note] InnoDB: Ignoring data file '.\oa\formmain_0495.ibd' with space ID 1585. Another data file called .\oa\#sql-ib1600-4242521553.ibd exists with the same space ID. 2025-10-28T20:43:24.641629+08:00 0 [Note] InnoDB: Ignoring data file '.\oa\formmain_0495.ibd' with space ID 1583. Another data file called .\oa\#sql-ib1602-4242521554.ibd exists with the same space ID. 2025-10-28T20:43:24.643587+08:00 0 [Note] InnoDB: Ignoring data file '.\oa\formmain_0495.ibd' with space ID 1585. Another data file called .\oa\#sql-ib1600-4242521553.ibd exists with the same space ID. 2025-10-28T20:43:24.647490+08:00 0 [Note] InnoDB: Ignoring data file '.\oa\formmain_0495.ibd' with space ID 1583. Another data file called .\oa\#sql-ib1602-4242521554.ibd exists with the same space ID. 2025-10-28T20:43:24.661159+08:00 0 [Note] InnoDB: Database was not shutdown normally! 2025-10-28T20:43:24.661159+08:00 0 [Note] InnoDB: Starting crash recovery. 2025-10-28T20:43:24.844755+08:00 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 47 row operations to undo 2025-10-28T20:43:24.845734+08:00 0 [Note] InnoDB: Trx id counter is 14848 2025-10-28T20:43:24.859405+08:00 0 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 2025-10-28T20:43:25.704135+08:00 0 [Note] InnoDB: Apply batch completed 2025-10-28T20:43:32.680689+08:00 0 [Note] InnoDB: Starting in background the rollback of uncommitted transactions 2025-10-28T20:43:32.681665+08:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2025-10-28T20:43:32.681665+08:00 0 [Note] InnoDB: Rolling back trx with id 14454, 47 rows to undo 2025-10-28T20:43:32.682647+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2025-10-28T20:43:32.684595+08:00 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2025-10-28T20:43:32.695334+08:00 0 [Note] InnoDB: Rollback of trx with id 14454 completed 2025-10-28T20:43:32.696312+08:00 0 [Note] InnoDB: Rollback of non-prepared transactions completed 2025-10-28T20:43:32.732452+08:00 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB. 2025-10-28T20:43:32.757857+08:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2025-10-28T20:43:32.758821+08:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2025-10-28T20:43:32.760764+08:00 0 [Note] InnoDB: Waiting for purge to start 2025-10-28T20:43:32.812522+08:00 0 [Note] InnoDB: 5.7.12 started; log sequence number 933994745 2025-10-28T20:43:32.812522+08:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8438ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.) 2025-10-28T20:43:32.813529+08:00 0 [Note] Plugin 'FEDERATED' is disabled. 2025-10-28T20:43:32.813529+08:00 0 [Note] InnoDB: Loading buffer pool(s) from D:\newmysql\mysql-5.7.12-winx64\data\ib_buffer_pool 2025-10-28T20:43:32.830109+08:00 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key 2025-10-28T20:43:32.832067+08:00 0 [Note] Server hostname (bind-address): '*'; port: 3306 2025-10-28T20:43:32.834022+08:00 0 [Note] IPv6 is available. 2025-10-28T20:43:32.834022+08:00 0 [Note] - '::' resolves to '::'; 2025-10-28T20:43:32.835011+08:00 0 [Note] Server socket created on IP: '::'. 2025-10-28T20:43:32.854535+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 251028 20:43:32 2025-10-28T20:43:32.904319+08:00 0 [Note] Event Scheduler: Loaded 0 events 2025-10-28T20:43:32.905296+08:00 0 [Note] D:\newmysql\mysql-5.7.12-winx64\bin\mysqld: ready for connections. Version: '5.7.12-log' socket: '' port: 3306 MySQL Community Server (GPL)
最新发布
10-29
<think>我们面对的问题是在MySQL 5.7.12中执行`ALTER TABLE formmain_0495 DROP COLUMN field0028`语句时,出现了崩溃问题,包括InnoDB文件被占用、断言失败、内存陷阱等错误。根据引用内容,我们可以从以下几个方面来分析和解决: ### 问题分析 1. **InnoDB存储引擎恢复状态**:根据引用[2],当InnoDB出现崩溃时,我们可以通过调整`innodb_force_recovery`参数来尝试启动MySQL服务,并导出数据。该参数有6个级别(1-6),级别越高,强制恢复的程度越大,但数据损坏的风险也越高。 2. **表结构损坏**:错误信息中提到了`innodb_table_stats`表(引用[4]),这表明系统表可能出现了损坏。 3. **共享表空间问题**:引用[1]和[3]提到了`innodb_file_per_table`设置,如果设置为OFF,则所有表的数据都存放在共享表空间ibdata1中。如果共享表空间损坏,恢复难度较大。 ### 解决步骤 #### 步骤1: 尝试使用innodb_force_recovery启动MySQL 1. **停止MySQL服务**(如果正在运行): ```bash net stop mysql ``` 2. **修改配置文件my.ini**(或my.cnf): 在`[mysqld]`部分添加: ```ini innodb_force_recovery = 1 ``` 保存并尝试启动服务: ```bash net start mysql ``` 如果启动失败,逐步增加`innodb_force_recovery`的值(从2到6),直到能启动为止。 3. **启动后立即备份数据**: - 使用`mysqldump`备份所有数据库: ```bash mysqldump -u root -p --all-databases > all_dbs_backup.sql ``` - 特别注意备份出问题的表`formmain_0495`。 4. **注意**: - 在`innodb_force_recovery`模式下,MySQL处于只读状态,不能执行写操作。 - 完成备份后,移除`innodb_force_recovery`设置,并正常重启MySQL。 #### 步骤2: 修复系统表 如果错误涉及到系统表(如`innodb_table_stats`),可以尝试修复系统表: 1. 启动MySQL后,执行: ```sql mysql_upgrade -u root -p ``` 这会检查并修复系统表。 2. 如果上述方法无效,可以考虑重新初始化系统表(注意:这将重置权限等系统数据): - 停止MySQL服务。 - 备份整个数据目录(特别是ibdata1、ib_logfile*等)。 - 删除`mysql`数据库目录(通常是数据目录下的`mysql`文件夹)。 - 重新初始化系统数据库: ```bash mysqld --initialize-insecure --user=mysql ``` - 启动MySQL服务,然后运行`mysql_upgrade`。 #### 步骤3: 修复用户表 如果问题表`formmain_0495`损坏,可以尝试以下方法: 1. **使用REPAIR TABLE**: ```sql REPAIR TABLE formmain_0495; ``` 注意:此命令对InnoDB表通常无效,但可以尝试。 2. **重建表**: 如果表损坏,可以尝试通过重建表来修复: ```sql ALTER TABLE formmain_0495 ENGINE=InnoDB; ``` #### 步骤4: 恢复备份 如果以上步骤不能解决问题,并且你有备份,则可以: 1. 创建一个新的数据库实例。 2. 将备份导入到新实例中。 3. 在新实例中执行原先导致崩溃的`ALTER TABLE`语句,看是否仍然出错。如果在新环境中不出错,则可能是原环境的数据文件损坏。 #### 步骤5: 使用专业工具恢复 如果数据非常重要且没有备份,可以考虑使用专业的数据恢复工具(如undrop-for-innodb,引用[1][5])来恢复数据。但此过程复杂且成功率不确定。 ### 注意事项 1. **备份第一**:在进行任何修复操作前,尽可能备份当前数据文件(整个数据目录)。 2. **错误日志**:检查MySQL错误日志(通常位于数据目录下的`hostname.err`文件),它可能提供更详细的错误信息。 3. **硬件检查**:频繁崩溃可能是硬件问题(如磁盘故障)导致,建议检查磁盘健康状况。 ### 相关问题 §§ 相关问题 §§ 1. 如何设置`innodb_force_recovery`参数?各个级别的含义是什么? 2. MySQL系统表损坏有哪些常见修复方法? 3. 如何安全地执行ALTER TABLE操作以避免崩溃? 4. 如何检测和修复InnoDB表空间损坏? > 通过以上步骤,大部分由表损坏或InnoDB引擎故障引起的崩溃问题可以得到解决。如果问题依然存在,建议升级到MySQL的最新补丁版本(5.7.x的最新版)或考虑迁移到MySQL 8.0。 引用来源: - [^1] [^3] [^5]: undrop-for-innodb工具使用说明 - [^2]: `innodb_force_recovery`参数的应用 - [^4]: 系统表`innodb_table_stats`的存在性检查 </think>### 解决MySQL 5.7.12执行ALTER TABLE崩溃问题的方案 #### 一、紧急恢复步骤(确保数据安全) 1. **强制InnoDB恢复模式启动** 修改`my.ini`配置文件,添加: ```ini [mysqld] innodb_force_recovery = 4 # 级别4跳过回滚日志[^2] ``` 重启服务: ```bash net stop mysql net start mysql ``` 📌 注意:此模式下数据库只读,需立即备份数据 2. **备份崩溃表数据** 启动后立即导出问题表: ```bash mysqldump -u root -p --skip-add-drop-table your_db formmain_0495 > rescue.sql ``` 3. **检查系统表状态** 验证核心系统表是否损坏: ```sql CHECK TABLE mysql.innodb_table_stats; # 引用[4] ``` #### 二、崩溃原因分析与修复 ##### 可能原因: 1. **InnoDB文件锁冲突** - 其他进程占用`.ibd`文件(如备份工具) - 未提交事务阻塞DDL操作 2. **数据字典损坏** - 系统表`innodb_index_stats`异常 - 列元数据不一致(`field0028`定义损坏) 3. **内存管理故障** - 断言失败通常由缓冲池页损坏引起 - 内存陷阱可能是InnoDB自适应哈希索引问题 ##### 修复方案: 1. **文件锁冲突解决** ```bash # 查找占用进程 handle.exe -p mysqld.exe | findstr ibdata # 强制解锁(谨慎使用) net stop mysql del /F /Q ib_logfile* ``` 2. **重建数据字典** ```sql /* 重建问题表结构 */ CREATE TABLE formmain_0495_new LIKE formmain_0495; ALTER TABLE formmain_0495_new DROP COLUMN field0028; -- 测试操作是否成功 INSERT INTO formmain_0495_new SELECT * FROM formmain_0495; RENAME TABLE formmain_0495 TO formmain_0495_old, formmain_0495_new TO formmain_0495; ``` 3. **内存问题处理** 修改配置预防: ```ini [mysqld] innodb_buffer_pool_size = 2G # 适当减小防止OOM innodb_adaptive_hash_index = OFF # 关闭自适应哈希[^2] innodb_force_recovery = 0 # 恢复正常模式 ``` #### 三、高级修复(无备份情况) 若表已损坏且无备份,使用undrop-for-innodb工具: ```bash # 编译恢复工具[^5] gcc `mysql_config --cflags` `mysql_config --libs` -o sys_parser sys_parser.c # 扫描表空间文件 ./sys_parser -d data_dir formmain_0495 ``` > 操作步骤: > 1. 从`ibdata1`提取数据页[^1][^3] > 2. 使用`page_parser`重组表结构 > 3. 用`c_parser`导出数据为SQL #### 四、预防措施 1. **DDL操作安全规范** ```sql /* 推荐流程 */ START TRANSACTION; CREATE TABLE new_table AS SELECT ...; -- 排除目标列 RENAME TABLE old_table TO backup, new_table TO old_table; COMMIT; ``` 2. **关键配置优化** ```ini innodb_file_per_table = ON # 启用独立表空间[^3] innodb_flush_log_at_trx_commit = 1 # 确保事务安全 skip_name_resolve = ON # 避免反向解析锁 ```
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值