MYSQL数据库文件误删恢复

# ls
auto.cnf htest hua ib_buffer_pool ibdata1 ibtmp1 mysql performance_schema sys test
# rm ibdata1
# ls
auto.cnf htest hua ib_buffer_pool ibtmp1 mysql performance_schema sys test

mysql> select count(*) from htest.tables;
+----------+
| count(*) |
+----------+
| 287 |
+----------+
1 row in set (0.00 sec)

# ps -ef|grep 23301
mysql 11849 10193  0 14:59 pts/0    00:00:05 /usr/local/mysql/bin/mysqld --defaults-
file=/data/mysql/db_order/conf/order.cnf --
basedir=/usr/local/mysql --datadir=/data/mysql/db_order/data 
--plugin-dir=/usr/local/mysql/lib/plugin --log-
error=/data/mysql/db_order/elog/mysql.err --open-files-
limit=650 --pid-file=/data/mysql/db_order/mysql.pid --
socket=/data/mysql/db_order/mysql.sock --port=23301

$ ll /proc/11849/fd |egrep ibdata
lrwx------ 1 mysql mysql 64 Mar 23 15:33 11 -> 
/data/mysql/db_order/data/ibdata1 (deleted)

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

[mysql@mysqltest1 data]$ cd /proc/11849/fd
[mysql@mysqltest1 fd]$ cp 11 /data/mysql/db_order/data/ibdata1
[mysql@mysqltest1 fd]$ cd /data/mysql/db_order/data
[mysql@mysqltest1 data]$ ll
total 24612
-rw-r----- 1 mysql mysql 56 Jan 21 2019 auto.cnf
drwxr-x--- 2 mysql mysql 56 Mar 23 15:27 htest
drwxr-x--- 2 mysql mysql 58 Feb 24 00:56 hua
-rw-r--r-- 1 mysql mysql 299 Mar 23 14:57 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Mar 23 16:33 ibdata1
-rw-r--r-- 1 mysql mysql 12582912 Mar 23 15:28 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Jan 21 2019 mysql
drwxr-x--- 2 mysql mysql 8192 Jan 21 2019 performance_schema
drwxr-x--- 2 mysql mysql 8192 Jan 21 2019 sys
drwxr-x--- 2 mysql mysql 20 Jan 22 2019 test

[mysql@mysqltest1 bin]$ sh shutdown.sh
Enter password:
[mysql@mysqltest1 bin]$ sh startup.sh
[mysql@mysqltest1 bin]$ sh login.sh
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.25-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type help; or h for help. Type c to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| htest |
| hua |
| mysql |
| performance_schema |
| sys |
+--------------------+
9 rows in set (0.00 sec)

mysql> set global innodb_max_dirty_pages_pct=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------+
| mysql-bin.000024 |    11969 | |                  | 73b5b55c-1d8d-11e9-9aae-00155d607300:1-63 |
+------------------+----------+--------------+------------------+----------------------------------+

mysql> show engine innodb statusG;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2020-03-23 16:26:54 0x7f35e406c700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 44 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 8 srv_active, 0 srv_shutdown, 5216 srv_idle
srv_master_thread log flush and writes: 5224
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 11
OS WAIT ARRAY INFO: signal count 11
RW-shared spins 0, rounds 16, OS waits 8
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 16.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 42540
Purge done for trxs n:o < 42540 undo n:o < 0 state: running but idle
History list length 4   #确保后台线程purge把undo log全部清刷掉
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421344771516240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
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] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/os:, sync i/os:
Pending flushes (fsync) log: 0; buffer pool: 0
387 OS file reads, 133 OS file writes, 47 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 #确保合并插入缓存等于1
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 7172011
Log flushed up to 7172011
Pages flushed up to 7172011 #确保这里三个值保持一致，并且不再变化
Last checkpoint at 7172002
0 pending log flushes, 0 pending chkp writes
32 log i/os done, 0.00 log i/os/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 179916
Buffer pool size 8192
Free buffers 7798
Database pages 393
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 350, created 43, written 88
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: 393, 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
Process ID=11849, Main thread ID=139869459859200, state: sleeping
Number of rows inserted 311, updated 2, deleted 0, read 651
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.18 sec)