mysql InnoDB crash and recover

Jan052018

作者：Fisher 发布：2018-01-05 12:27 分类：数据库, 系统抢沙发

[摘要] 因为机器突然断电，mysql innodb crash 了。数据文件损坏导致mysql进程起不来，几经折腾，终于把服务起来了，但是解决过程并不完美，下面还是把处理经过记录一下。

Mysql版本：5.6.36，
操作系统：CentOS release 6.8

先贴出来mysql.log 日志的报错情况：

information that should help you find out what is causing the crash.
2018-01-05 08:19:44 13811 [Note] Plugin 'FEDERATED' is disabled.
2018-01-05 08:19:44 13811 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-01-05 08:19:44 13811 [Note] InnoDB: The InnoDB memory heap is disabled
2018-01-05 08:19:44 13811 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-01-05 08:19:44 13811 [Note] InnoDB: Memory barrier is not used
2018-01-05 08:19:44 13811 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-01-05 08:19:44 13811 [Note] InnoDB: Using Linux native AIO
2018-01-05 08:19:44 13811 [Note] InnoDB: Using CPU crc32 instructions
2018-01-05 08:19:44 13811 [Note] InnoDB: Initializing buffer pool, size = 8.0G
2018-01-05 08:19:44 13811 [Note] InnoDB: Completed initialization of buffer pool
2018-01-05 08:19:44 13811 [Note] InnoDB: Highest supported file format is Barracuda.
2018-01-05 08:19:44 13811 [Note] InnoDB: Log scan progressed past the checkpoint lsn 229983134766
2018-01-05 08:19:44 13811 [Note] InnoDB: Database was not shutdown normally!
2018-01-05 08:19:44 13811 [Note] InnoDB: Starting crash recovery.
2018-01-05 08:19:44 13811 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-01-05 08:19:44 13811 [Note] InnoDB: Restoring possible half-written data pages
2018-01-05 08:19:44 13811 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 229983251745
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 691.
InnoDB: You may have to recover from a backup.
2018-01-05 08:19:45 7f3dd3ed1720 InnoDB: Page dump in ascii and hex (16384 bytes):

InnoDB: End of page dump
2018-01-05 08:19:45 7f3dd3ed1720 InnoDB: uncompressed page, 
stored checksum in field1 1575536212, calculated checksums for field1: crc32 1100691290, 
innodb 1575536212, none 3735928559, stored checksum in field2 2238042318, calculated checksums for field2: 
crc32 1100691290, innodb 3402961953, none 3735928559, page LSN 53 2349971314, low 4 bytes of LSN at page end 2349884494, 
page number (if stored to page already) 691, space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an update undo log page
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 691.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
2018-01-05 08:19:45 7f3dd3ed1720  
InnoDB: Assertion failure in thread 139903525263136 in file buf0buf.cc line 4295
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.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
00:19:45 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.
key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=700
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 = 285949 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
8388608 + 131072 * 700
8388608 + 91750400 = 97792


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/sbin/mysqld(my_print_stacktrace+0x35)[0x8e5d85]
/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x6692b4]
/lib64/libpthread.so.0[0x35ea00f790]
/lib64/libc.so.6(gsignal+0x35)[0x35e9c32625]
/lib64/libc.so.6(abort+0x175)[0x35e9c33e05]
/usr/sbin/mysqld[0xa4fc37]
/usr/sbin/mysqld[0xa65454]
/usr/sbin/mysqld[0xa6589b]
/usr/sbin/mysqld[0xa53851]
/usr/sbin/mysqld[0xa1dadb]
/usr/sbin/mysqld[0xa12a45]
/usr/sbin/mysqld[0xa12dc6]
/usr/sbin/mysqld[0xa1527e]
/usr/sbin/mysqld[0x9ff480]
/usr/sbin/mysqld[0x942b5d]
/usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x5ac828]
/usr/sbin/mysqld[0x6f3171]
/usr/sbin/mysqld(_Z11plugin_initPiPPci+0xbb6)[0x6f6fd6]
/usr/sbin/mysqld[0x59ea28]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x42d)[0x5a3cdd]
/lib64/libc.so.6(__libc_start_main+0xfd)[0x35e9c1ed5d]
/usr/sbin/mysqld[0x595349]
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.

100

101

102

103

104

information that should help you find out what is causing the crash.

2018-01-05 08:19:44 13811 [Note] Plugin 'FEDERATED' is disabled.

2018-01-05 08:19:44 13811 [Note] InnoDB: Using atomics to ref count buffer pool pages

2018-01-05 08:19:44 13811 [Note] InnoDB: The InnoDB memory heap is disabled

2018-01-05 08:19:44 13811 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-01-05 08:19:44 13811 [Note] InnoDB: Memory barrier is not used

2018-01-05 08:19:44 13811 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-01-05 08:19:44 13811 [Note] InnoDB: Using Linux native AIO

2018-01-05 08:19:44 13811 [Note] InnoDB: Using CPU crc32 instructions

2018-01-05 08:19:44 13811 [Note] InnoDB: Initializing buffer pool, size = 8.0G

2018-01-05 08:19:44 13811 [Note] InnoDB: Completed initialization of buffer pool

2018-01-05 08:19:44 13811 [Note] InnoDB: Highest supported file format is Barracuda.

2018-01-05 08:19:44 13811 [Note] InnoDB: Log scan progressed past the checkpoint lsn 229983134766

2018-01-05 08:19:44 13811 [Note] InnoDB: Database was not shutdown normally!

2018-01-05 08:19:44 13811 [Note] InnoDB: Starting crash recovery.

2018-01-05 08:19:44 13811 [Note] InnoDB: Reading tablespace information from the .ibd files...

2018-01-05 08:19:44 13811 [Note] InnoDB: Restoring possible half-written data pages

2018-01-05 08:19:44 13811 [Note] InnoDB: from the doublewrite buffer...

InnoDB: Doing recovery: scanned up to log sequence number 229983251745

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 691.

InnoDB: You may have to recover from a backup.

2018-01-05 08:19:45 7f3dd3ed1720 InnoDB: Page dump in ascii and hex (16384 bytes):

InnoDB: End of page dump

2018-01-05 08:19:45 7f3dd3ed1720 InnoDB: uncompressed page,

stored checksum in field1 1575536212, calculated checksums for field1: crc32 1100691290,

innodb 1575536212, none 3735928559, stored checksum in field2 2238042318, calculated checksums for field2:

crc32 1100691290, innodb 3402961953, none 3735928559, page LSN 53 2349971314, low 4 bytes of LSN at page end 2349884494,

page number (if stored to page already) 691, space id (if created with >= MySQL-4.1.1 and stored already) 0

InnoDB: Page may be an update undo log page

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 691.

InnoDB: You may have to recover from a backup.

InnoDB: It is also possible that your operating

InnoDB: system has corrupted its own file cache

InnoDB: and rebooting your computer removes the

InnoDB: error.

InnoDB: If the corrupt page is an index page

InnoDB: you can also try to fix the corruption

InnoDB: by dumping, dropping, and reimporting

InnoDB: the corrupt table. You can use CHECK

InnoDB: TABLE to scan your table for corruption.

InnoDB: See also http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.

InnoDB: Ending processing because of a corrupt database page.

2018-01-05 08:19:45 7f3dd3ed1720

InnoDB: Assertion failure in thread 139903525263136 in file buf0buf.cc line 4295

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.6/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.

00:19:45 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.

key_buffer_size=8388608

read_buffer_size=131072

max_used_connections=0

max_threads=700

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 = 285949 K bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

8388608 + 131072 * 700

8388608 + 91750400 = 97792

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/sbin/mysqld(my_print_stacktrace+0x35)[0x8e5d85]

/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x6692b4]

/lib64/libpthread.so.0[0x35ea00f790]

/lib64/libc.so.6(gsignal+0x35)[0x35e9c32625]

/lib64/libc.so.6(abort+0x175)[0x35e9c33e05]

/usr/sbin/mysqld[0xa4fc37]

/usr/sbin/mysqld[0xa65454]

/usr/sbin/mysqld[0xa6589b]

/usr/sbin/mysqld[0xa53851]

/usr/sbin/mysqld[0xa1dadb]

/usr/sbin/mysqld[0xa12a45]

/usr/sbin/mysqld[0xa12dc6]

/usr/sbin/mysqld[0xa1527e]

/usr/sbin/mysqld[0x9ff480]

/usr/sbin/mysqld[0x942b5d]

/usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x5ac828]

/usr/sbin/mysqld[0x6f3171]

/usr/sbin/mysqld(_Z11plugin_initPiPPci+0xbb6)[0x6f6fd6]

/usr/sbin/mysqld[0x59ea28]

/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x42d)[0x5a3cdd]

/lib64/libc.so.6(__libc_start_main+0xfd)[0x35e9c1ed5d]

/usr/sbin/mysqld[0x595349]

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.

根据http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html的提示，需要修复数据库和表。
由于mysql现在仍无法启动，所以需要增加如下：
[mysqld]
innodb_force_recovery = 4

关于innodb_force_recovery的几个值介绍：

As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.

1 (SRV_FORCE_IGNORE_CORRUPT)

Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Does not run transaction rollbacks after crash recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevents insert buffer merge operations. If they would cause a crash, does not do them. 
Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.

6 (SRV_FORCE_NO_LOG_REDO)

Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, 
which in turn may introduce more corruption into B-trees and other database structures.

As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.

1 (SRV_FORCE_IGNORE_CORRUPT)

Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)

Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)

Does not run transaction rollbacks after crash recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)

Prevents insert buffer merge operations. If they would cause a crash, does not do them.

Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.

6 (SRV_FORCE_NO_LOG_REDO)

Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state,

which in turn may introduce more corruption into B-trees and other database structures.

在my.cnf中添加完 innodb_force_recovery 配置后，mysql可以启动了，然后尝试修复所有的数据库：

mysqlcheck -u root -p --auto-repair --check --all-databases
common_schema.metadata                             OK
common_schema.numbers                              OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK

mysqlcheck -u root -p --auto-repair --check --all-databases

common_schema.metadata OK

common_schema.numbers OK

mysql.columns_priv OK

mysql.db OK

mysql.event OK

mysql.func OK

mysql.general_log OK

mysql.help_category OK

mysql.help_keyword OK

全部修复完成，去掉innodb_force_recovery，再重启mysql，仍然报错。

使用xtrabackup的操作中发现了这样一个提示：
xtrabackup: Error: failed to read page after 10 retries. File ./ibdata1 seems to be corrupted.

于是尝试在 innodb_force_recovery = 4 的情况下，使用xtrabackup备份一下数据库，然后重建mysql。
这个过程中 innobackupex 备份是成功的，重建mysql也是成功的，重新apply-log和copy-back也是成功，但是恢复之后的数据库仍然无法启动，跟之前的情况类似，已经很无语了。

无奈之下选择使用了innobackupex最近的一次备份进行恢复，完成后重启mysql服务成功！
但是问题是最近的备份距现在已经过去了20个小时，innodb crash 这段时间的更新会丢失，由于在innodb_force_recovery = 4的状态下，mysql是可以启动的，所以在原来的数据基础上启动了新的端口：3307，然后手动比较3307和3306两套数据的变化！
唉，太挫了~

本文固定链接: https://www.sudops.com/mysql-innodb-crash-recover.html | 运维·速度

该日志由 Fisher 于2018年01月05日发表在数据库, 系统分类下，你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: mysql InnoDB crash and recover | 运维·速度
关键字: crash, mysql

【上一篇】wordpress 插件升级出现 Call to undefined function bws_general_menu 错误
【下一篇】cloudera manager 忘记密码的解决办法

mysql InnoDB crash and recover

您可能还会对这些文章感兴趣！

mysql InnoDB crash and recover：等您坐沙发呢！

发表评论

最新日志热评日志随机日志

赞助链接

标签云

最新文章

最近评论

最活跃的读者

欢迎关注运维·速度微信公众号

最新评论

标签云集

博客统计

mysql InnoDB crash and recover

您可能还会对这些文章感兴趣！

mysql InnoDB crash and recover：等您坐沙发呢！

发表评论

最新日志热评日志随机日志

赞助链接

标签云

最新文章

最近评论

最活跃的读者

欢迎关注 运维·速度 微信公众号

最新评论

标签云集

博客统计

欢迎关注运维·速度微信公众号