MySQL企業級備份

目錄

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

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

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

​2.全量備份和增量備份​

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

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

​​2.1.全量備份的概念​​

​​2.2.增量備份的概念​​

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

​3.MySQL常用的備份方式​

​​3.2.1.實體備份​​

​​3.2.2.實體備份的特點​​

​​3.2.3.實體備份的常用工具或方法​​

​​3.2.4.實體備份的企業應用場景​​

​​3.2.1.1.冷備方法​​

​​3.2.1.2.熱備方法​​

​​3.1.1.邏輯備份​​

​​3.1.2.邏輯備份的特點​​

​​3.1.3.邏輯備份的常用工具​​

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

​​3.1.邏輯備份方式​​

​​3.2.實體備份方式​​

​​3.3.實體備份與邏輯備份的差別​​

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

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

​​4.1.2.全備的資料何時可以派上用場​​

​​4.1.3.中小企業增量備份政策​​

​​4.1.4.備份binlog增量檔案何時可以派上用場​​

​​4.1.5.企業裡MySQL備份政策選擇​​

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

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

​5.分庫分表的生産備份政策​

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

​​5.2.如何進行分庫備份​​

​​5.3.如何進行分表備份​​

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

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

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

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

2.全量備份和增量備份

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

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

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

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

<col>

周一00點全量備份

周二00點全量備份

周三00點全量備份

......

01.sql.gz

02.sql.gz

03.sql.gz

周一增量備份

周二增量備份

周三增量備份

mysql-bin.000021、......

mysql-bin.000035、......

mysql-bin.000049、......

按天全備的特點：

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

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

每周一00點全量備份

一直到下周日增量備份

按周全備的特點：

大型企業由于資料量特别大，每天全備時間太長，是以有可能會采用周備的政策，這樣不僅有利于節省資料存儲空間而且不會影響使用者通路資料庫的體驗。

3.MySQL常用的備份方式

MySQL備份的常用方式有邏輯備份和實體備份。

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

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

恢複資料庫的方法之一為：

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

恢複增量資料：

邏輯備份的優點為操作簡單、友善、可靠，并且備份的資料可以跨平台、跨版本、甚至跨軟體、跨作業系統，還可以實作分庫分表備份；邏輯備份也有一定的缺點，例如，備份速度比實體備份慢、恢複的效率也不是特别高等。

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

适用于資料量不是特别大的場景，打包前不大于30GB的資料庫資料，30GB的值主要是考慮備份效率的問題，以及管理者使用複雜度的平滑。不過，在跨版本、跨軟體更新或遷移資料的時候，此時實體備份一般就不能使用。

MySQL的實體備份方法之一是使用cp、rsync、tar、scp等複制工具把MySQL資料檔案複制成多份，由于在備份期間資料仍然有寫入操作，是以，直接複制的備份方式會引起資料丢失。另外在恢複資料庫時，對新資料庫的路徑、配置也有要求，一般要和原庫的配置保持一緻（版本、路徑、配置盡可能一樣）。

為了確定備份期間資料的一緻性，可以選擇人工停庫或者鎖庫後再進行實體複制，而這在生産環境中一般是不允許的，除非是可以申請停機或鎖表時間，是以使用傳統Linux指令複制工具還是比較粗的冷備份方式，應避免使用。

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

除了在Linux指令行通過指令直接複制MySQL資料檔案之外，還有一些其他的第三方的開源或商業實體熱備份工具，如Xtrabackup。使用這個工具可以實作實體全備及增量備份。

實體備份的優缺點正好與邏輯備份相反，是以在企業裡應根據需求，互補使用。

Linux下冷備份工具為cp、tar，備份時需要鎖表或者停庫以確定資料的一緻性；開源的熱備份（基于InnoDB）工具則是Xtrabackup。

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

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

實體備份與邏輯備份的對比：

邏輯備份

實體備份

備份原理

以SQL語句的形式

直接複制磁盤實體檔案或其他非SQL語句方式的備份

相關指令

mysqldump、mysql、mysqlbinlog

cp、rsync、tar、scp、Xtrabackup（熱備）

備份要求

需要鎖表但不需要停庫。鎖表會影響資料庫更新，InnoDB引擎可以不鎖表，而采用事務備份方案

冷備需要鎖表或停機，熱備不需要鎖表（僅事務引擎，例如InnoDB）或停機

配置特點

恢複時與系統版本、庫的配置基本版本無關

實體複制需要系統、配置、版本盡可能地一緻

性能特點

速度慢

速度快

友善性考慮

安全、易掌握、容易控制，一般不會丢失資料

冷備簡單，但應用場景少，熱備工具操作複制一些，較難掌握

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

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

簡單的備份腳步：

稍微複雜點的腳步：

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

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

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

若是因為硬體或删除實體檔案導緻資料庫故障，就不需要用備份資料恢複了，可以直接把主庫關閉，在從庫上配置好VIP等配置後，啟動從庫提供服務即可。

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

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

當需要完整恢複資料庫資料的時候，就會需要binlog增量恢複。

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

完整恢複資料庫資料需要具備的條件：

模拟0點開始對資料庫oldboy資料進行全備：

模拟0點全備後使用者繼續寫入資料：

模拟上午10點管理者删除oldboy資料庫：

恢複前準備，移走所有binlog增量檔案，防止二次破壞，并确認是否有全備：

開始恢複：

5.分庫分表的生産備份政策

全備指令把2個庫備份成了一個備份檔案：

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

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

分庫備份的腳本：

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

分表備份的腳本：

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

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

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

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