mysql8从ibd文件恢复数据 mysql idb恢复删除数据

转载

小鱼儿 2024-02-21 13:06:37

文章标签 mysql8从ibd文件恢复数据 mysql 数据库数据文件 文章分类 MySQL 数据库

场景：

昨天，有个朋友对公司内部使用的一个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. 删除ibdata1和ib_logfile的情况下，所有业务数据库的所有表都要经过上述方法来进行恢复，通过恢复所有数据库中的所有表之后，备份数据库，然后重新初始化数据库，然后在把备份导入数据库是比较好的解决方法。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：MySQL 多级collection mysql 多数据库

下一篇：springbootlog4j2配置 springboot log4j2

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯