mysql> show variables like "%innodb_data%";
+-----------------------+-------------------------------------+
|Variable_name
+-----------------------+-------------------------------------+
| innodb_data_file_path | ibdata1:200M;ibdata2:10M:autoextend|
| innodb_data_home_dir
+-----------------------+-------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)
mysql> show
日志(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
+----------------------------+-------+
| 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
+----------------------------+-------+
| innodb_max_dirty_pages_pct |0
+----------------------------+-------+
1 row in set (0.00 sec)
mysql>
现在,回到系统shell:
[root@localhost fd]# netstat -lntp|grepmysql
tcp
[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
[root@localhost innodata]# ll
total 317772
-rw-r----- 1 root root 209715200 Apr 29 11:56 ibdata1
-rw-r----- 1 root root
-rw-r----- 1 root root
-rw-r----- 1 root root
[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
-rw-r----- 1 mysql mysql
-rw-r----- 1 mysql mysql
[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
[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]#
已经成功恢复了删除的文件。
完。