Mysql備份還有這麼多套路，還不了解下？

邏輯備份和物理備份

邏輯備份

邏輯備份用於備份數據庫的結構（CREAET DATABASE、CREATE TABLE）和數據（INSERT），這種備份類型適合數據量小、跨SQL服務器、需要修改數據等場景。如mysqldump命令就是產生一個邏輯備份工具，使用mysqldump輸出的文件包含CREATE TABLE和INSERT語句，能夠直接重建表內容和表結構。

使用邏輯備份有以下優勢和劣勢：

優勢

• 可移植性高，SQL語句可直接適用於其他SQL服務器；
• 在數據恢復之前可增加、修改數據；
• 數據恢復粒度小可以是服務器、數據庫、表級別；
• 使用文本格式，可讀性高；

劣勢

• 備份時需要訪問mysql服務器，影響其他客戶端；
• 需要將數據轉換成邏輯格式（SQL，CSV）；
• 如果命令運行在客戶端，mysql服務器還需要將數據發送給客戶端；
• 因為輸出格式為文本文件，佔用空間較大；

物理備份

物理備份是包括存儲數據庫內容的目錄和文件的副本，這種類型的備份適用於需要在出現問題時快速恢復的大型重要數據庫。

優勢

• 完整的Mysql文件和目錄備份，只需要複製文件不需要轉換，速度比邏輯備份更快；
• 除了備份數據，還能備份配置文件和日誌文件；
• 不需要運行Mysql服務器就可以完成備份；
• 備份工具簡單使用cp、scp、tar命令即可完成備份；

劣勢

• 可移植性不高，恢複數據只適用於相同或類似的機器上；
• 為了保持數據庫文件的一致性，需要停機備份；
• 恢復粒度不能按表或用戶恢復；

在線備份和離線備份

在線備份需要mysql服務器處理運行狀態，以便備份工具從mysql服務器中獲取數據。離線備份表示mysql服務器處理停止狀態。兩種備份形式也可以稱為“熱備份”和“冷備份“。

在線備份的主要特性

• 備份不需要停機，對其他客戶端影響較小其他連接能夠正常訪問mysql服務器（依賴操作類型，如讀操作）；
• 備份需要加鎖，以免在備份期間對數據做出修改；

離線備份的主要特性

• 備份期間服務器不可用；
• 備份過程更簡單，不會受到客戶端的干擾；

邏輯備份（mysqldump使用）

mysqldump屬於邏輯備份命令，使用mysqldump備份的優勢是它非常方便和靈活，可以直接編輯輸出文件或者使用導入到其他的SQL服務器中去，但是它不能用作備份大量數據的快速解決方案，對於大數據量，即使備份花費的時候可以接受，但是恢複數據也可能會非常緩慢，因為執執行SQL語句會涉及磁盤I/O進行插入，創建索引等。mysqldump的使用方式非常簡單：

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

使用mysqldump備份時要注意：數據庫的一致狀態，在執行mysqldump命令時要保證數據不會再發生變更，保持數據的一致性有二種方法：

• 使Mysql服務器只讀
• 使用事務加上隔離級別：REPEATABLE READ

使用REPEATABLE READ事務隔離級別執行mysqldump命令（使用事務保持數據庫的一致狀態）：

mysqldump --master-data=2 \
 --flush-logs  \
 --single-transaction  \
 --all-databases > /backup/`date +%F-%H`-mysql-all.sql  

備份參數說明：

• –master-data: 將二進制日誌文件的名稱和位置備份
• –flush-logs: 開始備份之前刷新mysql服務器日誌文件
• –single-transaction：開始備份之前設置事務隔離級別為REPEATABLE READ然後發送一個START TRANSACTION命令。
• –all-databases：備份所有數據庫

物理備份（複製原始文件）

為了保證複製文件的完整性，備份原始文件最好是停止mysql服務器，複製原始文件備份由以下步驟完成：

1. 停止mysql服務器
   $ mysqladmin shutdown
2. 使用合適的工具複製原始數據文件
   $ tar cf /tmp/dbbackup.tar ./data
3. 備份完成后，運行mysql服務器
   $ mysqld_safe

使用主從備份模式

使用mysqldump和tar備份或多或少都會對業務產生影響，使用mysqldump備份需要對數據加鎖，加鎖就意味着其他客戶端操作受到限制。使用tar命令需要停止服務器直接導致數據庫服務器不可用，有沒有辦法能解決這兩種問題呢？答案是有的，就是使用主從備份模式。

在單機的基礎上增加一台Slave機器對Master機器的數據進行同步：

開始備份時對Slave進行備份，這樣即使Slave停機或對數據加鎖也不會影響業務的正常使用，如果公司有條件或業務非常重要可以選擇這種方案來備份數據。

歡迎關注微信公眾號《架構文摘》，高質量技術文章第一時間推送。