mysql數據庫誤刪除后怎么辦

小編給大家分享一下mysql數據庫誤刪除后怎么辦，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

首先，要確保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：鎖表
--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文件中了

看完了這篇文章，相信你對“mysql數據庫誤刪除后怎么辦”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！