基於Innobackupex的全備恢複

基於Innobackupex的全備恢複

對於MySQL資料庫的熱備，xtrabackup是大多數DBA朋友們的選擇。xtrabackup內嵌了一個innobackupex可用於熱備MySQL資料庫。本文描述了基於innobackupex這個工具全備下的恢複並給出示範供大家參考。

有關Innobackupex的全備可參考：Innobackupex 全備資料庫

1、Innobackupex恢複原理

After creating a backup, the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.

To prepare a backup with innobackupex you have to use the --apply-log and the path to the backup directory as an argument:

Innobackupex replayed the committed transactions in the log files (some transactions could have been done while the backup was being done) and rolled back the uncommitted ones. Once this is done, all the information lay in the tablespace (the InnoDB files), and the log files are re-created.

在備份期間(copy資料時)事務存在不一致，即copy開始時，有些事務已開始，有些剛剛開始，而copy結束前或結束後才提交或復原。

這些不確定的事務需要在恢複前來確定最終是否最終提交或復原。在這個階段的操作稱之為prepare階段。

這個prepare階段依賴於備份時的xtrabackup log(來自innodb logfile)，使用--apply-log參數實現一致性。

--apply-log參數會根據xtrabackup log做相應的前滾或復原，完成後會重建innodb logfile檔案。

The --use-memory option The preparing process can be speed up by using more memory in it. It depends on the free or available RAM on your system, it defaults to 100MB. In general, the more memory available to the process,the better. The amount of memory used in the process can be specified by multiples of bytes:

恢複期間，--use-memory選項可以加速prepare過程，如果系統可用記憶體夠大的話，該值預設被設定為100MB。

Innobackupex恢複

2、示範恢複全備

a、當前環境
robin@localhost[(none)]> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version      | 5.6.12-log |
+---------------+------------+

robin@localhost[(none)]> create database fullbakdb;
Query OK, 1 row affected (0.01 sec)

robin@localhost[(none)]> use fullbakdb

robin@localhost[fullbakdb]> create table tb(id smallint,val varchar(20));

robin@localhost[fullbakdb]> insert into tb values(1,'robin'),(2,'leshami');

robin@localhost[fullbakdb]> select * from tb;
+------+---------+
| id  | val    |
+------+---------+
|    1 | robin  |
|    2 | leshami |
+------+---------+

b、全備資料庫
SHELL> innobackupex --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock \ 
> --defaults-file=/data/inst3606/data3606/my3606.cnf /data/bak/hotbak 

-- 下面是備份完成後的內容
SHELL>  pwd
/data/bak/hotbak
SHELL>  ll
drwxr-xr-x 7 root root 4096 2014/12/22 09:04 2014-12-22_09-04-05

--查看備份產生的相關檔案
SHELL>  ll 2014-12-22_09-04-05
total 77944
-rw-r--r-- 1 root root      357 2014/12/22 09:04 backup-my.cnf
drwx------ 2 root root    4096 2014/12/22 09:04 fullbakdb
-rw-r----- 1 root root 79691776 2014/12/22 09:04 ibdata1
drwx------ 2 root root    4096 2014/12/22 09:04 mysql
drwxr-xr-x 2 root root    4096 2014/12/22 09:04 performance_schema
drwx------ 2 root root    4096 2014/12/22 09:04 recover
drwx------ 2 root root    4096 2014/12/22 09:04 sakila
-rw-r--r-- 1 root root      26 2014/12/22 09:04 xtrabackup_binlog_info
-rw-r----- 1 root root      93 2014/12/22 09:04 xtrabackup_checkpoints
-rw-r--r-- 1 root root      684 2014/12/22 09:04 xtrabackup_info
-rw-r----- 1 root root    2560 2014/12/22 09:04 xtrabackup_logfile

c、清空表tb以便測試恢複功能
robin@localhost[fullbakdb]> truncate table tb;
Query OK, 0 rows affected (0.01 sec)

robin@localhost[fullbakdb]> select * from tb;
Empty set (0.00 sec)

d、恢複全備
--關閉原有執行個體
SHELL> mysqldown -P3606
SHELL> netstat -nltp|grep mysql|grep 3606

--準備全備檔案
SHELL> innobackupex --apply-log --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock \
> --defaults-file=/data/inst3606/data3606/my3606.cnf /data/bak/hotbak/2014-12-22_09-04-05

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
            ......非重要訊息忽略，下同......
xtrabackup: Starting InnoDB instance for recovery.  --開始innodb執行個體恢複
            ...........
InnoDB: Starting crash recovery.    --開始crash recovery
            ...........
141222 09:13:59  innobackupex: Restarting xtrabackup with command: xtrabackup  --defaults-file="/data/inst3606/data3606/my3606.cnf"
--defaults-group="mysqld" --prepare --target-dir=/data/bak/hotbak/2014-12-22_09-04-05 --tmpdir=/tmp
for creating ib_logfile*          --注意這裡，使用了--prepare，並且建立innodb的logfile

xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
            ............
-- Author : Leshami
-- Blog  :