人工误删除了MYSQL的InnoDB ibdata数据文件，如何快速恢复

小咪咪 2021-09-25 22:36 299阅读 0赞

最近看了一本 mysql管理之道的书，有关于人工不小心删除 mysql的表空间文件和重做日志文件时该如何恢复，

这里笔记一下以防用的上。

查看目前MYSQL数据目录文件情况：

\[root@myDBserver data\]\# ls -al  
总用量 110704  
drwxr-xr-x. 5 mysql mysql     4096 7月   3 14:29 .  
drwxr-xr-x. 4 mysql mysql     4096 6月   4 22:56 ..  
....  
\-rw-rw----. 1 mysql mysql 12582912 7月   3 14:29 ibdata1  
\-rw-rw----. 1 mysql mysql 50331648 7月   3 14:29 ib\_logfile0  
\-rw-rw----. 1 mysql mysql 50331648 6月   4 23:08 ib\_logfile1  
....  
然后使用 rm -f ib\* 删除数据文件ibdata1和redo日志文件ib\_logfile0、ib\_logfile1  
  
  
恢复过程：  
**(1) 确保mysqld进程还存在，切勿 pkill掉该进程**  
\[root@myDBserver /\]\# netstat -ntlp | grep mysqld  
tcp  0    0 :::3306   :::\*    LISTEN    3415/mysqld    
注意最后信息 "3415/mysqld"  
  
  
**(2) 查看刚才被删除的文件**  
\[root@myDBserver /\]\# ll  /proc/3415/fd | egrep 'ib\_|ibdata'  
lrwx------ 1 root root 64 7月   3 14:33 10 -> /opt/mysql/data/ib\_logfile1  
lrwx------ 1 root root 64 7月   3 14:33 4 -> /opt/mysql/data/ibdata1  
lrwx------ 1 root root 64 7月   3 14:33 9 -> /opt/mysql/data/ib\_logfile0  
  
  
其中 10,4,9就是需要恢复的文件  
  
  
**(3) 关闭前端业务或执行 FLUSH TABLES WITH READ LOCK;**  
  
  
**(4) 验证是否此刻数据库已经没有写入操作了**  
mysql> set global innodb\_max\_dirty\_pages\_pct = 0;  
  
  
【4.1】查看InoDB 目前的状态，确保脏数据已经刷入磁盘  
mysql> show engine innodb status\\G;  
\------------  
TRANSACTIONS  
\------------  
Trx id counter 6435  
Purge done for trx's n:o < 6435 undo n:o < 0 state: running but idle  
\#\# 确保事务号一致  
\-------------------------------------  
INSERT BUFFER AND ADAPTIVE HASH INDEX  
\-------------------------------------  
Ibuf: size 1, free list len 0, seg size 2, 0 merges  
\#\# 确保insert buffer 合并插入缓存等于1  
\---  
LOG  
\---  
Log sequence number 1721029  
Log flushed up to   1721029  
Last checkpoint at  1721029  
\#\# 确保这三个值不变  
\----------------------  
BUFFER POOL AND MEMORY  
\----------------------  
Total memory allocated 274726912; in additional pool allocated 0  
Dictionary memory allocated 76753  
Buffer pool size   16383  
Free buffers       16181  
Database pages     202  
Old database pages 0  
Modified db pages  0  
\#\# 确保脏页数量为0  
\--------------  
ROW OPERATIONS  
\--------------  
0 queries inside InnoDB, 0 queries in queue  
0 read views open inside InnoDB  
Main thread process no. 3415, id 140701545637632, state: sleeping  
Number of rows inserted 0, updated 0, deleted 0, read 5  
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s  
\#\# 确保插入、更新、删除为0  
  
  
【4.2】查看主库目前的状态，确保已经不再发生变化  
mysql> show master status;  
\+------------------+----------+--------------+------------------+-------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
\+------------------+----------+--------------+------------------+-------------------+  
| mysql-bin.000016 |      120 |              |                  |                   |  
\+------------------+----------+--------------+------------------+-------------------+  
1 row in set (0.00 sec)  
  
  
mysql> show master status;  
\+------------------+----------+--------------+------------------+-------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
\+------------------+----------+--------------+------------------+-------------------+  
| mysql-bin.000016 |      120 |              |                  |                   |  
\+------------------+----------+--------------+------------------+-------------------+  
1 row in set (0.00 sec)  
  
  
  
  
**(5) 恢复数据文件和日志文件**  
\[root@myDBserver /\]\# ll  /proc/3415/fd | egrep 'ib\_|ibdata'  
lrwx------ 1 root root 64 7月   3 14:33 10 -> /opt/mysql/data/ib\_logfile1  
lrwx------ 1 root root 64 7月   3 14:33 4 -> /opt/mysql/data/ibdata1  
lrwx------ 1 root root 64 7月   3 14:33 9 -> /opt/mysql/data/ib\_logfile0  
\[root@myDBserver /\]\# cd /proc/3415/fd  
\[root@myDBserver fd\]\# pwd  
/proc/3415/fd  
  
  
\[root@myDBserver fd\]\# cp 10 /opt/mysql/data/ib\_logfile1  
\[root@myDBserver fd\]\# cp 4 /opt/mysql/data/ibdata1  
\[root@myDBserver fd\]\# cp 9 /opt/mysql/data/ib\_logfile0  
  
  
\[root@myDBserver fd\]\# cd  /opt/mysql/data/  
\[root@myDBserver data\]\# chown mysql:mysql ib\*  
  
  
**(6) 重启mysql 即可完成**  
\[root@myDBserver /\]\# /etc/init.d/mysql restart;  
Shutting down MySQL.. SUCCESS!   
Starting MySQL.. SUCCESS!   
\[root@myDBserver /\]\#