1. 编程号首页

如何在删除ibdata1的情况下恢复

编程小号 • • 未分类

如何在删除ibdata1的情况下恢复参考：https://www.cnblogs.com/ivictor/p/5784258.html基本步骤1，将原数据文件保存至其他路径2，创建同名表结构3，导出表空间4，将原数据.ibd文件copy回来5，导入表空间 创建表t1，并插入数据mysql>showcreatetablet1;+-+-…

参考：https://www.cnblogs.com/ivictor/p/5784258.html

基本步骤

1， 将原数据文件保存至其他路径

2，创建同名表结构

3， 导出表空间

4， 将原数据.ibd文件copy回来

5， 导入表空间

 

创建表t1，并插入数据

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | qing   |
|    1 | 大秦   |
+------+--------+
2 rows in set (0.00 sec)

mysql>

将ibdata1和ib_logfile0-2移走：

[root@localhost data]# ls
auto.cnf  ibdata1      ib_logfile1  itdb                       localhost-relay-bin.000005  master.info  mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  percona             relay-log.info  tony
books     ib_logfile0  ib_logfile2  localhost.localdomain.pid  localhost-relay-bin.index   mysql        mysql-bin.000002  mysql-bin.000004  mysql-bin.index   performance_schema  test
[root@localhost data]#
[root@localhost data]# mkdir /tmp/bak
[root@localhost data]# mv ibdata1 /tmp/bak
[root@localhost data]# mv ib_log* /tmp/bak
[root@localhost data]# ls
auto.cnf  itdb                       localhost-relay-bin.000005  master.info  mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  percona             relay-log.info  tony
books     localhost.localdomain.pid  localhost-relay-bin.index   mysql        mysql-bin.000002  mysql-bin.000004  mysql-bin.index   performance_schema  test
[root@localhost data]#

重启mysql，系统会重新创建者几个文件

[root@localhost data]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
[root@localhost data]# ls
auto.cnf  ibdata1      ib_logfile1  itdb                       localhost-relay-bin.000006  master.info  mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  mysql-bin.index  performance_schema  test
books     ib_logfile0  ib_logfile2  localhost.localdomain.pid  localhost-relay-bin.index   mysql        mysql-bin.000002  mysql-bin.000004  mysql-bin.000006  percona          relay-log.info      tony
[root@localhost data]#

查看error log也可以看到创建的记录：

2018-01-21 13:15:32 2753 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-01-21 13:15:32 2753 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2018-01-21 13:15:32 2753 [Note] InnoDB: Database physically writes the file full: wait...
2018-01-21 13:15:32 2753 [Note] InnoDB: Setting log file ./ib_logfile101 size to 32 MB
2018-01-21 13:15:32 2753 [Note] InnoDB: Setting log file ./ib_logfile1 size to 32 MB
2018-01-21 13:15:32 2753 [Note] InnoDB: Setting log file ./ib_logfile2 size to 32 MB
2018-01-21 13:15:32 2753 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

然后登陆mysql，访问刚刚创建的t1，显示 ERROR 1146 (42S02): Table ‘tony.t1’ doesn’t exist

mysql> show tables;
+----------------+
| Tables_in_tony |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> desc t1;
ERROR 1146 (42S02): Table 'tony.t1' doesn't exist
mysql> select * from t1;
ERROR 1146 (42S02): Table 'tony.t1' doesn't exist
mysql>

为什么呢？虽然我们开启了innodb_file_per_table功能，让innodb的数据存储在t1.ibd下，但是共享表空间ibdata1中存储有数据字典，innodb没有数据字典，那么它就找不到这个表了；参考

https://blog.csdn.net/jswangchang/article/details/81138240

[root@localhost data]# cat /etc/my.cnf | grep innodb_file_per_table
innodb_file_per_table=1					#very table has individule data space
[root@localhost data]# ls tony/
db.opt  t1.frm  t1.ibd

接下来我们把表数据移动至其他地方，然后创建相同表结构的表：

[root@localhost tony]# mv * /tmp/bak/
[root@localhost tony]# ls
[root@localhost tony]# ls /tmp/bak/
db.opt  ibdata1  ib_logfile0  ib_logfile1  ib_logfile2  t1.frm  t1.ibd
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1146 (42S02): Table 'tony.t1' doesn't exist
mysql>
mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 'tony.t1'
mysql> CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_tony |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

此时t1是个新表，里面没有数据，此时我们导出表空间，导出后ibd文件就看不到了，然后我们把原t1.ibd文件copy过来；然后修改属组和属主权限

mysql> select * from  t1;
Empty set (0.01 sec)

mysql> alter table t1 discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql>
[2]+  Stopped                 mysql  (wd: /tmp/mysql-5.6/data)
(wd now: /tmp/mysql-5.6/data/tony)
[root@localhost tony]# ls
t1.frm
[root@localhost tony]# cp /tmp/bak/t1.ibd ./
[root@localhost tony]# ls
t1.frm  t1.ibd
[root@localhost tony]# ll
total 108
-rw-rw---- 1 mysql mysql  8586 Jan 21 13:24 t1.frm
-rw-r----- 1 root  root  98304 Jan 21 13:27 t1.ibd
[root@localhost tony]# chown mysql.mysql t1.ibd
mysql> alter table t1 import tablespace;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './tony/t1.cfg', will attempt to import without schema verification |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | qing   |
|    1 | 大秦   |
+------+--------+
2 rows in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | qing   |
|    1 | 大秦   |
+------+--------+
2 rows in set (0.01 sec)

然后导入表空间，再select就可以查到数据了；flush tables刷新内存后依然可以；

删除ibdata1的时候，会影响所有的innodb表，包括系统表中的innodb表；有哪些系统表是Innodb? 在V5.6中比较少，只有下面几个，但是在V5.7以后就有很多了；

mysqlv5.6 >select table_schema,table_name,engine from information_schema.tables where engine='innodb' and table_schema='mysql';
+--------------+----------------------+--------+
| table_schema | table_name           | engine |
+--------------+----------------------+--------+
| mysql        | innodb_index_stats   | InnoDB |
| mysql        | innodb_table_stats   | InnoDB |
| mysql        | slave_master_info    | InnoDB |
| mysql        | slave_relay_log_info | InnoDB |
| mysql        | slave_worker_info    | InnoDB |
+--------------+----------------------+--------+
5 rows in set (0.01 sec)

mysqlv5.6 >select * from mysql.innodb_table_stats;
ERROR 1146 (42S02): Table 'mysql.innodb_table_stats' doesn't exist
mysql5.7 >select table_schema,table_name,engine from information_schema.tables where engine='innodb' and table_schema in ('mysql','sys','information_schema','performance_schema');
+--------------------+---------------------------+--------+
| table_schema       | table_name                | engine |
+--------------------+---------------------------+--------+
| information_schema | COLUMNS                   | InnoDB |
| information_schema | EVENTS                    | InnoDB |
| information_schema | OPTIMIZER_TRACE           | InnoDB |
| information_schema | PARAMETERS                | InnoDB |
| information_schema | PARTITIONS                | InnoDB |
| information_schema | PLUGINS                   | InnoDB |
| information_schema | PROCESSLIST               | InnoDB |
| information_schema | ROUTINES                  | InnoDB |
| information_schema | TRIGGERS                  | InnoDB |
| information_schema | VIEWS                     | InnoDB |
| mysql              | engine_cost               | InnoDB |
| mysql              | gtid_executed             | InnoDB |
| mysql              | help_category             | InnoDB |
| mysql              | help_keyword              | InnoDB |
| mysql              | help_relation             | InnoDB |
| mysql              | help_topic                | InnoDB |
| mysql              | innodb_index_stats        | InnoDB |
| mysql              | innodb_table_stats        | InnoDB |
| mysql              | plugin                    | InnoDB |
| mysql              | server_cost               | InnoDB |
| mysql              | servers                   | InnoDB |
| mysql              | slave_master_info         | InnoDB |
| mysql              | slave_relay_log_info      | InnoDB |
| mysql              | slave_worker_info         | InnoDB |
| mysql              | time_zone                 | InnoDB |
| mysql              | time_zone_leap_second     | InnoDB |
| mysql              | time_zone_name            | InnoDB |
| mysql              | time_zone_transition      | InnoDB |
| mysql              | time_zone_transition_type | InnoDB |
| sys                | sys_config                | InnoDB |
+--------------------+---------------------------+--------+
30 rows in set (0.01 sec)

如果ibdata1被删除了要恢复，必须新建表结构，按照上面的步骤来一遍；

今天的文章如何在删除ibdata1的情况下恢复分享到此就结束了，感谢您的阅读。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/5767.html

赞 (0)
编程小号编程小号
0

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注