學會用各種姿勢備份MySQL資料庫

前言

為什麼需要備份資料？

資料的備份類型

mysql備份資料的方式

備份需要考慮的問題

設計合适的備份政策

實戰演練

使用cp進行備份

使用mysqldump+複制binary log備份

使用lvm2快照備份資料

使用xtrabackup備份

總結

我們試着想一想, 在生産環境中什麼最重要？如果我們伺服器的硬體壞了可以維修或者換新, 軟體問題可以修複或重新安裝, 但是如果資料沒了呢？這可能是最恐怖的事情了吧, 我感覺在生産環境中應該沒有什麼比資料跟更為重要. 那麼我們該如何保證資料不丢失、或者丢失後可以快速恢複呢？隻要看完這篇, 大家應該就能對<code>mysql</code>中實作資料備份和恢複能有一定的了解。

其實在<code>前言</code>中也大概說明了為什麼要備份資料, 但是我們還是應該具體了解一下為什麼要備份資料 在生産環境中我們資料庫可能會遭遇各種各樣的不測進而導緻資料丢失, 大概分為以下幾種. 硬體故障 軟體故障 自然災害 黑客攻擊 誤操作 (占比最大) 是以, 為了在資料丢失之後能夠恢複資料, 我們就需要定期的備份資料, 備份資料的政策要根據不同的應用場景進行定制, 大緻有幾個參考數值, 我們可以根據這些數值進而定制符合特定環境中的資料備份政策 能夠容忍丢失多少資料 恢複資料需要多長時間 需要恢複哪一些資料

資料的備份類型根據其自身的特性主要分為以下幾組 完全備份 部分備份 完全備份指的是備份整個資料集( 即整個資料庫 )、部分備份指的是備份部分資料集(例如: 隻備份一個表) 而部分備份又分為以下兩種 增量備份 差異備份 增量備份指的是備份自上一次備份以來(增量或完全)以來變化的資料; 特點: 節約空間、還原麻煩  差異備份指的是備份自上一次完全備份以來變化的資料 特點: 浪費空間、還原比增量備份簡單 示意圖

在<code>mysql</code>中我們備份資料一般有幾種方式 熱備份 溫備份 冷備份 熱備份指的是當資料庫進行備份時, 資料庫的讀寫操作均不是受影響  溫備份指的是當資料庫進行備份時, 資料庫的讀操作可以執行, 但是不能執行寫操作  冷備份指的是當資料庫進行備份時, 資料庫不能進行讀寫操作, 即資料庫要下線 <code>mysql</code>中進行不同方式的備份還要考慮存儲引擎是否支援 myisam   熱備 ×  溫備 √  冷備 √ innodb  熱備 √ 我們在考慮完資料在備份時, 資料庫的運作狀态之後還需要考慮對于<code>mysql</code>資料庫中資料的備份方式 實體備份一般就是通過<code>tar</code>,<code>cp</code>等指令直接打包複制資料庫的資料檔案達到備份的效果  邏輯備份一般就是通過特定工具從資料庫中導出資料并另存備份(邏輯備份會丢失資料精度) 實體備份 邏輯備份

定制備份政策前, 我們還需要考慮一些問題 我們要備份什麼? 一般情況下, 我們需要備份的資料分為以下幾種 資料 二進制日志, innodb事務日志 代碼(存儲過程、存儲函數、觸發器、事件排程器) 伺服器配置檔案 備份工具 這裡我們列舉出常用的幾種備份工具  <code>mysqldump</code> : 邏輯備份工具, 适用于所有的存儲引擎, 支援溫備、完全備份、部分備份、對于innodb存儲引擎支援熱備  <code>cp, tar 等歸檔複制工具</code>: 實體備份工具, 适用于所有的存儲引擎, 冷備、完全備份、部分備份  <code>lvm2 snapshot</code>: 幾乎熱備, 借助檔案系統管理工具進行備份  <code>mysqlhotcopy</code>: 名不副實的的一個工具, 幾乎冷備, 僅支援myisam存儲引擎  <code>xtrabackup</code>: 一款非常強大的innodb/xtradb熱備工具, 支援完全備份、增量備份, 由<code>percona</code>提供

針對不同的場景下, 我們應該制定不同的備份政策對資料庫進行備份, 一般情況下, 備份政策一般為以下三種 直接cp,tar複制資料庫檔案 mysqldump+複制bin logs lvm2快照+複制bin logs xtrabackup 以上的幾種解決方案分别針對于不同的場景 如果資料量較小, 可以使用第一種方式, 直接複制資料庫檔案 如果資料量還行, 可以使用第二種方式, 先使用mysqldump對資料庫進行完全備份, 然後定期備份binary log達到增量備份的效果 如果資料量一般, 而又不過分影響業務運作, 可以使用第三種方式, 使用<code>lvm2</code>的快照對資料檔案進行備份, 而後定期備份binary log達到增量備份的效果 如果資料量很大, 而又不過分影響業務運作, 可以使用第四種方式, 使用<code>xtrabackup</code>進行完全備份後, 定期使用<code>xtrabackup</code>進行增量備份或差異備份

我們這裡使用的是使用yum安裝的<code>mysql-5.1</code>的版本, 使用的資料集為從網絡上找到的一個員工資料庫

檢視資料庫的資訊

向資料庫施加讀鎖

備份資料檔案

模拟資料丢失并恢複

我們通過mysqldump進行一次完全備份, 再修改表中的資料, 然後再通過binary log進行恢複 二進制日志需要在mysql配置檔案中添加 log_bin=on 開啟

<code>mysqldump</code>指令介紹

<code>mysqldump</code>是一個用戶端的邏輯備份工具, 可以生成一個重制建立原始資料庫和表的sql語句, 可以支援所有的存儲引擎, 對于innodb支援熱備 官方文檔介紹

使用<code>mysqldump</code>備份資料庫

做實驗之前我們先回顧一下<code>lvm2-snapshot</code>的知識 <code>lvm</code>快照簡單來說就是将所快照源分區一個時間點所有檔案的中繼資料進行儲存，如果源檔案沒有改變，那麼通路快照卷的相應檔案則直接指向源分區的源檔案，如果源檔案發生改變，則快照卷中與之對應的檔案不會發生改變。快照卷主要用于輔助備份檔案。 這裡隻簡單介紹，點選檢視詳細介紹

部署lvm環境

建立快照卷并備份

恢複資料

為了更好地示範, 我們這次使用<code>mariadb-5.5</code>的版本, 使用<code>xtrabackup</code>使用innodb能夠發揮其最大功效, 并且innodb的每一張表必須使用單獨的表空間, 我們需要在配置檔案中添加 <code>innodb_file_per_table = on</code> 來開啟

下載下傳安裝<code>xtrabackup</code>

<code>xtrabackup</code>介紹

<code>xtrabackup</code>是由<code>percona</code>提供的<code>mysql</code>資料庫備份工具，據官方介紹，這也是世界上惟一一款開源的能夠對innodb和xtradb資料庫進行熱備的工具。特點： 備份過程快速、可靠； 備份過程不會打斷正在執行的事務； 能夠基于壓縮等功能節約磁盤空間和流量； 自動實作備份檢驗； 還原速度快； 摘自馬哥的文檔

<code>xtrabackup</code>實作完全備份

我們這裡使用<code>xtrabackup</code>的前端配置工具<code>innobackupex</code>來實作對資料庫的完全備份 使用<code>innobackupex</code>備份時, 會調用<code>xtrabackup</code>備份所有的innodb表, 複制所有關于表結構定義的相關檔案(.frm)、以及myisam、merge、csv和archive表的相關檔案, 同時還會備份觸發器和資料庫配置檔案資訊相關的檔案, 這些檔案會被儲存至一個以時間命名的目錄.

備份過程

一般情況, 備份完成後, 資料不能用于恢複操作, 因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以, 此時的資料檔案仍不一緻, 是以我們需要”準備”一個完全備份

basedir指的是完全備份所在的目錄，此指令執行結束後，<code>innobackupex</code>指令會在<code>/extrabackup</code>目錄中建立一個新的以時間命名的目錄以存放所有的增量備份資料。另外，在執行過增量備份之後再一次進行增量備份時，其<code>--incremental-basedir</code>應該指向上一次的增量備份所在的目錄。 需要注意的是，增量備份僅能應用于innodb或xtradb表，對于myisam表而言，執行增量備份時其實進行的是完全備份。

整理增量備份

備份方法

備份速度

恢複速度

便捷性

功能

一般用于

cp

快

一般、靈活性低

很弱

少量資料備份

mysqldump

慢

一般、可無視存儲引擎的差異

一般

中小型資料量的備份

lvm2快照

一般、支援幾乎熱備、速度快

較快

實作innodb熱備、對存儲引擎有要求

強大

較大規模的備份

其實我們還可以通過<code>master-slave replication</code> 進行資料備份、由于本文篇幅過長、遂放在下篇和大家分享 作者水準很低, 如果有錯誤及時指出, 如果你覺得本文寫的好請點一波贊~(≧▽≦)/~  作者: anyisailn qq: 1449472454  感謝: mageedu