1.1 備份的原因

備份是數據安全的最后一道防線，對于任何數據丟失的場景，備份雖然不一定能恢復百分之百的數據 (取決于備份周期)，但至少能將損失降到最低。衡量備份恢復有兩個重要的指標：恢復點目標(RPO) 和恢復時間目標(RTO)，前者重點關注能恢復到什么程度，而后者則重點關注恢復需要多長時間。

1.1.1 備份的目的

做災難恢復：對損壞的數據進行恢復和還原

需求改變：因需求改變而需要把數據還原到改變以前

測試：測試新功能是否可用

1.1.2 備份中需要考慮的問題

可以容忍丟失多長時間的數據；

恢復數據要在多長時間內完；

恢復的時候是否需要持續提供服務；

恢復的對象，是整個庫，多個表，還是單個庫，單個表。

1.1.3 備份的類型

熱備份：

這些動態備份在讀取或修改數據的過程中進行，很少中斷或者不中斷傳輸或處理數據的功能。使用熱備份時，系統仍可供讀取和修改數據的操作訪問。

冷備份：

這些備份在用戶不能訪問數據時進行，因此無法讀取或修改數據。這些脫機備份會阻止執行任何使用數據的活動。這些類型的備份不會干擾正常運行的系統的性能。但是，對于某些應用程序，會無法接受必須在一段較長的時間里鎖定或完全阻止用戶訪問數據。

溫備份：

這些備份在讀取數據時進行，但在多數情況下，在進行備份時不能修改數據本身。這種中途備份類型的優點是不必完全鎖定最終用戶。但是，其不足之處在于無法在進行備份時修改數據集，這可能使這種類型的備份不適用于某些應用程序。在備份過程中無法修改數據可能產生性能問題。

1.2 備份的方式

1.2.1 冷備份

最簡單的備份方式就是，關閉 MySQL 服務器，然后將 data 目錄下面的所有文件進行拷貝保存，需要恢復時，則將目錄拷貝到需要恢復的機器即可。這種方式確實方便，但是在生產環境中基本沒什么作用。因為所有的機器都是要提供服務的，即使是 Slave 有時候也需要提供只讀服務，所以關閉 MySQL 停服備份是不現實的。與冷備份相對應的一個概念是熱備份，所謂熱備份是在不影響 MySQL 對外服務的情況下，進行備份。

     冷備份及停止業務進行備份。

1.2.2 快照備份

首先要介紹的熱備份是快照備份，快照備份是指通過文件系統支持的快照功能對數據庫進行備份。備份的原理是將所有的數據庫文件放在同一分區中，然后對該分區執行快照工作，對于 Linux 而言，需要通過 LVM(Logical Volumn Manager)來實現。LVM 使用寫時復制 (copy-on-write) 技術來創建快照，例如，對整個卷的某個瞬間的邏輯副本，類似于數據庫中的 innodb 存儲引擎的 MVCC，只不過 LVM 的快照在文件系統層面，而 MVCC 在數據庫層面，而且僅支持 innodb 存儲引擎。

LVM 有一個快照預留區域，如果原始卷數據有變化時，LVM 保證在任何變更寫入之前，會復制受影響塊到快照預留區域。簡單來說，快照區域內保留了快照點開始時的一致的所有 old 數據。對于更新很少的數據庫，快照也會非常小。

對于 MySQL 而言，為了使用快照備份，需要將數據文件，日志文件都放在一個邏輯卷中，然后對該卷快照備份即可。由于快照備份，只能本地，因此，如果本地的磁盤損壞，則快照也就損壞了。快照備份更偏向于對誤操作防范，可以將數據庫迅速恢復到快照產生的時間點，然后結合二進制日志可以恢復到指定的時間點。基本原理如下圖：

1.2.3 邏輯備份（文本表示：SQL 語句）

冷備份和快照備份由于其弊端在生產環境中很少使用，使用更多是 MySQL 自帶的邏輯備份和物理備份工具，這節主要講邏輯備份，MySQL 官方提供了 Mysqldump 邏輯備份工具，雖然已經足夠好，但存在單線程備份慢的問題。在社區提供了更優秀的邏輯備份工具 mydumper，它的優勢主要體現在多線程備份，備份速度更快。

1.2.4 其他常用的備份方式

物理備份（數據文件的二進制副本）

全量備份概念

全量數據就是數據庫中所有的數據（或某一個庫的全部數據）；

全量備份就是把數據庫中所有的數據進行備份。

mysqldump 會取得一個時刻的一致性數據.

增量備份（刷新二進制日志）

增量數據就是指上一次全量備份數據之后到下一次全備之前數據庫所更新的數據

對于 mysqldump,binlog 就是增量數據.

1.2.5 備份工具的介紹

1、mysqldump: mysql 原生自帶很好用的邏輯備份工具

2、mysqlbinlog: 實現 binlog 備份的原生態命令

3、xtrabackup: precona 公司開發的性能很高的物理備份工具

1.3 mysqldump 備份介紹

備份的基本流程如下：

1.調用FTWRL(flush tables with read lock)，全局禁止讀寫
2.開啟快照讀，獲取此時的快照(僅對innodb表起作用)
3.備份非innodb表數據(*.frm,*.myi,*.myd等)
4.非innodb表備份完畢后，釋放FTWRL鎖
5.逐一備份innodb表數據
6.備份完成。

整個過程，可以參考一張圖，但他的這張圖只考慮 innodb 表的備份情況，實際上在 unlock tables 執行完畢之前，非 innodb 表已經備份完畢，后面的 t1,t2 和 t3 實質都是 innodb 表，而且 5.6 的 mysqldump 利用保存點機制，每備份完一個表就將一個表上的 MDL 鎖釋放，避免對一張表鎖更長的時間。

1.3.1 mysqldump 備份流程

1.3.2 常用的備份參數

mysqldump -A -R --triggers --master-data=2 |gzip   >/opt/all_$(date +%F).sql.gz

1.3.3 -A 參數

備份全庫，備份語句

mysqldump -uroot -p123 -A  > /backup/full.sql

1.3.4 -B 參數

備某一個數據庫下的所有表

增加建庫（create）及 “use 庫” 的語句，可以直接接多個庫名，同時備份多個庫 * -B 庫 1 庫 2

mysqldump -uroot -p123 -B world  > /backup/worldb.sql

備份語句：

create database if not 存在use db1drop tablecreate tableinsert into

不加 - B 備份數據庫時，只是備份數據庫下的所有表，不會創建數據庫

     只能備份單獨的數據庫（一般用于備份單表時使用）

mysqldump -uroot -p123 world  > /backup/world.sql

     備份單表

mysqldump -uroot -p123 world  city  > /backup/world_city.sql

     對于單表備份的粒度，再恢復數據庫數據時速度最快。備份多個表

mysqldump 庫1 表1 表2 表3 >庫1.sql
mysqldump 庫2 表1 表2 表3 >庫2.sql

分庫備份: for 循環

mysqldump -uroot -p'mysql123' -B mysql ...
mysqldump -uroot -p'mysql123' -B mysql_utf8 ...
mysqldump -uroot -p'mysql123' -B mysql ...
......

分庫備份

for name in `mysql -e "show databases;"|sed 1d`
domysqldump -uroot -p'mysql123' -B $name
done

1.3.5 --master-data={1|2} 參數

告訴你備份后時刻的 binlog 位置

2 為注釋 1 為非注釋，要執行的 (主從復制)

[root@db02 logs]# sed -n '22p' /opt/t.sql
CHANGE MASTER TO MASTER_LOG_FILE='clsn-bin.000005', MASTER_LOG_POS=344;
[root@db02 logs]# mysqldump -B --master-data=2 clsn >/opt/t.sql

1.3.6 --single-transaction 參數

對 innodb 引擎進行熱備

     只支持 innodb 引擎使用該參數會單獨開啟一個事務進行備份，利用事務的快照技術實現。

基于事務引擎: 不用鎖表就可以獲得一致性的備份.

體現了 ACID 四大特性中的隔離性，生產中 99% 使用 innodb 事務引擎.

     雖然支持熱備，并不意味著你可以再任意時間點進行備份，特別是業務繁忙期，不要做備份策略，一般夜里進行備份。

innodb 引擎的備份命令如下：

mysqldump -A -B -R --triggers --master-data=2 --single-transaction |gzip >/opt/all.sql.gz

1.3.7 --flush-logs 參數 /-F

刷新 binlog 日志

每天晚上 0 點備份數據庫

mysqldump -A -B -F >/opt/$(date +%F).sql

[root@db02 ~]# ll /application/mysql/logs/
-rw-rw---- 1 mysql mysql 168 Jun 21 12:06 clsn-bin.000001
-rw-rw---- 1 mysql mysql 168 Jun 21 12:06 clsn-bin.000002
-rw-rw---- 1 mysql mysql 210 Jun 21 12:07 clsn-bin.index

     提示: 每個庫都會刷新一次.        

1.3.8 壓縮備份

壓縮備份命令:

mysqldump -B --master-data=2 clsn|gzip >/opt/t.sql.gz

解壓:

zcat t.sql.gz >t1.sql
gzip -d t.sql.gz #刪壓縮包
gunzip alL_2017-12-22.sql.gz

一個完整的備份語句

     innodb 引擎的備份命令如下：

mysqldump -A -R --triggers --master-data=2 --single-transaction |gzip >/opt/all.sql.gz

     適合多引擎混合（例如：myisam 與 innodb 混合）的備份命令如下：

mysqldump -A -R --triggers --master-data=2 |gzip   >/opt/alL_$(date +%F).sql.gz

1.3.9 使用 Mysqldump 備份進行恢復實踐

備份 innodb 引擎數據庫 clsn 并壓縮：

mysqldump -B -R --triggers --master-data=2 clsn|gzip >/opt/all_$(date +%F).sql.gz

人為刪除 clsn 數據庫：

[root@db02 opt]# mysql -e “drop database clsn;”
[root@db02 opt]# mysql -e “show databases;”

恢復數據庫：

使用gzip解壓 gzip -d xxx.gz
shell> mysql </opt/all_2017-1222.sql
或
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /backup/alL_2017-12-22.sql

驗證數據：

[root@db02 opt]#  mysql -e “use clsn;select * from test;”

1.4 【模擬】增量恢復企業案例

1.4.1 前提條件:

1. 具備全量備份（mysqldump）。

2. 除全量備份以外，還有全量備份之后產生的的所有 binlog 增量日志。

1.4.2 環境準備

(1) 準備環境：

drop database clsn;
CREATE DATABASE clsn;
USE `clsn`;
CREATE TABLE `test` (`id` int(4) NOT NULL AUTO_INCREMENT,`name` char(20) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `test` VALUES (1,'clsn'),(2,'znix'),(3,'inca'),(4,'zuma'),(5,'kaka');

查看創建好的數據

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | clsn |
|  2 | znix |
|  3 | inca |
|  4 | zuma |
|  5 | kaka |
+----+------+
5 rows in set (0.00 sec)

(2) 模擬環境：

mkdir /data/backup -p
date -s "2017/12/22"

全備份:

mysqldump -B --master-data=2 --single-transaction clsn|gzip>/data/backup/clsn_$(date +%F).sql.gz

模擬增量:

mysql -e "use clsn;insert into test values(6,'haha');"
mysql -e "use clsn;insert into test values(7,'hehe');"
mysql -e "select * from clsn.test;"

(3) 模擬誤刪數據：

date -s "2017/12/22 11:40"
mysql  -e "drop database clsn;show databases;"

出現問題 10 分鐘后, 發現問題, 刪除了數據庫了.

1.4.3 恢復數據準備

(1) 采用 iptables 防火墻屏蔽所有應用程序的寫入。

[root@clsn ~]# iptables -I INPUT -p tcp --dport 3306 ! -s 172.16.1.51 -j DROP 
#<==非172.16.1.51禁止訪問數據庫3306端口。

     或采用 mysql 配置參數，但是需要重啟數據庫

--skip-networking

     復制二進制日志文件

cp -a /application/mysql/logs/clsn-bin.* /data/backup/

     截取日志

zcat clsn_2017-12-22.sql.gz >clsn_2017-12-22.sql
sed -n '22p' clsn_2017-12-22.sql
mysqlbinlog -d clsn --start-position=339 clsn-bin.000008 -r bin.sql

需要恢復的日志:

1.clsn_2017-12-22.sql
2.bin.sql
grep -i drop bin.sql 
sed -i '/^drop.*/d' bin.sql

1.4.4 進行數據恢復

恢復數據

[root@db02 backup]# mysql <clsn_2017-12-22.sql
[root@db02 backup]# mysql -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| clsn               |
| znix               |
| performance_schema |
+--------------------+

查看數據庫

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | clsn |
|  2 | znix |
|  3 | inca |
|  4 | zuma |
|  5 | kaka |
+----+------+
5 rows in set (0.00 sec)

恢復增量數據：

[root@db02 backup]# mysql clsn <bin.sql
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | clsn |
|  2 | znix |
|  3 | inca |
|  4 | zuma |
|  5 | kaka |
|  6 | haha |
|  7 | hehe |
+----+------+
7 rows in set (0.00 sec)

恢復完畢。

調整 iptables 允許用戶訪問.

1.4.5 多個 binlog 問題

mysqlbinlog -d clsn --start-position=339 clsn-bin.000009 clsn-bin.0000010 -r bin1.sqlmysql clsn <bin1.sql

1.5 mysql 數據庫實際生產慘案

1.5.1 發生背景

1、mysql 服務器會在每天夜里 0 點全量備份

2、某個開發人員某個陽光明媚的上午，喝著茶，優雅的誤刪除了 clsn_oss（核心）數據庫。

3、導致公司業務異常停止，無法正常提供服務。

1.5.2 怎么解決的

1、當前系統進行評估。

什么損壞了，有沒有備份，

恢復數據時間（誤操作的數據有關，備份、恢復策略），

恢復業務時間

     2、恢復方案（1）恢復 0 點的全備，到測試庫（2）恢復 0 點開始到故障時間點的 binlog，到測試庫（3）將誤操作的數據導出，恢復到生產庫。（4）檢驗數據是不是完整的（開發測試環境測試恢復成功數據庫）（5）檢驗完成之后，重新開啟生產業務

1.5.3 項目總結

     1、經過我的恢復處理，30 分鐘整體業務重新提供服務（速度慢。。。）2、在以后的工作中制定嚴格的開發規范，開發，開發。3、將來制定更好的架構方案。

1.6 備份工具的選擇

數據量范圍：30G --> TB 級別

1.6.1 數據量大，變換量小

    （1）全備分花費的成本較高，mysqldump+binlog 實現全備 + 增量備份，缺點是恢復成本比備份時間成本還高（2）xtrabackup：可以較長時間做一次全備，其余時間都是增量，全量備份空間成本很高如果數據量在 30G-->TB 級別的話，更推薦使用 xtrabackup 工具。

1.6.2 數據量小，變化量大

只需要考慮時間成本。

只用全備備份即可，兩種工具選擇都可以。恢復成本上 xtrabackup 小一些

1.6.3 數據量、變化量都大

時間成本和空間成本都要考慮了。

數據量達到 PB 或更高時（facebook），mysqldump 可能成為首選，占用空間小，但技術成本高。需要對 mysqldump 進行二次開發（大數據量公司首選）。

1.7 xtrabackup 備份軟件

percona 公司官網 https://www.percona.com/

1.7.1 Xtrabackup 介紹

Xtrabackup 是由 percona 開源的免費數據庫熱備份軟件，它能對 InnoDB 數據庫和 XtraDB 存儲引擎的數據庫非阻塞地備份（對于 MyISAM 的備份同樣需要加表鎖）；mysqldump 備份方式是采用的邏輯備份，其最大的缺陷是備份和恢復速度較慢，如果數據庫大于 50G，mysqldump 備份就不太適合。

Xtrabackup 安裝完成后有 4 個可執行文件，其中 2 個比較重要的備份工具是 innobackupex**、xtrabackup**

1）xtrabackup 是專門用來備份InnoDB表的，和mysql server沒有交互；
2）innobackupex 是一個封裝xtrabackup的Perl腳本，支持同時備份innodb和myisam，但在對myisam備份時需要加一個全局的讀鎖。
3）xbcrypt 加密解密備份工具
4）xbstream 流傳打包傳輸工具，類似tar
5）物理備份工具，在同級數據量基礎上，都要比邏輯備份性能好的多，特別是在數據量較大的時候，體現的更加明顯。

1.7.1 Xtrabackup 優點

1）備份速度快，物理備份可靠

2）備份過程不會打斷正在執行的事務（無需鎖表）

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

4）自動備份校驗

5）還原速度快

6）可以流傳將備份傳輸到另外一臺機器上

7）在不增加服務器負載的情況備份數據

8）物理備份工具，在同級數據量基礎上，都要比邏輯備份性能要好的多。幾十 G 到不超過 TB 級別的條件下。但在同數據量級別，物理備份恢復數據上有一定優勢。

1.7.2 備份原理

拷貝數據文件、拷貝數據頁

對于 innodb 表可以實現熱備。

(1)在數據庫還有修改操作的時刻，直接將數據文件備走，此時，備份走的數據對于當前mysql來講是不一致的。(2)將備份過程中的redo和undo一并備走。(3)為了恢復的時候，只要保證備份出來的數據頁lsn能和redo lsn匹配，將來恢復的就是一致的數據。redo應用和undo應用。

對于 myisam 表實現自動鎖表拷貝文件。

備份開始時首先會開啟一個后臺檢測進程，實時檢測 mysql redo 的變化，一旦發現有新的日志寫入，立刻將日志記入后臺日志文件 xtrabackup_log 中，之后復制 innodb 的數據文件一系統表空間文件 ibdatax，復制結束后，將執行 flush tables with readlock, 然后復制. frm MYI MYD 等文件，最后執行 unlock tables, 最終停止 xtrabackup_log

1.7.3 xtrabackup 的安裝

安裝依賴關系

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

     下載軟件包，并安裝軟件

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

1.8 xtrabackup 實踐操作

1.8.1 全量備份與恢復

這一階段會啟動 xtrabackup 內嵌的 innodb 實例，回放 xtrabackup 日志 xtrabackup_log，將提交的事務信息變更應用到 innodb 數據 / 表空間，同時回滾未提交的事務 (這一過程類似 innodb 的實例恢復）。恢復過程如下圖：

備份

創建備份目錄

mkdir  /backup -p

     進行第一次全量備份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --socket=/application/mysql/tmp/mysql.sock --no-timestamp /backup/xfull

恢復前準備

恢復數據前的準備 (合并 xtabackup_log_file 和備份的物理文件)

innobackupex --apply-log --use-memory=32M /backup/xfull/

     查看合并后的 checkpoints 其中的類型變為 full-prepared 即為可恢復。

[root@db02 full]# cat xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 4114824
last_lsn = 4114824
compact = 0
recover_binlog_info = 0

破壞數據庫數據文件

[root@db02 full]# cd /application/mysql/data/
[root@db02 data]# ls
auto.cnf  db02.pid  ibdata2      mysql             mysql-bin.index     world
clsn      haha      ib_logfile0  mysql-bin.000001  oldboy
db02.err  ibdata1   ib_logfile1  mysql-bin.000002  performance_schema
[root@db02 data]# \rm -rf ./* 
[root@db02 data]# ls
[root@db02 data]# killall mysql

恢復方法

方法一: 直接將備份文件復制回來

cp -a /backup/full/ /application/mysql/data
chown -R mysql.mysql /application/mysql/data

方法二: 使用 innobackupex 命令進行恢復 **(**推薦)

[root@db02 mysql]# innobackupex --copy-back /backup/xfull
[root@db02 mysql]# chown -R mysql.mysql /application/mysql/

             說明：無論使用那種恢復方法都要恢復后需改屬組屬主，保持與程序一致。

[root@db02 data]# cd /application/mysql/data/
[root@db02 data]# ls
clsn     ibdata2      ibtmp1  performance_schema            xtrabackup_info
haha     ib_logfile0  mysql   world
ibdata1  ib_logfile1  oldboy  xtrabackup_binlog_pos_innodb

     啟動是數據庫

[root@db02 data]#  /etc/init.d/mysqld start

1.8.2 增量備份與恢復

innobackupex 增量備份過程中的 “增量” 處理，其實主要是相對 innodb 而言，對 myisam 和其他存儲引擎而言，它仍然是全拷貝 (全備份)

“增量” 備份的過程主要是通過拷貝 innodb 中有變更的 “頁”（這些變更的數據頁指的是 “頁” 的 LSN 大于 xtrabackup_checkpoints 中給定的 LSN）。增量備份是基于全備的，第一次增備的數據必須要基于上一次的全備，之后的每次增備都是基于上一次的增備，最終達到一致性的增備。增量備份的過程如下，和全備的過程很類似，區別僅在第 2 步。

增量備份從哪增量？

基于上一次的備份進行增量。

redo 默認情況下是一組兩個文件，并且有固定大小。其使用的文件是一種輪詢使用方式，他不是永久的，文件隨時可能被覆蓋。

注意：千萬不要在業務繁忙時做備份。

備份什么內容

1、可以使用 binlog 作為增量

2、自帶的增量備份，基于上次備份后的變化的數據頁，還要備份在備份過程中的 undo、redo 變化

怎么備份

     _1__、先進行第一次全備_

innobackupex  --user=root --password=123 --no-timestamp /bakcup/xfull

     對原庫做了修改，修改了小紅那行然后 commit。

2_、再進行增量備份_

innobackupex --user=root --password=123  --incremental --no-timestamp --incremental-basedir=/backup/xfull/  /backup/xinc1

怎么恢復

1、先應用全備日志（–apply-log，暫時不需要做回滾操作 --redo-only）

innobackupex --apply-log --redo-only /backup/xfull/     

2、合并增量到全備中（一致性的合并）

innobackupex --apply-log --incremental-dir=/backup/xinc1 /backup/xfull/
innobackupex --apply-log /backup/xfull

3、合并完成進行恢復

方法一: 直接將備份文件復制回來

cp -a /backup/full/ /application/mysql/data
chown -R mysql.mysql /application/mysql/data

方法二: 使用 innobackupex 命令進行恢復 **(**推薦)

[root@db02 mysql]# innobackupex --copy-back /backup/xfull
[root@db02 mysql]# chown -R mysql.mysql /application/mysql/

            說明：無論使用那種恢復方法都要恢復后需改屬組屬主，保持與程序一致。

1.8.3 數據庫備份策略

每周的周日進行一次全備；周一到周六每天做上一天增量，每周輪詢一次。

xfull       --apply-log --redo-only   保證last-lsn=周一增量開始lsn
xinc1        合并周一的增量到全備，并apply-log --redo-only  保證last-lsn=周二增量開始lsn
xinc2        合并周二的增量到全備，并apply-log --redo-only  保證last-lsn=周三增量開始lsn
xinc3       合并周三的增量到全備，并apply-log --redo-only  保證last-lsn=周四增量開始lsn
xinc4       合并周四的增量到全備，并apply-log --redo-only  保證last-lsn=周五增量開始lsn
xinc5       合并周五的增量到全備，并apply-log --redo-only  保證last-lsn=周六增量開始lsn
xinc6        合并周六的增量到全備，--apply-log  準備恢復即可

1.8.4 真實生產實戰案例分析

**背景：**某物流公司網站核心系統，數據量是 220G，每日更新量 100M-200M

備份方案： xtrabackup 全備 + 增量

備份策略（crontab**）**：

1、周六 晚上 0 點全備

0 0 * * 6 zjs_full.sh — 這行可以沒有

2、周一至周五、周日 是增量，基于上一天增量

0 1 * * 0-5 zjs_inc.sh— 這行可以沒有

故障場景：

周三的時候，下午兩點，開發人員誤刪除了一張表 zjs_base，大約 10G。

項目職責：

1) 指定恢復方案、利用現有備份;

          　　　　2)  恢復誤刪除數據;3)  制定運維、開發流程規范。

恢復流程：

	a)    準備上周六全備。b)    合并周日、周一 、周二增量。c)    在測試庫恢復以上數據，數據的目前狀態應該周三凌晨1:00d)    需要恢復的數據狀態是，下午2點鐘左右e)    從1點開始的binlog恢復到刪除之前轉臺f)    導出刪除的表zjs_base，恢復到生產庫，驗證數據可用性、完整性。g)    啟動應用連接數據庫。

總結：經過 30 分鐘將誤刪表恢復了。服務總共停止 40 分鐘。

1.8.5 故障恢復小結

恢復思路：

              1、首先確保斷開所有應用，保證數據的安全。2、檢查用于恢復的備份存在嗎。3、設計快速、安全恢復簡單方案，制定突發問題解決辦法。

具體恢復流程：

1、準備上周六全備，并--apply-log --redo-only2、合并增量，周日、周一 、周二  --apply-log --redo-only 周三 --apply-log3、在測試庫恢復以上數據，數據的目前狀態應該周三凌晨1:004、需要恢復的數據狀態是，下午2點鐘左右，刪除zjs_base之前的數據狀態從1點開始的binlog恢復到刪除之前的那個events的position。5、導出刪除的表zjs_base，恢復到生產庫，驗證數據可用性、完整性。6、啟動應用連接數據庫。

     **確定恢復所需時間**

恢復窗口要多長？----> 預計3小時和你恢復+驗證+意外情況有關。
業務停多長時間？----> 6小時？或者更多？更少？

1.8.6 【模擬】生產事故恢復

數據創建階段

1、創建備份需要的目錄

mkdir full  inc1 inc2

2、周日全備

innobackupex --user=root --password=123 --no-timestamp /backup/xbackup/full/

3、模擬數據變化

use oldboy
create table test(id int,name char(20),age int);
insert into test values(8,'outman',99);
insert into test values(9,'outgirl',100);
commit;

4、周一增量備份

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/xbackup/full/ /backup/xbackup/inc1

5、模擬數據變化

use oldboy
insert into test values(8,'outman1',119);
insert into test values(9,'outgirl1',120);
commit;

6、周二的增量備份

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/xbackup/inc1 /backup/xbackup/inc2

7. 再插入新的行操作

use oldboy
insert into test values(10,'outman2',19);
insert into test values(11,'outgirl2',10);
commit;

模擬誤操作事故

模擬場景，周二下午 2 點誤刪除 test 表

use oldboy;drop table test;

準備恢復數據

1. 準備 xtrabackup 備份，合并備份

innobackupex --apply-log --redo-only /backup/xbackup/full
innobackupex --apply-log --redo-only --incremental-dir=/backup/xbackup/inc1 /backup/xbackup/full
innobackupex --apply-log  --incremental-dir=/backup/xbackup/inc2 /backup/xbackup/full
innobackupex --apply-log /backup/xbackup/full

2．確認 binlog 起點，準備截取 binlog。

cd /backup/xbackup/inc2/cat xtrabackup_binlog_info mysql-bin.000001    1121

3. 截取到 drop 操作之前的 binlog

mysqlbinlog  --start-position=1121 /tmp/mysql-bin.000003 找到drop之前的event和postion號做日志截取，假如 1437mysqlbinlog  --start-position=1121 --stop-position=1437    /tmp/mysql-bin.000003 >/tmp/incbinlog.sql

4．關閉數據庫、備份二進制日志

/etc/init.d/mysqld stop
cd /application/mysql/data/
cp mysql-bin.000001 /tmp

5. 刪除 MySQL 所有數據

cd /application/mysql/data/
rm -rf *

恢復數據

1．將全量備份的數據恢復到數據目錄下

innobackupex --copy-back /backup/xbackup/full/
chown -R mysql.mysql /application/mysql/data/
/etc/init.d/mysqld start

2. 恢復 binlog 記錄

set sql_log_bin=0
source /tmp/incbinlog.sql

1.8.7 xtarbackup 導出

(1)“導出” 表 導出表是在備份的 prepare 階段進行的，因此，一旦完全備份完成，就可以在 prepare 過程中通過 --export 選項將某表導出了：

innobackupex --apply-log --export /path/to/backup

此命令會為每個 innodb 表的表空間創建一個以. exp 結尾的文件，這些以. exp 結尾的文件則可以用于導入至其它服務器。

(2)“導入” 表 要在 mysql 服務器上導入來自于其它服務器的某 innodb 表，需要先在當前服務器上創建一個跟原表表結構一致的表，而后才能實現將表導入：

mysql> CREATE TABLE mytable (...)  ENGINE=InnoDB;

然后將此表的表空間刪除：

mysql> ALTER TABLE mydatabase.mytable  DISCARD TABLESPACE;

接下來，將來自于 “導出” 表的服務器的 mytable 表的 mytable.ibd 和 mytable.exp 文件復制到當前服務器的數據目錄，然后使用如下命令將其“導入”：(記得改權限)

mysql> ALTER TABLE mydatabase.mytable  IMPORT TABLESPACE;

示例:

innobackupex --user=root --password=123 --no-timestamp /backup/xbackup/full/

進入到全備的數據庫目錄下

[root@db02 haha]# ls
db.opt  PENALTIES.frm  PENALTIES.ibd  PLAYERS.frm  PLAYERS.ibd
[root@db02 haha]# pwd
/backup/xbackup/full/haha

     導出表

[root@db02 haha]# innobackupex --apply-log --export /backup/xbackup/full/  
[root@db02 haha]# ls
db.opt         PENALTIES.exp  PENALTIES.ibd  PLAYERS.exp  PLAYERS.ibd
PENALTIES.cfg  PENALTIES.frm  PLAYERS.cfg    PLAYERS.frm

     創建出同結構表

CREATE TABLE `PLAYERS` (`PLAYERNO` int(11) NOT NULL,`NAME` char(15) NOT NULL,`INITIALS` char(3) NOT NULL,`BIRTH_DATE` date DEFAULT NULL,`SEX` char(1) NOT NULL,`JOINED` smallint(6) NOT NULL,`STREET` varchar(30) NOT NULL,`HOUSENO` char(4) DEFAULT NULL,`POSTCODE` char(6) DEFAULT NULL,`TOWN` varchar(30) NOT NULL,`PHONENO` char(13) DEFAULT NULL,`LEAGUENO` char(4) DEFAULT NULL,PRIMARY KEY (`PLAYERNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

     復制恢復數據到庫下

[root@db02 haha]# cp  PLAYERS.ibd  PLAYERS.exp  /application/mysql/data/backup/
cp: overwrite `/application/mysql/data/backup/PLAYERS.ibd'? y

     恢復數據

mysql> ALTER TABLE backup.PLAYERS  DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

1.8.8 innobackupex 參數說明

參數	參數說明
--compress	該選項表示壓縮 innodb 數據文件的備份。
--compress-threads???	該選項表示并行壓縮 worker 線程的數量。
--compress-chunk-size	該選項表示每個壓縮線程 worker buffer 的大小，單位是字節，默認是 64K。
--encrypt????????????	該選項表示通過 ENCRYPTION_ALGORITHM 的算法加密 innodb 數據文件的備份，目前支持的算法有 ASE128,AES192,AES256。
--encrypt-threads????	該選項表示并行加密的 worker 線程數量。
--encrypt-chunk-size?	該選項表示每個加密線程 worker buffer 的大小，單位是字節，默認是 64K。
--encrypt-key????????	該選項使用合適長度加密 key，因為會記錄到命令行，所以不推薦使用。
--encryption-key-file	該選項表示文件必須是一個簡單二進制或者文本文件，加密 key 可通過以下命令行命令生成：openssl rand -base64 24。
--include????????????	該選項表示使用正則表達式匹配表的名字 [db.tb]，要求為其指定匹配要備份的表的完整名稱，即 databasename.tablename。
--user???????????????	該選項表示備份賬號。
--password???????????	該選項表示備份的密碼。
--port???????????????	該選項表示備份數據庫的端口。
--host???????????????	該選項表示備份數據庫的地址。
--databases	該選項接受的參數為數據名，如果要指定多個數據庫，彼此間需要以空格隔開；如："xtra_test dba_test"，同時，在指定某數據庫時，也可以只指定其中的某張表。如："mydatabase.mytable"。該選項對 innodb 引擎表無效，還是會備份所有 innodb 表。此外，此選項也可以接受一個文件為參數，文件中每一行為一個要備份的對象。
--tables-file?????	該選項表示指定含有表列表的文件，格式為 database.table，該選項直接傳給 --tables-file。
--socket??????????	該選項表示 mysql.sock 所在位置，以便備份進程登錄 mysql。
--no-timestamp????	該選項可以表示不要創建一個時間戳目錄來存儲備份，指定到自己想要的備份文件夾。
--ibbackup????????	該選項指定了使用哪個 xtrabackup 二進制程序。IBBACKUP-BINARY 是運行 percona xtrabackup 的命令。這個選項適用于 xtrbackup 二進制不在你是搜索和工作目錄，如果指定了該選項,innoabackupex 自動決定用的二進制程序。
--slave-info??????	該選項表示對 slave 進行備份的時候使用，打印出 master 的名字和 binlog pos，同樣將這些信息以 change master 的命令寫入 xtrabackup_slave_info 文件。可以通過基于這份備份啟動一個從庫。
--safe-slave-backup	該選項表示為保證一致性復制狀態，這個選項停止 SQL 線程并且等到 show status 中的 slave_open_temp_tables 為 0 的時候開始備份，如果沒有打開臨時表，bakcup 會立刻開始，否則 SQL 線程啟動或者關閉知道沒有打開的臨時表。如果 slave_open_temp_tables 在 --safe-slave-backup-timeount （默認 300 秒）秒之后不為 0，從庫 sql 線程會在備份完成的時候重啟。
--kill-long-queries-timeout	該選項表示從開始執行 FLUSH TABLES WITH READ LOCK 到 kill 掉阻塞它的這些查詢之間等待的秒數。默認值為 0，不會 kill 任何查詢，使用這個選項 xtrabackup 需要有 Process 和 super 權限。
--kill-long-query-type????	該選項表示 kill 的類型，默認是 all，可選 select。
--ftwrl-wait-threshold????	該選項表示檢測到長查詢，單位是秒，表示長查詢的閾值。
--ftwrl-wait-query-type???	該選項表示獲得全局鎖之前允許那種查詢完成，默認是 ALL，可選 update。
--galera-info?????????????	該選項表示生成了包含創建備份時候本地節點狀態的文件 xtrabackup_galera_info 文件，該選項只適用于備份 PXC。
--stream??????????????????	該選項表示流式備份的格式，backup 完成之后以指定格式到 STDOUT，目前只支持 tar 和 xbstream。
--defaults-file???????????	該選項指定了從哪個文件讀取 MySQL 配置，必須放在命令行第一個選項的位置。
--defaults-extra-file?????	該選項指定了在標準 defaults-file 之前從哪個額外的文件讀取 MySQL 配置，必須在命令行的第一個選項的位置。一般用于存備份用戶的用戶名和密碼的配置文件。
----defaults-group???? ????	該選項表示從配置文件讀取的組，innobakcupex 多個實例部署時使用。
--no-lock	該選項表示關閉 FTWRL 的表鎖，只有在所有表都是 Innodb 表并且不關心 backup 的 binlog pos 點，如果有任何 DDL 語句正在執行或者非 InnoDB 正在更新時（包括 mysql 庫下的表），都不應該使用這個選項，后果是導致備份數據不一致，如果考慮備份因為獲得鎖失敗，可以考慮 --safe-slave-backup 立刻停止復制線程。
--tmpdir	該選項表示指定 --stream 的時候，指定臨時文件存在哪里，在 streaming 和拷貝到遠程 server 之前，事務日志首先存在臨時文件里。在使用參數 stream=tar 備份的時候，你的 xtrabackup_logfile 可能會臨時放在 /tmp 目錄下，如果你備份的時候并發寫入較大的話 xtrabackup_logfile 可能會很大 (5G+)，很可能會撐滿你的 /tmp 目錄，可以通過參數 --tmpdir 指定目錄來解決這個問題。
--history??????????????	該選項表示 percona server 的備份歷史記錄在 percona_schema.xtrabackup_history 表。
--incremental??????????	該選項表示創建一個增量備份，需要指定 --incremental-basedir。
--incremental-basedir??	該選項表示接受了一個字符串參數指定含有 full backup 的目錄為增量備份的 base 目錄，與 --incremental 同時使用。
--incremental-dir??????	該選項表示增量備份的目錄。
--incremental-force-scan	該選項表示創建一份增量備份時，強制掃描所有增量備份中的數據頁。
--incremental-lsn???	該選項表示指定增量備份的 LSN，與 --incremental 選項一起使用。
--incremental-history-name	該選項表示存儲在 PERCONA_SCHEMA.xtrabackup_history 基于增量備份的歷史記錄的名字。Percona Xtrabackup 搜索歷史表查找最近（innodb_to_lsn）成功備份并且將 to_lsn 值作為增量備份啟動出事 lsn. 與 innobackupex--incremental-history-uuid 互斥。如果沒有檢測到有效的 lsn，xtrabackup 會返回 error。
--incremental-history-uuid	該選項表示存儲在 percona_schema.xtrabackup_history 基于增量備份的特定歷史記錄的 UUID。
--close-files????????????	該選項表示關閉不再訪問的文件句柄，當 xtrabackup 打開表空間通常并不關閉文件句柄目的是正確的處理 DDL 操作。如果表空間數量巨大，這是一種可以關閉不再訪問的文件句柄的方法。使用該選項有風險，會有產生不一致備份的可能。
--compact????????????????	該選項表示創建一份沒有輔助索引的緊湊的備份。
--throttle???????????????	該選項表示每秒 IO 操作的次數，只作用于 bakcup 階段有效。apply-log 和 --copy-back 不生效不要一起用。

1.9 參考文獻

https://www.cnblogs.com/cchust/p/5452557.html
http://www.cnblogs.com/gomysql/p/3650645.html  xtrabackup 詳解
https://www.percona.com/software/mysql-database/percona-xtrabackup
https://learn.percona.com/hubfs/Manuals/Percona_Xtra_Backup/Percona_XtraBackup_2.4/Percona-XtraBackup-2.4.9.pdf

MySQL mysqldump數據導出詳解