误删ibdata1文件恢复方法

注意：以下演示过程前提为mysqld进程仍在运行中，否则无法使用下面演示过程进行恢复！

 

1.手工制造故障，删除ibdata1文件，注意不要重启mysql

shell > rm -rf ibdata1

 

2.登录mysql，进行如下操作

mysql > FLUSH TABLES WITH READ LOCK;  #全局加读锁，阻塞Innodb继续写入数据

mysql > SHOW engine innodb STATUS; #查看innodb status中的LSN位置

#找到这2个关键值

Log sequence number 1626744

Last checkpoint at  1626744

1)如果相减为0，则表示写入到磁盘中

#如果不为0，可以加速写入

mysql > SET global innodb_max_dirty_pages_pct=0;

2)同时要保证

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 0 merges

insert buffer thread. ibuf的大小应该=1

3) purge thread，它應該purge了全部的transactions

------------

TRANSACTIONS

------------

Trx id counter 14085

Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle

4)确保innodb不在进行写操作

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

172 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

 

3. 确认无误之后，/proc/pid/fd中找出ibdata1文件,（这个pid是mysqld 的进程号，如下图：）

#将ibdata1对应的链接文件5拷贝过去改名

shell > cp /proc/6954/fd/5 /data/mysql3306/ibdata1

 

4. 更改权限                  

shell > chown -R mysql.mysql /data/mysql3306/ibdata1

 

5. 重启[已可以正常登陆]

shell > mysqladmin shutdown -S /tmp/mysql3306.sock

shell > /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf start 3306

shell > mysql -uroot -S /tmp/mysql3306.sock