MySQL 資料庫誤刪除後的數據恢復操作說明

2021-02-19 數據分析與開發

（點擊上方公眾號，可快速關注）

作者：散盡浮華

www.cnblogs.com/kevingrace/p/5904800.html

如有好文章投稿，請點擊 → 這裡了解詳情

在日常運維工作中，對於資料庫的備份是至關重要的！資料庫對於網站的重要性使得我們對 MySQL 資料庫的管理不容有失！然而是人總難免會犯錯誤，說不定哪天大腦短路了，誤操作把資料庫給刪除了，怎麼辦？

下面，就 MySQL 資料庫誤刪除後的恢復方案進行說明。

一、工作場景

（1）MySQL資料庫每晚12:00自動完全備份。

（2）某天早上上班，9點的時候，一同事犯暈drop了一個資料庫！

（3）需要緊急恢復！可利用備份的數據文件以及增量的binlog文件進行數據恢復。

二、數據恢復思路

（1）利用全備的sql文件中記錄的CHANGE MASTER語句，binlog文件及其位置點信息，找出binlog文件中增量的那部分。

（2）用mysqlbinlog命令將上述的binlog文件導出為sql文件，並剔除其中的drop語句。

（3）通過全備文件和增量binlog文件的導出sql文件，就可以恢復到完整的數據。

三、實例說明

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

（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資料庫增量數據恢復的實例過程！

最後，總結幾點：

1）本案例適用於人為SQL語句造成的誤操作或者沒有主從複製等的熱備情況宕機時的修復

2）恢復條件為mysql要開啟binlog日誌功能，並且要全備和增量的所有數據

3）恢復時建議對外停止更新，即禁止更新資料庫

4）先恢復全量，然後把全備時刻點以後的增量日誌，按順序恢復成SQL文件，然後把文件中有問題的SQL語句刪除（也可通過時間和位置點），再恢復到資料庫。

看完本文有收穫？請轉發分享給更多人

關注「資料庫開發」，提升 DB 技能

相關焦點

  • MySQL備份和恢復方法匯總
    備份指定資料庫:要僅轉儲特定資料庫，請在命令行中命名它們並使用--databases選項：mysqldump -uroot -p --databases cjc cjcdb > dump1.sql--databases選項使命令行上的所有名稱都被視為資料庫名稱
  • 如何在Kubernetes上部署MySQL資料庫
    在本文中，我們將展示如何在Kubernetes中部署資料庫，以及可以使用哪些方法在Kubernetes中部署資料庫。資料庫資料庫是一種用於在計算機系統上存儲和處理數據的系統。資料庫引擎可以在資料庫上創建，讀取，更新和刪除。資料庫由資料庫管理系統（DBMS）控制。在大多數資料庫中，數據按行和列進行建模，稱為關係型，這種類型的資料庫在80年代佔主導地位。
  • Python 操作 MySQL 資料庫(上)
    但在被甲骨文公司收購後，Oracle 大幅調漲了 MySQL 商業版的售價，並且它不再支持另一個自由軟體項目的發展，導致一些原先使用 MySQL 的開源軟體逐漸流向其它資料庫，但是不管怎樣，MySQL 依然是一個不錯的資料庫選擇，足夠支持我們完成一個不小的網站。
  • MySQL和PostgreSQL資料庫安全配置
    （一）、MySQL資料庫          MySQL資料庫在安裝後默認存在mysql資料庫，該資料庫為系統表所在的資料庫，所有用戶記錄在mysql資料庫的user三個權限表的用戶列（包括host、user、password三個欄位）。
  • PHP+Mysql 實現資料庫增刪改查
    創建入口文件index.html（連接資料庫、查詢數據）<!php// 處理增加操作的頁面 require "dbconfig.php";// 連接mysql$link = @mysql_connect(HOST,USER,PASS) or die("提示：資料庫連接失敗！")
  • MysqlDump備份數據
    --databases 資料庫1 資料庫2 > xxx.sql二. mysqldump常用操作示例1.備份全部資料庫的數據和結構mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql2.備份全部資料庫的結構（加 -d 參數）mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql3.
  • 一個小時學會MySQL資料庫
    資料庫、使用GUI操作MySQL、如何使用SQL訪問MySQL資料庫、以及MySQL資料庫的下載程序、幫助、視頻這幾個方面展開。    4.5.1、添加數據4.5.2、刪除數據4.5.3、修改表結構4.5.4、外鍵4.5.5、唯一鍵5.5.1、添加列5.5.2、修改列5.5.3、刪除列5.5.4、重命名表5.5.5、刪除表5.5.6、刪除資料庫5.5.7、一千行
  • MySQL 定時備份資料庫詳解(非常全)!
    在操作數據過程中，可能會導致數據錯誤，甚至資料庫奔潰，而有效的定時備份能很好地保護資料庫。本篇文章主要講述了幾種方法進行 MySQL 定時備份資料庫。mysqldump -u root -p --databases 資料庫1 資料庫2 > xxx.sql二. mysqldump常用操作示例1.
  • Python與MySQL資料庫的交互實戰
    參數db：連接的資料庫名參數charset：當讀取數據出現中文會亂碼的時候，需要我們設置一下編碼；我們使用python操作資料庫的時候，那麼python就相當於是client，我們是用這個client來操作mysql的server伺服器，python3默認採用的utf8字符集，我的mysql伺服器默認採用latin1字符集，因此mysql
  • Mysql創建資料庫
    一、使用命令行 如果你的Mysql安裝好之後，打開命令行終端，就可以登陸Mysql操作界面了，輸入命令：mysql -u root -p 表示mysql -u root 表示用戶為 root  -p 表示下一步輸入密碼，由於密碼比較敏感，所以在輸入的時候，不會顯示在終端上。
  • 別再問我MySQL和Python怎麼操作MySQL了!
    MySQL是一種關係資料庫管理系統，關係資料庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度並提高了靈活性。MySQL所使用的 SQL 語言是用於訪問資料庫的最常用標準化語言。
  • MySQL定時備份資料庫方案(全庫備份)
    一、MySQL數據備份1.1、 mysqldump命令備份數據在MySQL中提供了命令行導出資料庫數據以及文件的一種方便的工具mysqldump,我們可以通過命令行直接實現資料庫內容的導出dump,首先我們簡單了解一下mysqldump命令用法:#MySQLdump常用mysqldump
  • 十二、創建資料庫測試計劃實戰(詳解教程)
    驅動包的版本一定要與資料庫的版本匹配，如果驅動版本低於mysql版本，有可能會導致連接失敗報錯：2、解壓下載的MySQL驅動，如下圖所示三、建立資料庫測試計劃 在本節中，將介紹如何創建用以測試資料庫伺服器和操作資料庫（增、刪、改、查）的測試計劃。
  • mysql資料庫1127
    在dos命令窗口輸入： 連接到資料庫： mysql -u root -p123456在mysql環境中輸入：查看當前連接下的資料庫
  • 第四章:SQL與關係資料庫基本操作--《資料庫系統原理》
    SQL概述一、SQL的特點SQL是一個綜合的、功能強大的且簡潔易學的的語言，是由很少的詞構成
  • 什麼是MySQL資料庫?看這一篇乾貨文章就夠了!
    資料庫，它是按照數據結構來組織，存儲和管理數據的倉庫。資料庫管理系統, 指資料庫系統中對數據進行管理的軟體系統。4.操作資料庫（創建，修改，刪除）創建資料庫SQL：create database [if not exists] db_name
  • 備份和恢復資料庫
    RMAN>BACKUP AS COPY DATABASE; //備份資料庫時創建所有數據文件的映像副本 備份完成後，執行下面的語句打開資料庫 ALTER DATABASE OPEN; 執行增量備份： 使用BACKUP INCREMENTAL創建資料庫的增量備份 備份級別：
  • MySQL 入門常用命令大全
    - 結果集縱向聯合JOIN - 結果集橫向拼接（4）DML（Data Manipulation Language，數據操作語言）供用戶對資料庫中數據的操作，包括數據的增加、刪除、更新，載入等操作。UPDATE - 更新資料庫表中的數據 DELETE - 從資料庫表中刪除數據 INSERT INTO - 向資料庫表中插入數據REPLACE INTO- 向資料庫表中插入數據，如果存在先刪除LOAD - 載入數據（5）TCL（Transaction Control Language，事務控制語言）又名TPL（Transaction
  • [深度Linux系統]安裝資料庫MySQL和MySQL workbench
    一.安裝MySQL1.打開命令行 ALT+CTRL+T2.輸入sudo apt-get install mysql-server
  • 恢復iPhone已刪除數據的五種方法
    眾所周知，iOS系統目前在全世界是加密度最高的系統，尤其是其獨特的文件加密結構和專用的協議讓數據恢復工作變得非常困難。iOS系統安全模型下幾乎無法恢復任何內容，除非有本地備份或雲備份。當然還有一些技巧可以恢復某些零散的數據，這篇文章我們將討論iOS設備中可恢復和不可恢復的數據內容。Apple將多種類型的用戶數據以SQLite格式存儲在各種資料庫中。