使用Mysqldump恢復備份方法

本文主要給大家介紹使用Mysqldump恢復備份方法，文章內容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下主題內容吧。

mysql是邏輯備份工具：基于mysql客戶端協議，可以遠程實現，遠程備份，要考慮到磁盤I/O，建議同一庫中不用使用多個引擎的原因之一是備份工具支持度不一樣

完全備份、部分備份；

    InnoDB：熱備或溫備；

    MyISAM：溫備；

二次封裝工具：

mydumper：perl腳本，實現模擬并行備份的效果，占用云服務器的IO

phpMyAdmin

備份機制是備份時先創建數據庫，然后創建表，最后在通過insert into插入所有數據

mysqldump備份機制：先創建一個庫，然后在創建相關表，最后往表里插入數據

庫：create  database

表：create table

數據：insert  into，使用一個insert into語句就可以把表的數據都插入，完成恢復。每一張表都要先create table，然后insert into進行恢復

執行myslqdump會把所有的內容都現在在屏幕上，然后在一步步操作，顯示的結果里面可以看到具體的操作，如果要恢復，需要將這些顯示結果重定向到一個文件，利用新生成的文件進行恢復

mysqldump用法，有三個，如下: 

方法一：

mysqldump [OPTIONS] database [tables]   # 備份單庫，可以只備份其中的一部分表（部分備份）；

方法二：

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]  # 備份多庫；，建議用這個方式實現，即使備份單個庫，因為不用自己創建庫

例子：

[root@CentOS7E ~]#mysqldump -u root -pPass123456 -h 192.168.1.71 --databases sunny

執行語句后，會把sunny這個數據庫的內容都打印到屏幕上,此時沒有重定向到相關文件，不能用于恢復，但是可以查看使用mysqldump命令過程執行的操作。

方法三：

mysqldump [OPTIONS] --all-databases [OPTIONS] # 備份所有庫；也是自己創建庫

注意，執行備份時，要添加選項，將對應的表鎖住，防止時間點不一致，不能用于還原數據

MyISAM存儲引擎：支持溫備，備份時要鎖定表；

-x, --lock-all-tables：鎖定所有庫的所有表，讀鎖；鎖定范圍大。

-l, --lock-tables：鎖定指定庫所有表；備份哪個表的時候就只鎖定對應的表

InnoDB存儲引擎：支持溫備和熱備；

可以使用-x或者-l來實現溫備

--single-transaction：創建一個事務，基于此快照執行備份，實現熱備；但是問題是如果備份時的一些事物沒有被commit或者回滾，導致恢復數據有問題，所以恢復的時候還要執行崩潰的恢復，因此還要借助崩潰后的恢復操作，實現數據的完整。

其它選項：

-R, --routines：備份指定庫的存儲過程和存儲函數；

--triggers：備份指定庫的觸發器；

-E, --events：  

例子

利用mysql實現數據備份和還原

熱備，添加相關的選項，并重定向到文件/root/sunny.sql

[root@CentOS7E sunny]#mysqldump -u root -pPass123456 -h 192.168.1.71 --single-transaction -R --triggers -E --databases sunny > /root/sunny.sql

例子：利用重定向，將生成的文件恢復到本地的mysql庫里，如果本地已經有數據庫，就不會創建重名的數據庫，直接新建表，將數據導入新建的表里。

注意，恢復前，將恢復的本地云服務器的二進制日志關閉，否則，新導入的數據會被記錄到二進制日志中，但是這部分日志是恢復的文件，不需要進行再次記錄

關閉二進制日志功能如下

MariaDB [sunny]> set @@session.sql_log_bin=off;

恢復數據庫

[root@CentOS7E ~]#mysql -uroot -pPass1234 </root/sunny.sql

恢復備份的時候，還有一小部分是沒有備份到備份文件中的，因此，需要用二進制文件來重放進行恢復，因此做全量備份的時候，要結合選項--master-data和--flush-logs

 --master-data[=#] 

1：記錄為CHANGE MASTER TO語句，此語句不被注釋；

2：記錄為CHANGE MASTER TO語句，此語句被注釋；一般是設置為2 

--flush-logs：鎖定表完成后，即進行二進制表的日志刷新操作；驅動云服務器日志滾動

例子：

[root@CentOS7E ~]#mysqldump -u root -pPass1234 -h 192.168.1.71 --single-transaction -R --triggers -E --databases sunny --master-data=2 --flush-logs > /root/sunny-$(date +%F-%H-%M-%S).sql

[root@CentOS7E ~]#less  sunny-2018-01-15-13-10-17.sql

查看新生成的備份文件，有如下的關鍵的注釋信息如下，

恢復的時候可以從master-log.000005這個文件的第245字節開始恢復，因為上次備份時有CHANGE MASTER TO=2，上次有滾動，恢復的時候只要把245之后的內容拿來重放就可以

-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000005', MASTER_LOG_POS=245;

假設此時在云服務器71上的sunny數據庫進行操作，則新生成的日志將記錄到二進制日志master-log.000005，假設71上操作如下

MariaDB [sunny]> delete from students where id=1000;

MariaDB [sunny]> delete from students where id=1002;

MariaDB [sunny]> insert into students(id,name,age,gender) values (1050,"new005",18,"M");

此時，在71的master-log.000005會記錄這些新生成的日志，查看如下

[root@CentOS7A mysql]#mysqlbinlog /mydata/log/master-log.000005

恢復到75這臺剛才導入全量備份的mysql云服務器上

恢復操作如下

首先，將master-log.000005這個文件進行重定向恢復，然后拷貝到75上進行重放

重定向，mysqlbinlog這個工具讀取數據后會轉換成sql語句,注意，這里-j 245可以不指定，因為245字節前沒有有效內容，如果是245之后開始的，注意要添加-j選項

[root@CentOS7A mysql]#mysqlbinlog -j 245  /mydata/log/master-log.000005 >/tmp/binlog005.sql

拷貝數據

[root@CentOS7A mysql]#scp /tmp/binlog005.sql 192.168.1.75:/root/

在75上將binlog005.sql重新導入數據庫

[root@CentOS7E ~]#mysql -uroot -pPass123456 < binlog005.sql 

注意，將來用mysqldump做備份的時候，建議把二進制文件也拷貝一份出來，用于異常備份，將來備份的時候，用mysqlbinlog命令讀出來生成mysql語句的文件，然后再進行二進制文件的恢復。不過mysqldump恢復效率低，建議使用xtrabackup這個工具來實現

看完以上關于使用Mysqldump恢復備份方法，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。