rm mysql 数据日志文件恢复

系统通过rm 误删除了 mysql 数据文件或日志： 此时如果有master/slave 或开启了binlog 日志还好，如果没有就悲剧了。

但是如果此时 mysqld 进程存在，并且mysql服务未关闭，通过系统级别可恢复出来。

1，创建一张测试表：

root@(none)) Mysql >use test;

Database changed

(root@test) Mysql >create table tab_rm_test as select * from mysql.innodb_index_stats;

Query OK, 3 rows affected (0.08 sec)

Records: 3 Duplicates: 0 Warnings: 0

(root@test) Mysql >flush privileges;

Query OK, 0 rows affected (0.00 sec)

(root@test) Mysql >show tables;

+----------------+

| Tables_in_test |

+----------------+

| tab_rm_test |

+----------------+

1 row in set (0.00 sec)

(root@test) Mysql >select * from tab_rm_test;

+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |

+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

| test | tab_rm_test | GEN_CLUST_INDEX | 2016-08-19 08:54:47 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |

| test | tab_rm_test | GEN_CLUST_INDEX | 2016-08-19 08:54:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| test | tab_rm_test | GEN_CLUST_INDEX | 2016-08-19 08:54:47 | size | 1 | NULL | Number of pages in the index |

+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

3 rows in set (0.00 sec)

2， 系统下查看对应的innodb 后缀 *.ibd

[mysql@sfpay mysql]$ cd test/

[mysql@sfpay test]$ ls

tab_rm_test.frm tab_rm_test.ibd

[mysql@sfpay test]$ ll -l

total 108

-rw-rw---- 1 mysql mysql 8886 Aug 19 08:54 tab_rm_test.frm

-rw-rw---- 1 mysql mysql 98304 Aug 19 08:54 tab_rm_test.ibd --本次仅测试删除*.ibd 文件。

[mysql@sfpay test]$ rm tab_rm_test.ibd

[mysql@sfpay test]$ ls

tab_rm_test.frm

3， 此处很关键，确保mysqld 存在，同时MYSQL服务open 状态。

[mysql@sfpay test]$ netstat -ntlp |grep mysqld

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp 0 0 :::3351 :::* LISTEN 16788/mysqld

--这里是 16788，之后执行很关键一步：

(root@test) Mysql >insert into tab_rm_test select * from tab_rm_test;

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

(root@test) Mysql >flush privileges;

Query OK, 0 rows affected (0.00 sec)

--这里做测试，即使删除后，只要MYSQL服务存在，DML 不受影响，虽然data 不存在了。

--（这是否存在某种关系系统上，虽然删除了，但是这个线程一致占有着）

这里接着上面 16788 标示号：

[mysql@sfpay test]$ ll /proc/16

16/ 1601/ 1612/ 16272/ 1628/ 1645/ 1661/ 16788/

[mysql@sfpay test]$ ll /proc/16788/fd

fd/ fdinfo/

[mysql@sfpay test]$ ll /proc/16788/fd |grep 'tab_r*'

lrwx------ 1 mysql mysql 64 Aug 19 09:00 71 -> /data/mysql/test/tab_rm_test.ibd (deleted)

[root@sfpay ~]# cd /proc/16788/fd

[root@sfpay fd]# ls

0 1 10 11 12 13 14 15 16 2 3 31 32 33 36 37 4 5 51 52 6 7 71 8 9

--这里对应 71 也非常重要：

4， 开始处理之前，需要考虑到mysql 运行状态，和连接会话等。

(root@test) Mysql > flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)

--这一步的作用是让数据库没有写入的操作，以便完成后面的恢复工作。

怎样验证没有写入操作，分一下几步：

(root@test) Mysql > show variables like 'innodb_max_dirty_pages%';

+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| innodb_max_dirty_pages_pct | 75 |

| innodb_max_dirty_pages_pct_lwm | 0 |

+--------------------------------+-------+

2 rows in set (0.00 sec)

(root@test) Mysql >set global innodb_max_dirty_pages_pct=0;

Query OK, 0 rows affected (0.00 sec)

--此参数，快速刷新至磁盘。

然后查看binlog 日志写入情况，确保FILE 和Position 的值不再变化。

(root@test) Mysql > show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000008 | 681 | | mysql | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

最后查看innodb 信息状态，确保脏页已经刷入 磁盘，(确保几项值不再变化）

(root@test) Mysql >show engine innodb status\G;

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

2016-08-19 09:06:17 7fafa3420700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 27 seconds

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

BACKGROUND THREAD

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

srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 188679 srv_idle

srv_master_thread log flush and writes: 188682

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 6

OS WAIT ARRAY INFO: signal count 6

Mutex spin waits 5, rounds 0, OS waits 0

RW-shared spins 6, rounds 180, OS waits 6

RW-excl spins 0, rounds 0, OS waits 0

Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl

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

TRANSACTIONS

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

Trx id counter 6421

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

##确保后台 purge 进程把undo log 全部清除掉，事务ID 要一致。

History list length 4

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 6420, not started

MySQL thread id 8, OS thread handle 0x7fafa3420700, query id 134 localhost root init

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

183 OS file reads, 58 OS file writes, 28 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 0 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number 1641009

Log flushed up to 1641009

Pages flushed up to 1641009

Last checkpoint at 1641009 ## 确保这4个值不再变化。

0 pending log writes, 0 pending chkp writes

18 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 66358

Buffer pool size 8191

Free buffers 8013

Database pages 178

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 167, created 11, written 39

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: 178, 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. 16788, id 140392328288000, state: sleeping

Number of rows inserted 6, updated 0, deleted 0, read 15

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.00 sec)

ERROR:

No query specified

4， 确认工作完成后，就可以进行恢复操作：

[root@sfpay fd]# cp 71 /data/mysql/test/ tab_rm_test.ibd

[root@sfpay fd]# cd /data/mysql/test/

[root@sfpay test]# ls

tab_rm_test.frm tab_rm_test.ibd

[root@sfpay test]# ls -l

total 108

-rw-rw---- 1 mysql mysql 8886 Aug 19 08:54 tab_rm_test.frm

-rw-r----- 1 root root 98304 Aug 19 09:08 tab_rm_test.ibd

[root@sfpay test]# chown mysql:mysql *.ibd

[root@sfpay test]# chmod 660 tab_rm_test.ibd

[root@sfpay test]# ll -l

total 108

-rw-rw---- 1 mysql mysql 8886 Aug 19 08:54 tab_rm_test.frm

-rw-rw---- 1 mysql mysql 98304 Aug 19 09:08 tab_rm_test.ibd

[root@sfpay test]# su - mysql

[mysql@sfpay ~]$ /etc/init.d/mysqld restart

Shutting down MySQL.... SUCCESS!

rm: cannot remove `/var/lock/subsys/mysql': Permission denied

Starting MySQL. SUCCESS!

(root@test) Mysql >use test;

Database changed

(root@test) Mysql >show tables;

+----------------+

| Tables_in_test |

+----------------+

| tab_rm_test |

+----------------+

1 row in set (0.00 sec)

(root@test) Mysql >select count(*) from tab_rm_test;

+----------+

| count(*) |

+----------+

| 6 |

+----------+

1 row in set (0.00 sec)

-----------THE END------------