mysql删除ibdatal_MySQL 误删ibdata、ib_logfile恢复案例

昨天为了测试mysql数据库快速删除大库的方案，一时起意把redo和undo log也一起删除了，由此才有下文

一、前言

InnoDB 有两块非常重要的日志，一个是undo log，另外一个是redo log，前者用来保证事务的原子性以及InnoDB的MVCC，后者用来保证事务的持久性。

由于删除了这两个log，数据库又重启了，因此就需要一些其他办法来恢复数据库

二、mysqlfrm工具安装

要恢复数据，我们需要用到mysqlfrm工具, 需要安装MySQL Utilities包，这里可以采用yum等形式来安装，如下：

yum install mysql-utilities.noarch

三、开始数据恢复

由于影响的数据库比较多，大概40多个库，表大概有2500多个，手动操作很不现实，因此中间需要一些脚本代替体力劳动

3.1  用frm工具批量提取建表语句

mysqlfrm 是一个恢复性质的工具，用来读取.frm文件并从该文件中找到表定义数据生成CREATE语句

for n in `ls -d 10.*`;do mysqlfrm --basedir=/home/mysql/mysql --port=3336 --user=root /home/mysql/data/mysql_3306/data/$n/ >> ~/data/mysql_3316/test/da_frm.sql;done

然后检查da_frm.sql文件，生成的create建表语句，应该如下所示：

# Spawning server with --user=root.

# Starting the spawned server on port 3336 ... done.

# Reading .frm files

#

# Reading the @0024_@0024Inception_backup_information@0024_@0024.frm file.

#

# CREATE statement for /home/mysql/data/mysql_3306/data/10_0_0_5_3306_data/@0024_@0024Inception_backup_information@0024_@0024.frm:

#

CREATE TABLE `10_0_0_5_3306_data`.`$_$Inception_backup_information$_$` (

`opid_time` varchar(50) NOT NULL DEFAULT '',

`start_binlog_file` varchar(512) DEFAULT NULL,

`start_binlog_pos` int(11) DEFAULT NULL,

`end_binlog_file` varchar(512) DEFAULT NULL,

`end_binlog_pos` int(11) DEFAULT NULL,

`sql_statement` text,

`host` varchar(64) DEFAULT NULL,

`dbname` varchar(64) DEFAULT NULL,

`tablename` varchar(64) DEFAULT NULL,

`port` int(11) DEFAULT NULL,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`type` varchar(20) DEFAULT NULL,

PRIMARY KEY (`opid_time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

#

# Reading the data_collection.frm file.

#

# CREATE statement for /home/mysql/data/mysql_3306/data/10_0_0_5_3306_data_mart/data_collection.frm:

#

CREATE TABLE `10_0_0_5_3306_data`.`collection` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`rollback_statement` mediumtext,

`opid_time` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

#

# Reading the data_log.frm file.

#

。。。。。。

。。。。。。

。。。。。。

我这里的建表语句都比较规范，因此可以直接用vim或者sed工具，在create建表语句后面加上分号

3.2  批量生成建库语句

这里的命令是批量生成建库语句，并创建数据库

for n in `ls -d 10.*`;do /home/mysql/mysql-5.7.21/bin/mysql -uroot -pxxxx -h127.0.0.1 -P3306 -e "create database $n;";done

3.3  导入建表语句

mysql> source /home/mysql/data/mysql_3306/test/da_frm.sql

这里导入进去之后，可以检查一下，表是否创建成功

3.4  删除新建表的独立表空间

这里我们因为是多个库的,因此也需要一个for循环

for n in `/home/mysql/mysql-5.7.21/bin/mysql -uroot -pxxxx -h127.0.0.1 -P3306 -e "select concat(concat('alter table ',table_schema,'.',table_name), ' discard tablespace;') from information_schema.tables where table_schema != 'test' and table_schema != 'mysql' and table_schema != 'performance_schema' and table_schema != 'information_schema' and engine ='InnoDB';"`;do echo $n >>./test.sql;done

查看test.sql文件

alter table data_mart.$_$Inception_backup_information$_$ discard tablespace;

alter table data_mart.data_collection discard tablespace;

alter table data_mart.data_log discard tablespace;

alter table data_mart.data_schema discard tablespace;

alter table data_mart.data_table discard tablespace;

alter table loc_recruit.$_$Inception_backup_information$_$ discard tablespace;

alter table loc_recruit.recruit_emp_info discard tablespace;

alter table loc_recruit.recruit_task_info discard tablespace;

alter table rainbow.$_$Inception_backup_information$_$ discard tablespace;

alter table rainbow.job_deps discard tablespace;

alter table rainbow.task discard tablespace;

......

......

......

然后在mysql里面执行

mysql> source /home/mysql/data/mysql_3306/test/test.sql

然后在服务器上查看mysql的物理库文件，独立表空间应该已经被删除了(最好在干净的环境做)

3.5  批量复制表ibd文件

for n in `ls -d 10.*`;do cp ~/data/mysql_3306/data/$n/*.ibd ./$n/;done

这里注意文件权限是否正确

chown mysql:mysql *.ibd

chmod 660 *.ibd

3.6  批量导入表空间

首先生成导入语句

alter table data_mart.$_$Inception_backup_information$_$ import tablespace;

alter table data_mart.data_collection import tablespace;

alter table data_mart.data_log import tablespace;

alter table data_mart.data_schema import tablespace;

alter table data_mart.data_table import tablespace;

alter table loc_recruit.$_$Inception_backup_information$_$ import tablespace;

alter table loc_recruit.recruit_emp_info import tablespace;

alter table loc_recruit.recruit_task_info import tablespace;

alter table rainbow.$_$Inception_backup_information$_$ import tablespace;

alter table rainbow.job_deps import tablespace;

alter table rainbow.task import tablespace;

......

......

......

在mysql上执行source就好了，这时候观察，数据库里面的表和数据都已经恢复了