MYSQL数据库文件误删恢复

IT那活儿 2021-12-30

1419

点击上方“IT那活儿”，关注后了解更多精彩内容！！

对于主从架构的mysql数据库，如果不小心误删除了ulog，rlog，ibdata1这三类文件中的任一种或多种，也不用过度紧张，如果操作得当，数据还是完全有希望恢复的。

首先去检查备库状态是否正常，如果备库状态正常，那么我们可以通过备库恢复，重新部署原主库；而如果考虑数据库较大，我们也可以尝试在本机完成修复，先去检查数据库状态，确认数据库工作正常，依然可以读写。

切记：这时候千万不要把mysqld的进程杀掉，否则本机将无法修复！！！

修复步骤还原

1. 模拟误删除ibdata1文件。

# lsauto.cnf htest hua ib_buffer_pool ibdata1 ibtmp1 mysql performance_schema sys test# rm ibdata1# lsauto.cnf htest hua ib_buffer_pool ibtmp1 mysql performance_schema sys test
复制

2. 数据库正常登录。

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

3. 查看mysqld进程pid。

# ps -ef|grep 23301mysql 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
复制

4. 使用如下命令，查看结果。

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

-- 删除一个文件时，并不是真正删除，而是打一个标记，同样在我们mysql数据库中，delete一条记录，实际的删除操作也没有发生。上面显示的结果中，其中11就是我们需要恢复的文件。

5. 在恢复文件前，需要先执行flush tables with read lock，确保数据库没有写入操作，以便我们完成恢复。

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

6. 把11号文件复制到原文件夹，注意文件权限，如果不是mysql：mysql，需要修改权限。

[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]$ lltotal 24612-rw-r----- 1 mysql mysql 56 Jan 21 2019 auto.cnfdrwxr-x--- 2 mysql mysql 56 Mar 23 15:27 htestdrwxr-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 ibtmp1drwxr-x--- 2 mysql mysql 4096 Jan 21 2019 mysqldrwxr-x--- 2 mysql mysql 8192 Jan 21 2019 performance_schemadrwxr-x--- 2 mysql mysql 8192 Jan 21 2019 sysdrwxr-x--- 2 mysql mysql 20 Jan 22 2019 test
复制

7. 重启MYSQL，修复完成。

[mysql@mysqltest1 bin]$ sh shutdown.shEnter password:[mysql@mysqltest1 bin]$ sh startup.sh[mysql@mysqltest1 bin]$ sh login.shEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.25-log Source distributionCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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)
复制

第五步提到了要确认数据库没有写入操作，那么如何确认数据库没有写入操作呢，下面提供一种方法以供参考。

数据库写入操作确认

1. 设置脏页刷新比例（让脏页尽快刷新到磁盘）。

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

2. 查看binlog日志写入情况，确保File和Position的值没有发生变化。

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 |+------------------+----------+--------------+------------------+----------------------------------+
复制

3. 查看innodb状态信息，确保脏页已经刷新到磁盘。

mysql> show engine innodb status\G;*************************** 1. row ***************************Type: InnoDBName: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_idlesrv_master_thread log flush and writes: 5224----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 11OS WAIT ARRAY INFO: signal count 11RW-shared spins 0, rounds 16, OS waits 8RW-excl spins 0, rounds 0, OS waits 0RW-sx spins 0, rounds 0, OS waits 0Spin rounds per wait: 16.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx------------TRANSACTIONS------------Trx id counter 42540Purge done for trx's n:o < 42540 undo n:o < 0 state: running but idleHistory list length 4   #确保后台线程purge把undo log全部清刷掉LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421344771516240, not started0 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: 0387 OS file reads, 133 OS file writes, 47 OS fsyncs0.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 #确保合并插入缓存等于1merged operations:insert 0, delete mark 0, delete 0discarded operations:insert 0, delete mark 0, delete 0Hash 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 7172011Log flushed up to 7172011Pages flushed up to 7172011 #确保这里三个值保持一致，并且不再变化Last checkpoint at 71720020 pending log flushes, 0 pending chkp writes32 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 137428992Dictionary memory allocated 179916Buffer pool size 8192Free buffers 7798Database pages 393Old database pages 0Modified db pages 0  #确保脏页数量为0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 350, created 43, written 880.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 393, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBProcess ID=11849, Main thread ID=139869459859200, state: sleepingNumber of rows inserted 311, updated 2, deleted 0, read 6510.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------#确保插入，更新，删除为0END OF INNODB MONITOR OUTPUT============================1 row in set (0.18 sec)
复制