目錄

• 1.資料庫管理員的兩大作業核心
  • 1.1.能夠讓資料安全得到保護
  • 1.2.能7*24小時提供服務
• 2.全量備份和增量備份
  • 2.1.全量備份的概念
  • 2.2.增量備份的概念
  • 2.3.全量與增量如何結合備份
    • 2.3.1.按天全備與增量備份資料
    • 2.3.2.按周全備與增量備份資料
• 3.MySQL常用的備份方式
  • 3.1.邏輯備份方式
    • 3.1.1.邏輯備份
    • 3.1.2.邏輯備份的特點
    • 3.1.3.邏輯備份的常用工具
    • 3.1.4.邏輯備份的企業應用場景
  • 3.2.物理備份方式
    • 3.2.1.物理備份
      • 3.2.1.1.冷備方法
      • 3.2.1.2.熱備方法
    • 3.2.2.物理備份的特點
    • 3.2.3.物理備份的常用工具或方法
    • 3.2.4.物理備份的企業應用場景
  • 3.3.物理備份與邏輯備份的區別
• 4.邏輯備份的企業級應用
  • 4.1.中小企業的MySQL備份實戰
    • 4.1.1.中小企業全備備份策略與應用
    • 4.1.2.全備的資料何時可以派上用場
    • 4.1.3.中小企業增量備份策略
    • 4.1.4.備份binlog增量檔案何時可以派上用場
    • 4.1.5.企業里MySQL備份策略選擇
  • 4.2.中小企業MySQL增量恢復案例
• 5.分庫分表的生產備份策略
  • 5.1.為什么要分庫分表備份
  • 5.2.如何進行分庫備份
  • 5.3.如何進行分表備份
• 6.MySQL生產常用備份架構方案

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

1.1.能夠讓資料安全得到保護

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

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

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

2.全量備份和增量備份

2.1.全量備份的概念

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

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

mysqldump -B --master-data=https://www.cnblogs.com/yanyanqaq/p/2 --single-transaction -A |gzip >/opt/all.sql.gz

備份oldboy一個庫中所有資料：

mysqldump -B --master-data=https://www.cnblogs.com/yanyanqaq/p/2 --single-transaction oldboy |gzip >/opt/oldboy.sql.gz

2.2.增量備份的概念

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

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

2.3.1.按天全備與增量備份資料

按天全備的特點：

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

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

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

2.3.2.按周全備與增量備份資料

按周全備的特點：

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=https://www.cnblogs.com/yanyanqaq/p/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.物理備份與邏輯備份的區別

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

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=https://www.cnblogs.com/yanyanqaq/p/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=https://www.cnblogs.com/yanyanqaq/p/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=https://www.cnblogs.com/yanyanqaq/p/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=https://www.cnblogs.com/yanyanqaq/p/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=https://www.cnblogs.com/yanyanqaq/p/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=https://www.cnblogs.com/yanyanqaq/p/2 $dbname$tablename|gzip >$bak_path/${dbname}_${tablename}_$(date +%F).sql.gz
    done
done

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

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

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

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

標籤：MySQL

上一篇：【Linux面試題8】資料庫筆試題集錦

下一篇：Xtrabackup作業原理