共計 5651 個字符，預計需要花費 15 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章將為大家詳細講解有關如何恢復 MySQL 數據庫數據，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

一、恢復方案

1、數據量不是特別大，可以將 mysqldump 命令備份的數據使用 mysql 客戶端命令或者 source 命令完成數據的恢復；
2、使用 Xtrabackup 完成數據庫的物理備份恢復，期間需要重啟數據庫服務；
3、使用 LVM 快照卷完成數據庫物理備份恢復，期間需要重啟數據庫服務；

二、使用 mysqlbinlog 進行時間點恢復

1、介紹

mysqlbinlog 是一個從二進制日志中讀取語句的工具，在 mysql 安裝完成之后自帶的。

2、二進制日志恢復原理

當使用 mysqldump 對數據庫進行備份時，生成的備份文件中包含了數據庫 DML 操作時的時間點以及備份時的二進制日志位置信息，如果單庫，可以從某個時間點開始，進行時間點恢復；如果是主從架構，可以根據備份時的 –master-data= 2 和 –single-transaction，完成根據時間點或者位置點的恢復。

3、二進制日志恢復示例

（1）單庫恢復示例

創建數據庫，并插入測試數據

mysql  SHOW CREATE DATABASE test_db;
mysql  CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `age` tinyint(4) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
mysql  INSERT INTO student (name,age) VALUES(Jack ,23),(Tomcat ,24),(XiaoHong ,22),(ZhangFei ,29);

使用 mysqldump 進行全量備份，備份時滾動日志，同時記住二進制日志文件名稱和日志的位置點

[root@WB-BLOG ~]# mysqldump -uroot -proot -h227.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events   /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e  show binary logs    bin_pos_`date +%F`.out

此時查看二進制日志文件名稱和日志點位置如下

mysql  SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1497 |
| mysql-bin.000002 | 397 |
+------------------+-----------+
2 rows in set (0.00 sec)

使用了一段時間，不小心誤操作，執行了如下的語句，將數據庫中的數據全部修改了

mysql  UPDATE STUDENT SET name =  admin

過了一段時間，可能是幾分鐘，也可能是幾個小時，有人反映網站登錄有問題了，查看發現好多數據被誤修改，而這段時間內，還一直有寫入操作，如又新增了如下的記錄

mysql  INSERT INTO student(name,age) VALUES(Hbase ,23),(BlackHole ,30);

此時需要恢復數據，首先為了防止數據繼續寫入，可以先鎖表，暫停寫入業務，通知用戶系統維護，然后執行如下操作：

# 登錄數據庫，鎖表，此時表只能讀，不能寫
mysql  USE test_db;
mysql  LOCK TABLE student READ;
#然后重新（注意是重新打開）打開一個 session 窗口，否則會話處出之后，鎖就會釋放。然后壓縮備份現有數據和二進制日志文件
[root@WB-BLOG mysql_logs]# tar zcvf mysql_data.tar.gz /mysql_data/*
[root@WB-BLOG mysql_logs]# tar zcvf mysql_bin.tar.gz /mysql_logs/*
#導入最近備份的一次全備數據
[root@WB-BLOG ~]# mysql -uroot -proot -h227.0.0.1 -P3306   /tmp/test_db.sql 
#查看全備時的二進制日志文件和日志點
[root@WB-BLOG ~]# cat bin_pos_2018-06-24.out 
 Log_name File_size
 mysql-bin.000001 1497
 mysql-bin.000002 397
#將 861 這個點之后的二進制日志文件轉換為一個 sql 文件
[root@WB-BLOG bin]# ./mysqlbinlog /mysql_logs/mysql-bin.000002 --start-position=397   /tmp/tmp.sql
#使用 vim 編輯器編輯這個 sql 文件，找到其中的未加條件的 UPDATE 語句，然后將其刪掉，然后將刪掉 UPDATE 語句之后的 sql 腳本內容導入到數據庫中
[root@WB-BLOG bin]# vim /tmp/tmp.sql
 use `test_db`/*!*/;
 SET TIMESTAMP=1522088753/*!*/;
 update student set name =  admin  # 刪掉這一句
[root@WB-BLOG bin]# mysql -uroot -proot -h227.0.0.1 -P3306   /tmp/tmp.sql
#登錄數據庫查詢數據是否恢復，可以查看被誤修改的數據是否還原，然后對表執行解鎖，再次全備數據
mysql  UNLOCK TABLES;

（2）主從架構數據恢復示例

環境

主庫：192.168.199.10(node01)
從庫：192.168.199.11(node02)

首先停止從庫的 SQL 線程，然后在從庫上全備數據，并輸入 SHOW SLAVE STATUS 信息到備份文件中，SHOW SLAVE STATUS 的輸出信息中記錄了當前應用到了主庫的哪個位置點的信息

# 登錄從庫，然后關閉 SQL 線程
mysql  STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)
#然后記錄從庫中當前應用的主庫的二進制日志文件信息
[root@node02 mysql_data]# mysql -e  SHOW SLAVE STATUS \G    slave_`date +%F`.info
[root@node02 mysql_data]# mysqldump -uroot -proot -h227.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction   /tmp/mysql_test_db_`date +%F`.sql

在從庫上備份完成之后，重新啟動從庫的 SQL 線程

mysql  START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

啟動 SQL 線程之后，備份這段時間內在主庫上的 DML 操作會重新同步到從庫上。假如在主庫上發生了一個誤操作，沒加條件更新了 student 表中的所有數據，導致了表中所有數據被修改，此時由于同步操作，從庫也被修改了

# 登錄主庫，修改數據庫的對外用戶，使其暫不提供服務，然后滾動日志
mysql  UPDATE mysql.user SET Host =  127.0.0.1  WHERE User= tomcat 
Query OK, 1 rows affected (0.00 sec)
#刷新權限表
mysql  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#滾動日志
mysql  FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)
#將從庫備份的數據及備份時刻的從庫 slave 信息傳到主庫上
[root@node02 mysql_data]# scp /tmp/mysql_test_db_2018-06-24.sql 192.168.199.10:/root/
[root@node02 mysql_data]# scp slave_2018-06-24.info node01:/root/

備份主庫的數據目錄和二進制日志文件目錄

[root@node01 mysql_logs]# tar zcvf mysql_master_data.tar.gz /mysql_data/*
[root@node01 mysql_logs]# tar zcvf mysql_logs.tar.gz /mysql_logs/*

導入從庫最近一次備份的數據

[root@node01 mysql_logs]# mysql -uroot -proot -h227.0.0.1 -P3306   /root/mysql_test_db_2018-03-26.sql 
#注意：上述的操作不能鎖主庫的表，否則全備數據無法導入。

查看備份時刻的從庫中應用到的主庫二進制日志文件名稱及位置點

[root@node01 mysql_logs]# cat /root/slave_2018-03-26.info
 Master_Log_File: master-bin.000002 # 備份時所應用的主庫二進制日志文件名稱
 Read_Master_Log_Pos: 395 # 備份時所應用的主庫二進制日志文件的位置 

從該日志文件及日志點開始，將 395 日志點之后的日志文件轉換為 sql 腳本，如果有多個二進制日志文件可以同時轉換為 sql 腳本，如下所示

[root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.000002 --start-position=395   /tmp/tmp.sql
#將 master-bin.000003,master-bin.000004,master-bin.000005 合并到 /tmp.sql 文件中
[root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.00000{3,4,5} --start-position=395   /tmp/tmp.sql

找到誤操作的 update 語句，然后刪除該語句，并將增量的 sql 腳本導入數據庫

[root@node01 mysql_logs]# vim /tmp/tmp.sql
 use `test_db`/*!*/;
 update student set name =  admin  # 刪掉這一句
[root@node01 mysql_logs]# mysql -uroot -proot -h227.0.0.1 -P3306   /tmp/tmp.sql

登錄數據庫，查看數據是否正常，被誤修改的數據是否已經恢復，如果恢復，則在主庫上全備數據，然后傳到從庫，完成從庫恢復

[root@node01 mysql_data]# mysqldump -uroot -proot -h227.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction --master-date=1   /tmp/master_test_db_`date +%F`.sql
[root@node01 mysql_data]# scp /tmp/master_test_db_2018-06-24.sql node01:/root/
#如果從庫設置了只讀，需要先去掉只讀限制
mysql  SET GLOBAL read_only = OFF;
Query OK, 0 rows affected (0.00 sec)
#將數據導入從庫
[root@node02 mysql_logs]# mysql -uroot -proot -h227.0.0.1 -P3306   /root/master_test_db_2018-06-24.sql
#開啟從庫的只讀
mysql  SET GLOBAL read_only = ON;
Query OK, 0 rows affected (0.00 sec)

由于在主庫上備份時添加了 –master-date= 1 參數，所以從庫導入之后，不需要重新執行 change master 操作。

登錄從庫，查看 SHOW SLAVE STATUS 信息是否正常，如果正常，登錄主庫，重新修改授權表，然后對外提供服務

mysql  UPDATE mysql.user set Host =  192.168.0.%  WHERE User =  tomcat 
mysql  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

關于如何恢復 MySQL 數據庫數據就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

向 AI 問一下細節