mysql 误删除ibdata1,数据库重启之后的恢复方法(二)

本文介绍了MySQL数据库在正常关闭下删除ibdata1和ib_logfile后的恢复步骤,包括创建测试数据、关闭数据库、复制数据文件等。还指出删除ibdata1会导致部分表无法访问,给出了重建表的两种方法,并总结了使用可传输表空间恢复数据库的要点。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

场景:

昨天,有个朋友对公司内部使用的一个MySQL实例开启binlog,但是在启动的过程中失败了(他也没提,为何会失败),在启动失败后,他删除了ibdata1和ib_logfile,后来,能正常启动了,但所有的表通过show tables能看到,但是select的过程中却报“Table doesn't exist”。

于是,建议可传输表空间。

恢复的基本步骤

1. 将原来的数据文件COPY到其它目录下。

2. 创建同名表,表结构必须保持一致。

3. 导出表空间

mysql> ALTER TABLE t DISCARD TABLESPACE;

4. 将原来的数据文件COPY回来

5. 导入表空间

mysql> ALTER TABLE t IMPORT TABLESPACE

创建测试数据

MariaDB [test]> create table t1(id int,hiredate datetime);

Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> create table t2(id int,hiredate datetime);

Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> insert into t1 values(1,now());

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into t1 values(2,now());

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into t2 values(1,now());

Query OK, 1 row affected (0.10 sec)

MariaDB [test]> insert into t2 values(2,now());

Query OK, 1 row affected (0.02 sec)

关闭数据库

service mysql stop

将原来的数据文件copy 至/data_bak数据库备份目录

[root@mariadb3 data]# cd /data/mysql/data
[root@mariadb3 data]# cp -r  * /data_bak/

删除ibdata1,ib_logfile0,ib_logfile1和ib_logfile2

[root@mariadb3 data]# rm -rf ibdata1

[root@mariadb3 data]# rm -rf ib_logfile*

[root@mariadb3 data]# ll

total 12332

-rw-rw----  1 mysql mysql    16384 Apr  3 15:49 aria_log.00000001

-rw-rw----  1 mysql mysql       52 Apr  3 15:49 aria_log_control

-rw-rw----  1 mysql mysql     4218 Apr  3 15:49 ib_buffer_pool

-rw-rw----  1 mysql mysql 12582912 Apr  3 15:49 ibtmp1

-rw-rw----  1 mysql mysql        0 Mar 28 14:30 multi-master.info

drwx------  2 mysql root      4096 Mar 28 14:30 mysql

-rw-rw----  1 mysql mysql        6 Apr  3 15:49 mysql.pid

drwx------  2 mysql mysql       20 Mar 28 14:30 performance_schema

drwx------  2 mysql mysql     4096 Mar 28 14:32 sakila

drwx------ 10 mysql root      4096 Apr  3 19:51 test

重启启动数据库

[root@mariadb3 data]# service mysql stop

Stopping mysql (via systemctl):                            [  OK  ]

[root@mariadb3 data]# service mysql start

Starting mysql (via systemctl):                            [  OK  ]

[root@mariadb3 data]# ll

total 1454124

-rw-rw----  1 mysql mysql      16384 Apr  3 19:55 aria_log.00000001

-rw-rw----  1 mysql mysql         52 Apr  3 19:55 aria_log_control

-rw-rw----  1 mysql mysql       4312 Apr  3 19:55 ib_buffer_pool

-rw-rw----  1 mysql mysql 1073741824 Apr  3 19:55 ibdata1

-rw-rw----  1 mysql mysql  134217728 Apr  3 19:55 ib_logfile0

-rw-rw----  1 mysql mysql  134217728 Apr  3 19:55 ib_logfile1

-rw-rw----  1 mysql mysql  134217728 Apr  3 19:55 ib_logfile2

-rw-rw----  1 mysql mysql   12582912 Apr  3 19:55 ibtmp1

-rw-rw----  1 mysql mysql          0 Mar 28 14:30 multi-master.info

drwx------  2 mysql root        4096 Mar 28 14:30 mysql

-rw-rw----  1 mysql mysql          6 Apr  3 19:55 mysql.pid

drwx------  2 mysql mysql         20 Mar 28 14:30 performance_schema

drwx------  2 mysql mysql       4096 Mar 28 14:32 sakila

drwx------ 10 mysql root        4096 Apr  3 19:51 test

在启动的过程中,MySQL会重建ibdata1和redo log。

登录mysql客户端,看之前创建的t1,t2是否能访问

MariaDB [test]> use test

Database changed

MariaDB [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t1             |

| t2             |

| t3             |

+----------------+

3 rows in set (0.00 sec)


MariaDB [test]> select * from t1;

ERROR 1932 (42S02): Table 'test.t1' doesn't exist in engine

通过mysqlfrm 查看表结构

[root@mariadb3 sakila]# mysqlfrm --basedir=/usr --port=3333 --user=mysql --basedir=/usr /data/mysql/data/test/t1.frm /data/mysql/data/test/t2.frm

或者

查看数据库目录下所有表的表结构

[root@mariadb3 sakila]# mysqlfrm --basedir=/usr --port=3333 --user=mysql --basedir=/usr /data/mysql/data/test/

# Spawning server with --user=mysql.

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

# Reading .frm files

#

# Reading the t1.frm file.

#

# CREATE statement for /data/mysql/data/test/t1.frm:

#


CREATE TABLE `test`.`t1` (

  `id` int(11) DEFAULT NULL,

  `hiredate` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8


#

# Reading the t2.frm file.

#

# CREATE statement for /data/mysql/data/test/t2.frm:

#


CREATE TABLE `test`.`t2` (

  `id` int(11) DEFAULT NULL,

  `hiredate` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

#...done.

创建表

创建表的时候报这个错误

MariaDB [test]> create table t1(id int,hiredate datetime);

ERROR 1050 (42S01): Table 't1' already exists

接下来,可先执行个drop table操作

MariaDB [test]> drop table t1;

Query OK, 0 rows affected, 1 warning (0.04 sec)


MariaDB [test]> drop table t2;

Query OK, 0 rows affected, 1 warning (0.03 sec)


MariaDB [test]> CREATE TABLE `test`.`t1` (

    ->   `id` int(11) DEFAULT NULL,

    ->   `hiredate` datetime DEFAULT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    ->

    -> ;

ERROR 1813 (HY000): Tablespace for table '`test`.`t1`' exists. Please DISCARD the tablespace before IMPORT


MariaDB [test]> CREATE TABLE `test`.`t2` (

    ->   `id` int(11) DEFAULT NULL,

    ->   `hiredate` datetime DEFAULT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    ->

    -> ;

ERROR 1813 (HY000): Tablespace for table '`test`.`t2`' exists. Please DISCARD the tablespace before IMPORT

解决方法到数据库test 目录删掉idb文件,上述问题时在非正常关闭数据库情况下会遇到的情况,如果数据库正常启动,drop table 时,ibd文件会自动删除

[root@mariadb3 test]# cd /data/mysql/data/test

[root@mariadb3 test]# rm -rf t1.ibd t2.ibd

MariaDB [test]> CREATE TABLE `test`.`t1` (

    ->   `id` int(11) DEFAULT NULL,

    ->   `hiredate` datetime DEFAULT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)


MariaDB [test]> CREATE TABLE `test`.`t2` (

    ->   `id` int(11) DEFAULT NULL,

    ->   `hiredate` datetime DEFAULT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    -> ;

Query OK, 0 rows affected (0.26 sec)

导出表空间

MariaDB [test]>  ALTER TABLE t1 DISCARD TABLESPACE;

Query OK, 0 rows affected (0.01 sec)


MariaDB [test]>  ALTER TABLE t2 DISCARD TABLESPACE;

Query OK, 0 rows affected (0.11 sec)

这个时候,数据目录下的test目录下,数据文件没有了,只剩下了表结构文件

[root@mariadb3 test]# ll

-rw-rw---- 1 mysql mysql   464 Apr  3 20:11 t1.frm

-rw-rw---- 1 mysql mysql   464 Apr  3 20:11 t2.frm

Copy 数据文件到原目录

只是将t1,t2表的数据文件COPY回来

[root@mariadb3 data_bak]# cd /data_bak/test/

[root@mariadb3 test]# cp t1.ibd t2.ibd /data/mysql/data/test/

[root@mariadb3 test]# cd /data/mysql/data/test/

[root@mariadb3 test]# chown mysql.mysql t1.ibd t2.ibd

导入表空间

MariaDB [test]> ALTER TABLE t1 IMPORT TABLESPACE;

Query OK, 0 rows affected, 1 warning (0.08 sec)


MariaDB [test]> ALTER TABLE t2 IMPORT TABLESPACE;

Query OK, 0 rows affected, 1 warning (0.06 sec)

查看数据有没有被还原回来

MariaDB [test]> select * from t1;

+------+---------------------+

| id   | hiredate            |

+------+---------------------+

|    1 | 2018-04-03 04:40:06 |

|    2 | 2018-04-03 04:40:14 |

+------+---------------------+

2 rows in set (0.00 sec)


MariaDB [test]> select * from t2;

+------+---------------------+

| id   | hiredate            |

+------+---------------------+

|    1 | 2018-04-03 04:41:09 |

|    2 | 2018-04-03 04:41:15 |

+------+---------------------+

2 rows in set (0.00 sec)

事实上,在数据库正常关闭下删除ibdata1,会导致mysql库中的以下几张表无法访问

MariaDB [test]> select table_name from information_schema.tables  where table_schema='mysql' and engine='innodb';

+--------------------+

| table_name         |

+--------------------+

| gtid_slave_pos     |

| innodb_index_stats |

| innodb_table_stats |

+--------------------+

MariaDB [test]> select * from mysql.innodb_index_stats;

ERROR 1932 (42S02): Table 'mysql.innodb_index_stats' doesn't exist in engine

MariaDB [test]> select * from mysql.gtid_slave_pos;

ERROR 1932 (42S02): Table 'mysql.gtid_slave_pos' doesn't exist in engine

MariaDB [test]> select * from mysql.innodb_table_stats;

ERROR 1932 (42S02): Table 'mysql.innodb_table_stats' doesn't exist in engine

要解决这个问题,只能重建这些表。有以下两种方法:

1)备份恢复好的数据库,然后初始化数据库生成新的mysql 数据库,还原备份的业务数据库;

2)安装新的实例,导出需要恢复的mysql表,在导入到原来的数据库中。

总结:

经过上面的一系列测试,可以看到

1. 使用可传输表空间,可以解决在删除ibdata1和ib_logfile的情况下恢复MySQL数据库,当然,本文测试的前提是数据库正常关闭下删除的ibdata1和ib_logfile。

2. 使用可传输表空间,建议新建表的表结构和原来的表结构完全一致,同时,在导入表空间前,只需COPY回原来的数据文件,即ibd

3. 删除ibdata1ib_logfile的情况下,所有业务数据库的所有表都要经过上述方法来进行恢复,通过恢复所有数据库中的所有表之后,备份数据库,然后重新初始化数据库,然后在把备份导入数据库是比较好的解决方法。

### 使用 Navicat Premium 15 恢复 MySQL 数据库误删除的数据 #### 停止 MySQL 服务 为了最大限度减少数据丢失的风险,在发现误删之后应当立即停止 MySQL 服务。这可以防止新的事务日志被创建并覆盖旧的日志条目。 ```bash sudo systemctl stop mysql.service ``` #### 备份现有数据文件 在尝试任何恢复操作前,建议先备份现有的数据库文件夹以防万一。如果 `innodb_file_per_table` 设置为 ON,则每个 InnoDB 表都有独立的 .ibd 文件;如果是 OFF,则所有表共享同一个 ibdata1 文件[^2]。 对于开启了 `innodb_file_per_table` 的情况: ```bash cp -r /var/lib/mysql/*_table.ibd ~/backup/ ``` 而对于未开启的情况则需复制整个 `/var/lib/mysql/` 目录下的内容作为备份。 #### 利用进制日志 (Binlog) 进行时间点恢复 如果启用了 Binlog 功能,并且有足够的历史记录来捕捉到删除事件之前的更改,那么可以通过解析这些日志来进行精确的时间点恢复。使用 Python 工具如 binlog2sql 来生成回滚 SQL 脚本是一个不错的选择[^4]: 安装依赖项: ```bash pip install pymysql==0.7.9 git clone https://github.com/danfengcui/binlog2sql.git cd binlog2sql ``` 执行命令转换 binlog 至可逆向应用的 SQL 语句: ```bash python binlog2sql/binlog2sql.py \ -h localhost \ # 主机地址 -P 3306 \ # 端口号 -u root \ # 用户名 -p'password' \ # 密码 -d database_name \ # 需要处理的目标数据库名称 -t table_deleted \ # 删除了哪张表 --start-file='binlog_filename' \ # 开始读取哪个 binlog 文件 --start-position=start_position_value \ # 日志中的起始位置 --stop-position=end_position_value \ # 结束的位置 -B > rollback.sql # 输出至指定路径下的 sql 文件 ``` 上述脚本会根据给定参数构建出能够撤销特定范围内变更的操作指令集合——即所谓的 "undo log" 或者说是反向 DML(DCL/DQL),从而允许管理员通过导入此文件的方式撤消错误动作的影响。 #### 应用自动生成的回滚脚本 一旦获得了合适的回滚 SQL 文件,就可以将其应用于目标实例以完成实际的数据恢复过程。在此之前记得重启 MySQL 并设置其处于单用户模式或只读状态以免再次遭受意外修改干扰。 ```sql SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; SOURCE /path/to/generated/rollback.sql ; COMMIT; ``` 最后一步就是验证所做的一切是否成功挽回损失的信息资源了! ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值