這篇文章將為大家詳細(xì)講解有關(guān)如何恢復(fù)MySQL數(shù)據(jù)庫(kù)數(shù)據(jù)，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

目前累計(jì)服務(wù)客戶超過(guò)千家，積累了豐富的產(chǎn)品開(kāi)發(fā)及服務(wù)經(jīng)驗(yàn)。以網(wǎng)站設(shè)計(jì)水平和技術(shù)實(shí)力，樹(shù)立企業(yè)形象，為客戶提供做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)、網(wǎng)站策劃、網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)絡(luò)營(yíng)銷、VI設(shè)計(jì)、網(wǎng)站改版、漏洞修補(bǔ)等服務(wù)。創(chuàng)新互聯(lián)建站始終以務(wù)實(shí)、誠(chéng)信為根本，不斷創(chuàng)新和提高建站品質(zhì)，通過(guò)對(duì)領(lǐng)先技術(shù)的掌握、對(duì)創(chuàng)意設(shè)計(jì)的研究、對(duì)客戶形象的視覺(jué)傳遞、對(duì)應(yīng)用系統(tǒng)的結(jié)合，為客戶提供更好的一站式互聯(lián)網(wǎng)解決方案，攜手廣大客戶，共同發(fā)展進(jìn)步。

一、恢復(fù)方案

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

二、使用mysqlbinlog進(jìn)行時(shí)間點(diǎn)恢復(fù)

1、介紹

mysqlbinlog是一個(gè)從二進(jìn)制日志中讀取語(yǔ)句的工具，在mysql安裝完成之后自帶的。

2、二進(jìn)制日志恢復(fù)原理

當(dāng)使用mysqldump對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份時(shí)，生成的備份文件中包含了數(shù)據(jù)庫(kù)DML操作時(shí)的時(shí)間點(diǎn)以及備份時(shí)的二進(jìn)制日志位置信息，如果單庫(kù)，可以從某個(gè)時(shí)間點(diǎn)開(kāi)始，進(jìn)行時(shí)間點(diǎn)恢復(fù)；如果是主從架構(gòu)，可以根據(jù)備份時(shí)的--master-data=2和--single-transaction，完成根據(jù)時(shí)間點(diǎn)或者位置點(diǎn)的恢復(fù)。

3、二進(jìn)制日志恢復(fù)示例

（1）單庫(kù)恢復(fù)示例

創(chuàng)建數(shù)據(jù)庫(kù)，并插入測(cè)試數(shù)據(jù)

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進(jìn)行全量備份，備份時(shí)滾動(dòng)日志，同時(shí)記住二進(jìn)制日志文件名稱和日志的位置點(diǎn)

[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

此時(shí)查看二進(jìn)制日志文件名稱和日志點(diǎn)位置如下

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

使用了一段時(shí)間，不小心誤操作，執(zhí)行了如下的語(yǔ)句，將數(shù)據(jù)庫(kù)中的數(shù)據(jù)全部修改了

mysql> UPDATE STUDENT SET name = 'admin';

過(guò)了一段時(shí)間，可能是幾分鐘，也可能是幾個(gè)小時(shí)，有人反映網(wǎng)站登錄有問(wèn)題了，查看發(fā)現(xiàn)好多數(shù)據(jù)被誤修改，而這段時(shí)間內(nèi)，還一直有寫入操作，如又新增了如下的記錄

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

此時(shí)需要恢復(fù)數(shù)據(jù)，首先為了防止數(shù)據(jù)繼續(xù)寫入，可以先鎖表，暫停寫入業(yè)務(wù)，通知用戶系統(tǒng)維護(hù)，然后執(zhí)行如下操作：

#登錄數(shù)據(jù)庫(kù)，鎖表，此時(shí)表只能讀，不能寫
mysql> USE test_db;
mysql> LOCK TABLE student READ;
#然后重新（注意是重新打開(kāi)）打開(kāi)一個(gè)session窗口，否則會(huì)話處出之后，鎖就會(huì)釋放。然后壓縮備份現(xiàn)有數(shù)據(jù)和二進(jìn)制日志文件
[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/*
#導(dǎo)入最近備份的一次全備數(shù)據(jù)
[root@WB-BLOG ~]# mysql -uroot -proot -h227.0.0.1 -P3306 < /tmp/test_db.sql 

#查看全備時(shí)的二進(jìn)制日志文件和日志點(diǎn)
[root@WB-BLOG ~]# cat bin_pos_2018-06-24.out 
  Log_name    File_size
  mysql-bin.000001   1497
  mysql-bin.000002    397
#將861這個(gè)點(diǎn)之后的二進(jìn)制日志文件轉(zhuǎn)換為一個(gè)sql文件
[root@WB-BLOG bin]# ./mysqlbinlog /mysql_logs/mysql-bin.000002 --start-position=397 > /tmp/tmp.sql
#使用vim編輯器編輯這個(gè)sql文件，找到其中的未加條件的UPDATE語(yǔ)句，然后將其刪掉，然后將刪掉UPDATE語(yǔ)句之后的sql腳本內(nèi)容導(dǎo)入到數(shù)據(jù)庫(kù)中
[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
#登錄數(shù)據(jù)庫(kù)查詢數(shù)據(jù)是否恢復(fù)，可以查看被誤修改的數(shù)據(jù)是否還原，然后對(duì)表執(zhí)行解鎖，再次全備數(shù)據(jù)
mysql> UNLOCK TABLES;

（2）主從架構(gòu)數(shù)據(jù)恢復(fù)示例

環(huán)境

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

首先停止從庫(kù)的SQL線程，然后在從庫(kù)上全備數(shù)據(jù)，并輸入"SHOW SLAVE STATUS"信息到備份文件中，"SHOW SLAVE STATUS"的輸出信息中記錄了當(dāng)前應(yīng)用到了主庫(kù)的哪個(gè)位置點(diǎn)的信息

#登錄從庫(kù)，然后關(guān)閉SQL線程
mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)
#然后記錄從庫(kù)中當(dāng)前應(yīng)用的主庫(kù)的二進(jìn)制日志文件信息
[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

在從庫(kù)上備份完成之后，重新啟動(dòng)從庫(kù)的SQL線程

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

啟動(dòng)SQL線程之后，備份這段時(shí)間內(nèi)在主庫(kù)上的DML操作會(huì)重新同步到從庫(kù)上。假如在主庫(kù)上發(fā)生了一個(gè)誤操作，沒(méi)加條件更新了student表中的所有數(shù)據(jù)，導(dǎo)致了表中所有數(shù)據(jù)被修改，此時(shí)由于同步操作，從庫(kù)也被修改了

#登錄主庫(kù)，修改數(shù)據(jù)庫(kù)的對(duì)外用戶，使其暫不提供服務(wù)，然后滾動(dòng)日志
mysql> UPDATE mysql.user SET Host = '127.0.0.1' WHERE User='tomcat';
Query OK, 1 rows affected (0.00 sec)
#刷新權(quán)限表
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#滾動(dòng)日志
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)
#將從庫(kù)備份的數(shù)據(jù)及備份時(shí)刻的從庫(kù)slave信息傳到主庫(kù)上
[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/

備份主庫(kù)的數(shù)據(jù)目錄和二進(jìn)制日志文件目錄

[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/*

導(dǎo)入從庫(kù)最近一次備份的數(shù)據(jù)

[root@node01 mysql_logs]# mysql -uroot -proot -h227.0.0.1 -P3306 < /root/mysql_test_db_2018-03-26.sql 
#注意：上述的操作不能鎖主庫(kù)的表，否則全備數(shù)據(jù)無(wú)法導(dǎo)入。

查看備份時(shí)刻的從庫(kù)中應(yīng)用到的主庫(kù)二進(jìn)制日志文件名稱及位置點(diǎn)

[root@node01 mysql_logs]# cat /root/slave_2018-03-26.info
  Master_Log_File: master-bin.000002 #備份時(shí)所應(yīng)用的主庫(kù)二進(jìn)制日志文件名稱
  Read_Master_Log_Pos: 395  #備份時(shí)所應(yīng)用的主庫(kù)二進(jìn)制日志文件的位置

從該日志文件及日志點(diǎn)開(kāi)始，將395日志點(diǎn)之后的日志文件轉(zhuǎn)換為sql腳本，如果有多個(gè)二進(jìn)制日志文件可以同時(shí)轉(zhuǎn)換為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語(yǔ)句，然后刪除該語(yǔ)句，并將增量的sql腳本導(dǎo)入數(shù)據(jù)庫(kù)

[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

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

[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/
#如果從庫(kù)設(shè)置了只讀，需要先去掉只讀限制
mysql> SET GLOBAL read_only = OFF;
Query OK, 0 rows affected (0.00 sec)
#將數(shù)據(jù)導(dǎo)入從庫(kù)
[root@node02 mysql_logs]# mysql -uroot -proot -h227.0.0.1 -P3306 < /root/master_test_db_2018-06-24.sql
#開(kāi)啟從庫(kù)的只讀
mysql> SET GLOBAL read_only = ON;
Query OK, 0 rows affected (0.00 sec)

由于在主庫(kù)上備份時(shí)添加了--master-date=1參數(shù)，所以從庫(kù)導(dǎo)入之后，不需要重新執(zhí)行change master操作。

登錄從庫(kù)，查看SHOW SLAVE STATUS信息是否正常，如果正常，登錄主庫(kù)，重新修改授權(quán)表，然后對(duì)外提供服務(wù)

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

關(guān)于如何恢復(fù)MySQL數(shù)據(jù)庫(kù)數(shù)據(jù)就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。