MYSQL 全量，增量備份與恢復

一數據備份的重要性

1 數據備份的重要性

2 數據庫備份類型

2.1 從物理與邏輯的角度分類

2.2. 從數據庫的備份策略角度分類從數據庫的備份策略角度,數據庫的備份可分為完全備份、差異備份和增量備份。

3 常見的備份方法

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

3.2 專用備份工具 mysqldump 或 mysqlhotcopy

3.3 通過啟用二進制日志進行增量備份

3.4通過第三方工具備份

?二. 數據庫完全備份操作上面提到根據數據庫備份策略分類,備份可分為完全備份、差異備份和增量備份。在本章中我們只介紹完全備份與增量備份,感興趣的同學可以課下自學差異備份的方法。

1 物理冷備份與恢復

1.1 備份數據庫

1.2 恢復數據庫

2.mysqldump 備份與恢復

2.1備份數據庫

2.2 查看備份文件

2.3 恢復數據庫

3 MySQL 增量備份與恢復

3.1 MySQL 增量備份概述

(1)增量備份的特點

(2)MySQL二進制日志對備份的意義

3.2 MySQL 增量恢復

3.3 MYSQL?備份案例

1. 一般恢復

（1）添加數據庫，表，錄入信息

（2）先進性一次性完全備份

（3）繼續錄入新的數據并進行增量備份

（4）模擬錯誤操作刪除user——info表

（5）恢復操作

2.基于位置恢復

3.基于時間恢復

三備份策略思路

四擴展：MYSQL的GTID 和XTRABACKUP

1.mysql的gtid

（1）配置my.cnf 開啟gtid

(2)創建基本測試庫，表，數據

（3）全量備份

（4）插入新數據

（5）模擬數據誤刪除

（6）導出增量備份

（7）恢復全量

（8）恢復增量

2.XtraBackup

（1）安裝xtrabackup和qpress包

(2)完整備份與恢復備份

（3）增量備份與恢復備份

一數據備份的重要性、、。

備份的主要目的是災難恢復,備份還可以測試應用、回滾數據修改、查詢歷史數據、審計等。之前已經學習過如何安裝 MySQL,本小節將從生產運維的角度了解備份恢復的分類與方法。

1 數據備份的重要性

在企業中數據的價值至關重要,數據保障了企業業務的正常運行。因此,數據的安全性及數據的可靠性是運維的重中之重,任何數據的丟失都可能對企業產生嚴重的后果。通常情況下造成數據丟失的原因有如下幾種:

> 程序錯誤。
> 人為操作錯誤。
> 運算錯誤。
>磁盤故障。
>災難(如火災、地震)和盜竊

2 數據庫備份類型

2.1 從物理與邏輯的角度分類

數據庫備份可以分為物理備份和邏輯備份。
物理備份是對數據庫操作系統物理文件(如數據文件、日志文件等)的備份。這種類型的備份適用于在出現問題時需要快速恢復的大型重要數據庫。

物理備份又可以分為冷備份(脫機備份)、熱備份(聯機備份)和溫備份。

> 冷備份: 在數據庫關閉狀態下進行備份操作。
> 熱備份: 在數據庫處于運行狀態時進行備份操作,該備份方法依賴數據庫的日志文件。
> 溫備份: 數據庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作。

邏輯備份是:對數據庫邏輯組件(如表等數據庫對象)的備份,表示為邏輯數據庫結構(CREATE DATABASE,CREATE TABLE 語句)和內容(INSERT 語句或分隔文本文件)的信息。這種類型的備份適用于可以編輯數據值或表結構較小的數據量,或者在不同的機器體系結構上重新創建數據。

2.2. 從數據庫的備份策略角度分類
從數據庫的備份策略角度,數據庫的備份可分為完全備份、差異備份和增量備份。

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

3 常見的備份方法

MySQL 數據庫的備份可以采用很多種方式,如直接打包數據庫文件(物理冷備份)、專用備份工具(mysqldump)、二進制日志增量備份、第三方工具備份等。

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

3.2 專用備份工具 mysqldump 或 mysqlhotcopy

? ?mysqldump 程序和 mysqlhotcopy 都可以做備份。mysqldump 是客戶端常用邏輯備份程序,能夠產生一組被執行以后再現原始數據庫對象定義和表數據的SQL 語句。它可以轉儲一個到多個MySQL數據庫,對其進行備份或傳輸到遠程SQL 服務器。mysqldump 更為通用,因為它可以備份各種表。mysqlhotcopy 僅適用于某些存儲引擎。

? mysqlhotcopy 是由 Tim Bunce 最初編寫和貢獻的Perl 腳本。mysqlhotcopy 僅用于備份MyISAM和ARCHIVE表。它只能運行在 UNIX或 Linux上。因為使用范圍小,因此本文中不做詳細介紹,如果同學們有興趣可以在課下研究。

3.3 通過啟用二進制日志進行增量備份

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

3.4通過第三方工具備份

Percona XtraBackup 是一個免費的MySQL 熱備份軟件,支持在線熱備份Innodb 和 XtraDB,也可以支持 MySQL 表備份,不過 MyISAM 表的備份要在表鎖的情況下進行。本節對于 Percona XtraBackupr 的敘述是基于 2.4 版本的。
? Percona XtrBackup 有三個主要的工具:xtrabackup、innobackupex、xbstream.

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

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

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

以上是備份過程,如果需要恢復數據,則在準備階段,xtrabackup 就需要使用之前復制的重做日志對備份出來的 Innodb 數據文件進行災難恢復,此階段完成之后,數據庫就可以進行重建還原了。

Percona XtraBackup 對 MySIAM 的復制,是按這樣的一個順序進行的:首先鎖定表,然后復制,再解鎖表。第三方工具備份操作本章中不做詳細介紹,如果同學們有興趣可以在課下研究

?二. 數據庫完全備份操作
上面提到根據數據庫備份策略分類,備份可分為完全備份、差異備份和增量備份。在本章中我們只介紹完全備份與增量備份,感興趣的同學可以課下自學差異備份的方法。

1 物理冷備份與恢復

物理冷備份一般用 tar 命令直接打包數據庫文件夾,而在進行備份之前需要使用“systemct1 stop mysqld”命令關閉 mysqld 服務。

1.1 備份數據庫

創建一個、backup 目錄作為備份數據存儲路徑，使用tar創建備份文件，整個數據庫文件夾備份屬于完全備份的建立。

1.2 恢復數據庫

執行下面操作將數據庫文件、usr/local/mysql/data/轉移至bak目錄下，模擬故障。

2.mysqldump 備份與恢復

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

2.1備份數據庫

使用 mysqldump 命令導出數據時,默認會直接在終端顯示,若要保存到文件,還需要結合 Shell的“>”重定向輸出操作,命令格式如下所示。

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

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

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

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

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

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

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

若需要備份整個 MySQL 服務器中的所有庫,應使用格式3。當導出的數據量較大的時候,可以添加“ - opt”選項以優化執行速度。例如,執行以下操作將創建備份文件 all-data.sql,其中包括 MySQL 服務器中的所有庫。

2.2 查看備份文件

通過 mysqldump 工具導出的 SQL 腳本是文本文件,其中“/ *…* /”部分或以“ -- ”開頭的行表示注釋信息。使用grep、less、cat 等文本工具可以查看腳本內容。例如,執行以下操作可以過濾出test.sql 腳本中的數據庫操作語句。

2.3 恢復數據庫

使用 mysqldump 命令導出的 SQL 備份腳本,在需要恢復時可以通過 mysql命令對其進行導入操作,命令格式如下所示。

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

當備份文件中只包含表的備份,而不包含創建的庫的語句時,執行導入操作時必須指定庫名,且目標庫必須存在。

例如,執行以下操作可以從備份文件 mysql-user.sql 中將表導入test 庫。其中“-e”選項是用于指定連接MySQL 后執行的命令,命令執行完后自動退出。

若備份文件中已經包括完整的庫信息,則執行導入操作時無須指定庫名。

例如執行以下操作可以從備份文件 test.sql恢復test庫

除了使用mysql命令結合“<”恢復數據外，還可以使用source命令恢復數據，具體如下

3 MySQL 增量備份與恢復

使用 mysqldump 進行完全備份,備份的數據中有重復數據,備份時間與恢復時間過長。而增量備份就是自上一次備份之后增加或改變的內容

3.1 MySQL 增量備份概述

(1)增量備份的特點

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

(2)MySQL二進制日志對備份的意義

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

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

Mysq18.0 默認已經開啟binlog,無需顯示配置binlog(默認binlog文件為:binlog.000001),如需自定義binlog配置,請添加如下配置項

3.2 MySQL 增量恢復

在維護數據庫時,因為各種各樣的原因可能會導致數據丟失,如:人為的 SQL語句破壞數據庫、在進行下一次全備份之前發生系統故障導致數據庫數據丟失、在數據庫主從架構中主庫的數據發生故障等。當出現以上場景時可以使用增量恢復來恢復數據。

常用的增量恢復的方法有三種:一般恢復、基于位置的恢復、基于時間點的恢復。

一般恢復:將所有備份的二進制日志內容全部恢復,命令格式如下所示。

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

基于位置的恢復:數據庫管理員在操作數據庫時可能在同一時間點既有錯誤的操作也有正確的操作,通過基于位置進行恢復可以更加精準,命令格式如下所示。

· 格式 1:恢復數據到指定位置。
mysqlbinlog -- stop-position='操作 id'二進制日志 |mysql-u用戶名-p
密碼

·格式 2:從指定的位置開始恢復數據。
mysqlbinlog -- start-position='操作 id'二進制日志 |mysql-u 用戶名 -p密碼

基于時間點的恢復:跳過某個發生錯誤的時間點實現數據恢復,而基于時間點的恢復可以分成三種情況。

·格式 1:從日志開頭截止到某個時間點的恢復。
mysqlbinlog [ -- no-defaults] -- stop-datetime='年-月-日小時:分鐘:秒’二進制日志 |mysql -u 用戶名 -p 密碼

·格式 2:從某個時間點到日志結尾的恢復。
mysqlbinlog [ -- no-defaults] -- start-datetime='年-月-日小時:分鐘:秒’二進制日志|mysql-u 用戶名 -p 密碼

·格式 3:從某個時間點到某個時間點的恢復。
mysqlbinlog [ -- no-defaults] -- start-datetime='年-月-日小時:分鐘:秒’-- stop-datetime='年-月-日小時:分鐘:秒’二進制日志|mysql-u用戶名 -p密碼

3.3 MYSQL?備份案例

1. 一般恢復

（1）添加數據庫，表，錄入信息

在進行備份之前,先根據給出的需求創建用戶信息數據庫 client、用戶資費數據表user_info,并且根據需求描述中的表格插入前三條用戶的數據。

（2）先進性一次性完全備份

（3）繼續錄入新的數據并進行增量備份

2.基于位置恢復

三備份策略思路

在進行熱備份時,備份操作和應用服務在同時運行,這樣就十分消耗系統資原了,導致數據庫服務性能下降,這就需要選擇一個合適的時間(如在應用負擔很小的時候)再來進行備份操作。

需要注意的是,不是備份完就萬事大吉,最好確認備份是否可用,所以備份之后的恢復測試是非常有必要的。同時備份時間也要靈活調整,如:

數據更新頻繁,則應該頻繁地備份。
數據的重要性,在有適當更新時進行備份

在數據庫壓力小的時間段進行備份,如一周一次完全備份,每天進行增量備份。

中小公司,完全備份一般一天一次即可。
大公司可每周進行一次完全備份,每天進行一次增量備份。
盡量為企業實現主從復制架構,以增加數據的可用性。

四擴展：MYSQL的GTID 和XTRABACKUP

1.mysql的gtid

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

GTID 實際上是由UUID+TID(即transactionId)組成的。其中UUID(即server_uuid)產生于auto.conf文件(cat/data/mysql/data/auto.cnf),是一個MySQL實例的唯一標識。TID代表了該實例上已經提交的事務數量,并且隨著事務提交單調遞增,所以GTID能夠保證每個MySQL實例事務的執行(不會重復執行同一個事務,并且會補全沒有執行的事務)。GTID在一組復制中,全局唯

通過下面的實驗,了解基于gtid的增量備份和恢復(Gtid的備份也是基于binlog的)