MySQL/MariaDB數據庫備份與恢復

前言

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

基礎知識

備份類型

完全備份：備份整個數據庫

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

增量備份：僅備份從上次完全備份或增量備份之后變化的數據部分

差異備份：備份上次備份后變化的數據部分，和增量備份區別在于差異備份只可以相對完全備份做備份

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

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

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

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

物理備份和邏輯備份：

物理備份：直接復制數據文件進行的備份

    優點：無需額外工具，直接copy即可，恢復直接復制備份文件即可

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

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

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

    缺點： 備份文件較大，備份較慢，無法保證浮點數的精度，使用邏輯備份數據恢復后，還需手動重建索引，十分消耗CPU資源

備份對象

數據文件

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

OS相關的配置文件，如crontab配置計劃及相關腳本

跟復制相關的配置信息

二進制日志文件

備份工具

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

cp, tar等文件系統工具：物理備份工具，適用于所有存儲引擎，冷備、完全備份、部分備份

lvm2的快照：幾乎熱備，借助于文件系統工具實現物理備份

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

數據庫備份

備份方案

①mysqldump+binlog: 

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

②lvm2快照+binlog：

幾乎熱備，物理備份

③xtrabackup: 

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

對MyISAM：溫備，只支持完全備份

備份須知

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

MariaDB [none]> show global variables like 'innodb_file_p%'; #查看是否開啟單獨表空間
MariaDB [none]> set global innodb_file_per_table=1; #開啟單獨表空間，也可在配置文件設置

mysqldump+binlog

命令的語法格式

mysqldump [OPTIONS] database [tables]：備份單個庫，或庫指定的一個或多個表
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：備份一個或多個庫
mysqldump [OPTIONS] --all-databases [OPTIONS]：備份所有庫

其他選項

-x, --lock-all-tables：鎖定所有表
-l, --lock-tables：鎖定備份的表
--single-transaction：啟動一個大的單一事務實現備份
-C, --compress：壓縮傳輸
-E, --events：備份指定庫的事件調度器
-R, --routines：備份存儲過程和存儲函數
--triggers：備份觸發器
--master-data={0|1|2}
    0：不記錄
    1：記錄CHANGE MASTER TO語句；此語句未被注釋
    2：記錄為注釋語句
-F，--flush-logs：鎖定表之后執行flush logs命令

注意：二進制日志文件與數據文件不應該放置于同一磁盤，這里是實驗便不可以修改

備份過程

準備備份目錄

準備備份數據庫及表

進行完整備份

向表中插入數據

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

繼續插入數據，在沒備份的情況下刪除數據庫，模擬誤操作

數據恢復

#建議關閉二進制日志，關閉其它用戶連接
MariaDB [(none)]> set session sql_log_bin=0;

由于最后我們沒有備份就刪除了數據庫，所以我們首先需要保護最后的二進制日志，查看刪除操作之前的position值

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

將最后操作的二進制日志備份

導入之前的所有備份

查看數據庫及數據

OK，至此數據成功恢復

注意：此方法不適用于大型數據庫，備份速度太慢

lvm2快照+binlog

備份過程

#請求鎖定所有表
MariaDB [test]> flush tables with read lock;
#滾動日志
MariaDB [test]> flush logs;
#記錄二進制日志位置
MariaDB [test]> show master status;
#創建快照卷
[root@MariaDB ~]# lvcreate -s -L 100M -n mydata-snap /dev/myvg/mydata -p r 
#釋放全局鎖
MariaDB [test]> unlock tables;
#創建快照掛載點
[root@MariaDB ~]# mkdir /snap
#掛載快照卷
[root@MariaDB ~]# mount /dev/myvg/mydata-snap /snap
#備份數據庫
[root@MariaDB ~]# cp -a /snap /backup/
#增量備份，查看完整備份之前的二進制日志位置和最后出錯操作前一位置
[root@MariaDB ~]# mysqlbinlog --start-position=245 --stop-position=534 /mydata/data/mys
ql-bin.000016 > /backup/binlog/binlog-`date +%F_%T`.sql

數據恢復

[root@MariaDB ~]# service mysqld stop
[root@MariaDB ~]# rm -rf /mydata/data/*
[root@MariaDB ~]# cp -a /backup/snap/* /mydata/data
[root@MariaDB ~]# service mysqld start
[root@MariaDB ~]# mysql < /backup/binlog/binlog-2015-05-21_20\:23\:41.sql

基于物理備份，數據恢復完成

xtrabackup（推薦）

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

特點：

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

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

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

(4)自動實現備份檢驗

(5)還原速度快

安裝

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

創建最小權限備份用戶

備份過程

完全備份

[root@MariaDB ~]# innobackupex --user=bakupuser --password=bakuppass /backup/
innobackupex: Backup created in directory '/backup/2015-05-21_21-55-08'
innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 245
150521 21:55:16  innobackupex: Connection to database server closed
150521 21:55:16  innobackupex: completed OK!

如果出現如下錯誤，請在my.cnf文件[mysqld] 中添加innodb_log_file_size = 5M 并重啟服務

InnoDB: Error: log file ./ib_logfile0 is of different size 5242880 bytes
InnoDB: than specified in the .cnf file 50331648 bytes!
innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2672.

增量備份

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

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

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

添加數據

做增量備份

[root@MariaDB ~]# innobackupex --incremental /backup/ --incremental-basedir=/backup/201
5-05-21_21-55-08/
innobackupex: Backup created in directory '/backup/2015-05-21_22-26-42'
innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 788
150521 22:26:57  innobackupex: Connection to database server closed
150521 22:26:57  innobackupex: completed OK!

再次添加數據

再次做增量備份

[root@MariaDB ~]# innobackupex --incremental /backup/ --incremental-basedir=/backup/201
5-05-21_22-26-42/ #在第一次增量備份的基礎上做增量備份
innobackupex: Backup created in directory '/backup/2015-05-21_22-32-01'
innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 1056
150521 22:32:10  innobackupex: Connection to database server closed
150521 22:32:10  innobackupex: completed OK!

數據恢復

準備階段

一般情況下，在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態?！皽蕚洹钡闹饕饔谜峭ㄟ^回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態

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

(1)需要在每個備份(包括完全和各個增量備份)上，將已經提交的事務進行“重放”。“重放”之后，所有的備份數據將合并到完全備份上。

(2)基于所有的備份將未提交的事務進行“回滾”。

于是，操作就變成了：

# innobackupex --apply-log --redo-only BASE-DIR

接著執行：

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

而后是第二個增量：

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

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

完整備份準備

[root@MariaDB ~]# innobackupex --apply-log /backup/2015-05-21_21-55-08/
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2766618
150521 23:02:43  innobackupex: completed OK!

增量備份準備

[root@MariaDB ~]# innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/
[root@MariaDB ~]# innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/ 
--incremental-dir=/backup/2015-05-21_22-26-42/
[root@MariaDB ~]# innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/ 
--incremental-dir=/backup/2015-05-21_22-32-01/

恢復階段

還原備份，即完全備份

[root@MariaDB ~]# innobackupex --copy-back /backup/2015-05-21_21-55-08/
[root@MariaDB ~]# chown -R mysql.mysql /mydata/data/
[root@MariaDB ~]# service mysqld start

測試數據是否恢復

OK，數據恢復成功

The end

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