前言
MySQL从5.5版本开始,InnoDB存储引擎已经成为默认存储引擎。从物理文件的结构来看,InnoDB包含ibdata1,ib_logfilexx,数据文件等其他日志文件;其中参数innodb_file_per_table用于设置是否开启独立表空间,当开启独立表空间时数据和索引会存在于ibd文件中,当未开启独立表空间时,此时使用的是共享表空间,数据和索引会存储在ibdata1文件中。如果在清理空间时不小心把ibdata1文件删除了,未开启独立表空间时,数据也会被删除,只能从备份中恢复,没有其他办法。如果开启了独立表空间,可从ibd文件中恢复数据。本文将介绍在开启独立表空间一些恢复案例,前提是无备份。
准备
MySQL版本:percona server 5.6.36
案例步骤
- 启动MySQL实例
- 插入数据,中间穿插创建删除表,操持ibdata1内部tablespace的ID真实性
- 模拟删除ibdata1和ib_logfile文件
- 保存数据文件和表结构文件到其他目录
- 提取该实例的所有表结构
- 通过提取的表结构,创建实例下的所有表
- 通过ALTER TABLE dbName.tableName DISCARD TABLESPACE删除新创建表的ibd文件
- 拷贝原数据文件到对应的ibd文件目录
- 修改ibd文件权限
- 通过ALTER TABLE dbName.tableName IMPORT TABLESPACE导入拷贝的ibd文件
- 使用mysqldump导出数据
- 重建实例,导入数据
原理分析
- 恢复的步骤中,其中修改InnoDB表的tablespace ID最为重要。默认情况下,当开启独立表空间时,即使表数据文件和索引存在于数据目录中,但是每个表都有一个表的空间ID做标识,这部分标识同样存在于ibdata1中,是一个关联的关系。当两者不一致时,数据字典里找不到表,InnoDB引擎就无法加载数据目录下的ibd文件。恢复的目的使两者保持一致,正常加载数据。
- ibdata1内部结构
- ibd内部结构
- 图片来源于Jeremy Cole,原理写的很清楚,请参考。
- ibdata1中的insert buffer/double buffer等可以不用关心,本次恢复用不到。
案例
启动MySQL实例
1 | [root@iZuf6c08fdv8duubho2b0rZ ~]# ll /hwdata/data/percona |
插入数据
1 | [root@iZuf6c08fdv8duubho2b0rZ ~]# cat auto_insert_data.sh #简单的插入脚本 |
查看数据
1 | [root@iZuf6c08fdv8duubho2b0rZ ~]# mysql -uroot -p123456 test1 |
删除ibdata1
1 | [root@iZuf6c08fdv8duubho2b0rZ ~]# cd /hwdata/data/percona |
保存数据文件
1 | [root@iZuf6c08fdv8duubho2b0rZ percona]# cp -a test1/ /usr/src/ #数据暂时保存到/usr/src目录下 |
恢复表结构
如果表结构保存的有,不需要此步骤,直接按照表结构重新创建表
此步骤使用官方工具mysql-utilities通过frm文件提取表结构,地址:
mysql-utilities原理
- 默认以再生实例启动,读取frm文件,再生实例关闭,清理临时文件
- 另一个模式是诊断模式,需要指定 –diagnostic 选项。byte-by-byte读取.frm文件,该模式有更多的局限性,不能校验字符集
- 请参考官网:
注意事项
- 某些引擎表在默认模式下不可读取的。如PARTITION, PERFORMANCE_SCHEMA,必需在诊断模式下可读。
- 要在创建语句中改变存储引擎,可使用–new-storage-engine 选项。如果有指定该选项,同时必须指定–frmdir选项,该工具生成新的.frm文件,前缀为new_,并保存在–frmdir目录下。
- 关掉所有信息除了CREATE 语句和警告或错误信息,使用–quiet选项。
- 使用–show-stats 选项统计每个.frm文件信息。
- 使用–user 选项指定再生的实例以哪个权限运行。
- 如果再生的实例超过10秒启动,需调大–start-timeout 选项参数。
mysql-utilities安装
由于yum安装的是1.3的版本,1.6有bug,使用1.5.6版本,故采用源码包的形式安装,安装过程中如报错,请先安装驱动程序Connector/Python
1 | [root@iZuf6c08fdv8duubho2b0rZ ~]# cd /usr/src/ |
以上为生成的可执行文件中,我们只需要用到mysqlfrm,用于表结构恢复
开始提取frm表结构
此用法启动了3333的实例,读取hwdata/data/percona/test1/下的所有frm文件,没有指定数据库,生成了以最后一个文件夹为DB名字的表结构
1 | [root@iZuf6c08fdv8duubho2b0rZ percona]# mysqlfrm --basedir=/usr/local/mysql --port=3333 --user=mysql /hwdata/data/percona/test1/ > table_frm.sql |
导入frm表结构
确认数据文件已经保存至其他目录
1 | [root@iZuf6c08fdv8duubho2b0rZ percona]# ll /usr/src/test1/ #确认下数据文件是否保存在此 |
删除新创建表的ibd文件
由于表比较多,通过脚本的形式去执行批量删除操作
1 | [root@iZuf6c08fdv8duubho2b0rZ ~]# cat auto_delete_tablespace.sh #简单的批量删除表空间脚本 |
导入原数据ibd文件
由于原表过多,通过脚本实现
1 | [root@iZuf6c08fdv8duubho2b0rZ ~]# cat auto_recovery_data.sh #恢复ibd文件脚本 |
数据验证
通过count计数,比较数据是否恢复正常
1 | [root@iZuf6c08fdv8duubho2b0rZ ~]# mysql -uroot -p123456 test1 |
mysqldump导出
1 | [root@iZuf6c08fdv8duubho2b0rZ ~]# mysqldump -uroot -p123456 test1 --set-gtid-purged=OFF --opt -q --master-data=2 --single-transaction -R --events --triggers > test1_20170728.sql |
重启导入
可把test1库删除后,重启服务,重新创建test1库,把mysqldump出的文件再次导入。
总结
- 像实验中案例,误删除ibdata1数据的经常会出现。
- 但是在最新的5.6.36版本中,不管是当前实验案例,还是直接从其他实例拷贝frm和ibd文件,在当前实例中重新创建表,再discard掉ibd文件,最后再import ibd文件,竟然没有报表空间ID不一致的错误。以前在5.5版本中,确认是会报表空间ID不一致情况,不确定是不是5.6版本改进了这方面的功能。
- 5.5版本版本恢复案例请参考(https://dbarobin.com/2016/04/23/ibd-recovery/),思路不错。