基於Innobackupex的全備恢複，innobackupex

基於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 fullbakdbrobin@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/hotbakSHELL>  lldrwxr-xr-x 7 root root 4096 2014/12/22 09:04 2014-12-22_09-04-05--查看備份產生的相關檔案SHELL>  ll 2014-12-22_09-04-05total 77944-rw-r--r-- 1 root root      357 2014/12/22 09:04 backup-my.cnfdrwx------ 2 root root     4096 2014/12/22 09:04 fullbakdb-rw-r----- 1 root root 79691776 2014/12/22 09:04 ibdata1drwx------ 2 root root     4096 2014/12/22 09:04 mysqldrwxr-xr-x 2 root root     4096 2014/12/22 09:04 performance_schemadrwx------ 2 root root     4096 2014/12/22 09:04 recoverdrwx------ 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_logfilec、清空表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 -P3606SHELL> 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-05InnoDB 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=/tmpfor creating ib_logfile*           --注意這裡，使用了--prepare，並且建立innodb的logfilextrabackup: notice: xtrabackup_logfile was already used to '--prepare'.xtrabackup: using the following InnoDB configuration for recovery:             ............-- Author : Leshami-- Blog   : http://blog.csdn.net/leshami    InnoDB: Shutdown completed; log sequence number 391275633141222 09:14:02  innobackupex: completed OK!        --成功恢複 --查看恢複後檔案的相關資訊SHELL>  ll 2014-12-22_09-04-05total 178404-rw-r--r-- 1 root root      357 2014/12/22 09:04 backup-my.cnfdrwx------ 2 root root     4096 2014/12/22 09:04 fullbakdb-rw-r----- 1 root root 79691776 2014/12/22 09:14 ibdata1-rw-r--r-- 1 root root 50331648 2014/12/22 09:14 ib_logfile0-rw-r--r-- 1 root root 50331648 2014/12/22 09:13 ib_logfile1drwx------ 2 root root     4096 2014/12/22 09:04 mysqldrwxr-xr-x 2 root root     4096 2014/12/22 09:04 performance_schemadrwx------ 2 root root     4096 2014/12/22 09:04 recoverdrwx------ 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--r-- 1 root root       24 2014/12/22 09:14 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root       93 2014/12/22 09:14 xtrabackup_checkpoints-rw-r--r-- 1 root root      684 2014/12/22 09:04 xtrabackup_info-rw-r----- 1 root root  2097152 2014/12/22 09:13 xtrabackup_logfile--觀察上面檔案夾內容的變化，可以看到09:14為新增或發生變化的檔案，主要是產生了系統資料表空間資料檔案及innodb記錄檔--同時有關Innodb的檢查點檔案也進行了更新(注，熱備只涉及到InnoDB引擎，所有與InnoDB相關的都會在apply-log時發生變化--將原有檔案夾重新命名到新位置，並建立原檔案夾SHELL> mv /data/inst3606/data3606 /data/inst3606/data3606bkSHELL> mkdir -p /data/inst3606/data3606--將已經恢複好的資料檔案複製回原始位置SHELL> innobackupex --user=robin -password=xxx --port=3606 --defaults-file=/data/inst3606/data3606bk/my3606.cnf \ > --copy-back /data/bak/hotbak/2014-12-22_09-04-05                    ......非重要訊息忽略，下同......innobackupex: Starting to copy files in '/data/bak/hotbak/2014-12-22_09-04-05' --啟動將備份的檔案複製回原路徑innobackupex: back to original data directory '/data/inst3606/data3606'        --原路徑位置                      ............複製所有的資料檔案，索引檔案個，格式檔案等............innobackupex: Starting to copy InnoDB system tablespace  --複製系統資料表空間innobackupex: in '/data/bak/hotbak/2014-12-22_09-04-05'innobackupex: back to original InnoDB data directory '/data/inst3606/data3606'innobackupex: Copying '/data/bak/hotbak/2014-12-22_09-04-05/ibdata1' to '/data/inst3606/data3606/ibdata1'innobackupex: Starting to copy InnoDB undo tablespaces   --複製undo資料表空間innobackupex: in '/data/bak/hotbak/2014-12-22_09-04-05'innobackupex: back to '/data/inst3606/data3606'innobackupex: Starting to copy InnoDB log files          --複製redo資料表空間innobackupex: in '/data/bak/hotbak/2014-12-22_09-04-05'innobackupex: back to original InnoDB log directory '/data/inst3606/data3606'innobackupex: Copying '/data/bak/hotbak/2014-12-22_09-04-05/ib_logfile1' to '/data/inst3606/data3606/ib_logfile1'innobackupex: Copying '/data/bak/hotbak/2014-12-22_09-04-05/ib_logfile0' to '/data/inst3606/data3606/ib_logfile0'innobackupex: Finished copying back files.141222 09:34:47  innobackupex: completed OK!--許可權修改SHELL> cp /data/inst3606/data3606bk/my3606.cnf /data/inst3606/data3606/my3606.cnfSHELL> chown -R mysql:mysql /data/inst3606/data3606--啟動恢複後的執行個體SHELL>  mysqld_safe --defaults-file=/data/inst3606/data3606bk/my3606.cnf &--驗證恢複情況SHELL>  sql -P3606robin@localhost[(none)]> select * from fullbakdb.tb;+------+---------+| id   | val     |+------+---------+|    1 | robin   ||    2 | leshami |+------+---------+robin@localhost[(none)]> drop database fullbakdb;Query OK, 1 row affected (0.01 sec)

3、小結
a、Xtrabackup恢複的目的要是保證事務(資料)的一致性，Xtrabackup log會記錄這些事務備份期間的狀態
b、恢複過程分為2個階段，一個是Prepare階段，一個是copy back階段，恢複前關閉原有執行個體
c、Prepare階段會根據從innodb logfile捕獲出來的資訊(記錄在xtrabackup log)來進行相應的前滾或復原
d、Prepare階段會在成功前滾或復原後建立新的innodb logfile(空)
e、copy back階段則是將成功恢複的全部檔案複製回原來或指定的資料目錄(目錄應為空白目錄)
f、copy back前需要關閉原有執行個體，如果恢複到不同的執行個體則不需要
g、copy back完成後應做相應的許可權修改
h、啟動恢複後的執行個體並進行相關驗證