MySQL ibdata文件删除故障分析

Rengar

云和恩墨交付战队

1279

1页

59次

2020-11-17

免费下载

MySQL ibdata⽂件删除故障分析

1. 故障复现

1.1 mv ibdata1 ibdata1_bak

1.1.1 查看状态

1.1.2 mv ⽂件

1.1.3 查看句柄状态

1.1.4 重启服务

1.2 先cp ibdata1备份然后rm ibdata1

1.2.1 先查看状态

1.2.2 删除ibdata⽂件

2 问题修复

2.1 MySQL进程还在

2.1.1 查看实例状态

2.1.2 停⽌复制

2.1.3 刷新脏⻚

2.1.4 查找MySQL进程句柄⽂件

2.2 MySQL进程不存在，原ibdata保留

2.2.1 先备份业务库表数据⽂件

2.2.2 修改my.cnf参数

2.2.3 dump表结构

2.2.4 创建表/导⼊表结构

2.2.5 删除业务表表空间

2.2.6 将备份业务库表数据⽂件拷⻉回数据⽬录

2.2.7 将恢复后的新实例作为业务库对外提供服务

2.3 MySQL进程不存在，原ibdata被删除

2.3.1 在新实例创建表

2.3.2 没有建表语句

2.3.3 将建表语句导⼊新实例

3 附录

3.1 ibdata 不同mv⽅式验证

3.1.1 mv到其他分区

3.2 innodb_force_recovery参数说明

3.3 拷⻉句柄⽂件恢复步骤

3.4 从frm⽂件获取建表语句

3.4.1 dbsake使⽤说明

3.4.1 dbsake 测试过程

MySQL ibdata⽂件删除故障分析

1. 故障复现

1.1 mv ibdata1 ibdata1_bak

1.1.1 查看状态

实例状态

⽂件句柄状态

1.1.2 mv ⽂件

mv ibdata

mv ibdata1 ibdata1_bak

1.1.3 查看句柄状态

句柄⽂件指向的⽬录⽂件发⽣了改变，猜测：inode未改变，所以句柄⽂件指向了新的⽂件；

已验证：在同分区，mv ibdata1到同分区其他⽬录下，⽂件句柄⽂件会刷新为的mv后的⽂件，并不会变为delete状态

mv 到其他分区请参考：附录3.1.1 mv到其他分区

1.1.4 重启服务

重启后MySQL服务并不会读取ibdata_bak 的备份⽂件

## 关闭mysql

mysql> shutdown;

## 重启 mysql

[root@centos-linux fd]# /data/mysql/base/base_5.7.27/bin/mysqld_safe --defaults-ﬁle=/data/mysql/base/base_5.7.27/etc/3301/my.cnf &

mysql-error.log

ibdata1不存在然后创建新ibdata1，但新ibdata1与已经存在的undolog不匹配导致报错，⽆法启动；

mv ibdata1后再删除undo redolog；可以正常启动，但表⽆法正常读取

1.2 先cp ibdata1备份然后rm ibdata1

1.2.1 先查看状态

使⽤cp ibdata1 ibdata1_bak 备份⼀份⽂件

使⽤stat命令查看ibdata1及ibdata1_bak⽂件inode

进⼊/proc/2821/fd ⽬录，使⽤ls 命令查看ibdata1操作系统句柄状态

1.2.2 删除ibdata⽂件

使⽤rm 命令删除ibdata1⽂件，保留ibdata1_bak⽂件

进⼊/proc/2821/fd ⽬录，使⽤ls 命令查看ibdata1操作系统句柄状态

复现现场故障现象

2 问题修复

2.1 MySQL进程还在

MySQL进程存在的情况下，操作系统还保留进程打开的⽂件句柄，可以进⼊/proc/$进程pid/fd ⽬录下查到⽂件对应的句柄⽂件，通过cp 拷⻉到原路径

下；

2.1.1 查看实例状态

##############for mgr

select * from performance_schema.replication_group_members;

SELECT ta.* ,tb.MEMBER_HOST,tb.MEMBER_PORT,tb.MEMBER_STATE FROM performance_schema.global_status ta,performance_schema.replication_

group_members tb WHERE ta.VARIABLE_NAME='group_replication_primary_member' and ta.VARIABLE_VALUE=tb.MEMBER_ID;

##################for slave

show slave status\G;

2.1.2 停⽌复制

##############for mgr

stop group_replication;

################for slave

stop slave；

###################################

2.1.3 刷新脏⻚

##### 添加全局读锁同时开启super_read_only

FLUSH TABLES WITH READ LOCK;

set global super_read_only=1;

##### 修改强制脏⻚刷新，调整innodb_io_capacity 增加IO刷新参数

set global innodb_max_dirty_pages_pct = 0;

set global innodb_io_capacity=2000;

##### 查找链接并组成kill 语句

select concat('kill ',id,';') from information_schema.processlist where db is not null;

##### 确认脏⻚是否刷新完成

show engine innodb status\G;

------------

TRANSACTIONS

------------

Trx id counter 6435

Purge done for trx's n:o < 6435 undo n:o < 0 state: running but idle

## 确保事务号⼀致

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 0 merges

## 确保insert buﬀer 合并插⼊缓存等于1

---

LOG

---

Log sequence number 1721029

Log ﬂushed up to 1721029

Last checkpoint at 1721029

## 确保这三个值不变

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 274726912; in additional pool allocated 0

Dictionary memory allocated 76753

Buﬀer pool size 16383

Free buﬀers 16181

Database pages 202

Old database pages 0

Modiﬁed db pages 0

## 确保脏⻚数量为0

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

0 read views open inside InnoDB

Main thread process no. 3415, id 140701545637632, state: sleeping

Number of rows inserted 0, updated 0, deleted 0, read 5

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

## 确保插⼊、更新、删除为0

2.1.4 查找MySQL进程句柄⽂件

## 查找MySQL进程

ps -ef |grep 端⼝号

## 根据进程号查找到对应的⽂件句柄

cd /proc/进程号/fd

ls -lrt|grep delete|grep innodb

## 备份源⽬录：

mv /ssddata/innodb/13390 /ssddata/innodb/13390_bak

mkdir /ssddata/innodb/13390

## 拷⻉⽂件句柄回去

cp 9 /ssddata/innodb/13770/ib_logﬁle1

cp 4 /ssddata/innodb/13770/ib_logﬁle0

cp 11 /ssddata/innodb/13770/ibtmp1

cp 10 /ssddata/innodb/13770/ibdata1

## ⽂件修改权限：

chown mysql:mysql -R /ssddata/innodb/13390

## 重启MySQL服务

mysql -h127.1 -P3390 -uroot -p'passwd' -A

shutdown;

##########For docker

docker restart container_id ####重启docker并启动MySQL

##########For 服务器

/$mysql_path/bin/mysqld_safe --defaults-ﬁle=/$path/my.cnf &

建议：实例重启后，使⽤mysqldump 导出⼀份表结构，然后备份所有的表数据⽂件；在新的实例导⼊表结构，然后使⽤表空间传输⽅式将数据恢复到

新实例；或者dump⼀份数据在新实例恢复

注意：1. 如果句柄最后变更时间停留在某个时间，会导致后来的DDL操作丢失

2.2 MySQL进程不存在，原ibdata保留

如果有最新的备份及binlog，可以使⽤备份+binlog的⽅式恢复；

ibdata1备份到其分区⽬录导致故障，因在删除后有数据写⼊，将原ibdata1⽂件拷⻉回原路径下，因LSN差距过⼤导致MySQL⽆法启动；

这种情况可以使⽤force recovery⽅式强制启动MySQL服务，导出建表语句在新实例重新创建表然后使⽤可传输表空间⽅式进⾏恢复；

已知表数据⽂件没有损坏；如果是MySQL Crash后force recovery启动进⾏数据恢复，建议先dump出表数据的⽅式进⾏数据恢复，因为在

MySQLCrash情况下⽆法确认表数据⽂件是否损坏

2.2.1 先备份业务库表数据⽂件

cp *.ibd /backup/*.ibd

2.2.2 修改my.cnf参数

在my.cnf⽂件添加innodb_force_recovery参数，建议从1 增加到6逐⼀进⾏尝试；数据越⼤对MySQL影响越⼤

innodb_force_recovery = [1-6]

innodb_force_recovery 参数说明请参考附录 3.1 innodb_force_recovery参数说明

2.2.3 dump表结构

使⽤mysqldump 把mysql.user表数据

mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=oﬀ --hex-blob --no-cre

ate-info

--tables mysql user > mysql_user.sql

使⽤mysqldump 导出业务库的表结构，如果ibdata⽂件不是最新可能会丢失部分表的表结构：

mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=oﬀ --hex-blob --no-data

employees > employees_schema.sql

2.2.4 创建表/导⼊表结构

在新的空实例中导⼊mysql.user表数据，及业务表；

source mysql_user.sql

source employees_schema.sql

2.2.5 删除业务表表空间

alter table tablename discard tablespace;

2.2.6 将备份业务库表数据⽂件拷⻉回数据⽬录

1. 拷⻉数据⽂件备份到数据⽬录

cp *ibd /data/mysql/data/3301/data/*.idb

2. 修改权限

chown mysql:mysql *

3. 导⼊表空间

组装sql语句，将业务表空间导⼊新实例

alter table tablename import tablespace;

2.2.7 将恢复后的新实例作为业务库对外提供服务

2.3 MySQL进程不存在，原ibdata被删除

2.3.1 在新实例创建表

如果可以获取到表建表语句，在新实例先创建所有的表，然后使⽤可传输表空间⽅法恢复表数据

可传输表空间请参考：2.2 MySQL进程不存在，原ibdata保留⽅式恢复

2.3.2 没有建表语句

如果没有建表语句，但表frm⽂件保存；可以使⽤⼯具解析frm获取建表语句

从frm⽂件获取建表语句请参考：3.4 从frm⽂件获取建表语句

2.3.3 将建表语句导⼊新实例

将使⽤dbsake获得的建表sql导⼊新实例，然后使⽤可传输表空间恢复表数据

3 附录

3.1 ibdata 不同mv⽅式验证

3.1.1 mv到其他分区

– 和直接删除相同，因为每个分区都是有⾃⼰的inode区，mv到其他分区相当于从当前分区删除该⽂件

3.2 innodb_force_recovery参数说明

当数据库崩溃时，可以尝试使⽤ SELECT … INTO OUTFILE 来导出数据，通常情况下，⽤这种⽅式导出来的数据是完整的。对于导不出来的

表，可以加上 order by PK 来导出部分数据。

崩溃⽐较严重时，这种⽅式可能因后台 innodb 相应线程中断⽽失效，甚⾄ innodb 会回滚数据来恢复崩溃。这种情况下，则可以在 [mysqld] 下

设置 innodb_force_recovery 来强制启动 innodb，同时避免后台操作，以⽅便导出数据。

该参数默认为 0，范围 1~6，每⼀级的功能不⼀样，较⼤的值包含较⼩值的功能。

仅在紧急情况下将 innodb_force_recovery 设置为⼤于 0 的值，以便可以启动 InnoDB 并转储表。在此之前，请确保有全量备份。

如果能够以 3 或者以下的值转储数据，则数据是相对安全的，只有损坏的单个⻚上的数据会丢失。对于⼤于 3 的数据转储，数据则就危险了。

做为⼀种安全措施，对于 0< x <=3，innodb 禁⽌ insert update delete（即此时可能会导致复制相关信息出错，因为⼀些复制信息是放在表中

的），但可以 drop create；对于 3< x <=6，innodb 则处于只读状态，在 5.7.17 及之前，此时也可以 drop，⾼于该版本后则不能。之所以有

drop 权限，可能是因为某张表损坏⽽导致的崩溃，删除后就好了。

设置为 3/4 后，重启依旧费时，此时有极⼤的可能是在处理 undo log、redo log，时间应该耗在这了。

⼤于等于 4 的值可能会永久地破坏数据⽂件，所以尝试使⽤该参数来启动数据库时，尽可能的从 1 开始逐渐往上升。

如果明确是哪张表引起的崩溃，则可以直接删除再启动。如果是因批量操作但回滚⽽引起的，则可以设置为 3 及以上，禁⽌事务的回滚，然后

再删除相应的表。

1. 忽略检测到的损坏⻚，尝试使⽤ select 去导出数据。

2. 禁⽌ master purge 线程运⾏。⽐如在 purge 期间可能导致崩溃，则会禁⽌ purge 线程。

3. 在崩溃恢复时禁⽌事务的回滚。

如果 change buffer 可能导致崩溃，则禁⽌ change buffer 功能；

该值可能会导致数据⽂件被破坏；

使⽤该值后，最好重建所有的⼆级索引。

设置 innodb 为只读。

启动的时候忽略 undo log，这必然会导致 innodb 将未 commit 的事物视为已 commit。

该值可能会导致数据⽂件被破坏；

设置 innodb 为只读。

不执⾏与恢复相关的 redo log 的前滚。

该值可能会导致数据⽂件被破坏；

使数据库⻚⾯处于过时状态，可能会导致其它未损坏的⻚也连带着损坏。此时多表关联查询也可能⽤不了。

设置 innodb 为只读。

3.3 拷⻉句柄⽂件恢复步骤

3.4 从frm⽂件获取建表语句

3.4.1 dbsake使⽤说明

[root@centos-linux dbsake]# ./dbsake –help

Usage: dbsake [options]

Options:

-d, –debug

-q, –quiet

-V, –version Show the version and exit.

-?, –help Show this message and exit.

Commands:

decode-tablename Decode a MySQL ﬁlename.

encode-tablename Encode a MySQL table identiﬁer.

ﬁncore Report cached pages for a ﬁle.

frmdump Dump schema from MySQL frm ﬁles.

help Show help for a command.

sandbox Create a sandboxed MySQL instance.

sieve Filter and transform mysqldump output.

uncache Uncache ﬁle(s) from the OS page cache.

unpack Unpack a MySQL backup archive.

upgrade-mycnf Upgrade a MySQL option ﬁle

3.4.1 dbsake 测试过程

## 下载dbsake⼯具

[root@centos-linux dbsake]# curl -s get.dbsake.net > dbsake

[root@centos-linux dbsake]# ll

total 436

-rw-r--r--. 1 root root 445959 May 29 10:27 dbsake

[root@centos-linux dbsake]# ll

total 436

-rw-r--r--. 1 root root 445959 May 29 10:27 dbsake

## 添加权限

[root@centos-linux dbsake]# chmod u+x dbsake

You have new mail in /var/spool/mail/root

[root@centos-linux dbsake]# ll

total 436

-rwxr--r--. 1 root root 445959 May 29 10:27 dbsake

## 解析frm⽂件

[root@centos-linux dbsake]# ./dbsake frmdump /data/mysql/data/3301/test/sbtest5.frm > sbtest5.sql

[root@centos-linux dbsake]# ll

total 440

-rwxr--r--. 1 root root 445959 May 29 10:27 dbsake

-rw-r--r--. 1 root root 386 May 29 10:28 sbtest5.sql

## 查看解析后的建表语句

[root@centos-linux dbsake]# cat sbtest5.sql

-- Table structure for table `sbtest5`

-- Created with MySQL Version 5.7.27

CREATE TABLE `sbtest5` (

`id` int(11) NOT NULL,

`k` int(11) NOT NULL DEFAULT '0',

`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',

`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `k_1` (`k`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

MySQL ibdata⽂件删除故障分析

1. 故障复现

1.1 mv ibdata1 ibdata1_bak

1.1.1 查看状态

1.1.2 mv ⽂件

1.1.3 查看句柄状态

1.1.4 重启服务

1.2 先cp ibdata1备份然后rm ibdata1

1.2.1 先查看状态

1.2.2 删除ibdata⽂件

2 问题修复

2.1 MySQL进程还在

2.1.1 查看实例状态

2.1.2 停⽌复制

2.1.3 刷新脏⻚

2.1.4 查找MySQL进程句柄⽂件

2.2 MySQL进程不存在，原ibdata保留

2.2.1 先备份业务库表数据⽂件

2.2.2 修改my.cnf参数

2.2.3 dump表结构

2.2.4 创建表/导⼊表结构

2.2.5 删除业务表表空间

2.2.6 将备份业务库表数据⽂件拷⻉回数据⽬录

2.2.7 将恢复后的新实例作为业务库对外提供服务

2.3 MySQL进程不存在，原ibdata被删除

2.3.1 在新实例创建表

2.3.2 没有建表语句

2.3.3 将建表语句导⼊新实例

3 附录

3.1 ibdata 不同mv⽅式验证

3.1.1 mv到其他分区

3.2 innodb_force_recovery参数说明

3.3 拷⻉句柄⽂件恢复步骤

3.4 从frm⽂件获取建表语句

3.4.1 dbsake使⽤说明

3.4.1 dbsake 测试过程

MySQL ibdata⽂件删除故障分析

1. 故障复现

1.1 mv ibdata1 ibdata1_bak

1.1.1 查看状态

实例状态

⽂件句柄状态

1.1.2 mv ⽂件

mv ibdata

mv ibdata1 ibdata1_bak

1.1.3 查看句柄状态

句柄⽂件指向的⽬录⽂件发⽣了改变，猜测：inode未改变，所以句柄⽂件指向了新的⽂件；

已验证：在同分区，mv ibdata1到同分区其他⽬录下，⽂件句柄⽂件会刷新为的mv后的⽂件，并不会变为delete状态

mv 到其他分区请参考：附录3.1.1 mv到其他分区

1.1.4 重启服务

重启后MySQL服务并不会读取ibdata_bak 的备份⽂件

## 关闭mysql

mysql> shutdown;

## 重启 mysql

[root@centos-linux fd]# /data/mysql/base/base_5.7.27/bin/mysqld_safe --defaults-ﬁle=/data/mysql/base/base_5.7.27/etc/3301/my.cnf &

mysql-error.log

ibdata1不存在然后创建新ibdata1，但新ibdata1与已经存在的undolog不匹配导致报错，⽆法启动；

mv ibdata1后再删除undo redolog；可以正常启动，但表⽆法正常读取

1.2 先cp ibdata1备份然后rm ibdata1

1.2.1 先查看状态

使⽤cp ibdata1 ibdata1_bak 备份⼀份⽂件

使⽤stat命令查看ibdata1及ibdata1_bak⽂件inode

进⼊/proc/2821/fd ⽬录，使⽤ls 命令查看ibdata1操作系统句柄状态

1.2.2 删除ibdata⽂件

使⽤rm 命令删除ibdata1⽂件，保留ibdata1_bak⽂件

进⼊/proc/2821/fd ⽬录，使⽤ls 命令查看ibdata1操作系统句柄状态

复现现场故障现象

2 问题修复

2.1 MySQL进程还在

MySQL进程存在的情况下，操作系统还保留进程打开的⽂件句柄，可以进⼊/proc/$进程pid/fd ⽬录下查到⽂件对应的句柄⽂件，通过cp 拷⻉到原路径

下；

2.1.1 查看实例状态

##############for mgr

select * from performance_schema.replication_group_members;

SELECT ta.* ,tb.MEMBER_HOST,tb.MEMBER_PORT,tb.MEMBER_STATE FROM performance_schema.global_status ta,performance_schema.replication_

group_members tb WHERE ta.VARIABLE_NAME='group_replication_primary_member' and ta.VARIABLE_VALUE=tb.MEMBER_ID;

##################for slave

show slave status\G;

2.1.2 停⽌复制

##############for mgr

stop group_replication;

################for slave

stop slave；

###################################

2.1.3 刷新脏⻚

##### 添加全局读锁同时开启super_read_only

FLUSH TABLES WITH READ LOCK;

set global super_read_only=1;

##### 修改强制脏⻚刷新，调整innodb_io_capacity 增加IO刷新参数

set global innodb_max_dirty_pages_pct = 0;

set global innodb_io_capacity=2000;

##### 查找链接并组成kill 语句

select concat('kill ',id,';') from information_schema.processlist where db is not null;

##### 确认脏⻚是否刷新完成

show engine innodb status\G;

------------

TRANSACTIONS

------------

Trx id counter 6435

Purge done for trx's n:o < 6435 undo n:o < 0 state: running but idle

## 确保事务号⼀致

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 0 merges

## 确保insert buﬀer 合并插⼊缓存等于1

---

LOG

---

Log sequence number 1721029

Log ﬂushed up to 1721029

Last checkpoint at 1721029

## 确保这三个值不变

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 274726912; in additional pool allocated 0

Dictionary memory allocated 76753

Buﬀer pool size 16383

Free buﬀers 16181

Database pages 202

Old database pages 0

Modiﬁed db pages 0

## 确保脏⻚数量为0

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

0 read views open inside InnoDB

Main thread process no. 3415, id 140701545637632, state: sleeping

Number of rows inserted 0, updated 0, deleted 0, read 5

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

## 确保插⼊、更新、删除为0

2.1.4 查找MySQL进程句柄⽂件

## 查找MySQL进程

ps -ef |grep 端⼝号

## 根据进程号查找到对应的⽂件句柄

cd /proc/进程号/fd

ls -lrt|grep delete|grep innodb

## 备份源⽬录：

mv /ssddata/innodb/13390 /ssddata/innodb/13390_bak

mkdir /ssddata/innodb/13390

## 拷⻉⽂件句柄回去

cp 9 /ssddata/innodb/13770/ib_logﬁle1

cp 4 /ssddata/innodb/13770/ib_logﬁle0

cp 11 /ssddata/innodb/13770/ibtmp1

cp 10 /ssddata/innodb/13770/ibdata1

## ⽂件修改权限：

chown mysql:mysql -R /ssddata/innodb/13390

## 重启MySQL服务

mysql -h127.1 -P3390 -uroot -p'passwd' -A

shutdown;

##########For docker

docker restart container_id ####重启docker并启动MySQL

##########For 服务器

/$mysql_path/bin/mysqld_safe --defaults-ﬁle=/$path/my.cnf &

建议：实例重启后，使⽤mysqldump 导出⼀份表结构，然后备份所有的表数据⽂件；在新的实例导⼊表结构，然后使⽤表空间传输⽅式将数据恢复到

新实例；或者dump⼀份数据在新实例恢复

注意：1. 如果句柄最后变更时间停留在某个时间，会导致后来的DDL操作丢失

2.2 MySQL进程不存在，原ibdata保留

如果有最新的备份及binlog，可以使⽤备份+binlog的⽅式恢复；

ibdata1备份到其分区⽬录导致故障，因在删除后有数据写⼊，将原ibdata1⽂件拷⻉回原路径下，因LSN差距过⼤导致MySQL⽆法启动；

这种情况可以使⽤force recovery⽅式强制启动MySQL服务，导出建表语句在新实例重新创建表然后使⽤可传输表空间⽅式进⾏恢复；

已知表数据⽂件没有损坏；如果是MySQL Crash后force recovery启动进⾏数据恢复，建议先dump出表数据的⽅式进⾏数据恢复，因为在

MySQLCrash情况下⽆法确认表数据⽂件是否损坏

2.2.1 先备份业务库表数据⽂件

cp *.ibd /backup/*.ibd

2.2.2 修改my.cnf参数

在my.cnf⽂件添加innodb_force_recovery参数，建议从1 增加到6逐⼀进⾏尝试；数据越⼤对MySQL影响越⼤

innodb_force_recovery = [1-6]

innodb_force_recovery 参数说明请参考附录 3.1 innodb_force_recovery参数说明

2.2.3 dump表结构

使⽤mysqldump 把mysql.user表数据

mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=oﬀ --hex-blob --no-cre

ate-info

--tables mysql user > mysql_user.sql

使⽤mysqldump 导出业务库的表结构，如果ibdata⽂件不是最新可能会丢失部分表的表结构：

mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=oﬀ --hex-blob --no-data

employees > employees_schema.sql

2.2.4 创建表/导⼊表结构

在新的空实例中导⼊mysql.user表数据，及业务表；

source mysql_user.sql

source employees_schema.sql

2.2.5 删除业务表表空间

alter table tablename discard tablespace;

2.2.6 将备份业务库表数据⽂件拷⻉回数据⽬录

1. 拷⻉数据⽂件备份到数据⽬录

cp *ibd /data/mysql/data/3301/data/*.idb

2. 修改权限

chown mysql:mysql *

3. 导⼊表空间

组装sql语句，将业务表空间导⼊新实例

alter table tablename import tablespace;

2.2.7 将恢复后的新实例作为业务库对外提供服务

2.3 MySQL进程不存在，原ibdata被删除

2.3.1 在新实例创建表

如果可以获取到表建表语句，在新实例先创建所有的表，然后使⽤可传输表空间⽅法恢复表数据

可传输表空间请参考：2.2 MySQL进程不存在，原ibdata保留⽅式恢复

2.3.2 没有建表语句

如果没有建表语句，但表frm⽂件保存；可以使⽤⼯具解析frm获取建表语句

从frm⽂件获取建表语句请参考：3.4 从frm⽂件获取建表语句

2.3.3 将建表语句导⼊新实例

将使⽤dbsake获得的建表sql导⼊新实例，然后使⽤可传输表空间恢复表数据

3 附录

3.1 ibdata 不同mv⽅式验证

3.1.1 mv到其他分区

– 和直接删除相同，因为每个分区都是有⾃⼰的inode区，mv到其他分区相当于从当前分区删除该⽂件

3.2 innodb_force_recovery参数说明

当数据库崩溃时，可以尝试使⽤ SELECT … INTO OUTFILE 来导出数据，通常情况下，⽤这种⽅式导出来的数据是完整的。对于导不出来的

表，可以加上 order by PK 来导出部分数据。

崩溃⽐较严重时，这种⽅式可能因后台 innodb 相应线程中断⽽失效，甚⾄ innodb 会回滚数据来恢复崩溃。这种情况下，则可以在 [mysqld] 下

设置 innodb_force_recovery 来强制启动 innodb，同时避免后台操作，以⽅便导出数据。

该参数默认为 0，范围 1~6，每⼀级的功能不⼀样，较⼤的值包含较⼩值的功能。

仅在紧急情况下将 innodb_force_recovery 设置为⼤于 0 的值，以便可以启动 InnoDB 并转储表。在此之前，请确保有全量备份。

如果能够以 3 或者以下的值转储数据，则数据是相对安全的，只有损坏的单个⻚上的数据会丢失。对于⼤于 3 的数据转储，数据则就危险了。

做为⼀种安全措施，对于 0< x <=3，innodb 禁⽌ insert update delete（即此时可能会导致复制相关信息出错，因为⼀些复制信息是放在表中

的），但可以 drop create；对于 3< x <=6，innodb 则处于只读状态，在 5.7.17 及之前，此时也可以 drop，⾼于该版本后则不能。之所以有

drop 权限，可能是因为某张表损坏⽽导致的崩溃，删除后就好了。

设置为 3/4 后，重启依旧费时，此时有极⼤的可能是在处理 undo log、redo log，时间应该耗在这了。

⼤于等于 4 的值可能会永久地破坏数据⽂件，所以尝试使⽤该参数来启动数据库时，尽可能的从 1 开始逐渐往上升。

如果明确是哪张表引起的崩溃，则可以直接删除再启动。如果是因批量操作但回滚⽽引起的，则可以设置为 3 及以上，禁⽌事务的回滚，然后

再删除相应的表。

1. 忽略检测到的损坏⻚，尝试使⽤ select 去导出数据。

2. 禁⽌ master purge 线程运⾏。⽐如在 purge 期间可能导致崩溃，则会禁⽌ purge 线程。

3. 在崩溃恢复时禁⽌事务的回滚。

如果 change buffer 可能导致崩溃，则禁⽌ change buffer 功能；

该值可能会导致数据⽂件被破坏；

使⽤该值后，最好重建所有的⼆级索引。

设置 innodb 为只读。

启动的时候忽略 undo log，这必然会导致 innodb 将未 commit 的事物视为已 commit。

该值可能会导致数据⽂件被破坏；

设置 innodb 为只读。

不执⾏与恢复相关的 redo log 的前滚。

该值可能会导致数据⽂件被破坏；

使数据库⻚⾯处于过时状态，可能会导致其它未损坏的⻚也连带着损坏。此时多表关联查询也可能⽤不了。

设置 innodb 为只读。

3.3 拷⻉句柄⽂件恢复步骤

3.4 从frm⽂件获取建表语句

3.4.1 dbsake使⽤说明

[root@centos-linux dbsake]# ./dbsake –help

Usage: dbsake [options]

Options:

-d, –debug

-q, –quiet

-V, –version Show the version and exit.

-?, –help Show this message and exit.

Commands:

decode-tablename Decode a MySQL ﬁlename.

encode-tablename Encode a MySQL table identiﬁer.

ﬁncore Report cached pages for a ﬁle.

frmdump Dump schema from MySQL frm ﬁles.

help Show help for a command.

sandbox Create a sandboxed MySQL instance.

sieve Filter and transform mysqldump output.

uncache Uncache ﬁle(s) from the OS page cache.

unpack Unpack a MySQL backup archive.

upgrade-mycnf Upgrade a MySQL option ﬁle

3.4.1 dbsake 测试过程

## 下载dbsake⼯具

[root@centos-linux dbsake]# curl -s get.dbsake.net > dbsake

[root@centos-linux dbsake]# ll

total 436

-rw-r--r--. 1 root root 445959 May 29 10:27 dbsake

[root@centos-linux dbsake]# ll

total 436

-rw-r--r--. 1 root root 445959 May 29 10:27 dbsake

## 添加权限

[root@centos-linux dbsake]# chmod u+x dbsake

You have new mail in /var/spool/mail/root

[root@centos-linux dbsake]# ll

total 436

-rwxr--r--. 1 root root 445959 May 29 10:27 dbsake

## 解析frm⽂件

[root@centos-linux dbsake]# ./dbsake frmdump /data/mysql/data/3301/test/sbtest5.frm > sbtest5.sql

[root@centos-linux dbsake]# ll

total 440

-rwxr--r--. 1 root root 445959 May 29 10:27 dbsake

-rw-r--r--. 1 root root 386 May 29 10:28 sbtest5.sql

## 查看解析后的建表语句

[root@centos-linux dbsake]# cat sbtest5.sql

-- Table structure for table `sbtest5`

-- Created with MySQL Version 5.7.27

CREATE TABLE `sbtest5` (

`id` int(11) NOT NULL,

`k` int(11) NOT NULL DEFAULT '0',

`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',

`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `k_1` (`k`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

of 1

免费下载

mysql

文档被以下合辑收录

墨天轮最受欢迎的技术文档-故障处理案例篇（共25篇）

本合辑整理了社区上一些数据库故障诊断思路、解决办法以及在日常运维中为防止故障发生可以进行的监控、配置优化方式和注意事项相关文档，主要包含 Oracle、MySQL 以及 PostgreSQL 三类数据库，欢迎大家下载、补充😀。

关注

文档被以下合辑收录

评论