之前三篇文章分別介紹了MySQL數據庫常見的備份方法，其中包括邏輯備份和物理備份，本篇將總結一下MySQL數據庫的數據恢復相關內容。這些數據恢復方案在之前備份內容介紹時，此處總結一下恢復方案，并結合數據庫的二進制日志做下數據恢復的示范！
一、恢復方案
1、數據量不是特別大，可以將mysqldump命令備份的數據使用mysql客戶端命令或者source命令完成數據的恢復；
2、使用Xtrabackup完成數據庫的物理備份恢復，期間需要重啟數據庫服務；
3、使用LVM快照卷完成數據庫物理備份恢復，期間需要重啟數據庫服務；
二、使用mysqlbinlog進行時間點恢復
1、介紹
mysqlbinlog是一個從二進制日志中讀取語句的工具，在mysql安裝完成之后自帶的。
2、二進制日志恢復原理
當使用mysqldump對數據庫進行備份時，生成的備份文件中包含了數據庫DML操作時的時間點以及備份時的二進制日志位置信息，如果單庫，可以從某個時間點開始，進行時間點恢復；如果是主從架構，可以根據備份時的--master-data=2和--single-transaction，完成根據時間點或者位置點的恢復。
3、二進制日志恢復示例
（1）單庫恢復示例
創(chuàng)建數據庫，并插入測試數據
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 -h127.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)
使用了一段時間，不小心誤操作，執(zhí)行了如下的語句，將數據庫中的數據全部修改了
mysql> UPDATE STUDENT SET name = 'admin';
過了一段時間，可能是幾分鐘，也可能是幾個小時，有人反映網站登錄有問題了，查看發(fā)現好多數據被誤修改，而這段時間內，還一直有寫入操作，如又新增了如下的記錄
mysql> INSERT INTO student(name,age) VALUES('Hbase',23),('BlackHole',30);此時需要恢復數據，首先為了防止數據繼續(xù)寫入，可以先鎖表，暫停寫入業(yè)務，通知用戶系統維護，然后執(zhí)行如下操作：
#登錄數據庫，鎖表，此時表只能讀，不能寫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 -h127.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 -h127.0.0.1 -P3306 < /tmp/tmp.sql#登錄數據庫查詢數據是否恢復，可以查看被誤修改的數據是否還原，然后對表執(zhí)行解鎖，再次全備數據mysql> UNLOCK TABLES;
（2）主從架構數據恢復示例
環(huán)境
主庫：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 -h127.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操作會重新同步到從庫上。假如在主庫上發(fā)生了一個誤操作，沒加條件更新了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 -h127.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 -h127.0.0.1 -P3306 < /tmp/tmp.sql
登錄數據庫，查看數據是否正常，被誤修改的數據是否已經恢復，如果恢復，則在主庫上全備數據，然后傳到從庫，完成從庫恢復
[root@node01 mysql_data]# mysqldump -uroot -proot -h127.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 -h127.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參數，所以從庫導入之后，不需要重新執(zhí)行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)
執(zhí)行完成之后，主從數據恢復完畢。
至此，數據恢復介紹完畢，上述介紹了使用全備加二進制日志實現單實例數據庫和主從數據庫的數據恢復過程，如有問題，歡迎評論指出。也希望大家多多支持VeVb武林網。
新聞熱點
疑難解答
圖片精選
