mysql被删库如何恢复_MySQL误删库恢复实战

最新推荐文章于 2025-01-20 22:46:50 发布

二楼的巴士

最新推荐文章于 2025-01-20 22:46:50 发布

阅读量747

点赞数

CC 4.0 BY-SA版权

文章标签： mysql被删库如何恢复

本文链接：https://blog.youkuaiyun.com/weixin_34474896/article/details/113633753

本文详细介绍了在MySQL中误删数据库后的恢复步骤，包括如何创建测试库、表，全备数据库，验证备份状态，以及如何通过binlog进行数据恢复，确保数据的完整性和一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建测试库、表

create database test;

use test;

create table leo (id int,name varchar(10));

插入数据

insert into leo values (1,"liufeng");

insert into leo values (2,"zhangsan");

insert into leo values (3,"liufeng");

insert into leo values (4,"zhangsan");

mysql> select * from leo;

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

| id | name |

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

| 1 | liufeng |

| 2 | zhangsan |

| 3 | liufeng |

| 4 | zhangsan |

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

.......

全备数据库

#!/bin/sh

bak_path=/home/mysql/backup

file_name=bak_$(date +%F)

if [ ! -e $bak_path ];then

mkdir -p $bak_path

mysqldump -uroot -proot -S /home/mysql/3306/data/mysql.sock -A --single-transaction --master-data=2 > $bak_path/${file_name}.sql

md5sum $bak_path/${file_name}.sql > $bak_path/${file_name}.flag

find $BakPath -name "*.sql.gz" -mtime +7|xargs rm -f

确认备份状态

[root@leo home]# cd mysql/backup/

[root@leo backup]# ll

total 792

-rw-r--r-- 1 root root 72 Jun 28 22:18 bak_2019-06-28.flag

-rw-r--r-- 1 root root 804496 Jun 28 22:18 bak_2019-06-28.sql

[root@leo backup]# more bak_2019-06-28.flag

db297e95d491ae3b85ed2b5d2496e527 /home/mysql/backup/bak_2019-06-28.sql

[root@leo backup]# cd ..

[root@leo mysql]#

[root@leo mysql]# md5sum -c /home/mysql/backup/bak_2019-06-28.flag

/home/mysql/backup/bak_2019-06-28.sql: OK

插入数据

mysql> insert into leo values (5,"liufeng");

Query OK, 1 row affected (0.01 sec)

mysql> insert into leo values (6,"zhangsan");

Query OK, 1 row affected (0.01 sec)

mysql> select * from leo;

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

| id | name |

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

| 1 | liufeng |

| 2 | zhangsan |

| 3 | liufeng |

| 4 | zhangsan |

| 5 | liufeng |

| 6 | zhangsan |

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

删除数据库

mysql> show databases;

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

| Database |

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

| information_schema |

| leo |

| mysql |

| performance_schema |

| sys |

| test |

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

6 rows in set (0.00 sec)

mysql> drop database test;

Query OK, 0 rows affected (0.01 sec)

mysql> drop database test;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

备份bin—log

[root@leo backup]# cd /home/mysql/3306/data/

[root@leo data]# ll

total 123740

-rw-r-----. 1 mysql mysql 56 Jun 18 00:59 auto.cnf

-rw-r----- 1 mysql mysql 456 Jun 28 17:41 ib_buffer_pool

-rw-r-----. 1 mysql mysql 12582912 Jun 29 02:45 ibdata1

-rw-r-----. 1 mysql mysql 50331648 Jun 29 02:45 ib_logfile0

-rw-r-----. 1 mysql mysql 50331648 Jun 18 00:59 ib_logfile1

-rw-r----- 1 mysql mysql 12582912 Jun 29 02:42 ibtmp1

drwxr-x---. 2 mysql mysql 4096 Jun 25 23:09 mysql

-rw-r----- 1 mysql mysql 177 Jun 25 22:54 mysql-bin.000003

-rw-r----- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004

-rw-r----- 1 mysql mysql 642 Jun 28 17:41 mysql-bin.000005

-rw-r----- 1 mysql mysql 5909 Jun 29 02:45 mysql-bin.000006

-rw-r----- 1 mysql mysql 76 Jun 28 19:36 mysql-bin.index

srwxrwxrwx 1 mysql mysql 0 Jun 28 19:36 mysql.sock

-rw------- 1 mysql mysql 5 Jun 28 19:36 mysql.sock.lock

drwxr-x---. 2 mysql mysql 4096 Jun 18 00:59 performance_schema

-rw-r----- 1 mysql mysql 5 Jun 28 19:36 resourcepool-0559.pid

drwxr-x---. 2 mysql mysql 12288 Jun 18 00:59 sys

[root@leo data]# cp -a mysql-bin.* /home/mysql/backup/

[root@leo data]# ll /home/mysql/backup/

total 1612

-rw-r--r-- 1 root root 72 Jun 29 02:42 bak_2019-06-29.flag

-rw-r--r-- 1 root root 804515 Jun 29 02:42 bak_2019-06-29.sql

-rw-r----- 1 mysql mysql 177 Jun 25 22:54 mysql-bin.000003

-rw-r----- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004

-rw-r----- 1 mysql mysql 642 Jun 28 17:41 mysql-bin.000005

-rw-r----- 1 mysql mysql 5909 Jun 29 02:45 mysql-bin.000006

-rw-r----- 1 mysql mysql 76 Jun 28 19:36 mysql-bin.index

恢复步骤

1、停止数据库对外访问，防止数据库因update导致数据破坏

2、由于备份语句中加入--master-data=2，可以记录全备后的binlog对应恢复点

[root@leo data]# cd /home/mysql/backup/

[root@leo backup]# ll

total 1612

-rw-r--r-- 1 root root 72 Jun 29 02:42 bak_2019-06-29.flag

-rw-r--r-- 1 root root 804515 Jun 29 02:42 bak_2019-06-29.sql

-rw-r----- 1 mysql mysql 177 Jun 25 22:54 mysql-bin.000003

-rw-r----- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004

-rw-r----- 1 mysql mysql 642 Jun 28 17:41 mysql-bin.000005

-rw-r----- 1 mysql mysql 5909 Jun 29 02:45 mysql-bin.000006

-rw-r----- 1 mysql mysql 76 Jun 28 19:36 mysql-bin.index

[root@leo backup]# sed -n '22p' bak_2019-06-29.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=5181;

3、将binlog转换SQL

[root@leo backup]# mysqlbinlog -d test mysql-bin.000006 --start-position=5181 -r test.sql

[root@leo backup]# cat test.sql

4、实际环境中后续还有 mysql-bin.000007 .... 需要陆续转换为SQL

5、删除test.sql中drop语句

[root@leo backup]# grep -w drop test.sql

drop database test

[root@leo backup]# sed -i '/drop database test/d' test.sql

[root@leo backup]# grep -w drop test.sql

6、全备恢复数据库

[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock < bak_2019-06-29.sql

Enter password:

[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock -e "select * from test.leo";

Enter password:

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

| id | name |

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

| 1 | liufeng |

| 2 | zhangsan |

| 3 | liufeng |

| 4 | zhangsan |

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

7、恢复binlog

[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock < test.sql

Enter password:

[root@leo backup]# mysql -u root -p -S /home/mysql/3306/data/mysql.sock -e "select * from test.leo";

Enter password:

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

| id | name |

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

| 1 | liufeng |

| 2 | zhangsan |

| 3 | liufeng |

| 4 | zhangsan |

| 5 | liufeng |

| 6 | zhangsan |

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

8、校验数据，恢复数据库对外访问