mysql 資料備份與恢復使用詳解(超完整詳細教學)

2022-11-23 14:01:49

一、前言

對一個執行中的線上系統來說，定期對資料庫進行備份是非常重要的，備份不僅可以確保資料的區域性完整性，一定程度上也為資料安全性提供了保障，設想如果某種極端的場景下，比如磁碟損壞導致某個時間段資料丟失，或者誤操作導致資料表資料被刪等...

這種情況在現實中可以說無處不在，為了避免資料丟失或損壞帶來的巨大損失，有必要對線上系統的資料定期做備份，而備份的直接好處就是，一旦資料需要做恢復的時候就可以利用這些備份資料快速恢復，從而最大程度減少損失。

二、資料備份策略

根據實際的業務需求，經驗參考可以據資料規模大小，伺服器磁碟容量，大致可分為下面幾種：

1、全備

備份完整的資料庫，全量資料就是資料庫中所有的資料（或某一個庫的全部資料）；

全量備份就是把資料庫中所有的資料進行備份；
mysqldump會取得一個時刻的一致性資料

2、增備

增量資料就是指上一次全量備份資料之後到下一次全備之前資料庫所更新的資料，對於mysqldump,binlog就是增量資料；

3、差異備份

備份自上一次完全備份後的全部改動和新檔案；
備份速度較快，恢復速度較快，對磁碟空間有要求；
能夠更快且簡單的恢復(相比較增量)；
需要最近一次完全備份和最後一次差異備份就能快速恢復；

三、資料備份型別

根據資料備份時對生產系統的影響，可以做如下分類：

1、冷備

停庫,停服務,備份

這些備份操作在使用者不能存取資料的時候進行，因此無法讀取或修改資料。這些離線備份會阻止執行任何使用資料的行為。這些型別的備份不會干擾正常執行的系統的效能。但是，對於某些應用程式，會無法接受必須在一段較長的時間裡鎖定或完全阻止使用者存取資料。

2、熱備

不停庫,不停服務,備份,也不會(鎖表)阻止使用者的寫入

這些動態備份在讀取或修改資料的過程中進行，很少中斷或者不中斷傳輸或處理資料的功能。使用熱備份時，系統仍可供讀取和修改資料的操作存取。

3、溫備

不停庫，不停服務，備份，會(鎖表) 阻止使用者的寫入

這些備份在讀取資料時進行，但在多數情況下，在進行備份時不能修改資料本身。這種中途備份型別的優點是不必完全鎖定終端使用者。但其不足之處在於無法在進行備份時修改資料集，這可能使這種型別的備份不適用於某些應用程式。在備份過程中無法修改資料可能產生效能問題。

四、前置準備

提前搭建好mysql服務（本文以mysql5.7為例進行說明），並建立一個測試使用的資料庫，若干資料表；

五、mysqldump 資料備份命令使用

mysqldump是mysql自帶的資料備份命令，使用該命令可以完成資料庫，資料表等多種備份策略，下面針對該命令的使用做詳細的說明；

1、命令格式

mysqldump [選項] 資料庫名 [表名] > 指令碼名
或
mysqldump [選項] --資料庫名 [選項 表名] > 指令碼名
或
mysqldump [選項] --all-databases [選項] > 指令碼名

關於選項部分，包含的引數是比較多的，下面列舉常用的一些引數選項

引數名							縮寫	含義
--host							-h		伺服器IP地址
--port							-P		伺服器埠號
--user							-u		MySQL 使用者名稱
--pasword						-p		MySQL 密碼
--databases								指定要備份的資料庫
--all-databases							備份mysql伺服器上的所有資料庫
--compact								壓縮模式，產生更少的輸出
--comments								新增註釋資訊
--complete-insert						輸出完成的插入語句
--lock-tables							備份前，鎖定所有資料庫表
--no-create-db/--no-create-info			禁止生成建立資料庫語句
--force									當出現錯誤時仍然繼續備份操作
--default-character-set					指定預設字元集
--add-locks								備份資料庫表時鎖定資料庫表
 
--no-create-db，  ---取消建立資料庫sql(預設存在)
--no-create-info，---取消建立表sql(預設存在)
--no-data         ---不匯出資料(預設匯出)
--add-drop-database ---增加刪除資料庫sql（預設不存在）
--skip-add-drop-table  ---取消每個資料表建立之前新增drop資料表語句(預設每個表之前存在drop語句)
--skip-add-locks       ---取消在每個表匯出之前增加LOCK TABLES（預設存在鎖）
--skip-comments        ---註釋資訊(預設存在)

2、案例演示

1）備份全庫

mysqldump -uroot -pXXX --all-databases > /usr/local/mysql/full.sql
或者
mysqldump -uroot -pXXX -A > /usr/local/mysql/full.sql

2）備份資料庫【一個或多個】

使用 -- databases 或 - B 引數，該引數後面跟資料庫名，多個庫間中間用空格，如果指定 databases 引數，備份檔案中會存在建立資料庫的語句，如果不指定引數，則不存在；

mysqldump –u user –h host –p -- databases [ 資料庫的名稱 1 [ 資料庫的名稱 2...]] > 備份檔名稱 .sql

mysqldump -uroot -pXXX --databases shake_date > /usr/local/mysql/shake_date_01.sql
或者
mysqldump -uroot -pXXX -B shake_date shake_flow > /usr/local/mysql/combine.sql

3）備份資料表

如果業務中不需要對全庫做備份，只想備份部分表的時候，

mysqldump –u user –h host –p密碼資料庫名 [ 表名 1 [ 表名 2...]] > 備份檔名稱 .sql

備份shake_date 下面的t_user表

4）只備份表結構

mysqldump -u使用者名稱 -pXXX --no-data 資料庫 資料表名稱 > 備份sql檔名.sql

備份shake_date下面的t_user表，只備份表結構

mysqldump -uroot -pXXX --no-data shake_date t_user > /usr/local/mysql/t_user_bk2.sql

可以開啟備份的檔案檢查下，可以發現這裡就只剩下表結構；

5）只備份表的部分資料

有時候一張表資料量很大，只需要部分資料，這時就可以使用 --where 選項了，where後面附
帶需要滿足的條件；

如下，備份t_user表中age大於12歲的使用者

mysqldump -uroot -pXXX shake_date t_user --where="age > 12 " > t_user_bk3.sql

6）排除某些表的備份

如果備份某個庫，但某些表資料量很大或與業務關聯不大，這時候可以考慮排除這些表的備份，適用選項 --ignore-table ；

如下備份shake庫下的表，排除t_no這個表

mysqldump -uroot -pXXX shake_date --ignore-table=shake_date.t_no > /usr/local/mysql/t_user_bk5.sql

3、其他重要引數選項補充

在實際生產中，備份資料需要考慮的因素其實更多，考慮的越細緻，在後續在資料恢復的時候就越精確，下面列舉一些實際生產備份中可能用得上的額外的引數選項；

-R 備份儲存過程及函數
--triggers 備份觸發器
-E 備份事件
-F 在備份開始時,重新整理一個新binlog紀錄檔
--master-data=2  以註釋的形式,儲存備份開始時間點的binlog的狀態資訊
--single-transaction   innodb 儲存引擎開啟熱備(快照備份)功能
--set-gtid-purged=auto

更多的引數，可以通過命令： mysqldump --help 進行檢視和學習，每一項都有詳細的介紹，可以結合官網一起學習；

在以上羅列的引數中，有下面三個引數選項這裡做一下補充說明，也是備份過程中常常會涉及到的

--master-data；
--single-transaction；
--set-gtid-purged；

1）--master-data

可選值： 1 ，2

通過mysqldump --help 命令可以清楚檢視官方對該命令的解釋，這裡簡單說下這個引數的作用

加上該選項之後，會在備份的sql中新增並記錄備份時間點binlog中的偏移量，利用這個偏移的位置，結合完整的binlog紀錄檔，可以全量恢復從這個時間點之前的資料 + 這個時間點之後的資料；
在mysql的主從模式中可以用到；
該值設定為1或2，在不同的引數值設定時，備份中帶來的結果不一樣，通常結合--single-transaction；一起使用，可以在備份期間進行鎖表，防止期間外部資料的讀寫造成備份資料的不一致；

以--master-data=2為例進行說明

以註釋的形式,儲存備份開始時間點的binlog的狀態資訊

在備份時，會自動記錄，二進位制紀錄檔檔名和位置號；
自動鎖表（FTWRL）；
如果配合--single-transaction，只對非InnoDB表進行鎖表備份，InnoDB表進行“熱“”備，實際上是實現快照備份；

2）--single-transaction

innodb 儲存引擎開啟熱備(快照備份)功能

直觀上理解，使用該引數，可以在資料備份期間開啟類似於事務的操作，這樣的話可以避免外部的DDL操作帶來備份時的資料上的影響，比如有如下場景，在mysql5.6版本中可能出現的情況：

100+G 有MyISAM表，做大批次DML，mysqldump備份資料庫出現hang住；
3000w表做DDL，改資料型別，備份期間，鎖表情況嚴重；

如果配合上面的master-data可以自動實現加鎖

不加--single-transaction ，啟動所有表的溫備份，所有表都鎖定；
加上--single-transaction , 對innodb進行快照備份, 對非innodb表可以實現自動鎖表功能；

3）--set-gtid-purged

此為預設引數，即使在命令中不寫，依舊生效，它的效果是在mysqldump輸出的備份檔案中生成 SET@@GLOBAL.GTID_PURGED語句；備份檔案中的這條語句記錄了GTID號，可選值：auto , on

使用場景

在構建主從模式時，主庫上有許多資料需要先備份出來並恢復到從庫上，以此來保持兩個庫沒有差異，然後再去設定主從，在這種場景下，需要將引數設定為 on，對於想要基於GTID實現主從複製的從庫來說，從庫是基於MASTER_AUTO_POSITION=1自動獲取並應用GTID的，因此如果再主庫匯出的備份檔案中沒有GTID，那麼從庫無法自動獲取並應用GTID；
設為off時，在mysqldump輸出中不包含SET@@GLOBAL.GTID_PURGED語句;

4）--max-allowed-packet=#

適當調大該引數，可以避免在備份資料量過大時因資料落盤時封包過大的備份失敗問題

5）-R -E --triggers

備份資料時連同觸發器函數等也一同進行備份

操作演示

--set-gtid-purged=OFF

mysqldump -uroot -pXXX -A --master-data=2 --single-transaction --set-gtid-purged=OFF >/usr/local/mysql/shake_user_empty.sql

--set-gtid-purged=on

設定該引數之後，最明顯的效果就是開啟備份指令碼檔案，可以看到下面的資訊，開啟這個引數後在構建主從複製的時候會用上

--max-allowed-packet=#

在一些資料量比較大的備份情況下，建議適當的調大該引數

mysqldump -uroot -pXXX -B shake_date -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/usr/local/mysql/shake_full_10.sql

六、mysqldump 資料恢復

使用mysqldump可以對備份的資料進行恢復，基本語法

mysql –u root –p [dbname] < backup .sql

1、全量恢復

如果備份檔案中包含了建立資料庫的語句，則恢復的時候不需要指定資料庫名稱

刪除shake_date資料庫

執行資料恢復，再次檢視發現資料就回來了；

mysql -uroot -pXXX < /usr/local/mysql/full.sql

2、全量備份中恢復單庫

有整個範例的備份，但只想恢復某個資料庫，這時可以從全量備份中分理出單個庫的備份，可以參考下面的命令，比如將shake_date資料庫從full.sql中分離出來；

sed -n '/^-- Current Database: `shake_date`/,/^-- Current Database: `/p' full.sql> /usr/local/mysql/shake_full.sql

3、從某個資料庫中恢復單表資料

這也是一種比較常見的需求了，比如業務中某些表的資料非常重要，優先恢復這些表，就可以用到這個功能，操作步驟如下：

用shell語法分離出建立表的語句及插入資料的語句；
再依次匯出完成資料恢復；

cat shake_full.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `t_user`/!d;q' > t_user_structure.sql

cat shake_full.sql | grep --ignore-case 'insert into `t_user`' > t_user_data.sql

首先，將shake_date庫中的t_user表刪除

依次執行上面的sql，即建表sql以及insert的資料sql從全庫的sql備份中分離出來

可以開啟一個看看

然後再執行資料的恢復

source /usr/local/mysql/t_user_structure.sql;
source /usr/local/mysql/t_user_data.sql;

4、使用dump + binlog進行資料恢復

下面說一個具體的場景，在生產環境下，假如定期對某個資料庫進行全備，比如每週2的晚上進行全備，但現在發生了一件事，即在週三的時候，某位同事不小心將某張表的資料刪除了，這該怎麼辦呢？

可以這麼考慮，從週二全備那天來看，備份的資料是截止到那個時間點的全量資料，備份的時候可以將 master-data引數加進去，這樣備份出來的資料中就有了那一刻binlog中的資料位置點，而在binlog紀錄檔中，記錄了完整的操作記錄行，那麼就可以結合起來對資料進行快速恢復；

操作步驟如下：

1）建立一個資料庫和資料表

給t1表隨機插入幾條資料

2）模擬周2對當前資料庫進行全備

mysqldump -uroot -pXXX --databases mydb --master-data=2 --single-transaction --set-gtid-purged=ON >/usr/local/mysql/mydb.sql

注意：

--master-data=2 這個引數一定要開啟，這個引數會記錄備份那一刻的位置

3）模擬周3又對t1表做了相關的資料操作

4）模擬周3不小心將資料庫刪了

5）執行資料恢復

恢復步驟

恢復截止到周1的備份資料；
尋找binlog的起始位置；
根據binlog恢復周1到周2之間的資料；

將備份的sql中的下面的資訊定位到，CHANGE 開頭的表示在binlog中記錄的位置號為4001，這就是說，剩下的待恢復的資料從4001開始擷取就可以了；

SET @@GLOBAL.GTID_PURGED='c38a5f35-67ac-11ed-aefa-525400633661:1-16';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=4001;

定位到mysql的binlog位置，結合上述的gtid，通過binlog進行恢復，使用下面的命令檢查最後刪除的位置點；

show binlog events in 'mysql-bin.000002';

在上面我們定位到備份中的sql檔案中的gtid是1~16，這裡最後drop的時候是20這個位置，則需要擷取的那部分就是17 ~ 20；

mysqlbinlog --skip-gtids --include-gtids='c38a5f35-67ac-11ed-aefa-525400633661:17-20' mysql-bin.000002 > /usr/local/mysql/mydb_extend.sql

執行恢復

檢查全備恢復後的資料，這樣就恢復到了週一的資料；

接下來再恢復周2的資料；

source /usr/local/mysql/mydb_extend.sql;

七、物理備份

所謂物理備份，最直接的就是將MySQL中的資料庫檔案複製出來，這種方式簡單粗暴，速度也快，比較省事，

比如在上面建立了一個mydb的資料庫，在mysql的資料目錄下就存在下面幾個資料檔案；

但是這種方式並不是特別推薦，一方面在不同的作業系統下，資料目錄有所不同，其次，這種備份的操作往往需要停服和鎖表，以避免外部的操作給備份資料時帶來的不一致的影響，另外，與mysql所使用的資料庫引擎也很有關係；

八、表的匯出與匯入

在一些場景下，直接將資料庫資料被分成sql檔案可能並不是一個特別好的選擇，因為匯出來的檔案格式不夠通用，比如還有其他的資料分析平臺需要使用這些資料的話，就比較麻煩了，這時候就可以考慮將資料庫以檔案的形式匯出；

表的匯出操作

1、使用SELECT…INTO OUTFILE匯出文字檔案

將shake_date庫下的t_user表資料匯出到檔案

SELECT * FROM t_user INTO OUTFILE "/var/lib/mysql-files/tuser.txt";

開啟該檔案檢查下

2. 使用mysqldump命令匯出文字檔案

使用mysqldump命令將將shake_date資料庫中tuser表中的記錄匯出到文字檔案：

 mysqldump -uroot -pXXX -T "/var/lib/mysql-files/" shake_date t_user

檢查下檔案

使用mysqldump將shake_date資料庫中的t_user表匯出到文字檔案，使用FIELDS選項，要求欄位之間使用逗號“，”間隔，所有字元型別欄位值用雙引號括起來；

mysqldump -uroot -pXXX -T "/var/lib/mysql-files/" shake_date t_user --fields-terminated-by=',' --fields-optionally-enclosed-by='"'

可以檢查下txt檔案，這種格式的資料在很多巨量資料處理業務場景中是一種比較好的資料格式

3、使用mysql命令匯出文字檔案

mysql -uroot -pXXX --execute="SELECT * FROM t_user;" shake_date> "/var/lib/mysql-files/tuser1.txt"

匯出的結果格式如下

使用 --veritcal 引數將該條件記錄分為多行顯示

mysql -uroot -pXXX --vertical --execute="SELECT * FROM t_user;" shake_date > "/var/lib/mysql-files/tuser2.txt"

得到的資料格式如下

使用 --xml引數將資料匯出為xml格式

mysql -uroot -pXXX --xml --execute="SELECT * FROM t_user;" shake_date>"/var/lib/mysql-files/tuser3.xml"

表的匯入操作

上面演示了將表以各自型別格式的檔案匯入，下面再看看如何將不同格式的表的資料進行匯入操作；

1. 使用LOAD DATA INFILE方式匯入文字檔案

使用 SELECT...INTO OUTFILE 將 shake_date 資料庫中 t_user 表的記錄匯出到文字檔案

執行下面的語句匯出

SELECT * FROM shake_date.t_user INTO OUTFILE '/var/lib/mysql-files/tuser1.txt';

刪除t_user表的資料

從文字檔案tuser1.txt中恢復資料

LOAD DATA INFILE '/var/lib/mysql-files/tuser1.txt' INTO TABLE shake_date.t_user;

2、將上面以逗號分割的檔案匯入到資料表

使用如下語句

LOAD DATA INFILE '/var/lib/mysql-files/t_user.txt' INTO TABLE shake_date.t_user FIELDS TERMINATED BY ',' ENCLOSED BY '"';

匯入之前先清理下表的資料

3、使用mysqlimport方式匯入文字檔案

仍然以上面的那個以逗號分割的檔案為例進行說明

先清理資料表

執行下面的語句進行資料匯入

mysqlimport -uroot -pXXX shake_date '/var/lib/mysql-files/t_user.txt' --fields-terminated-by=',' --fields-optionally-enclosed-by='"'

執行完成後，可以看到資料又恢復了；

到此這篇關於mysql 資料備份與恢復使用詳解的文章就介紹到這了,更多相關mysql 備份與恢復內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！