mysql之 innobackupex備份+binlog日誌的完全恢復（命令列執行模式）

前言：
MySQL的完全恢復，我們可以藉助於完整的 備份+binlog 來將資料庫恢復到故障點。
備份可以是熱備與邏輯備份(mysqldump)，只要備份與binlog是完整的，都可以實現完全恢復。

1. 準備實驗環境
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.25-log |
+------------+
1 row in set (0.00 sec)
mysql> create database com_rec;
Query OK, 1 row affected (0.00 sec)
mysql> use inc_rec;
Database changed
mysql> create table andy (id int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into andy values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
2. 全備
[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=oracle --port=3606 /xtrabackup/full/
xtrabackup: Transaction log of lsn (1662519) to (1662519) was copied.
170609 17:34:34 completed OK!
3. 檢視全備生成檔案
[root@mysql02 full]# ll /xtrabackup/full/
total 4
drwxr-x---. 6 root root 4096 Jun 9 17:34 2017-06-09_17-34-30
4. 模擬業務新資料
mysql> insert into andy values(3),(4);
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
5. 增量備份
-- 建立存放增量備份目錄並賦權
[root@mysql02 full]# mkdir -p /xtrabackup/incr/
[root@mysql02 full]# chown -R mysql:mysql /xtrabackup/incr/
[root@mysql02 full]# ll /xtrabackup/
total 8
drwxr-xr-x. 3 mysql mysql 4096 Jun 9 03:53 full
drwxr-xr-x. 2 mysql mysql 4096 Jun 9 04:00 incre
-- 正式開始增量備份
[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=oracle --incremental \
--incremental-basedir=/xtrabackup/full/2017-06-09_17-34-30/ /xtrabackup/incr/
########################################下面是增量備份輸出
。。。省略
xtrabackup: Transaction log of lsn (1665808) to (1665808) was copied.
170609 17:36:46 completed OK!
6. 再模擬新業務，該記錄在儲存在binlog，而不會存在於任何備份，這條記錄用於驗證完全恢復 
mysql> insert into andy values(5);
Query OK, 1 row affected (0.00 sec)
7. 記下操作後的 position 點
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 | 1104 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
8. 切換binlog日誌
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
9. 使用binlog events命令來檢視我們最後insert的一條記錄 
mysql> show binlog events in 'binlog.000005';
+---------------+------+-------------+-----------+-------------+-----------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+-------------+-----------+-------------+-----------------------------------------------+
| binlog.000005 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-log, Binlog ver: 4 |
| binlog.000005 | 120 | Query | 1 | 221 | drop database inc_rec |
| binlog.000005 | 221 | Query | 1 | 324 | create database com_rec |
| binlog.000005 | 324 | Query | 1 | 430 | use `com_rec`; create table andy (id int) |
| binlog.000005 | 430 | Query | 1 | 515 | BEGIN |
| binlog.000005 | 515 | Query | 1 | 625 | use `com_rec`; insert into andy values(1),(2) |
| binlog.000005 | 625 | Xid | 1 | 656 | COMMIT /* xid=67 */ |
| binlog.000005 | 656 | Query | 1 | 741 | BEGIN |
| binlog.000005 | 741 | Query | 1 | 851 | use `com_rec`; insert into andy values(3),(4) |
| binlog.000005 | 851 | Xid | 1 | 882 | COMMIT /* xid=81 */ |
| binlog.000005 | 882 | Query | 1 | 967 | BEGIN |
| binlog.000005 | 967 | Query | 1 | 1073 | use `com_rec`; insert into andy values(5) |
| binlog.000005 | 1073 | Xid | 1 | 1104 | COMMIT /* xid=96 */ |
| binlog.000005 | 1104 | Rotate | 1 | 1148 | binlog.000006;pos=4 |
+---------------+------+-------------+-----------+-------------+-----------------------------------------------+
14 rows in set (0.00 sec)

10. 檢視binlog的位置 與 datadir 的位置， 防止 mv datadir時誤操作 binlog ，影響恢復 （binlog與datadir一定要分開放置）
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/binarylog/binlog |
| sql_log_bin | ON |
+---------------------------------+------------------------------------+
6 rows in set (0.00 sec)

mysql> show variables like '%datadir%';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| datadir | /data/mysql/ |
+---------------+--------------+
1 row in set (0.00 sec)
11. 恢復
11.1 先做基於全備的apply，注意，此時使用了--redo-only 
[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log --redo-only /xtrabackup/full/2017-06-09_17-34-30/
170609 04:19:30 completed OK!
11.2 在恢復增量備份集： --此時沒有--redo-only，如果有多個增備，僅僅最後一個增備無需指定--redo-only 
[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /xtrabackup/full/2017-06-09_17-34-30/ --incremental-dir=/xtrabackup/incr/2017-06-09_17-36-39/
170609 04:24:45 completed OK! #結果出現completed OK表示完全成功
說明： /xtrabackup/full/2017-06-09_17-34-30/ 為全備基目錄 ， incremental-dir 為增量備份目錄
12. 關閉要恢復的例項
[root@mysql02 data]# /etc/init.d/mysql stop -p3306
netstat -nltp|grep mysql|grep 3606 
13.將原有資料夾重新命名到新位置，並建立原資料夾 
[root@mysql02 full]# mv /data/mysql /data/mysqlbak
[root@mysql02 full]# mkdir -p /data/mysql
14.執行複製恢復的檔案到原來的資料位置
[root@mysql02 full]# innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /xtrabackup/full/2017-06-09_17-34-30/
170609 04:33:06 completed OK! #結果出現completed OK表示完全成功
說明： /xtrabackup/full/2017-06-09_17-34-30/ 為全備基目錄
15. 許可權修改
[root@mysql02 ~]# mkdir -p /data/mysql/binarylog （說明：這裡我binlog在datadir在路徑下，所以要單獨為binlog建立目錄）
-- 將binlog日誌mv回原位置 （如果binlog不在datadir下，就不用操作）
[root@mysql02 xtrabackup]# ll /data/mysqlbak/binarylog/
total 28
-rw-rw----. 1 mysql mysql 164 Jun 9 06:12 binlog.000001
-rw-rw----. 1 mysql mysql 386 Jun 9 06:14 binlog.000002
-rw-rw----. 1 mysql mysql 143 Jun 9 06:53 binlog.000003
-rw-rw----. 1 mysql mysql 143 Jun 9 07:35 binlog.000004
-rw-rw----. 1 mysql mysql 1148 Jun 9 17:45 binlog.000005
-rw-rw----. 1 mysql mysql 143 Jun 9 17:48 binlog.000006
-rw-rw----. 1 mysql mysql 216 Jun 9 17:45 binlog.index

[root@mysql02 data]# mv /data/mysqlbak/binarylog/* /data/mysql/binarylog/
[root@mysql02 xtrabackup]# ll /data/mysql/binarylog/
total 28
-rw-rw----. 1 mysql mysql 164 Jun 9 06:12 binlog.000001
-rw-rw----. 1 mysql mysql 386 Jun 9 06:14 binlog.000002
-rw-rw----. 1 mysql mysql 143 Jun 9 06:53 binlog.000003
-rw-rw----. 1 mysql mysql 143 Jun 9 07:35 binlog.000004
-rw-rw----. 1 mysql mysql 1148 Jun 9 17:45 binlog.000005
-rw-rw----. 1 mysql mysql 143 Jun 9 17:48 binlog.000006
-rw-rw----. 1 mysql mysql 216 Jun 9 17:45 binlog.index

[root@mysql02 data]# chown -R mysql:mysql /data/mysql
16. 啟動恢復後的例項 
mysqld_safe --defaults-file=/etc/my.cnf &
17. 登入檢查
[root@mysql02 ~]# mysql -uroot -poracle
mysql> use com_rec
mysql> select * from andy;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 | 
| 4 | > 恢復成功，但是沒有恢復到最新，缺少 id=5 , 'Inbinlog'記錄並沒有被恢復 。 
+------+
18. 使用binlog做完全恢復
[root@mysql02 ~]# cd /xtrabackup/incr/2017-06-09_17-36-39/
--從innobackupex獲得binlog的位置 
[root@mysql02 2017-06-09_17-36-39]# more xtrabackup_binlog_info 
binlog.000005 882
--使用mysqlbinlog 追加的最新 
[root@mysql02 2017-06-09_17-36-39]# mysqlbinlog --start-position=882 --stop-position=1104 /data/mysql/binarylog/binlog.000005 | mysql -uroot -poracle 
補充：
這樣做確實可以，而且row模式的binlog，也可以透過這種方式來執行。但是這樣做有幾個缺點
a. 如果解析出來的binlog在執行的過程中報錯，如何處理？直接加 -f 強制執行嗎？
b. 執行中途如何停下來，下次接著跑？ 比如我想調整一下MySQL的引數（需要重啟）後繼續跑？
c. 只能單執行緒執行。而且mysqlbinlog解析再透過管道執行，有比較高的效能開銷。
19. 驗證，可以看到最後一條記錄以及被恢復
mysql> select * from andy;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)

總結
a、對於完全恢復，我們需要使用備份加binlog兩者結合的方式來實現
b、在恢復期間，首先使用帶read-only的apply-log方式來prepare全備
c、接下來使用帶read-only的apply-log方式來prepare增備，僅最後一個增備可以不用read-only
d、停止原有例項，並copy-back後啟動恢復後的例項
e、從Innobakcupex備份資訊中獲取最後的binlog日誌及位置資訊
f、使用mysqlbinlog方式將日誌追加到最新時刻