mariadb常用備份與還原工具介紹

一、備份的意義

從資料安全的角度來說，資料庫伺服器磁盤都會做RAID，Mariadb本身也有主從等容災機制，但它們都無法完全取代備份。容災和高可用能幫我們有效的應對實體的、硬體的、機械的故障，但是由我們手工操作導緻的邏輯錯誤、系統本身内生性BUG、惡意攻擊等方式破壞了你的資料卻無能為力。每一種邏輯錯誤發生的機率都極低，但是當多種可能性疊加的時候，小機率事件就放大成很大的安全隐患，這時候備份的必要性就凸顯了。

二、備份類型

1、根據備份時，資料庫伺服器是否線上：

冷備：cold backup

溫備：warm backup

熱備：hot backup

2、根據備份的資料集：

完全備份：full backup

部分備份: partial backup

3、根據備份時的接口（直接備份資料檔案還是通過mysql伺服器導出資料）：

實體備份：直接複制(歸檔)資料檔案的備份方式；physical backup

邏輯備份：把資料從庫中提出出來儲存為文本檔案；logical backup

4、根據備份時是備份整個資料還是僅備份變化的資料：

增量備份：incremental backup

差異備份：differential backup

5、備份政策：

選擇備份方式

選擇備份時間

考慮到恢複成本

恢複時長

備份成本

鎖時間

備份時長

備份負載

6、備份對象

資料

配置檔案

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

OS相關的配置檔案，如crontab配置計劃及相關的腳本，跟複制相關的配,二進制日志檔案

三、常見的備份工具

mariadb本身為我們提供了mysqldump、mysqlbinlog備份工具，percona也為我們提供了強大的Xtrabackup，加上開源的mydumper，還有基于主從同步的延遲備份、從庫冷備等方式，以及基于檔案系統快照的備份，其實将這些方式合理搭配已經能夠滿足我們的需要了。而備份本身是為了恢複，是以能夠讓我們在出現故障後迅速、準确恢複的備份方式，就是最适合我們的，當然，同時能夠省錢、省事，那就非常完美。下面就幾種備份工具進行一些比較，探讨下它們各自的适用場景，及簡單的使用做一下說明

1、mysqldump

⑴、mysqldump優缺點

mysqldump是最簡單的邏輯備份方式（工作方式單線程）。在備份myisam表的時候，如果要得到一緻的資料，就需要鎖表，簡單而粗暴。而在備份innodb表的時候，加上–master-data=2 –single-transaction 選項，在事務開始時刻，記錄下binlog-pos點，然後利用mvcc（多版本并發控制）來擷取一緻的資料，由于是一個大事務，在寫入和更新量很大的資料庫上，将産生非常多的undo，顯著影響性能，是以要慎用。

優點：簡單，可針對單表備份，在完全導出表結構的時候尤其有用。可以做到對不同的存儲引擎進行備份（InnoDB熱備、MyISAM溫備、Aria溫備）

缺點：簡單粗暴，單線程，備份慢而且恢複，不支援差異或增量備份，如果要進行差異或增量備份要結合binlog日志檔案

mydumper是mysqldump的加強版。相比mysqldump：

内置支援壓縮，可以節省2-4倍的存儲空間。

支援并行備份和恢複，是以速度比mysqldump快很多，但是由于是邏輯備份，仍不是很快，如果要進行差異或增量備份要結合binlog日志檔案

部分備份工具

SELECT clause INTO OUTFILE '/path/to/somefile'

LOAD DATA INFILE '/path/from/somefile'

不會備份關系定義，僅備份表中的資料；

邏輯備份工具，快于mysqldump。

⑵、mysqldump指令介紹及簡單使用

①指令介紹

mysqldump [options] [db_name [tbl_name ...]]

備份單個庫：

mysqldump [options] db_name

恢複時：如果目标庫不存在，需要事先手動建立

options說明

--all-databases:　備份所有庫

--databases db1 db2 ...: 備份指定的多個庫

注意：備份前要加鎖

--lock-all-tables：請求鎖定所有表之後再備份，對MyISAM、InnoDB、Aria做溫備

--single-transaction: 能夠對InnoDB存儲引擎實作熱備；

備份代碼：

--events: 備份事件排程器代碼

--routines: 備份存儲過程和存儲函數

--triggers：備份觸發器

備份時滾動日志：

--flush-logs: 備份前、請求到鎖之後滾動日志；

複制時的同步位置标記：

--master-data=[0|1|2]

0: 不記錄

1：記錄為CHANGE MASTER語句

2：記錄為注釋的CHANGE MASTER語句

注意

使用mysqldump備份

請求鎖：--lock-all-tables或使用--singe-transaction進行innodb熱備；

滾動日志：--flush-logs

標明要備份的庫：--databases

記錄二進制日志檔案及位置：--master-data=

恢複

建議：關閉二進制日志，關閉其它使用者連接配接；

建議備份政策：基于mysqldump

備份部分

mysqldump+二進制日志檔案；

完全備份+各二進制日志檔案中至此刻的事件，對MySQL配置檔案，以及與MySQL相關的OS配置檔案在每次修改後都應該直接進行備份；

②、執行個體

要求如下：

備份所有資料庫，要每周日淩晨自動執行；

③解決方案（此方法不唯一）

備份階段

第一步，先遠端登入到資料庫上，事先看一下現有的資料庫。

由上圖可見，除了hellodb資料庫，其它的資料庫都是系統自帶的，看一下hellodb中有那些表，及目前binlog日志的記錄的位置。

由于目前資料庫隻有一個使用者資料庫，是以我們在乎其表的存儲引擎來判斷使用什麼方式來備份。（是申請鎖或是單事務）

檢視表狀态發現hellodb中的表全部都是MyISAM的存儲引擎，那麼就可以使用申請全局鎖來備份了。

<code># mysqldump -uroot -p </code><code>--all-databases --lock-all-tables --flush-logs --master-data=2 >/tmp/all.sql</code>

這時資料庫全部備份完畢（此方法隻适用于資料量不是很大，挑選一個相對并發的寫請求不是特别多的時間或午夜備份。如果資料量特别大，此方法将不做參考範圍）。

假如在這之後我們又在原來的庫中建立新的表或插入資料，而在某一次我誤操作删除了此資料庫。将如何恢複？

由上圖可見，原來的hellodb資料庫中多了一張表，為不示範恢複效果我将hellodb删除，看怎麼樣恢複

現在hellodb己經删除，看如何恢複。

恢複部分

第一步，将伺服器離線，導出現在正在使用的binlog日志，還原之前的完全備份

檢視完全備份中binlog日志的起始位置。

<code>-- CHANGE MASTER TO MASTER_LOG_FILE=</code><code>'mysql-bin.000002'</code><code>, MASTER_LOG_POS=365;</code>

将此日志中到删除資料庫drop database hellodb之前的資料導出到存放起來，以供時間點還原之用

<code># mysqlbinlog --start-position=365 --stop-position=863 mysql-bin.000002 >/tmp/binlogbakup.sql</code>

登入資料庫

<code>[root@localhost bin]# mysql</code>

<code>Welcome </code><code>to</code> <code>the MariaDB monitor. Commands </code><code>end</code> <code>with</code> <code>; </code><code>or</code> <code>\g.</code>

<code>Your MariaDB </code><code>connection</code> <code>id </code><code>is</code> <code>4</code>

<code>Server version: 10.0.10-MariaDB-log Source distribution</code>

<code>Type </code><code>'help;'</code> <code>or</code> <code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>to</code> <code>clear the </code><code>current</code> <code>input statement.</code>

<code>MariaDB [(none)]></code>

注：此時要臨時關閉二進制日志，關閉其它使用者連接配接；

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

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.00 sec)</code>

導入之前的備份　

<code>MariaDB [(none)]> source /tmp/</code><code>all</code><code>.sql</code>

檢視資料庫中的表，裡面沒有我們備份之後建立的表，這時就用到了之前備份的binlog日志了

<code>MariaDB [test]> show databases;</code>

<code>| </code><code>Database</code> <code>|</code>

<code>| hellodb |</code>

<code>| information_schema |</code>

<code>| mysql |</code>

<code>| performance_schema |</code>

<code>MariaDB [test]> use hellodb</code>

<code>Database</code> <code>changed</code>

<code>MariaDB [hellodb]> show tables;</code>

<code>| Tables_in_hellodb |</code>

<code>| classes |</code>

<code>| courses |</code>

<code>| scores |</code>

<code>| students |</code>

<code>| teachers |</code>

導入binlog日志，完全恢複資料。

<code>MariaDB [hellodb]> source /tmp/binlogbakup.sql</code>

<code>| addressbook |</code>

<code>MariaDB [hellodb]> </code><code>select</code> <code>* </code><code>from</code> <code>addressbook;</code>

這回資料就完整了。

附上一個基于mysqldump簡單複制單庫的myisam的腳本

<code>#此腳本用來單庫完全份</code>

<code>#每個星期日做一次完全備份</code>

<code>#設定使用者名和密碼</code>

<code>v_password=</code><code>"mysql"</code>

<code>#mysql安裝可執行程式所在位置</code>

<code>MysqlDir=</code><code>/usr/local/mari/bin</code>

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

<code>database=</code><code>"hellodb"</code>

<code>#設定備份路徑，建立備份檔案夾</code>

<code>BackupDir=</code><code>/maria_bak</code>

<code>Full_Backup=$BackupDir</code><code>/Full_backup</code>

<code>mkdir</code> <code>-p $Full_Backup/$(</code><code>date</code> <code>+%Y%m%d)</code>

<code>#開始備份,記錄備份開始時間</code>

<code>echo</code> <code>'###'</code><code>$(</code><code>date</code> <code>+</code><code>"%Y-%m-%d %H:%M:%S"</code><code>)</code><code>'###'</code><code>"備份開始"</code><code>>>$Full_Backup</code><code>/full_buckup</code><code>.log</code>

<code>$MysqlDir</code><code>/mysqldump</code> <code>-u$v_user -p$v_password --lock-all-tables --flush-logs --master-data=2 --databases $database>$Full_Backup/$(</code><code>date</code> <code>+%Y%m%d)</code><code>/full_backup</code><code>.sql</code>

<code>#壓縮備份檔案</code>

<code>gzip</code> <code>$Full_Backup/$(</code><code>date</code> <code>+%Y%m%d)</code><code>/full_backup</code><code>.sql</code>

<code>echo</code> <code>'###'</code><code>$(</code><code>date</code> <code>+</code><code>"%Y-%m-%d %H:%M:%S"</code><code>)</code><code>'###'</code><code>"備份完成"</code><code>>>$Full_Backup</code><code>/full_buckup</code><code>.log</code>

2. 基于檔案系統的快照

基于檔案系統的快照，是實體備份的一種。在備份前需要進行一些複雜的設定，在備份開始時刻獲得快照并記錄下binlog pos點，然後采用類似copy-on-write的方式，把快照進行轉儲。轉儲快照本身會消耗一定的IO資源，而且在寫入壓力較大的執行個體上，儲存被更改資料塊的前印象也會消耗IO，最終表現為整體性能的下降。而且伺服器還要為copy-on-write快照預留較多的磁盤空間，這本身對資源也是一種浪費。是以這種備份方式在生産環境使用的不多。

注：

當剛為數資料檔案所在的卷（源卷）建立快照時，此時的快照卷是空的。

當源卷中的資料發生變化後，則将發生變化的那部分資料複制到快照卷上，此時通過快照通路則是未發生變化的資料來自于源卷，變化後的資料來自于快照卷。

快照不是備份，它是僅是提供通路檔案的一條通路，當源卷變化的量超出快照卷的大小時，快照卷将崩潰失效。

執行個體

前提：

首先，mariadb的資料檔案在邏輯卷上，如果使用的是支援事務的存儲引擎的話，那麼要将事務日志與資料檔案放在同一個卷上，這樣才能保證資料的完整性。

看一下資料檔案的屬性

步驟：

第一步，如果對源卷建立快照，那麼首先要對資料庫申請全局鎖，滾動二進制日志，記錄二進制日志位置标記

⑴、申請全局鎖

<code>MariaDB [(none)]> flush tables </code><code>with</code> <code>read</code> <code>lock;</code>

<code>MariaDB [(none)]> flush logs;</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.02 sec)</code>

<code>MariaDB [(none)]> show master status;</code>

将此二進制日志資訊記錄下來

<code># mysql -e 'show master status;'>/maribak/binlog_pos/binlog_$(date +%F_%T).txt</code>

<code># cat /maribak/binlog_pos/binlog_2014-04-14_04\:17\:13.txt</code>

<code>File Position Binlog_Do_DB Binlog_Ignore_DB</code>

<code>mysql-bin.000012 365</code>

此時可以建立快照了

<code># lvcreate -L 200M -s -n databak_snap -p r /dev/mydata/lv1</code>

<code> </code><code>Logical volume </code><code>"databak_snap"</code> <code>created</code>

第二步，建立完快照後解鎖

當快照卷建立完成後，可以對資料庫解鎖（建立鎖與解鎖必須是同一會話，也就是說，當建立完鎖之後，這個會話不可退出，之後還要在這個會話中對資料庫解鎖，一但退出，此會話的全局鎖将失效）

<code>MariaDB [(none)]> unlock tables;</code>

<code>Query OK, 0 rowsaffected (0.00 sec)</code>

這時資料庫又可以正常進行寫入操作了

第三步，備份快照

挂載備份之後的快照

<code>[root@bogon mnt]</code><code># mount /dev/mydata/databak_snap -o ro /mnt</code>

<code>[root@bogon mnt]</code><code># cd /mnt</code>

<code>[root@bogon mnt]</code><code># ll</code>

<code>total 176172</code>

<code>-rw-rw---- 1 mysql mysql 16384 Apr 14 04:11 aria_log.00000001</code>

<code>-rw-rw---- 1 mysql mysql 52 Apr 14 04:11 aria_log_control</code>

<code>-rw-r----- 1 mysql root 1099 Apr 14 04:11 bogon.err</code>

<code>-rw-rw---- 1 mysql mysql 5 Apr 14 04:11 bogon.pid</code>

<code>drwx------ 2 mysql mysql 4096 Apr 14 04:12 employees</code>

<code>-rw-rw---- 1 mysql mysql 79691776 Apr 14 04:14 ibdata1</code>

<code>-rw-rw---- 1 mysql mysql 50331648 Apr 14 04:14 ib_logfile0</code>

<code>-rw-rw---- 1 mysql mysql 50331648 Apr 14 04:14 ib_logfile1</code>

<code>-rw-rw---- 1 mysql mysql 0 Apr 14 04:11 multi-master.info</code>

<code>drwx------ 2 mysql root 4096 Apr 14 04:11 mysql</code>

<code>drwx------ 2 mysql mysql 4096 Apr 14 04:11 performance_schema</code>

<code>drwx------ 2 mysql root 4096 Apr 14 04:11 </code><code>test</code>

複制快照中的内容

<code>[root@bogon mnt]</code><code># cp -a * /maribak/databak/</code>

<code>[root@bogon mnt]</code><code># cd /maribak/databak/</code>

<code>[root@bogon databak]</code><code># ll</code>

删除快照卷

<code>#lvremove /dev/mydata/databak_snap</code>

裡源卷的資料庫employees中添入一些資料。

<code>MariaDB [employees]> </code><code>insert</code> <code>employees </code><code>values</code><code>(99,</code><code>'1998-09-21'</code><code>,</code><code>'JEYY'</code><code>,</code><code>'jose'</code><code>,</code><code>'M'</code><code>,</code><code>'2012-12-24'</code><code>);</code>

<code>MariaDB [employees]> </code><code>insert</code> <code>employees </code><code>values</code><code>(999999,</code><code>'1995-09-21'</code><code>,</code><code>'king'</code><code>,</code><code>'tom'</code><code>,</code><code>'M'</code><code>,</code><code>'2012-12-24'</code><code>);</code>

<code>MariaDB [employees]></code><code>insert</code> <code>employees </code><code>values</code><code>(5211345,</code><code>'1995-09-21'</code><code>,</code><code>'king'</code><code>,</code><code>'tom'</code><code>,</code><code>'F'</code><code>,</code><code>'2012-12-24'</code><code>);</code>

當備份過完成後，過了幾個小時，資料庫誤删除了一些資料，這時就可用到此次完全備份與即時點還原日志。

<code>MariaDB [employees]> </code><code>delete</code> <code>from</code> <code>employees </code><code>where</code> <code>gender=</code><code>'M'</code>

<code>Query OK, 179975 </code><code>rows</code> <code>affected (3 </code><code>min</code> <code>52.82 sec)</code>

還原部分

第一步，停止資料庫服務，将備份後的資料中的某一張表或整個庫替換此資料庫中誤删除的表或整個庫。

<code>[root@bogon employees]</code><code># service mari stop</code>

<code>Shutting down MySQL............... [ OK ]</code>

替換　

<code>[root@bogon employees]</code><code># cp -a /maribak/databak/employees/employees.* .</code>

<code>cp</code><code>: overwrite `.</code><code>/employees</code><code>.frm'? y</code>

<code>cp</code><code>: overwrite `.</code><code>/employees</code><code>.ibd'? y</code>

<code>[root@bogon employees]</code><code># ll</code>

<code>total 237840</code>

<code>-rw-rw---- 1 mysql mysql 61 Apr 14 04:12 db.opt</code>

<code>-rw-rw---- 1 mysql mysql 1571 Apr 14 04:12 departments.frm</code>

<code>-rw-rw---- 1 mysql mysql 114688 Apr 14 04:12 departments.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1999 Apr 14 04:12 dept_emp.frm</code>

<code>-rw-rw---- 1 mysql mysql 31457280 Apr 14 05:03 dept_emp.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1999 Apr 14 04:12 dept_manager.frm</code>

<code>-rw-rw---- 1 mysql mysql 131072 Apr 14 04:55 dept_manager.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1164 Apr 14 04:12 employees.frm</code>

<code>-rw-rw---- 1 mysql mysql 23068672 Apr 14 04:12 employees.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1501 Apr 14 04:12 salaries.frm</code>

<code>-rw-rw---- 1 mysql mysql 146800640 Apr 14 05:03 salaries.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1647 Apr 14 04:12 titles.frm</code>

<code>-rw-rw---- 1 mysql mysql 41943040 Apr 14 05:03 titles.ibd</code>

注：此時檔案的屬性要與原檔案的一緻

第二步，啟動服務

<code>[root@bogon employees]</code><code># service mari start</code>

<code>Starting MySQL.. [ OK ]</code>

檢視完全備份資訊

<code>MariaDB [employees]> </code><code>select</code> <code>* </code><code>from</code> <code>employees </code><code>where</code> <code>hire_date ></code><code>'2000-01-01'</code><code>;</code>

<code>| 47291 | 1960-09-09 | Ulf | Flexer | M | 2000-01-12 |</code>

<code>| 60134 | 1964-04-21 | Seshu | Rathonyi | F | 2000-01-02 |</code>

<code>| 72329 | 1953-02-09 | Randi | Luit | F | 2000-01-02 |</code>

<code>| 205048 | 1960-09-12 | Ennio | Alblas | F | 2000-01-06 |</code>

<code>| 222965 | 1959-08-07 | Volkmar | Perko | F | 2000-01-13 |</code>

<code>| 226633 | 1958-06-10 | Xuejun | Benzmuller | F | 2000-01-04 |</code>

<code>| 227544 | 1954-11-17 | Shahab | Demeyer | M | 2000-01-08 |</code>

<code>| 422990 | 1953-04-09 | Jaana | Verspoor | F | 2000-01-11 |</code>

<code>| 424445 | 1953-04-27 | Jeong | Boreale | M | 2000-01-03 |</code>

<code>| 428377 | 1957-05-09 | Yucai | Gerlach | M | 2000-01-23 |</code>

<code>| 463807 | 1964-06-12 | Bikash | Covnot | M | 2000-01-28 |</code>

<code>| 499553 | 1954-05-06 | Hideyuki | Delgrande | F | 2000-01-22 |</code>

并沒有備份後插入的資料，是以在用到binlog日志了,檢視目前的binlog的位置

<code>MariaDB [employees]> show master status;</code>

跨檔案了~~~，沒關系統，先還原同一個日志檔案中的資料，再恢複這個日志中的資料，在mysql-bin.000012中找到delect之前的位置，并使用mysqlbinlog将其導出，臨時性關閉binlog日志功能，恢複此間資料。

<code># mysqlbinlog /mysql/binlog/mysql-bin.000012</code>

<code>#140414 4:53:25 server id 1 end_log_pos 1146 GTID 0-1-7138</code>

<code>/*!100001 SET @@session.gtid_seq_no=7138*</code><code>//</code><code>*!*/;</code>

<code>BEGIN</code>

<code>#140414 4:53:25 server id 1 end_log_pos 1254 Query thread_id=6 exec_time=233 error_code=0</code>

<code>SET TIMESTAMP=1397422405/*!*/;</code>

<code>delete from employees where gender=</code><code>'M'</code>

從日志中看辨別到1108即可恢複到删除之前，将此區間導出

<code>[root@bogon binlog_pos]</code><code># mysqlbinlog --start-position=365 --stop-position=1108 /mysql/binlog/mysql-bin.000012 >zengliang.sql</code>

<code>[root@bogon binlog_pos]</code><code># ls</code>

<code>binlog_2014-04-14_04:17:13.txt zengliang.sql</code>

在資料中臨時關閉binlog日志功能。

<code>MariaDB [employees]> </code><code>set</code> <code>session sql_log_bin=0;</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.01 sec)</code>

<code>MariaDB [employees]> source /maribak/binlog_pos/zengliang.sql;</code>

檢視一下原來的資料