文档章节

MySQL意外断电,InnoDB数据库恢复

夕拾贰
 夕拾贰
发布于 2015/11/05 18:49
字数 951
阅读 5172
收藏 98

客户数据库在运行中突然断电,当服务器重启发现MySQL无法启动,查看日志,报错如下:


151105 11:24:52 mysqld_safe Starting mysqld daemon with databases from /data/mysql/datafile
151105 11:24:52 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
151105 11:24:52 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
151105 11:24:52 InnoDB: The InnoDB memory heap is disabled
151105 11:24:52 InnoDB: Mutexes and rw_locks use GCC atomic builtins
151105 11:24:52 InnoDB: Compressed tables use zlib 1.2.3
151105 11:24:52 InnoDB: Initializing buffer pool, size = 512.0M
151105 11:24:52 InnoDB: Completed initialization of buffer pool
151105 11:24:52 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
151105 11:24:52  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
151105 11:24:52  InnoDB: ERROR: We were only able to scan the log up to
InnoDB: 2735615488, but a checkpoint was at 2735615806.
InnoDB: It is possible that the database is now corrupt!
InnoDB: 3 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 3 row operations to undo
InnoDB: Trx id counter is 6D84900
InnoDB: Last MySQL binlog file position 0 446116929, file name /data/mysql/binlog/mysql-bin.001288
InnoDB: Cleaning up trx with id 6D7C116
InnoDB: Cleaning up trx with id 6D74419
InnoDB: Cleaning up trx with id 6D6F27E
151105 11:24:52  InnoDB: Waiting for the background threads to start
151105 11:24:52  InnoDB: Assertion failure in thread 140108568532736 in file trx0purge.c line 848
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
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.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
03:24:52 UTC - mysqld got signal 6 ;
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.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=402653184
read_buffer_size=4194304
max_used_connections=0
max_threads=2002
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 16818378 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
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...
stack_bottom = 0 thread_stack 0x40000
/usr/local/percona-server-5.5.38-35.2/bin/mysqld(my_print_stacktrace+0x2e)[0x7785ae]
/usr/local/percona-server-5.5.38-35.2/bin/mysqld(handle_fatal_signal+0x43a)[0x66a1da]
/lib64/libpthread.so.0[0x379c20f710]
/lib64/libc.so.6(gsignal+0x35)[0x379be32925]
/lib64/libc.so.6(abort+0x175)[0x379be34105]
/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x814fb0]
/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x8153f7]
/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x8e318f]
/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x8d98d5]
/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x813c4e]
/usr/local/percona-server-5.5.38-35.2/bin/mysqld[0x809d2c]
/lib64/libpthread.so.0[0x379c2079d1]
/lib64/libc.so.6(clone+0x6d)[0x379bee8b5d]
You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
151105 11:24:52 mysqld_safe mysqld from pid file /data/mysql/datafile/mysql.pid ended

分析日志后发现,数据库无法重启的原因是因为ibdata1文件损坏,重启后无法正常恢复。

现在我们就需要跳过恢复步骤,修改my.cnf文件,在my.cnf中的[mysqld]中添加

innodb_force_recovery = 6
innodb_purge_threads = 0

innodb_force_recovery可以设置为1-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):不执行前滚的操作。

再次启动,如果还无法启动则删除数据目录datafile下的 ibdata1,ib_logfile*等文件。

启动后导出MySQL数据库,重新恢复即可。

© 著作权归作者所有

夕拾贰
粉丝 12
博文 16
码字总数 12732
作品 0
杨浦
程序员
私信 提问
加载中

评论(8)

Havencode
Havencode
运气真好。
夕拾贰
夕拾贰 博主

引用来自“Havencode”的评论

没有数据丢失吗?

木有
Havencode
Havencode
没有数据丢失吗?
夕拾贰
夕拾贰 博主

引用来自“酒瓶不倒”的评论

引用来自“Tuesday”的评论

ibdata1,ib_logfile*删除了, 那inno的表还能用? 楼主不测试一下?

同问
删除后,MySQL可以正常启动,启动后MySQL会自己重建ibdata1等文件,inno的表不能用了。 通过恢复模式启动后,inno的表只能通过只读的方式打开。可以将数据导出。 回复模式具体文档见https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
酒瓶不倒
酒瓶不倒

引用来自“Tuesday”的评论

ibdata1,ib_logfile*删除了, 那inno的表还能用? 楼主不测试一下?

同问
夕拾贰
夕拾贰 博主

引用来自“Tuesday”的评论

ibdata1,ib_logfile*删除了, 那inno的表还能用? 楼主不测试一下?
所以通过innodb的恢复模式启动MySQL,这也就可以查看并导出数据了。
Tuesday
Tuesday
ibdata1,ib_logfile*删除了, 那inno的表还能用? 楼主不测试一下?
winchoo
winchoo
不错的文章,学习了
InnoDB中的doublewrite buffer技术原理

先简单解释下什么是双写缓冲。InnoDB使用了一种叫做doublewrite的特殊文件flush技术,在把pages写到date files之前,InnoDB先把它们写到一个叫doublewrite buffer的连续区域内,在写doublew...

jockchou
2015/07/13
709
0
mysql innodb断电恢复,不支持innodb,表空间丢失

mysql innodb断电恢复,不支持innodb,表空间丢失 一、需求 在办公网络中有一测试pc,跑mysql服务,周末大厦停电,导致mysql 异常; 具体表象为不支持innodb Current database: kkyoo_ucente...

xiaomaimai
2018/06/26
0
0
如何设置innodb_log_file_size

在这片文章中,我会提供一些关于如何设置 MySQL的 参数的一些建议。 跟其他数据库管理系统一样,MySQL通过日志来实现数据的持久性(在使用InnoDB存储引擎的前提下)。这确保了当一个事务提交...

kai404
2018/05/08
0
0
Mysql一些重要配置参数的学习与整理(二)

原文地址:Mysql一些重要配置参数的学习与整理(二) 上一篇,Mysql一些重要配置参数的学习与整理(一)中,我们了解和学习了mysql配置中的一些重要参数,今天继续进行学习,mysql的配置参数...

Realfighter
2015/01/07
678
0
Mysql ibdata 丢失或损坏如何通过frm&ibd 恢复数据

mysql存储在磁盘中,各种天灾人祸都会导致数据丢失。大公司的时候我们常常需要做好数据冷热备,对于小公司来说要做好所有数据备份需要支出大量的成本,很多公司也是不现实的。万一还没有做好...

观澜而索源
2013/07/27
175
0

没有更多内容

加载失败,请刷新页面

加载更多

cpu load过高问题排查

load average的概念 top命令中load average显示的是最近1分钟、5分钟和15分钟的系统平均负载。 系统平均负载被定义为在特定时间间隔内运行队列中(在CPU上运行或者等待运行多少进程)的平均进程...

mskk
45分钟前
6
0
用spring boot 实现websocket

import java.io.IOException;import javax.websocket.OnClose;import javax.websocket.OnError;import javax.websocket.OnMessage;import javax.websocket.OnOpen;import java......

jingshishengxu
55分钟前
4
0
shell介绍,命令历史,命令补全和别名,通配符,输入输出重定向,管道符和作业控制

shell介绍 可以使用 yum list |grep zsh 或者 yum list |grep ksh 这样可以搜索 zsh 和 ksh ,有需要的话可以安装 总之,默认使用的就是 .bash shell 命令历史 输入过的命令会被保存在一个文...

doomcat
今天
7
0
1995年的资深工程师,和你谈谈如何进阶

1995年的资深工程师,和你谈谈如何进阶 自我介绍 网络ID:杭城小刘,城市:顾名思义,人在杭州。1995年出生,本科毕业,现在是一名 iOS 资深工程师,年薪 35w。兴趣爱好广泛:乒乓球、美食、...

杭城小刘
今天
10
0
Kafka 面试题

1.Kafka中的ISR、AR代表什么? ISR:与leader保持同步的follower集合 AR:分区的所有副本 2.Kafka中的HW、LEO分别代表什么? LEO:每个副本的最后条消息的offset HW:一个分区中所有副本最小...

GrayWorld
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部