当前位置: 代码迷 >> 综合 >> mysql innodb ibdata 数据文件误删除恢复过程
  详细解决方案

mysql innodb ibdata 数据文件误删除恢复过程

热度:55   发布时间:2023-12-19 16:34:15.0

1.首先绝对不要关闭mysql 进程!!!
pidof mysql

2.确认误删除的文件是否存在
ls -trl /proc/PID/fd

3.禁止数据库写操作,防止数据恢复时不一致 
flush tables with read lock;

4.写出内存脏页到磁盘,防止数据恢复时不一致
set global innodb_max_dirty_pages_pct=0;

5. 查看binlog 日志写入情况,确保file 和position 的值没有在变化
show master status;

6.恢复操作前,最后检查

查看innodb 状态,确保脏页已经刷入磁盘;
show engine innodb status\G


mysql> show engine innodb status\G
。。。。
------------
TRANSACTIONS
------------
Trx id counter 9474
Purge done for trx's n:o < 4548 undo n:o < 0 state: running but idle
####确保后台purge 进程已经把undo log 全部清除掉,事务ID 要一致。

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
#####insert buffer 合并插入缓存等于1

---
LOG
---
Log sequence number 1774886
Log flushed up to   1774886
Pages flushed up to 1774886
Last checkpoint at  1774886
####确保以上3个值不在变化

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 63733
Buffer pool size   8191
Free buffers       7931
Database pages     260
Old database pages 0
Modified db pages  0
####确保脏页数量为0

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 16855, id 139960302381376, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
####确保插入、更新、删除为0


复制 /proc/PID/fd/ 路径下删除的ib_logfile,ibdata文件到 原来位置,并修改用户属性:
chown mysql:mysql ib*


确保所有文件已经正常恢复后,重启mysqld 进程
service mysqld restart
or
/etc/init.d/mysql restart





  相关解决方案