一、mysql运行中，删除ibdata1文件
[root@orderer data]# rm -rf ibdata1 [root@orderer data]# rm -rf ib_logfile0 [root@orderer data]# rm -rf ib_logfile1 [root@orderer data]#
[root@orderer data]# ll ??? 12436 -rw-r----- 1 mysql mysql 56 1? 20 09:13 auto.cnf -rw------- 1 mysql mysql 1680 1? 20 09:13 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 1? 20 09:13 ca.pem -rw-r--r-- 1 mysql mysql 1112 1? 20 09:13 client-cert.pem -rw------- 1 mysql mysql 1676 1? 20 09:13 client-key.pem -rw-r----- 1 mysql mysql 220 1? 20 11:39 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 1? 20 11:40 ibtmp1
二、这种情况下，mysql还能正常运行，在这种状态下，我们可以从proc目录下对应的文件夹恢复这三个文件
查看当前mysqld服务的进程号
[root@orderer data]# ps -ef|grep mysqld root 10696 3934 0 11:40 pts/3 00:00:00 /bin/sh /home/mysql-5.7.26/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf mysql 10855 10696 0 11:40 pts/3 00:00:01 /home/mysql-5.7.26/bin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/home/mysql-5.7.26 --datadir=/home/mysql-5.7.26/data --plugin-dir=/home/mysql-5.7.26/lib/plugin --user=mysql --log-error=/home/mysql-5.7.26/log/mysqld.log --pid-file=/home/mysql-5.7.26/run/mysqld.pid --socket=/home/mysql-5.7.26/mysql.sock root 12705 10188 0 11:58 pts/5 00:00:00 grep --color=auto mysqld [root@orderer data]#
当前进程号位10855，再执行如下命令查看ibdata 和ib_log相关文件
[root@orderer fd]# cd /proc/10855/fd [root@orderer fd]# ll |grep -e ibdata -e ib_ lrwx------ 1 root root 64 1? 20 11:42 10 -> /home/mysql-5.7.26/data/ibdata1 (deleted) lrwx------ 1 root root 64 1? 20 11:42 4 -> /home/mysql-5.7.26/data/ib_logfile0 (deleted) lrwx------ 1 root root 64 1? 20 11:42 9 -> /home/mysql-5.7.26/data/ib_logfile1 (deleted)
可以看到这3个文件被标记了deleted，只要mysql服务部停止，proc下面就存在这3个文件
但是不能直接把文件复制过去，这样会造成数据文件损坏，我们必须保证所有buffer pool中的数据修改都保存到了硬盘文件上面， 为此，首先要停止更多的写入/更新/删除操作，然后等待innodb flush pages to disk. 停止写入的话，可以把网站应用关闭，或者lock tables: 通过锁表停止写入:
mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql>
看所有的page都flush到硬盘文件中
mysql> show engine innodb statusG; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2020-01-20 14:12:43 0x7fd2dc0f5700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 1 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 4 srv_active, 0 srv_shutdown, 9145 srv_idle srv_master_thread log flush and writes: 9149 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 4 OS WAIT ARRAY INFO: signal count 4 RW-shared spins 0, rounds 8, OS waits 4 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 8.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 1283 Purge done for trx's n:o < 783 undo n:o < 0 state: running but idle History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422018894125792, 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/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 267 OS file reads, 70 OS file writes, 16 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 merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1221256 Log flushed up to 1221256 Pages flushed up to 1221256 Last checkpoint at 1221247 0 pending log flushes, 0 pending chkp writes 16 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137428992 Dictionary memory allocated 51903 Buffer pool size 8191 Free buffers 8028 Database pages 163 Old database pages 0 Modified db pages 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 128, created 35, written 46 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: 163, 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=10855, Main thread ID=140543543736064, state: sleeping Number of rows inserted 5, updated 0, deleted 0, read 42 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.02 sec) ERROR: No query specified mysql>
checkpoint 就是 Log sequence number的值减去 Last checkpoint at的值， 如果为0，那么表示所有的page都flush到硬盘文件中了。
为了加速这个flush的过程，可以这样设置一下：
mysql> set global innodb_max_dirty_pages_pct=0; Query OK, 0 rows affected (0.00 sec)
此外，还需要保证一些后台的线程完成了它们的工作：
insert buffer thread. ibuf=1
------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges
还有purge thread，它应该purge了全部的transactions:
------------
TRANSACTIONS
------------
Trx id counter 1283
Purge done for trx's n:o < 783 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422018894125792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
还要确保innodb不再进行写操作了：
--------
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/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
267 OS file reads, 70 OS file writes, 16 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
满足上面的几个条件后，我们就可以直接把proc下面的文件复制过去
[root@orderer fd]# cp 10 /home/mysql-5.7.26/data/ibdata1 [root@orderer fd]# cp 4 /home/mysql-5.7.26/data/ib_logfile0 [root@orderer fd]# cp 9 /home/mysql-5.7.26/data/ib_logfile1 [root@orderer fd]#
最后，将3个文件的所有者权限变更为mysql
[root@orderer data]# chown -R mysql:mysql ibdata1 [root@orderer data]# chown -R mysql:mysql ib_logfile0 [root@orderer data]# chown -R mysql:mysql ib_logfile1 [root@orderer data]#
三、重启数据库
mysql> shutdown; Query OK, 0 rows affected (0.00 sec) mysql>
[root@orderer mysql-5.7.26]# mysqld_safe --defaults-file=/etc/mysql/my.cnf 2020-01-20T06:24:23.104597Z mysqld_safe Logging to '/home/mysql-5.7.26/log/mysqld.log'. 2020-01-20T06:24:23.150412Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data
连接数据库，并访问表
[root@orderer data]# mysql -uroot -p -S /home/mysql-5.7.26/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.7.26-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> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 1 | aaaaa | | 2 | bbbb | | 3 | ccccc | | 4 | dddddd | | 5 | eeeeee | | 6 | fffff | | 7 | rrrrr | | 8 | uuuuuu | | 10 | eerrrr | | 11 | rrrrr | | 12 | hhhhhh | | 13 | jjjjj | | 14 | hhhhh | +------+--------+ 13 rows in set (0.00 sec) mysql>
可以正常访问。