[TOC] 1.資料庫管理員的兩大工作核心 1.1.能夠讓數據安全得到保護所謂的數據安全，最容易被人誤以為是只有數據丟失，其實還包括數據被脫庫、泄密等方面。 1.2.能7 24小時提供服務資料庫具備7 24小時提供服務的能力，是資料庫管理員的重要職責。 2.全量備份和增量備份 2.1.全量備份的 ...

1.資料庫管理員的兩大工作核心
- 1.1.能夠讓數據安全得到保護
- 1.2.能7*24小時提供服務
2.全量備份和增量備份
3.MySQL常用的備份方式
4.邏輯備份的企業級應用
- 4.1.中小企業的MySQL備份實戰
- 4.2.中小企業MySQL增量恢復案例
5.分庫分表的生產備份策略
6.MySQL生產常用備份架構方案

1.資料庫管理員的兩大工作核心

1.1.能夠讓數據安全得到保護

所謂的數據安全，最容易被人誤以為是只有數據丟失，其實還包括數據被脫庫、泄密等方面。

**1.2.能7*24小時提供服務**

資料庫具備7*24小時提供服務的能力，是資料庫管理員的重要職責。

2.全量備份和增量備份

2.1.全量備份的概念

全量數據就是資料庫中所有的數據（或某一個庫的全部數據）；全量備份就是把資料庫中所有的數據進行備份。

備份資料庫中所有庫的所有數據：

mysqldump -B --master-data=2 --single-transaction -A |gzip >/opt/all.sql.gz

備份oldboy一個庫中所有數據：

mysqldump -B --master-data=2 --single-transaction oldboy |gzip >/opt/oldboy.sql.gz

2.2.增量備份的概念

增量數據就是指上一次全量備份數據之後到下一次全量備份之前資料庫所更新的數據。在使用mysqldump命令做全備時，增量數據就是MySQL的binlog日誌，因此，對binlog日誌的備份在此處就可以稱為增量備份，當然，有些工具本身就可以實現全量以及增量數據備份，例如Xtrabackup。

2.3.全量與增量如何結合備份

2.3.1.按天全備與增量備份數據

周一00點全量備份	周二00點全量備份	周三00點全量備份	......
01.sql.gz	02.sql.gz	03.sql.gz	......
周一增量備份	周二增量備份	周三增量備份	......
mysql-bin.000021、......	mysql-bin.000035、......	mysql-bin.000049、......	......

按天全備的特點：

1、優點：恢複數據時需要的數據文件數量少，恢復時間短，維護成本低。
2、缺點：每天一個全備，占用空間多，占用系統資源多，經常備份會影響用戶體驗。

中小企業用得最多的策略就是按天全備，然後根據空間情況保留全備份數，例如僅保留7天內的備份數據，如果企業數據很重要，則可以使用磁帶機等設備留存一年以上的備份數據。

binlog增量的清理可以通過在my.cnf中配置“過期清理天數”的相關參數（expire_logs_days=7）來實現，例如保留7天內的binlog日誌，理論上如果每天進行全備，那麼binlog只要保留1天的。

2.3.2.按周全備與增量備份數據

每周一00點全量備份
01.sql.gz
周一增量備份	周二增量備份	周三增量備份	一直到下周日增量備份
mysql-bin.000021、......	mysql-bin.000035、......	mysql-bin.000049、......	......

按周全備的特點：

1、優點：每周僅有一個完整備份，因此占用磁碟總空間小，占用系統資源少，備份次數少，用戶體驗好一些。
2、缺點：恢復時數據文件多，導致恢復麻煩，維護成本高，恢復時間長。

大型企業由於數據量特別大，每天全備時間太長，因此有可能會採用周備的策略，這樣不僅有利於節省數據存儲空間而且不會影響用戶訪問資料庫的體驗。

3.MySQL常用的備份方式

MySQL備份的常用方式有邏輯備份和物理備份。

3.1.邏輯備份方式

3.1.1.邏輯備份

MySQL的邏輯備份其實就是使用MySQL自帶的mysqldump命令或其他相關工具，把MySQL數據以SQL語句的形式導出或備份成文件。在恢復的時候則通過執行mysql恢覆命令（或source等）將存儲的SQL語句文件數據還原到MySQL資料庫中。

實現邏輯備份的常用工具為MySQL自帶的mysqldump命令，備份所有庫：

mysqldump -A -B --master-data=2 --single-transaction |gzip >/opt/all.sql.gz

恢複數據庫的方法之一為：

zcat opt/all.sql.gz|mysql

使用此種邏輯備份方式進行全量備份後的增量數據就是資料庫記錄的binlog日誌文件，那麼，如何增量恢復binlog日誌呢？mysqlbinlog工具可以把binlog日誌轉換成SQL語句，然後通過mysql恢覆命令（或source等）將SQL語句還原到MySQL資料庫中。

恢復增量數據：

mysqlbinlog mysql-bin.000008 mysql-bin.000009 >bin.sql  #將binlog文件解析為SQL語句
mysql <bin.sql  #恢復到資料庫

3.1.2.邏輯備份的特點

邏輯備份的優點為操作簡單、方便、可靠，並且備份的數據可以跨平臺、跨版本、甚至跨軟體、跨操作系統，還可以實現分庫分表備份；邏輯備份也有一定的缺點，例如，備份速度比物理備份慢、恢復的效率也不是特別高等。

3.1.3.邏輯備份的常用工具

mysqldump是MySQL官方自帶的最常用的邏輯備份工具，還能實現分表分庫備份，還有一個mydumper工具，它是一個在GPL許可下發佈的高性能MySQL備份和恢復工具集。

3.1.4.邏輯備份的企業應用場景

適用於數據量不是特別大的場景，打包前不大於30GB的資料庫數據，30GB的值主要是考慮備份效率的問題，以及管理員使用複雜度的平滑。不過，在跨版本、跨軟體升級或遷移數據的時候，此時物理備份一般就不能使用。

3.2.物理備份方式

3.2.1.物理備份

3.2.1.1.冷備方法

MySQL的物理備份方法之一是使用cp、rsync、tar、scp等複製工具把MySQL數據文件複製成多份，由於在備份期間數據仍然有寫入操作，所以，直接複製的備份方式會引起數據丟失。另外在恢複數據庫時，對新資料庫的路徑、配置也有要求，一般要和原庫的配置保持一致（版本、路徑、配置儘可能一樣）。

為了確保備份期間數據的一致性，可以選擇人工停庫或者鎖庫後再進行物理複製，而這在生產環境中一般是不允許的，除非是可以申請停機或鎖表時間，所以使用傳統Linux命令複製工具還是比較粗的冷備份方式，應避免使用。

一般在進行大規模資料庫遷移時，先停庫，然後物理遷移，這樣做是很有效率的方案。

3.2.1.2.熱備方法

除了在Linux命令行通過命令直接複製MySQL數據文件之外，還有一些其他的第三方的開源或商業物理熱備份工具，如Xtrabackup。使用這個工具可以實現物理全備及增量備份。

3.2.2.物理備份的特點

物理備份的優缺點正好與邏輯備份相反，因此在企業里應根據需求，互補使用。

1、優點：速度快，效率高。
2、缺點：不容易跨平臺、跨版本、跨軟體、跨操作系統，可以實現分庫分表備份，但恢復時會麻煩很多，軟體的使用也比較複雜一些。

3.2.3.物理備份的常用工具或方法

Linux下冷備份工具為cp、tar，備份時需要鎖表或者停庫以確保數據的一致性；開源的熱備份（基於InnoDB）工具則是Xtrabackup。

3.2.4.物理備份的企業應用場景

資料庫總數據量超過30GB的，可使用Xtrabackup熱備工具進行備份，以提升效率。

可以選擇在資料庫的從庫上進行備份，備份時停止SQL線程應用數據到資料庫，然後通過cp或tar打包備份，這也是一種不錯的冷備方案，不會影響資料庫的服務。

3.3.物理備份與邏輯備份的區別

物理備份與邏輯備份的對比：

	邏輯備份	物理備份
備份原理	以SQL語句的形式	直接複製磁碟物理文件或其他非SQL語句方式的備份
相關命令	mysqldump、mysql、mysqlbinlog	cp、rsync、tar、scp、Xtrabackup（熱備）
備份要求	需要鎖表但不需要停庫。鎖表會影響資料庫更新，InnoDB引擎可以不鎖表，而採用事務備份方案	冷備需要鎖表或停機，熱備不需要鎖表（僅事務引擎，例如InnoDB）或停機
配置特點	恢復時與系統版本、庫的配置基本版本無關	物理複製需要系統、配置、版本儘可能地一致
性能特點	速度慢	速度快
方便性考慮	安全、易掌握、容易控制，一般不會丟失數據	冷備簡單，但應用場景少，熱備工具操作複製一些，較難掌握

4.邏輯備份的企業級應用

4.1.中小企業的MySQL備份實戰

4.1.1.中小企業全備備份策略與應用

中小企業一般會採用邏輯備份，常用的工具就是mysqldump命令，備份的策略一般是每日進行全量備份，備份會選擇在資料庫業務流量低估時執行，備份時可以鎖表或者採用事務方式備份。

簡單的備份腳步：

vim bak.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/bin
bak_path=/server/backup
[ ! -d $bak_path ] && mkdir -p $bak_path  #若備份路徑不存在則創建
mysqldump -B -A --master-data=2 |gzip >$bak_path/${file_name}.sql.gz  #如果僅為innodb引擎，則可以再加上--single-transaction參數
rsync -az $bak_path/ [email protected]::mysql/ --password-file=/etc/rsync.password
#備份完成後立刻推送至備份伺服器，需要提前部署rsync服務
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7|xargs rm -f  #刪除本地的7天備份

稍微複雜點的腳步：

vim bak.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/bin
bak_path=/server/backup
[ ! -d $bak_path ] && mkdir -p $bak_path  #若備份路徑不存在則創建
if [ $(date +%w) -eq 6 ]  #如果時間為周六，則
then
    file_name=bak_$(date +%w_%F)  #將備份文件名改為周和日期，目的是在備份伺服器上保留每周六的數據
else
    file_name=bak_$(date +%F)  #否則，備份文件名為日期
fi
mysqldump -B -A --master-data=2 |gzip >$bak_path/${file_name}.sql.gz
md5sum $bak_path/${file_name}.sql.gz >$bak_path/${file_name}.flag  #做md5指紋的目的是用於未來檢測備份及傳輸結果是否正常
rsync -az $bak_path/ [email protected]::mysql/ --password-file=/etc/rsync.password
#備份完成後立刻推送至備份伺服器，需要提前部署rsync服務
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7|xargs rm -f  #刪除本地的7天備份

配置定時任務，使其每日0點執行腳本：

crontab -e
bak mysql for oldboy at 20200515
00 00 * * * /bin/sh /server/scripts/bak.sh &>/dev/null

保留最近7天的所有備份，同時保留每周六的全部備份：

find /server/backup/ -type f -name "bak_*" -mtime +7 ! -name "bak_6*"
find /server/backup/ -type f -name "bak_*" -mtime +7 ! -name "bak_6*" |xargs rm -f

4.1.2.全備的數據何時可以派上用場

使用mysqldump全備的數據什麼時候可以派上用場：

1、遷移或者升級資料庫時。
2、增加從庫時。
3、人為執行DDL、DML語句破壞資料庫數據時（此時若使用主從庫就會無法防止數據丟失，因為所有庫都會執行破壞語句）。
4、跨機房災備時，此時需要將全備份複製到異地。

若是因為硬體或刪除物理文件導致資料庫故障，就不需要用備份數據恢復了，可以直接把主庫關閉，在從庫上配置好VIP等配置後，啟動從庫提供服務即可。

4.1.3.中小企業增量備份策略

中小企業增量備份就是備份binlog文件，在MySQL沒有主從複製功能或主從複製功能不完善的時候，我們就曾採取定時或實時推binlog文件的方法。例如每分鐘推一次binlog到備份伺服器上，或者通過mysqlbinlog參數read-from-remote-server，在其他伺服器上遠程讀取binlog。

但是這類方法都不是最佳的，因為有可能會丟失數據。比較好的binlog增量備份或MySQL備份方法就是為MySQL資料庫配置異機主從複製功能（實時複製功能），即binlog會被實時地發送到從伺服器上，這樣效果才是最好的。當然，也要相應地在主從複製的從庫上實現全備。

4.1.4.備份binlog增量文件何時可以派上用場

當需要完整恢複數據庫數據的時候，就會需要binlog增量恢復。

4.1.5.企業里MySQL備份策略選擇

大多數中小企業的資料庫環境都為一主多從，因此，可採取在一個從庫伺服器上專門做全量以及增量備份（需要開啟從庫記錄binlog日誌功能），至於備份方法，採用mysqldump、Xtrabackup均可。

4.2.中小企業MySQL增量恢復案例

完整恢複數據庫數據需要具備的條件：

1、具備全量備份（mysqldump）。
2、除全量備份以外，還有全量備份之後產生的所有binlog增量日誌。

模擬0點開始對資料庫oldboy數據進行全備：

mysqldump -B --master-data=2 --single-transaction oldboy|gzip >/data/backup/oldboy_$(date +%F).sql.gz

模擬0點全備後用戶繼續寫入數據：

mysql -e "use oldboy;insert into test values(6,'bingbing');"
mysql -e "use oldboy;insert into test values(7,'xiaoting');"

模擬上午10點管理員刪除oldboy資料庫：

mysql -e "drop database oldboy;show databases;"

恢復前準備，移走所有binlog增量文件，防止二次破壞，並確認是否有全備：

cp -a /application/mysql/data/mysql-bin.* /data/backup/

開始恢復：

1、停止資料庫對外訪問。因為是通過drop命令刪除資料庫的，後面不會有寫入操作，因此，可以不用額外停止寫入。但如果是因為update導致的數據破壞，最好是停庫處理或對外停止寫入。這裡採用iptables防火牆屏蔽所有應用程式的寫入：
iptables -I INPUT -p tcp --dport 3306 ! -s 172.16.1.51 -j DROP  #非172.16.1.51禁止訪問資料庫3306埠。

2、解壓全備的數據：
gzip -cd oldboy_2020-05-19.sql.gz >oldboy.sql

3、解析binlog文件增量數據：
sed -n '22p' oldboy.sql

從代碼里可以看到，要從mysql-bin.000004文件的7181位置點開始恢復增量數據：
mysqlbinlog -d oldboy mysql-bin.000004 --start-position=7181 -r bin.sql

恢復後面的所有binlog文件：
mysqlbinlog -d oldboy mysql-bin.000005 mysql-bin.000006 -r bin1.sql

4、剔除誤刪除資料庫的drop語句：
grep -w drop bin.sql  #過濾drop單詞的行。
sed -i '/drop database oldboy/d' bin.sql  #刪除drop資料庫oldboy的語句。

恢復0點以前的全備數據：
mysql </data/backup/oldboy.sql  #先恢復全備，即0點以前的備份

恢復增量備份：
mysql oldboy</data/backup/bin.sql  #恢復增量文件

5.分庫分表的生產備份策略

5.1.為什麼要分庫分表備份

全備命令把2個庫備份成了一個備份文件：

mysqldump -B --master-data=2 --single-transaction oldboy mysql|gzip>/data/backup/all.sql.gz

在還原時，很多時候只需要還原一個庫或者多個庫的一個表，這個時候，整個備份文件就會很難拆分，給恢復也會帶來麻煩。對於這種情況，最好是分庫分表備份。

5.2.如何進行分庫備份

最佳的方法就是從資料庫中取出所有庫名，然後對每個資料庫執行一次備份。

分庫備份的腳本：

vi fenku.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/server/backup/$(date +%F)
[ ! -d $bak_path ] && mkdir -p $bak_path
for dbname in `mysql -e "show databases"|sed '1,2d'|grep -v _schema`  #取庫名輪詢備份
do
    mysqldump -B --master-data=2 $dbname|gzip >$bak_path/${dbname}_$(date +%F).sql.gz  #註意備份的名字
done

5.3.如何進行分表備份

分表備份比分庫更細，實際上就是先取一個庫名，然後迴圈讀取該庫里的表進行備份，備份完之後，再取下一個庫名，繼續迴圈庫里的所有表進行備份，知道所有庫里的所有表都備份完畢。

分表備份的腳本：

vi fenbiao.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/server/backup/$(date +%F)
[ ! -d $bak_path ] && mkdir -p $bak_path
for dbname in `mysql -e "show databases"|sed '1,2d'|grep -v _schema`
do
    for tablename in `mysql -e "show tables from $dbname;"|sed '1d'`
    do
        mysqldump -B --master-data=2 $dbname$tablename|gzip >$bak_path/${dbname}_${tablename}_$(date +%F).sql.gz
    done
done

6.MySQL生產常用備份架構方案

在中小公司一般比較常用的做法是，每日0點執行全備任務，先把數據按照日期備份到資料庫本地，然後推送到資料庫備份伺服器，由於本地空間有限，因此本地僅保留3-7日的全備。

如果有備用的伺服器資源可用，那麼最好通過主從同步的方式進行備份，這樣即使物理機損壞了也可以很快地切換到新伺服器（還可以HA自動切換），但是主從複製的缺點是不能解決錯誤執行SQL語句的問題。

因此，我們一般會在某一臺不對外提供業務的從庫上使用mysqldump或Xtrabackup來進行定時備份。這裡有個需要特別註意的地方，用於備份從庫的二進位日誌記錄功能必須打開。

MySQL企業級備份