Mysql入门mysql 误删除ibdata1的恢复方法
《Mysql入门mysql 误删除ibdata1的恢复方法》要点: 问题描述: 观察一下网站,发现一切都很正常,数据的读取与写入操作都完全正常.MYSQL必读 其实,mysqld在运行状态中,会保持这些文件为打开状态, 例如: lrwx------ 1 root? root? 64 Aug? 7 23:29 3 -> /var/lib/mysql/ibdata1 (deleted) lrwx------ 1 root? root? 64 Aug? 7 23:29 8 -> /var/lib/mysql/ib_logfile0 (deleted) lrwx------ 1 root? root? 64 Aug? 7 23:29 9 -> /var/lib/mysql/ib_logfile1 (deleted) 14101是mysqld的pid(进程ID) ? 只要mysqld不结束,就可以通过proc文件系统找到这几个被删除的文件(已经被Mark为deleted状态). Query OK,0 ROWS affected (0.37 sec) ? 这时就要等它flush结束,怎样知道有没有结束呢?观察checkpoint age就可以了. --- LOG --- Log SEQUENCE NUMBER 363096003 Log flushed up TO 363096003 LAST checkpoint at 363096003 ? checkpoint age 就是 Log sequence number的值减去 Last checkpoint at的值, --- LOG --- Log SEQUENCE NUMBER 363096003 Log flushed up TO 363096003 LAST checkpoint at 363096003 ? checkpoint age 就是 Log sequence number的值减去 Last checkpoint at的值,那么表示所有的page都flush到硬盘文件中了.MYSQL必读 加速flush的过程,可以这样设置: Query OK,0 ROWS affected (0.01 sec) ? 此外,还必须保证一些后台的线程完成了它们的工作, Ibuf: SIZE 1,free list len 398,seg SIZE 400, ? 还有purge thread,它应该purge了全部的transactions: TRANSACTIONS ------------ Trx id counter 0 16644 Purge done FOR trx's n:o < 0 16644 undo n:o < 0 0 确保innodb不再进行写操作: -------- I/O thread 0 state: waiting FOR i/o request (INSERT buffer thread) I/O thread 1 state: waiting FOR i/o request (log thread) I/O thread 2 state: waiting FOR i/o request (READ thread) ?I/O thread 3 state: waiting FOR i/o request (WRITE thread) Pending normal aio reads: 0,aio writes: 0, ibuf aio reads: 0,log i/o's: 0,sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 332 OS file reads,47 OS file writes,32 OS fsyncs 0.00 reads/s,0 avg bytes/READ,0.00 writes/s,0.00 fsyncs/s 然后,把文件复制回去: root@localhost:/var/lib/mysql# cp /proc/14101/fd/8 /var/lib/mysql/ib_logfile0 root@localhost:/var/lib/mysql# cp /proc/14101/fd/9 /var/lib/mysql/ib_logfile1 修改权限: 重启mysqld root@localhost:/var/lib/mysql# /etc/init.d/mysql restart 总结: 《Mysql入门mysql 误删除ibdata1的恢复方法》是否对您有启发,欢迎查看更多与《Mysql入门mysql 误删除ibdata1的恢复方法》相关教程,学精学透。编程之家PHP学院为您提供精彩教程。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |