MySQL数据库断电修复（Database page corruption on disk or a failed）

目录

​编辑

一、报错信息

二、解决方案

2.1 修改配置

2.2导出数据脚本

2.3 删除ib_logfile0、ib_logfile1、ibdata1

2.4 配置my.cnf

2.5 将数据导入MySQL数据库

一、报错信息

启动日志如下：

2022-09-30 15:46:10 7078 [Note] Plugin 'FEDERATED' is disabled.
2022-09-30 15:46:10 7078 [Note] InnoDB: Using atomics to ref count buffer pool pages
2022-09-30 15:46:10 7078 [Note] InnoDB: The InnoDB memory heap is disabled
2022-09-30 15:46:10 7078 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-09-30 15:46:10 7078 [Note] InnoDB: Memory barrier is not used
2022-09-30 15:46:10 7078 [Note] InnoDB: Compressed tables use zlib 1.2.3
2022-09-30 15:46:10 7078 [Note] InnoDB: Using Linux native AIO
2022-09-30 15:46:10 7078 [Note] InnoDB: Using CPU crc32 instructions
2022-09-30 15:46:10 7078 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2022-09-30 15:46:10 7078 [Note] InnoDB: Completed initialization of buffer pool
2022-09-30 15:46:10 7078 [Note] InnoDB: Highest supported file format is Barracuda.
2022-09-30 15:46:10 7078 [Note] InnoDB: Log scan progressed past the checkpoint lsn 3029362122819
2022-09-30 15:46:10 7078 [Note] InnoDB: Database was not shutdown normally!
2022-09-30 15:46:10 7078 [Note] InnoDB: Starting crash recovery.
2022-09-30 15:46:10 7078 [Note] InnoDB: Reading tablespace information from the .ibd files...
2022-09-30 15:46:11 7078 [Note] InnoDB: Restoring possible half-written data pages 
2022-09-30 15:46:11 7078 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 3029367365632
InnoDB: Doing recovery: scanned up to log sequence number 3029372608512
InnoDB: Doing recovery: scanned up to log sequence number 3029377851392
InnoDB: Doing recovery: scanned up to log sequence number 3029383094272
InnoDB: Doing recovery: scanned up to log sequence number 3029388337152
InnoDB: Doing recovery: scanned up to log sequence number 3029393580032
InnoDB: Doing recovery: scanned up to log sequence number 3029398822912
InnoDB: Doing recovery: scanned up to log sequence number 3029404065792
InnoDB: Doing recovery: scanned up to log sequence number 3029409308672
InnoDB: Doing recovery: scanned up to log sequence number 3029410197499
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 261696.
InnoDB: You may have to recover from a backup.
2022-09-30 15:46:13 7efd7d4b9720 InnoDB: Page dump in ascii and hex (16384 bytes):
InnoDB: End of page dump
2022-09-30 15:46:13 7efd7d4b9720 InnoDB: uncompressed page, stored checksum in field1 2954782913, calculated checksums for field1: crc32 4105743401, innodb 3656047717, none 3735928559, stored checksum in field2 1636468951, calculated checksums for field2: crc32 4105743401, innodb 1782267872, none 3735928559, page LSN 705 1413703129, low 4 bytes of LSN at page end 626886507, page number (if stored to page already) 261696, 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 261696.
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.
2022-09-30 15:46:13 7efd7d4b9720  InnoDB: Assertion failure in thread 139627193931552 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.
07:46:13 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=2000
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 = 801785 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/sbin/mysqld(my_print_stacktrace+0x35)[0x8e5d85]
/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x6692b4]
/lib64/libpthread.so.0(+0xf7e0)[0x7efd7d0a07e0]
/lib64/libc.so.6(gsignal+0x35)[0x7efd7bb34495]
/lib64/libc.so.6(abort+0x175)[0x7efd7bb35c75]
/usr/sbin/mysqld[0xa4fc37]
/usr/sbin/mysqld[0xa65454]
/usr/sbin/mysqld[0xa6589b]
/usr/sbin/mysqld[0xa53851]
/usr/sbin/mysqld[0xa1ac5e]
/usr/sbin/mysqld[0xa17204]
/usr/sbin/mysqld[0xa17805]
/usr/sbin/mysqld[0xa15059]
/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)[0x7efd7bb20d1d]
/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.

看日志的大体的意思是数据页的损坏。

二、解决方案

2.1 修改配置

 /etc/my.cnf 配置文件修改innodb 启动参数修改

[mysqld]
 
innodb_force_recovery = 1

如果innodb_force_recovery = 1不生效，则可尝试2-6几个数字。

然后重启mysql，重启成功。然后使用mysqldump或 pma 导出数据，执行修复操作等。修复完成后，把该参数注释掉，还原默认值0。

配置文件的参数：innodb_force_recovery

innodb_force_recovery影响整个InnoDB存储引擎的恢复状况。默认为0，表示当需要恢复时执行所有的恢复操作（即校验数据页/purge undo/insert buffer merge/rolling back&forward），当不能进行有效的恢复操作时，mysql有可能无法启动，并记录错误日志；

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。

2.2导出数据脚本

### 数据库导出 
mysqldump -uroot -pwinner -A -B   --force  >  ipvacloud20220929.sql

注意：这里的数据一定要备份成功。然后删除原数据库中的数据。

2.3 删除ib_logfile0、ib_logfile1、ibdata1

备份MySQL数据目录下的ib_logfile0、ib_logfile1、ibdata1三个文件，然后将这三个文件删除，还删除了此数据库文件夹下的 .ibd结尾的文件。

2.4 配置my.cnf

将my.cnf中innodb_force_recovery = 1或2——6几个数字这行配置删除或者配置为innodb_force_recovery = 0 重启MySQL服务

2.5 将数据导入MySQL数据库

 mysql -uroot -pwinner repair_azkaban   <  ipvacloud20220929.sql ;

此种方法下要注意的问题：

ib_logfile0、ib_logfile1、ibdata1这三个文件一定要先备份后删除；

一定要确认原数据导出成功了，最后导入 成功 恢复了数据库。