人工误删除InnoDB ibdata数据文件与ib_logile重做日志文件如何恢复详细过程

  1. 云栖社区>
  2. 博客>
  3. 正文

人工误删除InnoDB ibdata数据文件与ib_logile重做日志文件如何恢复详细过程

科技小能手 2017-11-12 14:42:00 浏览584
展开阅读全文

    有人因为不熟悉InnoDB引擎,而误删除innoDB ibdata(数据文件)和ib_logfile(redo log重做事务日志文件),结果导致了悲剧的发生。如果有做主从复制同步那还好,如果是单机呢?如何恢复?

1)使用rm –f ib* 删除数据文件和重做日志文件

下面就来使用具体看看如何恢复。

若此时你发现数据库还可以正常工作,数据照样可以写入,切记,这时千万别把mysqld进程杀死,否则没法挽救。

先找到mysqld的进程pid,如下所示。

mysql01:/data/mysql3306/mysql # netstat -ntlp | grep mysqld

tcp   0      0 :::3306        :::*       LISTEN      13206/mysqld

 

这里是13206

之后要执行很关键的一步,输入如下命令,并查看结果:

mysql01:/data/mysql3306 # ll /proc/13206/fd | egrep 'ib_|ibdata'

lrwx------ 1 root root 64 Oct 28 14:08 10 -> /data/mysql3306/ib_logfile1 (deleted)

lrwx------ 1 root root 64 Oct 28 14:08 11 -> /data/mysql3306/ib_logfile2 (deleted)

lrwx------ 1 root root 64 Oct 28 14:08 4 -> /data/mysql3306/ibdata1 (deleted)

lrwx------ 1 root root 64 Oct 28 14:08 9 -> /data/mysql3306/ib_logfile0 (deleted)

 

10、11、4、9就是我们要恢复的文件,对应的文件。

 

这时,你可以把前端业务关闭,或者执行:

mysql@mysql01 ~ $mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.26-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

root@(none) 02:15:00>flush table with read lock;

Query OK, 0 rows affected (0.00 sec)

 

先输入以下命令,让脏页尽快刷入到磁盘里。

root@(none) 02:15:08>set global innodb_max_dirty_pages_pct=0;

Query OK, 0 rows affected (0.00 sec)

 

然后查看binlog日志写入情况

root@(none) 02:18:27>show master status;

+------------------+----------+--------------+------------------+-------------------------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |

+------------------+----------+--------------+------------------+-------------------------------------------+

| mysql-bin.000018 |      191 |              |                  | 279f439b-5d2f-11e5-ad29-000c294cec8f:1-35 |

+------------------+----------+--------------+------------------+-------------------------------------------+

1 row in set (0.00 sec)

 

最后再查看innodb状态信息,确保脏页已经刷入磁盘

root@(none) 02:19:31>show engine innodb status\G;

*************************** 1. row ***************************

  Type: InnoDB

  Name:

Status:

=====================================

2015-10-28 14:23:18 7f267ce54700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 30 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 2465 srv_idle

srv_master_thread log flush and writes: 2465

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 2

OS WAIT ARRAY INFO: signal count 2

Mutex spin waits 0, rounds 0, OS waits 0

RW-shared spins 2, rounds 60, OS waits 2

RW-excl spins 0, rounds 0, OS waits 0

Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl

------------

TRANSACTIONS

------------

Trx id counter 7943

Purge done for trx's n:o < 6922 undo n:o < 0 state: running but idle

##确保后台Purge进程把undo log全部清除掉,事务ID要一致。

History list length 73

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 4, OS thread handle 0x7f267ce54700, query id 27 localhost root init

show engine innodb status

--------

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

424 OS file reads, 5 OS file writes, 5 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 0 merges

##insert buffer 合并插入缓存等于1

merged operations:

 insert 0, delete mark 0, delete 0

discarded operations:

 insert 0, delete mark 0, delete 0

Hash table size 276671, node heap has 0 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number 1841823

Log flushed up to   1841823

Pages flushed up to 1841823

Last checkpoint at  1841823

0 pending log writes, 0 pending chkp writes

8 log i/o's done, 0.00 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 137363456; in additional pool allocated 0

Dictionary memory allocated 63832

Buffer pool size   8191

Free buffers       7920

Database pages     271

Old database pages 0

Modified db pages  0     #确保脏页数据为0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 0, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 271, created 0, written 1

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 271, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

0 read views open inside InnoDB

Main thread process no. 13206, id 139803249243904, state: sleeping

Number of rows inserted 0, updated 0, deleted 0, read 4

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s   #确保插入、更新、删除为 0

----------------------------

END OF INNODB MONITOR OUTPUT

============================

 

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

root@(none) 02:23:18>

 

上面一系列确认工作完成之后 ,就可以进行恢复操作了,删除文件

mysql01:/data/mysql3306 # ll /proc/13206/fd | egrep 'ib_|ibdata'

lrwx------ 1 root root 64 Oct 28 14:08 10 -> /data/mysql3306/ib_logfile1 (deleted)

lrwx------ 1 root root 64 Oct 28 14:08 11 -> /data/mysql3306/ib_logfile2 (deleted)

lrwx------ 1 root root 64 Oct 28 14:08 4 -> /data/mysql3306/ibdata1 (deleted)

lrwx------ 1 root root 64 Oct 28 14:08 9 -> /data/mysql3306/ib_logfile0 (deleted)

 

把这些文件复制 到原来 目录下

mysql01:/data/mysql3306 # cd /proc/13206/fd

mysql01:/proc/13206/fd # cp 10 /data/mysql3306/ib_logfile1

mysql01:/proc/13206/fd # cp 11 /data/mysql3306/ib_logfile2

mysql01:/proc/13206/fd # cp 4 /data/mysql3306/ibdata1

mysql01:/proc/13206/fd # cp 9 /data/mysql3306/ib_logfile0

 

然后修改用户属性与权限

mysql01:/proc/13206/fd # cd /data/mysql3306/

mysql01:/data/mysql3306 # ll

total 160148

-rw-rw---- 1 mysql app        56 Sep 17 19:28 auto.cnf

drwx------ 2 mysql app      4096 Oct 13 05:17 huizhe

-rw-r----- 1 root  root 50331648 Oct 28 14:33 ib_logfile0

-rw-r----- 1 root  root 50331648 Oct 28 14:32 ib_logfile1

-rw-r----- 1 root  root 50331648 Oct 28 14:32 ib_logfile2

-rw-r----- 1 root  root 12582912 Oct 28 14:33 ibdata1

drwx------ 2 mysql app      4096 Oct 28 09:32 mysql

-rw-rw---- 1 mysql app       214 Oct 29  2015 mysql-bin.000008

-rw-rw---- 1 mysql app       214 Oct 26 17:24 mysql-bin.000009

-rw-rw---- 1 mysql app       576 Oct 28 09:26 mysql-bin.000010

-rw-rw---- 1 mysql app      2166 Oct 28 09:30 mysql-bin.000011

-rw-rw---- 1 mysql app      5262 Oct 28 09:32 mysql-bin.000012

-rw-rw---- 1 mysql app       214 Oct 28 09:32 mysql-bin.000013

-rw-rw---- 1 mysql app       214 Oct 28 09:34 mysql-bin.000014

-rw-rw---- 1 mysql app       362 Oct 28 09:39 mysql-bin.000015

-rw-rw---- 1 mysql app       362 Oct 28 11:19 mysql-bin.000016

-rw-rw---- 1 mysql app       214 Oct 28 13:42 mysql-bin.000017

-rw-rw---- 1 mysql app       191 Oct 28 13:42 mysql-bin.000018

-rw-rw---- 1 mysql app       209 Oct 28 13:42 mysql-bin.index

-rw-rw---- 1 mysql app     12821 Oct 28 13:43 mysql01-slow.log

-rw-r----- 1 mysql root   129423 Oct 28 13:43 mysql01.err

-rw-rw---- 1 mysql app         6 Oct 28 13:42 mysql01.pid

drwx------ 2 mysql app      4096 Sep 17 19:24 performance_schema

drwx------ 2 mysql app      4096 Sep 17 19:24 test

drwx------ 2 mysql app      4096 Oct 28 09:59 test02

drwx------ 2 mysql app      4096 Oct 28 09:36 test08

    

mysql01:/data/mysql3306 # chown mysql:app ib*

mysql01:/data/mysql3306 # chmod 660 ib*

 

mysql01:/data/mysql3306 # ll

total 160148

-rw-rw---- 1 mysql app        56 Sep 17 19:28 auto.cnf

drwx------ 2 mysql app      4096 Oct 13 05:17 huizhe

-rw-rw---- 1 mysql app  50331648 Oct 28 14:33 ib_logfile0

-rw-rw---- 1 mysql app  50331648 Oct 28 14:32 ib_logfile1

-rw-rw---- 1 mysql app  50331648 Oct 28 14:32 ib_logfile2

-rw-rw---- 1 mysql app  12582912 Oct 28 14:33 ibdata1

drwx------ 2 mysql app      4096 Oct 28 09:32 mysql

-rw-rw---- 1 mysql app       214 Oct 29  2015 mysql-bin.000008

-rw-rw---- 1 mysql app       214 Oct 26 17:24 mysql-bin.000009

-rw-rw---- 1 mysql app       576 Oct 28 09:26 mysql-bin.000010

-rw-rw---- 1 mysql app      2166 Oct 28 09:30 mysql-bin.000011

-rw-rw---- 1 mysql app      5262 Oct 28 09:32 mysql-bin.000012

-rw-rw---- 1 mysql app       214 Oct 28 09:32 mysql-bin.000013

-rw-rw---- 1 mysql app       214 Oct 28 09:34 mysql-bin.000014

-rw-rw---- 1 mysql app       362 Oct 28 09:39 mysql-bin.000015

-rw-rw---- 1 mysql app       362 Oct 28 11:19 mysql-bin.000016

-rw-rw---- 1 mysql app       214 Oct 28 13:42 mysql-bin.000017

-rw-rw---- 1 mysql app       191 Oct 28 13:42 mysql-bin.000018

-rw-rw---- 1 mysql app       209 Oct 28 13:42 mysql-bin.index

-rw-rw---- 1 mysql app     12821 Oct 28 13:43 mysql01-slow.log

-rw-r----- 1 mysql root   129423 Oct 28 13:43 mysql01.err

-rw-rw---- 1 mysql app         6 Oct 28 13:42 mysql01.pid

drwx------ 2 mysql app      4096 Sep 17 19:24 performance_schema

drwx------ 2 mysql app      4096 Sep 17 19:24 test

drwx------ 2 mysql app      4096 Oct 28 09:59 test02

drwx------ 2 mysql app      4096 Oct 28 09:36 test08

 

 

现在,只需要重启MySQL即可。重启如下

mysql01:/data/mysql3306 # /etc/init.d/mysqld restart

Shutting  down  MySQL............     done                                                                                                                                                         

Starting MySQL..      done

友情提醒:不要在生产环境做测试哦。



本文转自 jxzhfei  51CTO博客,原文链接:http://blog.51cto.com/jxzhfei/1707284

网友评论

登录后评论
0/500
评论
科技小能手
+ 关注