现在的位置: 首页 > 关系型数据库 > MySQL数据库 > 正文

mysql误删除ibdata1之后的恢复方法

时间:2014年03月07日 | 分类:MySQL数据库 | 评论:0 条 | 浏览:2,919 次
环境模拟:
1.删除掉数据目录下,ib*文件:
   [root@jj-svn160_231 data]# rm -rf ib*
2.登录数据库插入数据:[发现数据可以正常插入]
   mysql> use test
   mysql> insert into a select * from a;
解决方法如下:
1.数据库不能停机
2.查看当前数据库进程号:从下面可以看出mysql的进程号为:20301
   [root@jj-svn160_231 test]# ps -ef |grep mysql
root     14473  4261  0 19:27 tty1     00:00:00 ./mysql -uroot -px xxxxxxx
root     19918     1  0 20:14 pts/0    00:00:00 /bin/sh /opt/mysql/bin/mysqld_safe --datadir=/home/mysql/data --pid-file=/home/mysql/data/jj-   svn160_231.dacn.org.pid
mysql    20301 19918  0 20:14 pts/0    00:00:00 /opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/home/mysql/data --plugin-dir=/opt/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/home/mysql/data/jj-svn160_231.dacn.org.pid --socket=/tmp/mysql.sock --port=3306
root     25443 14404  0 21:27 pts/0    00:00:00 ./mysql -uroot -px xxxxxxx
root     25624 14450  0 21:29 pts/1    00:00:00 grep mysql
3.从内存中查看当前ibdatta和ib_logfile文件信息(看到这里我们放心多了,文件还在,只是被标记为删除状态)
  [root@jj-svn160_231 test]# ls -la /proc/20301/fd/ |grep -e ibdata -e ib_
lrwx------ 1 root  root  64 Mar  6 21:30 10 -> /home/mysql/data/ib_logfile0 (deleted)
lrwx------ 1 root  root  64 Mar  6 21:30 11 -> /home/mysql/data/ib_logfile1 (deleted)
lrwx------ 1 root  root  64 Mar  6 21:30 4 -> /home/mysql/data/ibdata1 (deleted)
lrwx------ 1 root  root  64 Mar  6 21:30 9 -> /home/mysql/data/ibdata2 (deleted)
4.此时,直接将内存信息复制到数据目录是不行的,会造成空间损坏,因此我们应该采取加全局读锁操作,讲脏页全部刷入磁盘
   mysql> flush tables with read lock;
5.查看当前innodb引擎的状态:[保证log sequence number的值和last checkpoint 值相等]
   mysql> show  engine innodb  status\G;
---
LOG
---
Log sequence number 1838299
Log flushed up to   1838299
Last checkpoint at  1838299
  注:有时脏页比较多,可以设置全局参数,加快速度:mysql> set global innodb_max_dirty_pages_pct=0;
         保证一些后台线程完成它们的工作,如.ibuf=1
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
         purge thread 应该purge了全部的transcations
------------
TRANSACTIONS
------------
Trx id counter 2B02
Purge done for trx's n:o < 2503 undo n:o < 0
History list length 39
         确保innodb不再进行写操作:
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
239 OS file reads, 15 OS file writes, 13 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
6.以上数值如果都正常的话,则可以把内存的数据复制到数据目录,并修改数据的权限
    [root@jj-svn160_231 test]# cp /proc/20301/fd/10 /home/mysql/data/ib_logfile0
    [root@jj-svn160_231 test]# cp /proc/20301/fd/11 /home/mysql/data/ib_logfile1
    [root@jj-svn160_231 test]# cp /proc/20301/fd/4 /home/mysql/data/ibdata1
    [root@jj-svn160_231 test]# cp /proc/20301/fd/9 /home/mysql/data/ibdata2
    [root@jj-svn160_231 data]# chown mysql. ib*
7.释放全局读锁,并重启数据库,验证数据:
     mysql> unlock tables;
     [root@jj-svn160_231 bin]# /etc/init.d/mysqld restart
     至此,ib* 相关文件就已经全部恢复完成

×