β

一次人工误删除 innodb ibdata数据文件的恢复操作

小强的博客 132 阅读

由于不熟悉innodb引擎导致误删了 innodb ibdata(数据文件) 和 ib_logfile(rodo log重做事务日志文件).由于是测试环境没有太大的问题。但是遇到了问题就要去解决多积累经验。当时想到文件被进程占用只要不杀掉进程文件是可以找回来的(/pro/pid/fd/* 这个目录下可以找到被删除的文件)再经过google后这样的文章也很多,写下来记录一下。希望以后不要用到…….

注意:
如果mysql还可以正常工作的话,千万不要重启mysqld 否则就无法弥补了。

解决思路:
(1)找到mysql进程的pid


netstat -lnptl | grep mysqld
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1709/mysqld

(2)查看被删除的文件


[root@mysql_master ~]# ll /proc/1709/fd | egrep 'ib_|ibdata'
lrwx------ 1 root root 64 4月  10 06:21 10 -> /data/server/mysql-5.6.23/data/ib_logfile1 (deleted)
lrwx------ 1 root root 64 4月  10 06:21 11 -> /data/server/mysql-5.6.23/data/ib_logfile2 (deleted)
lrwx------ 1 root root 64 4月  10 06:21 4 -> /data/server/mysql-5.6.23/data/ibdata1 (deleted)
lrwx------ 1 root root 64 4月  10 06:21 9 -> /data/server/mysql-5.6.23/data/ib_logfile0 (deleted)

(3)锁定全局库只读或者关闭前端业务(为了防止写操作)


flush tables with read lock

验证是否还有写操作:
1> 脏页尽快刷入到磁盘


set global innodb_max_dirty_pages_pct=0

查看binlog写入情况,确保file 和 position的值没有再发生变化


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000042 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

查看innodb信息确保脏页数据已经刷入到磁盘了


mysql> show engine innodb status\G
------------
TRANSACTIONS
------------
Trx id counter 345349
Purge done for trx's n:o < 344366 undo n:o < 0 state: running but idle
# 确保后台purge进程把 undo log全部清除掉,事务id要一致
History list length 288
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2, OS thread handle 0x7fc216196700, query id 33 localhost root init
show engine innodb status

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
# innodb buffer 合并插入缓存等于1
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1106381, node heap has 2 buffer(s)
.00 hash searches/s, 0.00 non-hash searches/s

---
LOG
---
Log sequence number 5823134036
Log flushed up to   5823134036
Pages flushed up to 5823134036
Last checkpoint at  5823134036
 pending log writes, 0 pending chkp writes
 log i/o's done, 0.00 log i/o's/second
# 确保这三个值不再发上变化

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 549453824; in additional pool allocated 0
Dictionary memory allocated 60113
Buffer pool size   32767
Free buffers       31967
Database pages     798
Old database pages 314
Modified db pages  0
# 脏页数量为0

--------------
ROW OPERATIONS
--------------
 queries inside InnoDB, 0 queries in queue
 read views open inside InnoDB
Main thread process no. 1709, id 140471628355328, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
#确保插入 更新 删除 读取值为0
&#91;/python&#93;


<strong>如果以上工作都确认完毕后可以进行恢复工作了:</strong>
[python]
cd /proc/1709/fd 
cp 4     /data/server/mysql-5.6.23/data/ibdata1
cp 9     /data/server/mysql-5.6.23/data/ib_logfile0
cp 10   /data/server/mysql-5.6.23/data/ib_logfile1 
cp 11   /data/server/mysql-5.6.23/data/ib_logfile2

修改文件的用户属性


chown mysql:mysql /data/server/mysql-5.6.23/data/ib*

重启mysql


/etc/init.d/mysqld
作者:小强的博客
IT运维技术

发表评论