MySQL数据库误删除后如何恢复

共计 4940 个字符，预计需要花费 13 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 MySQL 数据库误删除后如何恢复，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

首先，要确保 mysql 开启了 binlog 日志功能。在 /etc/my.cnf 文件里的 [mysqld] 区块添加：

log-bin=mysql-bin

然后重启 mysql 服务

（1）在 ops 库下创建一张表 customers

mysql  use ops; mysql  create table customers( -  id int not null auto_increment, -  name char(20) not null, -  age int not null, -  primary key(id) -  )engine=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql  show tables; +---------------+ | Tables_in_ops | +---------------+ | customers | +---------------+ 1 row in set (0.00 sec) mysql  desc customers; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | +-------+----------+------+-----+---------+----------------+ 3 rows in set (0.02 sec) mysql  insert into customers values(1, wangbo , 24  Query OK, 1 row affected (0.06 sec) mysql  insert into customers values(2, guohui , 22  Query OK, 1 row affected (0.06 sec) mysql  insert into customers values(3, zhangheng , 27  Query OK, 1 row affected (0.09 sec) mysql  select * from customers; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | wangbo | 24 | | 2 | guohui | 22 | | 3 | zhangheng | 27 | +----+-----------+-----+ 3 rows in set (0.00 sec)

（2）现在进行全备份

[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip  /opt/backup/ops_$(date +%F).sql.gz Enter password: [root@vm-002 ~]# ls /opt/backup/ ops_2016-09-25.sql.gz

参数说明：

-B：指定数据库

-F：刷新日志

-R：备份存储过程等

-x：锁表

ndash;master-data：在备份语句里添加 CHANGE MASTER 语句以及 binlog 文件及位置点信息

（3）再次插入数据

mysql  insert into customers values(4, liupeng , 21  Query OK, 1 row affected (0.06 sec) mysql  insert into customers values(5, xiaoda , 31  Query OK, 1 row affected (0.07 sec) mysql  insert into customers values(6, fuaiai , 26  Query OK, 1 row affected (0.06 sec) mysql  select * from customers; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | wangbo | 24 | | 2 | guohui | 22 | | 3 | zhangheng | 27 | | 4 | liupeng | 21 | | 5 | xiaoda | 31 | | 6 | fuaiai | 26 | +----+-----------+-----+ 6 rows in set (0.00 sec)

（4）此时误操作，删除了 test 数据库

mysql  drop database ops; Query OK, 1 row affected (0.04 sec)

此时，全备之后到误操作时刻之间，用户写入的数据在 binlog 中，需要恢复出来!

(5)查看全备之后新增的 binlog 文件

[root@vm-002 ~]# cd /opt/backup/ [root@vm-002 backup]# ls ops_2016-09-25.sql.gz [root@vm-002 backup]# gzip -d ops_2016-09-25.sql.gz [root@vm-002 backup]# ls ops_2016-09-25.sql [root@vm-002 backup]# grep CHANGE ops_2016-09-25.sql -- CHANGE MASTER TO MASTER_LOG_FILE= mysql-bin.000002 , MASTER_LOG_POS=106;

这是全备时刻的 binlog 文件位置，即 mysql-bin.000002 的 106 行，因此在该文件之前的 binlog 文件中的数据都已经包含在这个全备的 sql 文件中了

（6）移动 binlog 文件，并导出为 sql 文件，剔除其中的 drop 语句，查看 mysql 的数据存放目录，有下面可知是在 /var/lib/mysql 下

[root@vm-002 backup]# ps -ef|grep mysql root 9272 1 0 01:43 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql mysql 9377 9272 0 01:43 pts/1 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock [root@vm-002 backup]# cd /var/lib/mysql/ [root@vm-002 mysql]# ls ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock test [root@vm-002 mysql]# cp mysql-bin.000002 /opt/backup/

将 binlog 文件导出 sql 文件，并 vim 编辑它删除其中的 drop 语句

[root@vm-002 backup]# mysqlbinlog -d ops mysql-bin.000002  002bin.sql [root@vm-002 backup]# ls 002bin.sql mysql-bin.000002 ops_2016-09-25.sql [root@vm-002 backup]# vim 002bin.sql # 删除里面的 drop 语句

注意：在恢复全备数据之前必须将该 binlog 文件移出，否则恢复过程中，会继续写入语句到 binlog，最终导致增量恢复数据部分变得比较混乱

（7）恢复数据

[root@vm-002 backup]# mysql -uroot -p   ops_2016-09-25.sql Enter password: [root@vm-002 backup]#

查看数据库，看看 ops 库在不在

mysql  show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | ops | | test | +--------------------+ 4 rows in set (0.00 sec) mysql  use ops; 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 customers; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | wangbo | 0 | | 2 | guohui | 0 | | 3 | zhangheng | 0 | +----+-----------+-----+ 3 rows in set (0.00 sec)

此时恢复了全备时刻的数据。接着，使用 002bin.sql 文件恢复全备时刻到删除数据库之间，新增的数据

[root@vm-002 backup]# mysql -uroot -p ops  002bin.sql Enter password: [root@vm-002 backup]#

再次查看数据库，发现全备份到删除数据库之间的那部分数据也恢复了！！

mysql  select * from customers; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | wangbo | 24 | | 2 | guohui | 22 | | 3 | zhangheng | 27 | | 4 | liupeng | 21 | | 5 | xiaoda | 31 | | 6 | fuaiai | 26 | +----+-----------+-----+ 6 rows in set (0.00 sec)

关于 MySQL 数据库误删除后如何恢复就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。