誤刪除InnoDB ibdata數據文件怎么辦

175次閱讀

共計 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 服務