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

2022-04-19 00:44:32

有人因为不熟悉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)

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;

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

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

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

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

码农公寓

相关文章