当前位置:运维
版权声明:本文为CSDN博主「努力努力学习中」的原创文章,遵循CC 4.0 BY-SA版权协议
原文链接
2020-12-02T05:42:04.649000Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-02T05:42:04.649000Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2020-12-02T05:42:04.649000Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2020-12-02T05:42:04.649000Z 0 [Note] MySQL5.7.19 (mysqld 5.7.19-log) starting as process 1160 ...
2020-12-02T05:42:04.658000Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2020-12-02T05:42:04.658000Z 0 [Note] InnoDB: Uses event mutexes
2020-12-02T05:42:04.658000Z 0 [Note] InnoDB: Memory barrier is not used
2020-12-02T05:42:04.659000Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2020-12-02T05:42:04.659000Z 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB
2020-12-02T05:42:04.660000Z 0 [Note] InnoDB: Number of pools: 1
2020-12-02T05:42:04.660000Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2020-12-02T05:42:04.662000Z 0 [Note] InnoDB: Initializing buffer pool, total size = 8M, instances = 1, chunk size = 8M
2020-12-02T05:42:04.663000Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-12-02T05:42:04.682000Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-12-02T05:42:04.696000Z 0 [ERROR] InnoDB: Trying to access page number 4294967167 in space 0, space name innodb_system, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2020-12-02T05:42:04.697000Z 0 [ERROR] InnoDB: Server exits.
1.备份数据:
1)修改my.ini文件(mysql配置文件)innodb_force_recovery = 6
[mysqld]
#innodb_force_recovery 设置后可以使宕机的数据库重新启动,便于恢复数据和排查使用
innodb_force_recovery = 6
innodb_force_recovery介绍
参数innodb_force_recovery影响了整个Innodb存储引擎的恢复状况。该值默认为0,表示当需要恢复时执行所有的恢复操作。当不能进行有效恢复时,如数据页发生了corruption,Mysql数据库可能会宕机,并把错误写入错误日志中。但在某些情况下,可能不需要执行完整的恢复操作。例如在进行alter table操作时,这时发生意外,数据库重启时会对Innodb表执行回滚操作。对于一个大表,这需要很长时间,甚至可能是几个小时。这时可以自行恢复,例如将表删除,从备份中重新将数据导入表中,这些操作可能要快于回滚操作。
Innodb_force_recovery可以设置6个非零值:
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
备注: 当设置innodb_force_recovery大于0后,可以对标进行select、create、drop操作,但insert、update或者delete这类操作是不允许的。
数据库修复完后此参数需要注释掉。
2)修改完配置文件后重新启动mysql服务
3)利用Navicat等数据库工具将数据表结构及数据导出(如果导出过程中,数据库又异常,可能是该数据库的某张表异常了,此时利用navicat的查询功能,依次查询表格,观察是查询哪张表的时候出错,备份时忽略该表数据【这张表的数据如何恢复暂时还没了解到,可以评论探讨】)
2.重新安装数据库
3.导入备份的表结构
千知博客