恢复误删的innodb共享表空间文件

  innodb的共享表空间文件对于innodb数据库来说，那就是它的心脏，丢掉了这个东西意味着什么，不言而喻，当我们在一台整在运行的innodb数据库上误删了innodb共享表空间文件及innodb日志文件，是不是就意味着彻底完了呢，不是这样的，数据库在运行的时候，如果你误删了innodb共享表空间文件，或者是innodb日志文件，或者是其中之一，只要你不重启数据库，还是有办法恢复过来的，请你先不要惊慌，下面做个测试：

 

mysql> show variables like "%innodb_data%";
+-----------------------+-------------------------------------+
|Variable_name        |Value                              |
+-----------------------+-------------------------------------+
| innodb_data_file_path | ibdata1:200M;ibdata2:10M:autoextend|
| innodb_data_home_dir  |/opt/app/mysql5.5/innodata         |
+-----------------------+-------------------------------------+
2 rows in set (0.00 sec)

mysql>

 在我们发现表空间被误删出后，请立刻把它与前端业务隔离开，不要在继续往库里写数据（当然这时候还是可以继续往里面读写内容的）为了保证数据的一致性，请隔离业务，（切记，此时千万不要关闭mysql，如果关闭或者重启了mysql，此方法无效）然后登陆数据库，执行

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)

 

 刷新表并禁止继续写数据；然后使用

mysql> show  engine innodb status\G

 

 观察想关的状态，脏页（BUFFER POOL AND MEMORY: ->Modified db pages  0）为0，
日志（LOG: ->LSN 的三个位置不在变化），插入缓存（INSERT BUFFER AND ADAPTIVE HASHINDEX：—> Ibuf: size 1,），文件读写是否是0（FILE I/O ：-> 0.00 reads/s, 0avg bytes/read, 0.00 writes/s, 0.00fsyncs/s）;这些条件都具备了后，继续往下操作：
如果脏页不为0，那么请设置参数：

 

mysql> show variables like"%dirty_pages_pct%";      
+----------------------------+-------+
|Variable_name             | Value |
+----------------------------+-------+
| innodb_max_dirty_pages_pct |75    |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_max_dirty_pages_pct = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like"innodb_max_dirty_pages_pct";
+----------------------------+-------+
|Variable_name             | Value |
+----------------------------+-------+
| innodb_max_dirty_pages_pct |0    |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql>

 

现在，回到系统shell：

 

[root@localhost fd]# netstat -lntp|grepmysql         
tcp       0     00.0.0.0:3306        0.0.0.0:*             LISTEN     10560/mysqld       
[root@localhost fd]# ll /proc/10560/fd|egrep 'ib_|ibda'
lrwx------ 1 root root 64 Apr 29 11:32 10 ->/opt/app/mysql5.5/innodata/ib_logfile0
lrwx------ 1 root root 64 Apr 29 11:32 11 ->/opt/app/mysql5.5/innodata/ib_logfile1
lrwx------ 1 root root 64 Apr 29 11:32 4 ->/opt/app/mysql5.5/innodata/ibdata1
lrwx------ 1 root root 64 Apr 29 11:32 9 ->/opt/app/mysql5.5/innodata/ibdata2
[root@localhost fd]#
 
现在我们删除“/opt/app/mysql5.5/innodata/”下的共享表空间，innodb及日志文件，再来看看有什么变化：

[root@localhost innodata]# ll /proc/10560/fd|egrep'ib_|ibda'
lrwx------ 1 root root 64 Apr 29 11:32 10 ->/opt/app/mysql5.5/innodata/ib_logfile0 (deleted)
lrwx------ 1 root root 64 Apr 29 11:32 11 ->/opt/app/mysql5.5/innodata/ib_logfile1 (deleted)
lrwx------ 1 root root 64 Apr 29 11:32 4 ->/opt/app/mysql5.5/innodata/ibdata1 (deleted)
lrwx------ 1 root root 64 Apr 29 11:32 9 ->/opt/app/mysql5.5/innodata/ibdata2 (deleted)
[root@localhost innodata]#

 

文件标记为删除状态 ： (deleted)

下面我们将恢复innodb日志和innodb共享表空间，接下来，我们把相应的文件拷贝回去：

 

[root@localhost innodata]# cd /proc/10560/fd
[root@localhost fd]# cp 10/opt/app/mysql5.5/innodata/ib_logfile0
[root@localhost fd]# cp 11/opt/app/mysql5.5/innodata/ib_logfile1
[root@localhost fd]# cp 4 /opt/app/mysql5.5/innodata/ibdata1
[root@localhost fd]# cp 9 /opt/app/mysql5.5/innodata/ibdata2

 

回到数据目录下，

 

[root@localhost fd]# cd /opt/app/mysql5.5/innodata/
[root@localhost innodata]# ls
ibdata1  ibdata2 ib_logfile0  ib_logfile1

[root@localhost innodata]# ll
total 317772
-rw-r----- 1 root root 209715200 Apr 29 11:56 ibdata1
-rw-r----- 1 root root  10485760 Apr 29 11:57ibdata2
-rw-r----- 1 root root  52428800 Apr 29 11:56ib_logfile0
-rw-r----- 1 root root  52428800 Apr 29 11:56ib_logfile1
[root@localhost innodata]# chown -R mysql.mysql ./*

[root@localhost innodata]# ll
total 317772
-rw-r----- 1 mysql mysql 209715200 Apr 29 11:56 ibdata1
-rw-r----- 1 mysql mysql  10485760 Apr 29 11:57ibdata2
-rw-r----- 1 mysql mysql  52428800 Apr 29 11:56ib_logfile0
-rw-r----- 1 mysql mysql  52428800 Apr 29 11:56ib_logfile1
[root@localhost innodata]#

[root@localhost innodata]#/opt/app/mysql5.5/support-files/mysql.server restart
Shutting down MySQL..... SUCCESS!
Starting MySQL..... SUCCESS!
[root@localhost innodata]#
 
重启成功，我们在看文件状态,

 

[root@localhost innodata]# netstat -lntp|grep mysql
tcp       0     00.0.0.0:3306          0.0.0.0:*             LISTEN     14499/mysqld  

     
[root@localhost innodata]# ll /proc/14499/fd|egrep'ib_|ibda'   
lrwx------ 1 root root 64 Apr 29 11:59 10 ->/opt/app/mysql5.5/innodata/ib_logfile0
lrwx------ 1 root root 64 Apr 29 11:59 11 ->/opt/app/mysql5.5/innodata/ib_logfile1
lrwx------ 1 root root 64 Apr 29 11:59 4 ->/opt/app/mysql5.5/innodata/ibdata1
lrwx------ 1 root root 64 Apr 29 11:59 9 ->/opt/app/mysql5.5/innodata/ibdata2
[root@localhost innodata]#

 

已经成功恢复了删除的文件。

完。