一、MySQL備份

1.數(shù)據(jù)備份的重要性

在企業(yè)中數(shù)據(jù)的價值至關(guān)重要，數(shù)據(jù)保障了企業(yè)業(yè)務(wù)的正常運(yùn)行。因此，數(shù)據(jù)的安全性及數(shù)據(jù)的可靠性是運(yùn)維的重中之重,任何數(shù)據(jù)的丟失都可能對企業(yè)產(chǎn)生嚴(yán)重的后果。通常情況下造成數(shù)據(jù)丟失的原因有如下幾種:

• 程序錯誤。
• 人為操作錯誤
• 運(yùn)算錯誤。
• 磁盤故障。
• 災(zāi)難(如火災(zāi)、地震)和盜竊

2.數(shù)據(jù)庫備份類型

從物理與邏輯角度分類

數(shù)據(jù)庫備份可以分為物理備份和邏輯備份。 

物理備份是對數(shù)據(jù)庫操作系統(tǒng)物理文件(如數(shù)據(jù)文件、日志文件等)的備份。

這種類型的備份適用于在出現(xiàn)問題時需要快速恢復(fù)的大型重要數(shù)據(jù)庫。

物理備份又可以分為冷備份(脫機(jī)備份)、熱備份(聯(lián)機(jī)備份)和溫備份

• 冷備份:在數(shù)據(jù)庫關(guān)閉狀態(tài)下進(jìn)行備份操作.
• 熱備份:在數(shù)據(jù)庫處于運(yùn)行狀態(tài)時進(jìn)行備份操作，該備份方法依賴數(shù)據(jù)庫的日志文件。
• 溫備份:數(shù)據(jù)庫鎖定表格(不可寫入但可讀)的狀態(tài)下進(jìn)行備份操作。

邏輯備份是對數(shù)據(jù)庫邏輯組件(如表等數(shù)據(jù)庫對象)的備份，表示為邏輯數(shù)據(jù)庫結(jié)構(gòu)(CREATEDATABASE，CREATETABLE語句)和內(nèi)容(INSERT 語句或分隔文本文件)的信息。這種類型的備份適用于可以編輯數(shù)據(jù)值或表結(jié)構(gòu)較小的數(shù)據(jù)量，或者在不同的機(jī)器體系結(jié)構(gòu)上重新創(chuàng)建數(shù)據(jù)。

從數(shù)據(jù)庫的備份策略角度分類

從數(shù)據(jù)庫的備份策略角度，數(shù)據(jù)庫的備份可分為完全備份、差異備份和增量備份。

• 完全備份:每次對數(shù)據(jù)進(jìn)行完整的備份，即對整個數(shù)據(jù)庫、數(shù)據(jù)庫結(jié)構(gòu)和文件結(jié)構(gòu)的備份,保存的是備份完成時刻的數(shù)據(jù)庫,是差異備份與增量備份的基礎(chǔ)。完全備份的備份與恢復(fù)操作都非常簡單方便，但是數(shù)據(jù)存在大量的重復(fù)，并且會占用大量的磁盤空間，備份的時間也很長。
• 差異備份:備份那些自從上次完全備份之后被修改過的所有文件，備份的時間節(jié)點是從上次完整備份起，備份數(shù)據(jù)量會越來越大?；謴?fù)數(shù)據(jù)時，只需恢復(fù)上次的完全備份與最近的一次差異備份。
• 增量備份:只有那些在上次完全備份或者增量備份后被修改的文件才會被備份。以上次完整備份或上次增量備份的時間為時間點,僅備份這之間的數(shù)據(jù)變化，因而備份的數(shù)據(jù)量小，占用空間小，備份速度快。但恢復(fù)時，需要從上一次的完整備份開始到最后一次增量備份之間的所有增量依次恢復(fù),如中間某次的備份數(shù)據(jù)損壞，將導(dǎo)致數(shù)據(jù)的丟失。

常見的備份方法

MySQL 數(shù)據(jù)庫的備份可以采用很多種方式，如直接打包數(shù)據(jù)庫文件(物理冷備份)、專用備份工具(mysql dump)、二進(jìn)制日志增量備份、第三方工具備份考。

物理冷備份

物理冷備份時需要在數(shù)據(jù)庫處于關(guān)閉狀態(tài)下，能夠較好地保證數(shù)據(jù)庫的完整性。物理冷備份一般用于非核心業(yè)務(wù)，這類業(yè)務(wù)一般都允許中斷，物理冷備份的特點就是速度快，恢復(fù)時也是最為簡單的。通常通過直接打包數(shù)據(jù)庫文件夾(本章中的數(shù)據(jù)庫文件夾位于/usr/local/mysql/data)來實現(xiàn)備份。

專業(yè)備份工具mysql dump或mysql hostcopy

mysql dump 程序和 mysql hotcopy 都可以做備份。mysql dump 是客戶端常用邏輯備份程序，能夠產(chǎn)生一組被執(zhí)行以后再現(xiàn)原始數(shù)據(jù)庫對象定義和表數(shù)據(jù)的SQL語句。它可以轉(zhuǎn)儲一個到多個 MySQL 數(shù)據(jù)庫，對其進(jìn)行備份或傳輸?shù)竭h(yuǎn)程SQL 服務(wù)器。mysql dump 更為通用，因為它可以備份各種表。mysql hotcopy 僅適用于某些存儲引擎。

mysql hotcopy是由TimBunce最初編寫和貢獻(xiàn)的Perl腳本mysql hotcopy 僅用于備份MyISAM和 ARCHIVE 表。它只能運(yùn)行在 UNIX或 Linux上。因為使用范圍小

通過啟用二進(jìn)制日志進(jìn)行增量備份

MySQL 支持增量備份，進(jìn)行增量備份時必須啟用二進(jìn)制日志。二進(jìn)制日志文件為用戶 提供復(fù)制，對執(zhí)行備份點后進(jìn)行的數(shù)據(jù)庫更改所需的信息進(jìn)行恢復(fù)。如果進(jìn)行增量備份(包含自上次完全備份或增量備份以來發(fā)生的數(shù)據(jù)修改)，需要刷新二進(jìn)制日志。

通過第三方工具備份

Percona XtraBackup 是一個免費(fèi)的 MySQL 熱備份軟件，支持在線熱備份Innodb 和 XtraDB,也可以支持 MySQL 表備份,不過 MyISAM 表的備份要在表鎖的情況下進(jìn)行。本節(jié)對于 Percona XtraBackupr 的敘述是基于 2.4 版本的。

Percona XtrBackup 有三個主要的工具:xtrabackup、innobackupex、xbstream.

• xtrabackup:是一個編譯了的二進(jìn)制文件，只能備份Innodb/Xtradb 數(shù)據(jù)文件。
• innodbackupex :是一個封裝了 xtrabackup 的Perl腳本，除了可以備份Innodb/Xtradb 之外，還可以備份 MySIAM。
• xbstream:是一個新組件，能夠允許將文件格式轉(zhuǎn)成xbstream 格式或從xbstream 格式轉(zhuǎn)到文件格式。

xtrabackup 工具可以單獨使用，但推薦使用 innobackupex 來進(jìn)行備份,這是因為 innobackupex本身就已經(jīng)包含了 xtrabackup 的所有功能。

xtrabackup 是基于 Innodb 的災(zāi)難恢復(fù)功能進(jìn)行設(shè)計的，備份工具復(fù)制Innodb 的數(shù)據(jù)文件。但是，由于不鎖表，這樣復(fù)制出來的數(shù)據(jù)將不一致。Innodb維護(hù)了一個重做日志，包含 Innodb 數(shù)據(jù)的所有改動情況。在 xtrabackup 備份Innodb 數(shù)據(jù)的同時，xtrabackup 還有另外一個線程用來監(jiān)控重做日志，一但日志發(fā)生變化，就把發(fā)生變化的日志數(shù)據(jù)復(fù)制走。這樣就可以利用重做日志做災(zāi)難恢復(fù)了。

以上是備份過程，如果需要恢復(fù)數(shù)據(jù)，則在準(zhǔn)備階段，xtrabackup 就需要使用之前復(fù)制的重做日志對備份出來的 Innodb 數(shù)據(jù)文件進(jìn)行災(zāi)難恢復(fù),此階段完成之后，數(shù)據(jù)庫就可以進(jìn)行重建還原了。

Percona XtraBackup 對 MySIAM 的復(fù)制，是按這樣的一個順序進(jìn)行的:首先鎖定表，然后復(fù)制，再解鎖表。

二、數(shù)據(jù)庫完全備份操作

物理冷備份與恢復(fù)

物理冷備份一般用 tar 命令直接打包數(shù)據(jù)庫文件夾，而在進(jìn)行備份之前需要使用“systemctl stop mysqld”命令關(guān)閉 mysqld 服務(wù)。

備份數(shù)據(jù)庫

創(chuàng)建一個/backup 目錄作為備份數(shù)據(jù)存儲路徑，使用tar 創(chuàng)建備份文件

整個數(shù)據(jù)庫文件夾備份屬于完全備份。

• 關(guān)機(jī)

恢復(fù)數(shù)據(jù)庫

執(zhí)行下面操作將數(shù)據(jù)庫文件/usr/local/mysql/data/轉(zhuǎn)移至 bak 目錄下模擬故障。

可進(jìn)數(shù)據(jù)庫

mysqldump備份與恢復(fù)

通過 mysqldump 命令可以將指定的庫、表或全部的庫導(dǎo)出為 SQL 腳本，便于該命令在不同版本的 MySQL 服務(wù)器上使用。例如，當(dāng)需要升級 MySQL 服務(wù)器時，可以先使用 mysqldump 命令將原有庫信息導(dǎo)出,然后直接在升級后的 MySQL服務(wù)器中導(dǎo)入即可。

備份數(shù)據(jù)庫使用 mysqldump 命令導(dǎo)出數(shù)據(jù)時，默認(rèn)會直接在終端顯示，若要保存到文件，還需要結(jié)合 Shell 的“>”重定向輸出操作，命令格式如下所示。

格式1:備份指定庫中的部分表

mysqldump [選項] 庫名 [表名1] [表名2]…> /備份路徑/備份文件名

格式 2:備份一個或多個完整的庫(包括其中所有的表)

mysqldump [選項] --databases 庫名1 [庫名2]…

格式3:備份MySQL 服務(wù)器中所有的庫。

mysqldump [選項] --all -databases > /備份路徑/備份文件名

其中，常用的選項包括“-u”、“-p”，分別用于指定數(shù)據(jù)庫用戶名、密碼例如，以下操作分別使用格式 1、格式 2，將mysql 庫中的 user 表導(dǎo)出為mysql-user.sql，將整個 test 庫導(dǎo)出為 test.sql 文件，所有操作均以 root用戶身份驗證。

若需要備份整個 MySQL 服務(wù)器中的所有庫，應(yīng)使用格式 3。當(dāng)導(dǎo)出的數(shù)據(jù)量較大的時候，可以添加“- -opt”選項以優(yōu)化執(zhí)行速度。例如，執(zhí)行以下操作將創(chuàng)建備份文件 all-data.sql，其中包括 MySQL 服務(wù)器中的所有庫。

查看備份文件

通過 mysqldump 工具導(dǎo)出的 SQL 腳本是文本文件，其中“/* ····*/”部分或以“- -”開頭的行表示注釋信息。使用 grep、less、cat 等文本工具可以査看腳本內(nèi)容。例如,執(zhí)行以下操作可以過濾出 aaa.sql 腳本中的數(shù)據(jù)庫操作語句。

恢復(fù)數(shù)據(jù)庫

使用 mysqldump 命令導(dǎo)出的 SQL 備份腳本，在需要恢復(fù)時可以通過 mysql命令對其進(jìn)行導(dǎo)入操作，命令格式如下所示。

mysql [選項] [庫名] [表名] < /備份路徑/備份文件名

當(dāng)備份文件中只包含表的備份，而不包含創(chuàng)建的庫的語句時，執(zhí)行導(dǎo)入操作時必須指定庫名，且目標(biāo)庫必須存在。

例如，執(zhí)行以下操作可以從備份文件 aaa.sql 中將表導(dǎo)入aaa庫。其中“-e”選項是用于指定連接 MySQL 后執(zhí)行的命令，命令執(zhí)行完后自動退出。

若備份文件中已經(jīng)包括完整的庫信息，則執(zhí)行導(dǎo)入操作時無須指定庫名

例如，執(zhí)行以下操作可以從備份文件 aaa.sql 恢復(fù) aaa庫。

刪除aaa數(shù)據(jù)庫，模擬故障

查看test數(shù)據(jù)庫是否存在

執(zhí)行導(dǎo)入恢復(fù)操作

確認(rèn)恢復(fù)

除了使用 mysql 命令結(jié)合“<”恢復(fù)數(shù)據(jù)外，還可以使用 source 命令恢復(fù)數(shù)據(jù)，具體用法如下

MySQL增量備份與恢復(fù)

使用 mysqldump 進(jìn)行完全備份，備份的數(shù)據(jù)中有重復(fù)數(shù)據(jù)，備份時間與恢復(fù)時間過長。而增量備份就是自上一次備份之后增加或改變的內(nèi)容。

（1）mysql增量備份特點

與完全備份不同，增量備份沒有重復(fù)數(shù)據(jù)，備份量不大，時間短;但其恢復(fù)麻煩，需要上次完全備份及完全備份之后所有的增量備份才能恢復(fù)，而且要對所有增量備份進(jìn)行逐個反推恢復(fù)。MySQL 沒有提供直接的增量備份辦法，可以通過MySQL 提供的二進(jìn)制日志 (binary logs)間接實現(xiàn)增量備份。

（2）MySQL二進(jìn)制日志對備份意義

二進(jìn)制日志保存了所有更新數(shù)據(jù)庫的操作。二進(jìn)制日志在啟動 MySQL 服務(wù)器后開始記錄，并在文件達(dá)到二進(jìn)制日志所設(shè)置的最大值或者接收到flush logs 命令后重新創(chuàng)建新的日志文件，生成二進(jìn)制文件序列，并及時把這些日志保存到安全的存儲位置，即可完成一個時間段的增量備份。使 max_binlog_size配置項可以設(shè)置二進(jìn)制日志文件的最大值，如果二進(jìn)制文件的大小超過了max_binlog_size，它就會自動創(chuàng)建新的二進(jìn)制文件。

要進(jìn)行 MySQL 的增量備份，首先要開啟二進(jìn)制日志功能。開啟 MySQL 的二進(jìn)制日志功能的實現(xiàn)方法有很多種，最常用的是在MySQL配置文件的 mysqld項下加入“log-bin=/文件路徑/文件名”前綴，如log-bin=/usr/local/mysql/mysql-bin,然后重啟 MySQL 服務(wù)就可 以在指定路徑下查看二進(jìn)制日志文件了。默認(rèn)情況下，二進(jìn)制日志文件的擴(kuò)展名是一個六位的數(shù)字，如 mysql-bin.000001

Mysql8.0 默認(rèn)已經(jīng)開啟 binlog，無需顯示配置 binlog(默認(rèn) binlog 文件為:binlog.000001)，如需自定義binlog 配置，請?zhí)砑尤缦屡渲庙?/p>

增量恢復(fù)

在維護(hù)數(shù)據(jù)庫時,因為各種各樣的原因可能會導(dǎo)致數(shù)據(jù)丟失,如:人為的 SQI語句破壞數(shù)據(jù)庫、在進(jìn)行下一次全備份之前發(fā)生系統(tǒng)故障導(dǎo)致數(shù)據(jù)庫數(shù)據(jù)丟失、在數(shù)據(jù)庫主從架構(gòu)中主庫的數(shù)據(jù)發(fā)生故障等。當(dāng)出現(xiàn)以上場景時可以使用增量恢復(fù)來恢復(fù)數(shù)據(jù)。常用的增量恢復(fù)的方法有三種:一般恢復(fù)、基于位置的恢復(fù)、基于時間點的恢復(fù)。

一般恢復(fù):將所有備份的二進(jìn)制日志內(nèi)容全部恢復(fù)，命令格式如下所示。

mysqlbinlog [- -no-defaults] 增量備份文件 | mysql -u 用戶名 -p 密碼

基于位置的恢復(fù):數(shù)據(jù)庫管理員在操作數(shù)據(jù)庫時可能在同一時間點既有錯誤的操作也有正確的操作，通過基于位置進(jìn)行恢復(fù)可以更加精準(zhǔn)，命令格式如下所、

格式 1:恢復(fù)數(shù)據(jù)到指定位置。

mysqlbinlog --stop-position='操作 id’ 二進(jìn)制日志 | mysql -u 用戶名 -p密碼

格式 2:從指定的位置開始恢復(fù)數(shù)據(jù)。

mysqlbinlog --start-position='操作 id’ 二進(jìn)制日志 | mysql -u 用戶名 -p密碼

基于時間點的恢復(fù):跳過某個發(fā)生錯誤的時間點實現(xiàn)數(shù)據(jù)恢復(fù)，而基于時間點的恢復(fù)可以分成三種情況。

格式 1:從日志開頭截止到某個時間點的恢復(fù)。

mysqlbinlog [–no-defaults] - -stop-datetime='年-月-日 小時:分鐘:秒’ 二進(jìn)制日志 | mysql -u用戶名 -p 密碼

格式 2:從某個時間點到日志結(jié)尾的恢復(fù)。

mysqlbinlog [–no-defaults] --start-datetime='年-月-日 小時:分鐘:秒 二進(jìn)制日志 | mysql -u 用戶名 -p 密碼

格式 3:從某個時間點到某個時間點的恢復(fù)。

mysqlbinlog [–no-defaults] --start-datetime='年-月-日小時:分鐘:秒 - -stop-datetime=’年-月-日小時:分鐘:秒’ 二進(jìn)制日志 | mysql -u 用戶名 -p密碼

案例：

一般恢復(fù)

（1）添加數(shù)據(jù)庫、表，錄入信息

（2）進(jìn)行完全備份

為方便驗證二進(jìn)制日志的增量恢復(fù)功能，在插入二條用戶數(shù)據(jù)后先對test 數(shù)據(jù)庫的 t1表進(jìn)行一次完全備份。然后在 Linux 系統(tǒng)命令行下執(zhí)行’“mysqladmin -uroot -p flush-logs”命令或在“mysql>”命令提示符下執(zhí)行“flush logs;”生成新的二進(jìn)制日志。

（3）繼續(xù)錄入新的數(shù)據(jù)并增量備份

繼續(xù)錄入兩個用戶的數(shù)據(jù)，并執(zhí)行“mysqladmin -uroot -p flush-logs命令刷新二進(jìn)制日志,進(jìn)行增量備份。如此,二進(jìn)制日志文件 mysql-bin.000002中僅保留插入兩個用戶數(shù)據(jù)的操作。

（4）模擬誤操作刪除t1表

（5）恢復(fù)操作

再執(zhí)行恢復(fù)操作時，需要先恢復(fù)完全備份，然后恢復(fù)增量備份

基于位置恢復(fù)（指定開始，結(jié)束）

由于前面已經(jīng)做過備份操作，接下來直接進(jìn)行模擬故障與數(shù)據(jù)恢復(fù)的操作。

模擬誤操作刪除 t1 表

查看t1表是否存在

恢復(fù)完全備份

想要實現(xiàn)基于位置或時間點恢復(fù)數(shù)據(jù),必須先通過查看二進(jìn)制日志文件確定恢復(fù)的位置或時間點。使用“mysqlbinlog --no-defaults 二進(jìn)制日志文件”可以查看二進(jìn)制日志文件的具體內(nèi)容。

基于時間點恢復(fù)（指定開始、結(jié)束）

開始時間

結(jié)束時間

三、制定企業(yè)備份策略的思路

在企業(yè)中備份策略并不是千篇一律的，而是根據(jù)每個企業(yè)的實際生產(chǎn)環(huán)境與業(yè)務(wù)需求制定合適的備份策略。無論是選擇完全備份，還是選擇增量備份，都需考慮它們的優(yōu)缺點，是否適合當(dāng)前的生產(chǎn)環(huán)境。同時，為了保證恢復(fù)的完整性，建議開啟二進(jìn)制日志功能，二進(jìn)制日志文件給恢復(fù)工作也帶來了很大的靈活性，可以基于時間點或位置進(jìn)行恢復(fù)。考慮到數(shù)據(jù)庫性能，可以將二進(jìn)制日志文件保存到其他安全的硬盤中。

在進(jìn)行熱備份時，備份操作和應(yīng)用服務(wù)在同時運(yùn)行，這樣就十分消耗系統(tǒng)資源了，導(dǎo)致數(shù)據(jù)庫服務(wù)性能下降，這就需要選擇一個合適的時間(如在應(yīng)用負(fù)擔(dān)很小的時候)再來進(jìn)行備份操作。

需要注意的是，不是備份完就萬事大吉，最好確認(rèn)備份是否可用，所以備份之后的恢復(fù)測試是非常有必要的

數(shù)據(jù)更新頻繁，則應(yīng)該頻繁地備份。數(shù)據(jù)的重要性，在有適當(dāng)更新時進(jìn)行備份。在數(shù)據(jù)庫壓力小的時間段進(jìn)行備份，如一周一次完全備份，每天進(jìn)行增量備份。中小公司，完全備份一般一天一次即可。大公司可每周進(jìn)行一次完全備份，每天進(jìn)行一次增量備份。盡量為企業(yè)實現(xiàn)主從復(fù)制架構(gòu)，以增加數(shù)據(jù)的可用性。四、擴(kuò)展：MySQL的GTID 和XtraBackupMySQL的GTID

GTID 即全局事務(wù) ID(global transaction identifier)，其保證為每一個在主上提交的事務(wù)在復(fù)制集群中可以生成一個唯一的 ID。GTID 最初由 google實現(xiàn)，官方 MySQL在 5.6才加入該功能

GTID 實際上是由 UUID+TID(即 transactionId)組成的。其中 UUID(即server uuid)產(chǎn)生于auto.conf 文件(cat /data/mysql/data/auto.cnf)，是個 MySQL, 實例的唯一標(biāo)識。TID 代表了該實例上已經(jīng)提交的事務(wù)數(shù)量，并且隨著事務(wù)提交單調(diào)遞增，所以GTID能夠保證每個MySQL實例事務(wù)的執(zhí)行(不會重復(fù)執(zhí)行同一個事務(wù)，并且會補(bǔ)全沒有執(zhí)行的事務(wù))。GTID在一組復(fù)制中，全局唯一，Gtid 的備份也是基于binlog 的

（1）配置my.cnf開啟gtid

（2）創(chuàng)建基本測試庫，表，數(shù)據(jù)

創(chuàng)建測試庫 test，測試表 user，并導(dǎo)入3條數(shù)據(jù)

表示在該實例上第一個到第四個事務(wù)的所有操作

（3）全量備份

關(guān)于數(shù)據(jù)一致性的提醒的警告信息，可忽略

（4）插入新數(shù)據(jù)

（5）模擬誤操作

（6）導(dǎo)出增量數(shù)據(jù)

這里把 3-6的事務(wù)導(dǎo)出，也就是全量備份后，新插入的兩條數(shù)據(jù)，第7個事務(wù)不能導(dǎo)除，因為它是 drop database 的誤刪除語句

（7）恢復(fù)全量

清空 GTID 歷史,不然恢復(fù)全量備份時會產(chǎn)生沖突，生產(chǎn)環(huán)境謹(jǐn)慎操作須提前備份

（8）恢復(fù)增量備份

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。