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

这篇文章主要介绍了误删除InnoDB ibdata数据文件怎么办，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。下边这个案例模拟人为误删除数据文件和重做日志文件。
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 ##确保后天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服务
感谢你能够认真阅读完这篇文章，希望小编分享的“误删除InnoDB ibdata数据文件怎么办”这篇文章对大家有帮助，同时也希望大家多多支持云技术，关注云技术行业资讯频道，更多相关知识等着你来学习!

相关推荐: mysql中processlist命令的详细说明

这篇文章主要讲解了“mysql中processlist命令的详细说明”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“mysql中processlist命令的详细说明”吧！ processlist命令的输出结果显示…