ibd数据恢复到mysql ibd文件恢复数据

转载

mob6454cc780924 2024-02-05 13:16:58

文章标签 ibd数据恢复到mysql 网络数据库前端 ViewUI 文章分类 MySQL 数据库

有人因为不熟悉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
lrwx------ 1 root root 64 Oct 28 14:08 11
lrwx------ 1 root root 64 Oct 28 14:08 4
lrwx------ 1 root root 64 Oct 28 14:08 9

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

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

转载于:https://blog.51cto.com/jxzhfei/1707284

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。