误删除InnoDB ibdata数据文件怎么办

137次阅读

共计 4939 个字符，预计需要花费 13 分钟才能阅读完成。

这篇文章主要介绍了误删除 InnoDB ibdata 数据文件怎么办，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

下边这个案例模拟人为误删除数据文件和重做日志文件。
1)删除数据文件和重做日志文件
cd /mysql/data
rm -rf ib*
2) 若此时数据库可以正常工作，数据可以正常写入，千万不要将 mysqld 杀死，否则没法挽救，找到 mysqld 的 pid
[root@mysql data]# netstat -nltp |grep mysqld
tcp 0 0 :::3306 :::* LISTEN 29691/mysqld
这里是 29691
[root@mysql mysql]# ll /proc/29691/fd |egrep ib_|ibdata
lrwx—— 1 root root 64 Aug 8 13:32 10 – /mysql/ib_logfile1 (deleted)
lrwx—— 1 root root 64 Aug 8 13:32 4 – /mysql/ibdata1 (deleted)
lrwx—— 1 root root 64 Aug 8 13:32 9 – /mysql/ib_logfile0 (deleted)
10,4,9 就是需要我们恢复的文件。
3)关闭前端业务或者执行：
flush tables with read lock;
目的是让数据库没有写入操作。
4)执行以下命令使脏页尽快刷入磁盘
set global innodb_max_dirty_pages_pct=0;
5) 然后查看 binlog 日志写入情况，确保 file 和 position 的值没有变化。
mysql show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000004 | 980 | | | |
+——————+———-+————–+——————+——————-+
6)查看 InnoDB 状态信息，确保脏页已经刷入磁盘。
mysql show engine innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2017-08-08 13:46:24 7f4d3e2b2700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
—————–
BACKGROUND THREAD
—————–
srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 8043 srv_idle
srv_master_thread log flush and writes: 8046
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 9
OS WAIT ARRAY INFO: signal count 9
Mutex spin waits 2, rounds 60, OS waits 2
RW-shared spins 6, rounds 180, OS waits 6
RW-excl spins 0, rounds 30, OS waits 1
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 30.00 RW-excl
————
TRANSACTIONS
————
Trx id counter 31247
Purge done for trx s n:o 31242 undo n:o 0 state: running but idle
## 确保后天 purge 进程把 undo log 全部清除掉，事务 ID 要一致
History list length 969
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0, not started
MySQL thread id 4, OS thread handle 0x7f4d3e230700, query id 151 10.10.10.1 root
—TRANSACTION 31246, not started
MySQL thread id 2, OS thread handle 0x7f4d3e2b2700, query id 160 localhost root init
show engine innodb status
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (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
404 OS file reads, 25 OS file writes, 22 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 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
—
LOG
—
Log sequence number 3401065960
Log flushed up to 3401065960
Pages flushed up to 3401065960
Last checkpoint at 3401065960
## 确保这 4 个值不在变化
0 pending log writes, 0 pending chkp writes
16 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 63833
Buffer pool size 8191
Free buffers 7802
Database pages 387
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 387, created 0, written 10
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: 387, 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. 29691, id 139969685923584, state: sleeping
Number of rows inserted 1, updated 1, deleted 0, read 31
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.03 sec)
7) 开始恢复工作
[root@mysql mysql]# ll /proc/29691/fd |egrep ib_|ibdata
lrwx—— 1 root root 64 Aug 8 13:32 10 – /mysql/ib_logfile1 (deleted)
lrwx—— 1 root root 64 Aug 8 13:32 4 – /mysql/ibdata1 (deleted)
lrwx—— 1 root root 64 Aug 8 13:32 9 – /mysql/ib_logfile0 (deleted)
cd /proc/29691/fd
cp 10 /mysql/ib_logfile1
cp 4 /mysql/ibdata1
cp 9 /mysql/ib_logfile0
8) 更改数据文件和重做日志文件权限
cd /mysql/
chown mysql:mysql ib*
9) 重启 MySQL 服务