电脑在使用过程中死机,重启后发现 mysql 没有启动成功,查看错误日志发现是 innodb 出现问题导致 mysql 启动失败.
错误日志
$ mysql.server start Starting MySQL.ERROR ! The server quit without updating PID file(/usr/local /
var / mysql / fdipzonedeMacBook - Air.local.pid).22 : 08 : 37 mysqld_safe Starting mysqld daemon with databases from / usr / local /
var / mysql 2016 - 04 - 23 22 : 08 : 38 0[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.Please use--explicit_defaults_for_timestamp server option(see documentation
for more details).2016 - 04 - 23 22 : 08 : 38 0[Note] / usr / local / Cellar / mysql / 5.6.24 / bin / mysqld(mysqld 5.6.24) starting as process 3604...2016 - 04 - 23 22 : 08 : 38 3604[Warning] Setting lower_case_table_names = 2 because file system
for / usr / local /
var / mysql / is
case insensitive 2016 - 04 - 23 22 : 08 : 38 3604[Note] Plugin 'FEDERATED'is disabled.2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Using atomics to ref count buffer pool pages 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: The InnoDB memory heap is disabled 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Memory barrier is not used 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Compressed tables use zlib 1.2.3 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Using CPU crc32 instructions 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Initializing buffer pool,
size = 128.0M 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Completed initialization of buffer pool 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Highest supported file format is Barracuda.2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Log scan progressed past the checkpoint lsn 68929933440 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Database was not shutdown normally ! 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Starting crash recovery.2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Reading tablespace information from the.ibd files...2016 - 04 - 23 22 : 08 : 38 3604[ERROR] InnoDB: checksum mismatch in tablespace. / test_user / user_recommend_code#P#pmax.ibd(table test_user / user_recommend_code#P#pmax) 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 1024 Pages to analyze: 64 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 1024,
Possible space_id count: 0 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 2048 Pages to analyze: 48 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 2048,
Possible space_id count: 0 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 4096 Pages to analyze: 24 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 4096,
Possible space_id count: 0 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 8192 Pages to analyze: 12 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 8192,
Possible space_id count: 0 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 16384 Pages to analyze: 6 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: VALID: space: 2947354 page_no: 3 page_size: 16384 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Page size: 16384,
Possible space_id count: 1 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: space_id: 2947354,
Number of pages matched: 1 / 1(16384) 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Chosen space: 2947354 2016 - 04 - 23 22 : 08 : 38 3604[Note] InnoDB: Restoring page 0 of tablespace 2947354 2016 - 04 - 23 22 : 08 : 38 3604[Warning] InnoDB: Doublewrite does not have page_no = 0 of space: 2947354 2016 - 04 - 23 22 : 08 : 38 7fff79b9e300 InnoDB: Operating system error number 2 in a file operation.InnoDB: The error means the system cannot find the path specified.InnoDB: If you are installing InnoDB,
remember that you must create InnoDB: directories yourself,
InnoDB does not create them.InnoDB: Error: could not open single - table tablespace file. / test_user / user_recommend_code#P#pmax.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.
解决方法
1. 如果数据不重要或已经有备份,只需要恢复 mysql 启动
进入 mysql 目录,一般是:/usr/local/var/mysql/
删除 ib_logfile*
删除 ibdata*
删除所有数据库物理目录(例如数据库为 test_db, 则执行 rm -rf test_db)
重启动 mysql
重新建立数据库或使用备份覆盖
2. 如果数据很重要且没有备份
可以使用 innodb_force_recovery 参数,使 mysqld 跳过恢复步骤,启动 mysqld,将数据导出然后重建数据库.
innodb_force_recovery 可以设置为 1-6,大的数字包含前面所有数字的影响
(SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页.
(SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行,如主线程需要执行 full purge 操作,会导致 crash.
(SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作.
(SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作.
(SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志,InnoDB 存储引擎会将未提交的事务视为已提交.
(SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作.
在 my.cnf(windows 是 my.ini) 中加入
innodb_force_recovery = 6
innodb_purge_thread = 0
重启 mysql
这时只可以执行 select,create,drop 操作,但不能执行 insert,update,delete 操作
执行逻辑导出,完成后将 innodb_force_recovery=0,innodb_purge_threads=1,然后重建数据库,最后把导出的数据重新导入
来源: http://lib.csdn.net/article/mysql/36305