MySQL/MariaDB資料庫備份與恢複

前言

資料庫一般存放着企業最為重要的資料，它關系到企業業務能否正常運轉，資料庫伺服器總會遇到一些不可抗拒因素，導緻資料丢失或損壞，而資料庫備份可以幫助我們避免由于各種原因造成的資料丢失或着資料庫的其他問題。本文将講解MySQL/MariaDB資料庫的幾種備份方法。

基礎知識

備份類型

完全備份：備份整個資料庫

部分備份：僅備份其中的一張表或多張表

增量備份：僅備份從上次完全備份或增量備份之後變化的資料部分

差異備份：備份上次備份後變化的資料部分，和增量備份差別在于差異備份隻可以相對完全備份做備份

熱備份、溫備份和冷備份：

熱備份：線上備份，讀寫操作不受影響

溫備份：線上備份，讀操作可繼續進行，但寫操作不允許

冷備份：離線備份，資料庫伺服器離線，備份期間不能為業務提供讀寫服務

實體備份和邏輯備份：

實體備份：直接複制資料檔案進行的備份

    優點：無需額外工具，直接copy即可，恢複直接複制備份檔案即可

    缺點：與存儲引擎有關，跨平台能力較弱

邏輯備份：從資料庫中“導出”資料另存而進行的備份

    優點： 能使用編輯器處理，恢複簡單，能基于網絡恢複，有助于避免資料損壞

    缺點： 備份檔案較大，備份較慢，無法保證浮點數的精度，使用邏輯備份資料恢複後，還需手動重建索引，十分消耗CPU資源

備份對象

資料檔案

代碼：存儲過程，存儲函數，觸發器等

OS相關的配置檔案，如crontab配置計劃及相關腳本

跟複制相關的配置資訊

二進制日志檔案

備份工具

mysqldump: 邏輯備份工具，适用于所有存儲引擎，溫備、完全備份、部分備份；對InnoDB存儲引擎支援熱備

cp, tar等檔案系統工具：實體備份工具，适用于所有存儲引擎，冷備、完全備份、部分備份

lvm2的快照：幾乎熱備，借助于檔案系統工具實作實體備份

mysqlhotcopy: 幾乎冷備，僅适用于MyISAM存儲引擎

資料庫備份

備份方案

①mysqldump+binlog: 

完全備份，通過備份二進制日志實作增量備份

②lvm2快照+binlog：

幾乎熱備，實體備份

③xtrabackup: 

對InnoDB：熱備，支援完全備份和增量備份

對MyISAM：溫備，隻支援完全備份

備份須知

備份某一個資料庫和備份所有庫是有差別的，要備份某一個庫要確定所有的InnoDB存儲引擎的表都是存放在單個表空間中，否則必須執行全庫備份

1

2

<code>MariaDB [none]&gt; show global variables like </code><code>'innodb_file_p%'</code><code>; </code><code>#檢視是否開啟單獨表空間</code>

<code>MariaDB [none]&gt; </code><code>set</code> <code>global innodb_file_per_table=1; </code><code>#開啟單獨表空間，也可在配置檔案設定</code>

mysqldump+binlog

指令的文法格式

3

<code>mysqldump [OPTIONS] database [tables]：備份單個庫，或庫指定的一個或多個表</code>

<code>mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：備份一個或多個庫</code>

<code>mysqldump [OPTIONS] --all-databases [OPTIONS]：備份所有庫</code>

其他選項

4

5

6

7

8

9

10

11

12

<code>-x, --lock-all-tables：鎖定所有表</code>

<code>-l, --lock-tables：鎖定備份的表</code>

<code>--single-transaction：啟動一個大的單一事務實作備份</code>

<code>-C, --compress：壓縮傳輸</code>

<code>-E, --events：備份指定庫的事件排程器</code>

<code>-R, --routines：備份存儲過程和存儲函數</code>

<code>--triggers：備份觸發器</code>

<code>--master-data={0|1|2}</code>

<code>    </code><code>0：不記錄</code>

<code>    </code><code>1：記錄CHANGE MASTER TO語句；此語句未被注釋</code>

<code>    </code><code>2：記錄為注釋語句</code>

<code>-F，--flush-logs：鎖定表之後執行flush logs指令</code>

注意：二進制日志檔案與資料檔案不應該放置于同一磁盤，這裡是實驗便不可以修改

備份過程

準備備份目錄

<a href="http://s3.51cto.com/wyfs02/M01/6D/2A/wKiom1Vdju7Ss1idAAB_kLgyrAo223.jpg" target="_blank"></a>

準備備份資料庫及表

進行完整備份

<a href="http://s3.51cto.com/wyfs02/M01/6D/2A/wKiom1VdkXWgGX_WAACTf9D9SIs505.jpg" target="_blank"></a>

向表中插入資料

<a href="http://s3.51cto.com/wyfs02/M02/6D/2A/wKiom1VdkqvSMntSAAGEtI8YJrY763.jpg" target="_blank"></a>

進行增量備份，備份二進制日志

<a href="http://s3.51cto.com/wyfs02/M02/6D/2A/wKiom1VdlSni34YJAACoVEP8f7Y819.jpg" target="_blank"></a>

繼續插入資料，在沒備份的情況下删除資料庫，模拟誤操作

<a href="http://s3.51cto.com/wyfs02/M01/6D/26/wKioL1Vdl5Oyu7KyAAFbiUpxwuA845.jpg" target="_blank"></a>

資料恢複

<code>#建議關閉二進制日志，關閉其它使用者連接配接</code>

<code>MariaDB [(none)]&gt; </code><code>set</code> <code>session sql_log_bin=0;</code>

由于最後我們沒有備份就删除了資料庫，是以我們首先需要保護最後的二進制日志，檢視删除操作之前的position值

<code>[root@MariaDB ~]</code><code># mysqlbinlog /mydata/data/mysql-bin.000015</code>

<a href="http://s3.51cto.com/wyfs02/M00/6D/2B/wKiom1VdmJiCjTSRAAE-IM_0uts532.jpg" target="_blank"></a>

将最後操作的二進制日志備份

導入之前的所有備份

<a href="http://s3.51cto.com/wyfs02/M01/6D/26/wKioL1VdnMnjlyQ7AACpzl8js74689.jpg" target="_blank"></a>

檢視資料庫及資料

<a href="http://s3.51cto.com/wyfs02/M02/6D/26/wKioL1VdnROgS2hJAACLbJn68Fs831.jpg" target="_blank"></a>

OK，至此資料成功恢複

注意：此方法不适用于大型資料庫，備份速度太慢

lvm2快照+binlog

13

14

15

16

17

18

19

<code>#請求鎖定所有表</code>

<code>MariaDB [</code><code>test</code><code>]&gt; flush tables with </code><code>read</code> <code>lock;</code>

<code>#滾動日志</code>

<code>MariaDB [</code><code>test</code><code>]&gt; flush logs;</code>

<code>#記錄二進制日志位置</code>

<code>MariaDB [</code><code>test</code><code>]&gt; show master status;</code>

<code>#建立快照卷</code>

<code>[root@MariaDB ~]</code><code># lvcreate -s -L 100M -n mydata-snap /dev/myvg/mydata -p r </code>

<code>#釋放全局鎖</code>

<code>MariaDB [</code><code>test</code><code>]&gt; unlock tables;</code>

<code>#建立快照挂載點</code>

<code>[root@MariaDB ~]</code><code># mkdir /snap</code>

<code>#挂載快照卷</code>

<code>[root@MariaDB ~]</code><code># mount /dev/myvg/mydata-snap /snap</code>

<code>#備份資料庫</code>

<code>[root@MariaDB ~]</code><code># cp -a /snap /backup/</code>

<code>#增量備份，檢視完整備份之前的二進制日志位置和最後出錯操作前一位置</code>

<code>[root@MariaDB ~]</code><code># mysqlbinlog --start-position=245 --stop-position=534 /mydata/data/mys</code>

<code>ql-bin.000016 &gt; </code><code>/backup/binlog/binlog-</code><code>`</code><code>date</code> <code>+%F_%T`.sql</code>

<code>[root@MariaDB ~]</code><code># service mysqld stop</code>

<code>[root@MariaDB ~]</code><code># rm -rf /mydata/data/*</code>

<code>[root@MariaDB ~]</code><code># cp -a /backup/snap/* /mydata/data</code>

<code>[root@MariaDB ~]</code><code># service mysqld start</code>

<code>[root@MariaDB ~]</code><code># mysql &lt; /backup/binlog/binlog-2015-05-21_20\:23\:41.sql</code>

基于實體備份，資料恢複完成

xtrabackup（推薦）

Xtrabackup是由percona提供的mysql資料庫備份工具，據官方介紹，這也是世界上惟一一款開源的能夠對innodb和xtradb資料庫進行熱備的工具。

特點：

(1)備份過程快速、可靠

(2)備份過程不會打斷正在執行的事務

(3)能夠基于壓縮等功能節約磁盤空間和流量

(4)自動實作備份檢驗

(5)還原速度快

安裝

<code>[root@MariaDB ~]</code><code># yum install percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm -y</code>

建立最小權限備份使用者

完全備份

<code>[root@MariaDB ~]</code><code># innobackupex --user=bakupuser --password=bakuppass /backup/</code>

<code>innobackupex: Backup created </code><code>in</code> <code>directory </code><code>'/backup/2015-05-21_21-55-08'</code>

<code>innobackupex: MySQL binlog position: filename </code><code>'mysql-bin.000017'</code><code>, position 245</code>

<code>150521 21:55:16  innobackupex: Connection to database server closed</code>

<code>150521 21:55:16  innobackupex: completed OK!</code>

<a href="http://s3.51cto.com/wyfs02/M00/6D/34/wKiom1Vd5XHTrk7uAACqhpePaRQ631.jpg" target="_blank"></a>

如果出現如下錯誤，請在my.cnf檔案[mysqld] 中添加innodb_log_file_size = 5M 并重新開機服務

<code>InnoDB: Error: log </code><code>file</code> <code>.</code><code>/ib_logfile0</code> <code>is of different size 5242880 bytes</code>

<code>InnoDB: than specified </code><code>in</code> <code>the .cnf </code><code>file</code> <code>50331648 bytes!</code>

<code>innobackupex: Error: The xtrabackup child process has died at </code><code>/usr/bin/innobackupex</code> <code>line 2672.</code>

增量備份

每個InnoDB的頁面都會包含一個LSN資訊，每當相關的資料發生改變，相關的頁面的LSN就會自動增長。這正是InnoDB表可以進行增量備份的基礎，即innobackupex通過備份上次完全備份之後發生改變的頁面來實作

innobackupex指令會在備份目錄中建立一個新的以時間命名的目錄以存放所有的增量備份資料。另外，在執行過增量備份之後再一次進行增量備份時，其--incremental-basedir應該指向上一次的增量備份所在的目錄

注：增量備份僅能應用于InnoDB或XtraDB表，對于MyISAM表而言，執行增量備份時其實進行的是完全備份

添加資料

<a href="http://s3.51cto.com/wyfs02/M02/6D/34/wKiom1Vd6siC_h2JAACh2TBsIaI746.jpg" target="_blank"></a>

做增量備份

<code>[root@MariaDB ~]</code><code># innobackupex --incremental /backup/ --incremental-basedir=/backup/201</code>

<code>5-05-21_21-55-08/</code>

<code>innobackupex: Backup created </code><code>in</code> <code>directory </code><code>'/backup/2015-05-21_22-26-42'</code>

<code>innobackupex: MySQL binlog position: filename </code><code>'mysql-bin.000017'</code><code>, position 788</code>

<code>150521 22:26:57  innobackupex: Connection to database server closed</code>

<code>150521 22:26:57  innobackupex: completed OK!</code>

再次添加資料

<a href="http://s3.51cto.com/wyfs02/M01/6D/34/wKiom1Vd7BbzKaWcAAC9iyCq3Qw920.jpg" target="_blank"></a>

再次做增量備份

<code>5-05-21_22-26-42/ </code><code>#在第一次增量備份的基礎上做增量備份</code>

<code>innobackupex: Backup created </code><code>in</code> <code>directory </code><code>'/backup/2015-05-21_22-32-01'</code>

<code>innobackupex: MySQL binlog position: filename </code><code>'mysql-bin.000017'</code><code>, position 1056</code>

<code>150521 22:32:10  innobackupex: Connection to database server closed</code>

<code>150521 22:32:10  innobackupex: completed OK!</code>

準備階段

一般情況下，在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。“準備”的主要作用正是通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态

“準備”(prepare)增量備份與整理完全備份有着一些不同，尤其要注意的是：

(1)需要在每個備份(包括完全和各個增量備份)上，将已經送出的事務進行“重放”。“重放”之後，所有的備份資料将合并到完全備份上。

(2)基于所有的備份将未送出的事務進行“復原”。

于是，操作就變成了：

<code># innobackupex --apply-log --redo-only BASE-DIR</code>

接着執行：

<code># innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1</code>

而後是第二個增量：

<code># innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2</code>

其中BASE-DIR指的是完全備份所在的目錄，而INCREMENTAL-DIR-1指的是第一次增量備份的目錄，INCREMENTAL-DIR-2指的是第二次增量備份的目錄，其它依次類推，即如果有多次增量備份，每一次都要執行如上操作

<a href="http://s3.51cto.com/wyfs02/M01/6D/35/wKiom1Vd81nAIeoUAABVi6V8loE804.jpg" target="_blank"></a>

完整備份準備

<code>[root@MariaDB ~]</code><code># innobackupex --apply-log /backup/2015-05-21_21-55-08/</code>

<code>InnoDB: FTS optimize thread exiting.</code>

<code>InnoDB: Starting </code><code>shutdown</code><code>...</code>

<code>InnoDB: Shutdown completed; log sequence number 2766618</code>

<code>150521 23:02:43  innobackupex: completed OK!</code>

增量備份準備

<code>[root@MariaDB ~]</code><code># innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/</code>

<code>[root@MariaDB ~]</code><code># innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/ </code>

<code>--incremental-</code><code>dir</code><code>=</code><code>/backup/2015-05-21_22-26-42/</code>

<code>--incremental-</code><code>dir</code><code>=</code><code>/backup/2015-05-21_22-32-01/</code>

恢複階段

還原備份，即完全備份

<code>[root@MariaDB ~]</code><code># innobackupex --copy-back /backup/2015-05-21_21-55-08/</code>

<code>[root@MariaDB ~]</code><code># chown -R mysql.mysql /mydata/data/</code>

測試資料是否恢複

<a href="http://s3.51cto.com/wyfs02/M00/6D/35/wKiom1Vd-BiCuz8JAACAz3UA59k539.jpg" target="_blank"></a>

OK，資料恢複成功

The end

好了，MySQL/MariaDB資料庫備份與恢複就總結到這裡了，以上總結的三種方法各有各的特色，讀者可根據實際需求進行選擇，再啰嗦一句，實際生産環境中資料和二進制日志檔案一定要分開存放。以上僅為個人學習整理，如有錯漏，大神勿噴~~~

本文轉自 北城書生  51CTO部落格，原文連結:http://blog.51cto.com/scholar/1653792