MySQL损坏page问题分析

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 背景 MySQL5.7.21 Centos 7.4 innodb_force_recovery=0 信息收集 MySQL物理备份报错 190425 11:58:54 >> log scanned up to (174805994673) 190425 11:58:55 >> log.

背景

  • MySQL5.7.21
  • Centos 7.4
  • innodb_force_recovery=0

信息收集

  • MySQL物理备份报错
190425 11:58:54 >> log scanned up to (174805994673)
190425 11:58:55 >> log scanned up to (174805995101)
 524288000 of 524288000 100% in 9s 55.32 MB/s done
190425 11:58:56 >> log scanned up to (174805995156)
190425 11:58:57 >> log scanned up to (174805995156)
190425 11:58:58 >> log scanned up to (174805995165)
190425 11:58:59 >> log scanned up to (174805995485)
[01] xtrabackup: Database page corruption detected at page 143665, retrying...
[01] xtrabackup: Database page corruption detected at page 143665, retrying...
[01] xtrabackup: Database page corruption detected at page 143665, retrying...
[01] xtrabackup: Database page corruption detected at page 143665, retrying...
[01] xtrabackup: Database page corruption detected at page 143665, retrying...
[01] xtrabackup: Database page corruption detected at page 143665, retrying...
190425 11:59:00 >> log scanned up to (174805995593)
[01] xtrabackup: Database page corruption detected at page 143665, retrying...
[01] xtrabackup: Database page corruption detected at page 143665, retrying...
[01] xtrabackup: Database page corruption detected at page 143665, retrying...
[01] xtrabackup: Error: failed to read page after 10 retries. File ./sbtest/sbtest4.ibd seems to be corrupted.
[01] xtrabackup: Error: xtrabackup_copy_datafile() failed.
[01] xtrabackup: Error: failed to copy datafile.
upload: '<stdin>' -> 's3://mysql-test-87771/1556164635.tar' [part 10, 331MB]
 347810779 of 347810779 100% in 6s 54.92 MB/s done
Connection to mysql-test-877710-0.mysql-test-877710-headless.default.svc.clust closed.
AI 代码解读
  • 通过xtrabackup日志可以发现一个重要的报错信息:
File ./sbtest/sbtest4.ibd seems to be corrupted.
AI 代码解读
  • 手动到MySQL查询检查表sbtest4的,发现MySQL会重启
mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_force_recovery | 0 |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> use sbtest
Database changed

mysql> check table sbtest.sbtest4;
ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
AI 代码解读
  • 查看error.log,发现MySQL异常崩溃,并重新启动
2019-04-25T15:41:51.843625+08:00 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=135, page number=143665]. You may have to recover from a backup.
2019-04-25T15:41:51.843680+08:00 0 [Note] InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
...
InnoDB: End of page dump
2019-04-25T15:49:21.251992+08:00 0 [Note] InnoDB: Uncompressed page, stored checksum in field1 3293893214, calculated checksums for field1: crc32 3293893214/4282163675, innodb 271698160, none 3735928559, stored checksum in field2 3013555396, calculated checksums for field2: crc32 3293893214/4282163675, innodb 1359747787, none 3735928559, page LSN 40 340032547, low 4 bytes of LSN at page end 2116856341, page number (if stored to page already) 143665, space id (if created with >= MySQL-4.1.1 and stored already) 135
InnoDB: Page may be an index page where index id is 140
2019-04-25T15:49:21.252039+08:00 0 [Note] InnoDB: Index 140 is `PRIMARY` in table `sbtest`.`sbtest4`
2019-04-25T15:49:21.252048+08:00 0 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
...
AI 代码解读
  • 通过一系列日志收集,可以发现innodb_force_recovery=0时,index page对MySQL的启动无影响,但是有SQL查询到坏页的时候,MySQL会出现崩溃的情况

原因分析

导致page损坏的原因

  • 硬件问题
  • 驱动程序错误
  • 内核错误
  • 电源故障
  • 罕见的MySQL错误

参数解析

  • innodb_force_recovery默认为0(没有强制恢复的正常启动)。
  • 仅在紧急情况下将innodb_force_recovery设置为大于0的值,以便您可以启动InnoDB并转储表。在此之前,请确保您拥有数据库备份,以备需要重新创建时使用。值为4或更高可能会永久损坏数据文件。强制InnoDB恢复时,应始终以innodb_force_recovery = 1开始,并且只在必要时逐步增加值。
  • 如果您能够以innodb_force_recovery值为3或更低的值转储表,那么您相对安全,只有损坏的单个页面上的某些数据会丢失。值为4或更高被认为是危险的,因为数据文件可能会永久损坏。值6被认为是激烈的,因为数据库页面处于过时状态,这反过来可能会在B树和其他数据库结构中引入更多损坏。
  • innodb_force_recovery可以设置为6个非零值:1~6,大的数字包含了前面所有小数字的所有功能,具体情况如下:

    • 1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。即使检测到损坏的页面,也允许服务器运行。尝试使SELECT * FROM tbl_name跳过损坏的索引记录和页面,这有助于转储表。
    • 2(SRV_FORCE_NO_BACKGROUND):防止主线程和任何清除线程运行。如果在清除操作期间发生崩溃,则此恢复值会阻止它。
    • 3(SRV_FORCE_NO_TRX_UNDO):崩溃恢复后不运行事务回滚。
    • 4(SRV_FORCE_NO_IBUF_MERGE):阻止插入缓冲区合并操作。如果它们会导致崩溃,则不会这样做。不计算表统计信息。此值可能会永久损坏数据文件。使用此值后,请准备删除并重新创建所有二级索引。将InnoDB设置为只读。
    • 5(SRV_FORCE_NO_UNDO_LOG_SCAN):启动数据库时不查看撤消日志(undo log):InnoDB甚至将未完成的事务视为已提交。此值可能会永久损坏数据文件。将InnoDB设置为只读。
    • 6(SRV_FORCE_NO_LOG_REDO):不执行与恢复相关的重做日志前滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态,这反过来可能会在B树和其他数据库结构中引入更多损坏。将InnoDB设置为只读。

需要注意的是,当设置参数innodb_force_recovery大于0后,可以对表进行select、create、drop操作,但insert、update或者delete这类操作是不允许的。如果innodb_force_recovery设置为4或更高,则将InnoDB置于只读模式。

您可以从表中进行SELECT以转储它们。如果innodb_force_recovery值为3或更小,您可以DROP或CREATE表。 Innodb_force_recovery值也大于3,支持DROP TABLE,最高可达MySQL 5.7.17。从MySQL 5.7.18开始,不允许在innodb_force_recovery值大于4的情况下使用DROP TABLE。

如果您知道给定的表导致回滚崩溃,则可以删除它。如果遇到由大量导入失败或ALTER TABLE导致的失控回滚,则可以终止mysqld进程并将innodb_force_recovery设置为3以使数据库无需回滚,然后DROP导致失控回滚的表。

如果表数据中的损坏阻止您转储整个表内容,则具有ORDER BY primary_key DESC子句的查询可能能够在损坏的部分之后转储表的一部分。

如果启动InnoDB需要高innodb_force_recovery值,则可能存在可能导致复杂查询(包含WHERE,ORDER BY或其他子句的查询)失败的数据结构损坏。在这种情况下,您可能只能运行基本的SELECT * FROM t查询。

  • 设置innodb_force_recovery=1时,对表操作
mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_force_recovery | 1 |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> check table sbtest4;
+----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| sbtest.sbtest4 | check | status | OK |
+----------------+-------+----------+----------+
1 row in set (1 min 4.94 sec)

mysql> select count(*) from sbtest4;
+----------+
| count(*) |
+----------+
| 10997913 |
+----------+
1 row in set (3.58 sec)

mysql> drop table sbtest4_bak;
Query OK, 0 rows affected (0.21 sec)

mysql,> create table sbtest4_bak like sbtest4;
Query OK, 0 rows affected (0.45 sec)

mysql> insert into sbtest4_bak select * from sbtest4;
ERROR 1881 (HY000): Operation not allowed when innodb_forced_recovery > 0.

mysql> alter table sbtest4_bak engine=myisam MAX_ROWS=10000000;
ERROR 1025 (HY000): Error on rename of './sbtest/sbtest4_bak' to './sbtest/#sql2-1-1daa' (errno: 190 - Operation not allowed when innodb_forced_recovery > 0)
AI 代码解读
  • 在参数innodb_force_recovery为1时,可以对表进行select、create、drop操作,但insert、update或者delete这类操作是不允许的。

问题修复

方案1:针对于myisam引擎表

mysql> repair table sbtest4;
AI 代码解读

方案2:针对于innodb引擎表,损坏非二级索引

#### 建立一张新表,并修改存储引擎为myisam
mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_force_recovery | 0 |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> create table sbtest4_bak like sbtest4;

mysql> alter table sbtest4_bak engine = myisam MAX_ROWS=10000000;
备注:
1、为什么修改为myisam存储引擎?是因为在innodb_force_recovery>0时,innodb无法执行insert操作。或者在innodb_force_recovery>0直接手动create table xxx ... engine=myisam.
2、在行指针设置较小不够用的时候,为提高MyISAM表最大容量,可以调整表定义设定MAX_ROWS的值

#### 损坏页数据导入
mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_force_recovery | 1 |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> insert into sbtest4_bak select * from sbtest4;

#### 删除原表
mysql> drop table sbtest4;

#### 新表重命名
mysql> rename table sbtest4_bak to sbtest4;
AI 代码解读

注意:在执行insert into xxx select * from xxx;可能会引发问题:

mysql> insert into sbtest4_bak select * from sbtest4;
ERROR 1598 (HY000): Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.
AI 代码解读

错误日志显示:

...
2019-04-26T14:04:12.047240+08:00 59 [ERROR] mysqld: Table './sbtest/sbtest4_bak' is marked as crashed and should be repaired
2019-04-26T14:04:12.068042+08:00 59 [Warning] Checking table: './sbtest/sbtest4_bak'
...
AI 代码解读

避免措施是关闭binlog:

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sbtest4_bak select * from sbtest4;
Query OK, 10997913 rows affected (2 min 0.96 sec)
Records: 10997913 Duplicates: 0 Warnings: 0
AI 代码解读

方案3:针对innodb引擎表,损坏二级索引page

mysql> OPTIMIZE TABLE sbtest4;
AI 代码解读
  • 针对二级索引损坏page可以直接选择OPTIMIZE TABLE xxx;操作直接重新创建二级索引

方案4:物理备份表空间传输

mysql> use sbtest;
mysql> CREATE TABLE sbtest4_bak like sbtest4;
mysql> ALTER TABLE sbtest4_bak DISCARD TABLESPACE;
mysql> FLUSH TABLES t FOR EXPORT;

shell> scp /path/to/datadir/test/sbtest4.{ibd,cfg} destination-server:/path/to/datadir/test
shell> chown -R mysql:mysql /path/to/datadir/test

mysql> use test;
mysql> UNLOCK TABLES;

mysql> use test;
mysql> ALTER TABLE t IMPORT TABLESPACE;
AI 代码解读
  • 通过物理备份恢复到最近的时间点数据,通过innodb表空间传输技术快速恢复

方案5:物理备份逻辑恢复

shell> mysqldump -uxxx1 -hxxx1 -pxxx1 -B test --tables sbtest4 --single-transaction --master-data=2 --set-gtid-purged=off > /path/sbtest4.sql
shell> mysql -uxxx2 -hxxx2 -pxxx2 < /path/sbtest4.sql
AI 代码解读
  • 通过物理备份恢复到最近的时间点数据,通过mysqldump逻辑恢复

总结

  • 数据页面的主键索引(clustered key index)被损坏。这种情况和数据的二级索引(secondary indexes)被损坏相比要糟很多,因为后者可以通过使用OPTIMIZE TABLE命令来修复,但这和更难以恢复的表格目录(table dictionary)被破坏的情况来说要好一些。需要尝试另一个方法:insert into tb select * from ta limit X;甚至是dump出去,再load回来。
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
【MySQL】SQL分析的几种方法
以上就是SQL分析的几种方法。需要注意的是,这些方法并不是孤立的,而是相互关联的。在实际的SQL分析中,我们通常需要结合使用这些方法,才能找出最佳的优化策略。同时,SQL分析也需要对数据库管理系统,数据,业务需求有深入的理解,这需要时间和经验的积累。
103 12
MySQL 查询优化分析 - 常用分析方法
本文介绍了MySQL查询优化分析的常用方法EXPLAIN、Optimizer Trace、Profiling和常用监控指标。
无缝集成 MySQL,解锁秒级 OLAP 分析性能极限,完成任务可领取三合一数据线!
通过 AnalyticDB MySQL 版、DMS、DTS 和 RDS MySQL 版协同工作,解决大规模业务数据统计难题,参与活动完成任务即可领取三合一数据线(限量200个),还有机会抽取蓝牙音箱大奖!
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
229 7
MySQL事务日志-Undo Log工作原理分析
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
151 11
MySQL原理简介—4.深入分析Buffer Pool
本文介绍了MySQL的Buffer Pool机制,包括其作用、配置方法及内部结构。Buffer Pool是MySQL用于缓存磁盘数据页的关键组件,能显著提升数据库读写性能。默认大小为128MB,可根据服务器配置调整(如32GB内存可设为2GB)。它通过free链表管理空闲缓存页,flush链表记录脏页,并用LRU链表区分冷热数据以优化淘汰策略。此外,还探讨了多Buffer Pool实例、chunk动态调整等优化并发性能的方法,以及如何通过`show engine innodb status`查看Buffer Pool状态。关键词:MySQL内存数据更新机制。
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
321 11
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
2087 14
MySQL事务日志-Redo Log工作原理分析
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
135 4

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问