程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL備份恢復之XtraBackup

MySQL備份恢復之XtraBackup

編輯:MySQL綜合教程

一、 簡介

我們知道,針對InnoDB存儲引擎,MySQL本身沒有提供合適的熱備工具,ibbackup雖是一款高效的首選熱備方式,但它是是收費的。好在Percona公司給大家提供了一個開源、免費的Xtrabackup熱備工具,它可實現ibbackup的所有功能,並且還擴展支持真正的增量備份功能,是商業備份工具InnoDB Hotbackup的一個很好的替代品。

Xtrabackup包括兩個主要工具:Xtrabackup和innobackupex:

Xtrabackup只能備份InnoDB和XtraDB兩種引擎表,而不能備份MyISAM數據表。

innobackupex則是參考InnoDBHotbackup的innoback腳本修改而來的一個perl腳本,它封裝了xtrabackup,主要是為了能方便的同時備份InnoDB和MyISAM引擎表。但在處理MyISAM時需要加一個讀鎖,這會阻塞線上服務的寫操作,所以數據庫中MyISAM表越少越好。另外,該工具還加入了一些其它使用選項,比如slave-info,可以在備份中記錄一些Slave需要的信息,根據這些信息,可以很方便的利用備份重做Slave。

通過Xtrabackup,不但可在線(熱)備份整個庫的InnoDB、XtraDB表,還可在上一次整庫備份的基礎上做增量備份(InnoDB only),其工作原理如下:

首先完成一個完全備份,並記錄下此時檢查點的LSN(Log Sequence Number);在進行增量備份時,比較表空間中每個頁的LSN是否大於上次備份時的LSN,如果是,則備份該頁,同時記錄當前檢查點的LSN。

注意:在增量備份時,作為備份基礎的全備文件不能壓縮,否則備份失效;另外,增量備份期間,表結構變更的話,變更部分的備份也會失效。

二、安裝

Pecona官方網站提供了Xtrabackup源碼包、二進制包以及RPM包三種安裝包,下載地址為:http://www.percona.com/downloads/XtraBackup/ 本文選擇最簡單方便的RPM包安裝,如下:

[root@db ~]# rpm -ivh percona-xtrabackup-2.0.5-499.rhel5.x86_64.rpm

warning: percona-xtrabackup-2.0.5-499.rhel5.x86_64.rpm:Header V4 DSA signature: NOKEY, key ID cd2efd2a

Preparing... ########################################### [100%]

1:percona-xtrabackup ########################################### [100%]

安裝完成後,會在/usr/bin/目錄下生成如下命令工具:

[root@db ~]# cd /usr/bin/

[root@db bin]# ls xtr* inno*

innobackupex innochecksum xtrabackup_51 xtrapchar xtrapinfo xtrapproto xtrapstats

innobackupex-1.5.1 xtrabackup xtrabackup_55 xtrapin xtrapout xtrapreset

其中:

innobackupex 是備份時直接使用的工具,它是一個perl腳本,內部封裝xtrabackup;

innobackupex-1.5.1 是innobackupex文件的一個軟鏈接;

xtrabackup 是備份Innodb引擎的腳本;

xtrabackup_51 xtrabackup運行時需要調用的工具,適用於MySQL 5.1版本;

xtrabackup_55 xtrabackup運行時需要調用的工具,適用於MySQL 5.5版本;

三、Xtrabackup的使用

首先來學習使用Xtrabackup這個命令工具,前面提到:它只能備份InnoDB和XtraDB兩種引擎表,不能備份MyISAM數據表。

通過--help參數查看該命令的用法,如下:

Usage: [xtrabackup [--default-file=#] --backup | xtrabackup [--defaults-file=#] --prepare] [OPTIONS]

參數解讀如下:

--defaults-file=# 標識默認的配置文件,可顯式手工指定,也可不設置;若不設置,則Xtrabackup缺省將從以下目錄查找配置文件: /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf 然後從中讀取[mysqld]和[xtrabackup]配置段。[mysqld]中只需指定datadir、innodb_data_home_dir、innodb_data_file_path、innodb_log_group_home_dir、innodb_log_files_in_group、innodb_log_file_size這6個參數即可讓Xtrabackup正常工作。 --defaults-extra-file=# 如果使用了該參數,則會在讀取了全局配置文件後,再讀取這裡指定的配置文件 --target-dir=name 備份文件的存放路徑 --backup 執行備份操作,將備份文件存放到target-dir指定的目錄下 --prepare 對備份文件執行恢復前的准備(生成InnoDB logfile) --print-param 打印備份或恢復時需要的參數 --use-memory=# 該參數在prepare的時候使用,控制prepare時innodb實例使用的內存量 --suspend-at-end 在target-dir目錄下產生一個xtrabackup_suspended文件,將xtrabackup進程掛起,不停地將數據文件的變化同步到備份文件,直到用戶手工刪除xtrabackup_suspended文件 --throttle=# 每秒鐘IO次數,限制backup時使用的I/O操作量,使備份對數據庫正常業務的影響最小化 --log-stream 該參數在backup時使用,將xtrabackup_logfile的內容輸出到標准輸出,使用該參數時會自動使用suspend-at-end參數,innobackupex腳本的stream模式會使用該參數。 --incremental-lsn=name 增量備份時只拷貝LSN比該參數指定值新的ibd pages,前次備份到了哪個LSN可以看前次備份集的xtrabackup_checkpoints文件 --incremental-basedir=name 該參數在backup的時候使用,備份比該參數指定位置的備份集新的idb pages --incremental-dir=name 該參數在prepare的時候使用,指定prepare時產生的delta文件和日志文件的存放路徑 --tables=name 在備份file-per-table類型的文件時使用,使用正則表達式指定需要備份的innodb表 -h, --datadir=name MySQL數據庫的數據文件目錄

示例1:完全備份與恢復(InnoDB)

(1)備份

腳本內容:

[root@db xtrabak]# more xtra_backup.sh
# 2014-04-29
mkdir -p /data/xtrabak/bak_`date +%F`/
echo "backup begin" `date`
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/xtrabak/bak_`date +%F`/
cp -r /var/lib/mysql/test /data/xtrabak/bak_`date +%F`/

注意:xtrabackup只備份數據文件,並不備份數據表結構文件(.frm),所以還需手動拷貝一下,以便xtrabackup恢復時使用

執行備份腳本:

[root@db xtrabak]# sh xtra_backup.sh
backup begin Tue Apr 29 15:22:09 CST 2014
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 499)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/lib/mysql
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
>> log scanned up to (1830807)
[01] Copying /var/lib/mysql/ibdata1 to /data/xtrabak/bak_2014-04-29/ibdata1
[01] ...done
xtrabackup: The latest check point (for incremental): '1830807'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1830807)

xtrabackup: Transaction log of lsn (1830807) to (1830807) was copied.

從輸出可以看出,備份過程首先記錄LSN,然後拷貝數據文件(注意,並沒有備份表結構文件.frm),最後拷貝並記錄LSN。備份完成後,在指定目標目錄除了拷貝過來的數據文件ibdata1及表結構文件夾test外,還生成了另外兩個文件,分別記錄日志及檢查點,如下:

[root@db xtrabak]# cd bak_2014-04-29/
[root@db bak_2014-04-29]# ls
ibdata1 test xtrabackup_checkpoints xtrabackup_logfile

恢復

刪除庫test,然後通過備份文件執行恢復,如下:

腳本內容:

[root@db xtrabak]# more xtra_prepare.sh
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/xtrabak/bak_`date +%F`/
cp -r /data/xtrabak/bak_`date +%F`/test/ /var/lib/mysql/
rm /var/lib/mysql/ib*
cp /data/xtrabak/bak_`date +%F`/ib* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
service mysql restart

說明:腳本中步驟依次為:

實施對備份文件進行恢復前的准備;

從備份文件拷貝數據表結構到默認的數據目錄;

刪除默認數據目錄中對應的數據文件並復制備份的數據文件到默認數據目錄;

修改默認數據目錄權限並重啟MySQL服務

執行恢復腳本:

[root@db xtrabak]# sh xtra_prepare.sh
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 499)
xtrabackup: cd to /data/xtrabak/bak_2014-04-29/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1830807)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
140429 15:28:34 InnoDB: Initializing buffer pool, size = 100.0M
140429 15:28:34 InnoDB: Completed initialization of buffer pool
140429 15:28:34 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140429 15:28:34 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Last MySQL binlog file position 0 85912, file name ./mysql-bin.000026
140429 15:28:34 Percona XtraDB (http://www.percona.com) 1.0.17-12.5 started; log sequence number 1830807

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 85912, file name ./mysql-bin.000026

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140429 15:28:34 InnoDB: Starting shutdown...
140429 15:28:34 InnoDB: Shutdown completed; log sequence number 1832037
Shutting down MySQL.... [ OK ]
Starting MySQL. [ OK ]

執行完成後,檢查發現test庫已成功恢復。

示例2:增量備份與恢復

增量備份是以完全備份或上一次增量備份為基礎的備份,適合數據庫很大的情況,支持在線熱備,備份期間不鎖定表,不影響用戶使用,備份恢復效率都比較高。

下面我們來模擬一次增量備份:

(1) 完全備份

現在test庫有三張表,執行一次完全備份

--腳本內容:

[root@db xtrabak]# more xtra_full_bak.sh
# 2014-04-29
mkdir -p /data/xtrabak/full_bak_`date +%F`/
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/xtrabak/full_bak_`date +%F`/
cp -r /var/lib/mysql/test/ /data/xtrabak/full_bak_`date +%F`/

--執行腳本

[root@db xtrabak]# sh xtra_full_bak.sh
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 499)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/lib/mysql
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 67108864
>> log scanned up to (1653514)
[01] Copying /var/lib/mysql/ibdata1 to /data/xtrabak/full_bak_2014-04-29/ibdata1
[01] ...done
xtrabackup: The latest check point (for incremental): '1653514'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1653514)

xtrabackup: Transaction log of lsn (1653514) to (1653514) was copied.

執行完成後,查看備份文件

[root@db xtrabak]# cd full_bak_2014-04-29/
[root@db full_bak_2014-04-29]# ls
ibdata1 test xtrabackup_checkpoints xtrabackup_logfile

(2) 增量備份

向test庫中添加一張新表,然後執行增量備份

--腳本內容:

[root@db xtrabak]# more xtra_delta_bak.sh
# 2014-04-29
mkdir -p /data/xtrabak/delta_bak_`date +%F`/
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/xtrabak/delta_bak_`date +%F`/ --incremental-basedir=/data/xtrabak/full_bak_`date +%F`/
cp -r /var/lib/mysql/test/ /data/xtrabak/full_bak_`date +%F`/

說明:這裡需要再次拷貝test庫的表結構文件,因為期間可能會增加或刪除表

--執行腳本

[root@db xtrabak]# sh xtra_delta_bak.sh
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 499)
incremental backup from 1653514 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /var/lib/mysql
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/lib/mysql
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 67108864
>> log scanned up to (1660436)
[01] Copying /var/lib/mysql/ibdata1 to /data/xtrabak/delta_bak_2014-04-29/ibdata1.delta
[01] ...done
xtrabackup: The latest check point (for incremental): '1660436'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1660436)

xtrabackup: Transaction log of lsn (1660436) to (1660436) was copied.

執行完成後,查看備份文件

[root@db xtrabak]# cd delta_bak_2014-04-29/
[root@db delta_bak_2014-04-29]# ls
ibdata1.delta ibdata1.meta xtrabackup_checkpoints xtrabackup_logfile

注意:在增量備份目錄下,數據文件是以.delta結尾的,增量備份只備份上一次完全備份後被修改過的page;當然,若再次執行增量備份,可以上一次完全備份為基礎,也可以第一次增量備份為基礎,每次增量備份的目錄都是需要修改的。

(3) 恢復

刪除test庫,模擬故障,然後通過完全備份及增量備份文件執行恢復。

--腳本內容:

[root@db xtrabak]# more xtra_delta_prepare.sh
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/xtrabak/full_bak_`date +%F`/
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/xtrabak/full_bak_`date +%F`/ --incremental-dir=/data/xtrabak/delta_bak_`date +%F`/
cp -r /data/xtrabak/full_bak_`date +%F`/test/ /var/lib/mysql/
rm /var/lib/mysql/ib*
cp /data/xtrabak/full_bak_`date +%F`/ib* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
service mysql restart

--執行腳本

[root@db xtrabak]# sh xtra_delta_prepare.sh
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 499)
xtrabackup: cd to /data/xtrabak/full_bak_2014-04-29/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1653514)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
140429 18:28:21 InnoDB: Initializing buffer pool, size = 100.0M
140429 18:28:21 InnoDB: Completed initialization of buffer pool
140429 18:28:21 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140429 18:28:21 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Last MySQL binlog file position 0 2596, file name ./mysql-bin.000043
140429 18:28:22 Percona XtraDB (http://www.percona.com) 1.0.17-12.5 started; log sequence number 1653514


[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 2596, file name ./mysql-bin.000043


xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140429 18:28:22 InnoDB: Starting shutdown...
140429 18:28:22 InnoDB: Shutdown completed; log sequence number 1653514
xtrabackup version 2.0.5 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 499)
incremental backup from 1653514 is enabled.
xtrabackup: cd to /data/xtrabak/full_bak_2014-04-29/
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1660436)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /data/xtrabak/delta_bak_2014-04-29/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: page size for /data/xtrabak/delta_bak_2014-04-29//ibdata1.delta is 16384 bytes
Applying /data/xtrabak/delta_bak_2014-04-29//ibdata1.delta to ././ibdata1...
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /data/xtrabak/delta_bak_2014-04-29/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
140429 18:28:22 InnoDB: Initializing buffer pool, size = 100.0M
140429 18:28:22 InnoDB: Completed initialization of buffer pool
140429 18:28:22 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140429 18:28:22 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Last MySQL binlog file position 0 2703, file name ./mysql-bin.000045
140429 18:28:22 Percona XtraDB (http://www.percona.com) 1.0.17-12.5 started; log sequence number 1660436


[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 2703, file name ./mysql-bin.000045


xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140429 18:28:22 InnoDB: Starting shutdown...
140429 18:28:23 InnoDB: Shutdown completed; log sequence number 1660436
Shutting down MySQL... [ OK ]
Starting MySQL..... [ OK ]
執行成功後,檢查發現test數據庫已成功恢復。

四、 Innobackupex使用

前面提到,Innobackupex可同時備份InnoDB和MyISAM引擎表,所以通常都直接使用innobackupex。需要注意的是my.cnf中必須加上datadir這個參數,因為要根據它來定位InnoDB引擎的數據文件位置。

innobackupex也包含一系列參數,可通過innobackupex --help命令查看,此處不逐一說明了。

下面我們通過一個完整的示例來演示其用法,如下:

(1) 備份

--腳本內容:

[root@db xtrabak]# more backup.sh
# 2014-04-29
mkdir -p /data/xtrabak/bak_`date +%F`/
echo "backup begin" `date`
log=innobackupex_`date +%F`.log
str=bak_`date +%F`
innobackupex --user=root --password=root123 --defaults-file=/etc/my.cnf --stream=tar /data/xtrabak/$str/ 2>/data/xtrabak/$log | gzip>/data/xtrabak/$str/base.tar.gz
echo "backup end" `date`
cp /etc/my.cnf /data/xtrabak/my_`date +%F`.cnf

--執行腳本:

[root@db xtrabak]# sh backup.sh
backup begin Wed Apr 30 16:15:02 CST 2014
backup end Wed Apr 30 16:15:27 CST 2014

[root@db xtrabak]# cd bak_2014-04-30/
[root@db bak_2014-04-30]# ls
base.tar.gz

執行完成後,生成備份文件的壓縮包

(2) 增量日志備份

由於數據庫不停的對外提供服務,備份之後的操作會記錄到binlong日志文件中,所以我們還應備份後續的日志文件。

此處刪除幾張表,並切換日志以模擬真實環境,備份完整的binlog日志文件;然後關閉數據庫,刪除數據庫所有文件,以便模擬故障。

--腳本內容(binlog備份到遠程機器):

[root@db xtrabak]# more binlog_bak.sh
cd /var/lib/
DATE=`date -d '-1 hour' +%Y%m%d%H00`
touch -t "${DATE}" starttemp
echo "$DATE"
umount /mnt_log>/dev/null 2>&1
mount -t nfs 192.168.3.108:/logcenter /mnt_log
find /var/lib/mysql/mysql-bin.* -newer starttemp|xargs -I {} cp -p {} /mnt_log/mysql-binlog/192.168.3.28/
# umount -t nfs /mnt_log

(3) 恢復

--腳本內容:

[root@db xtrabak]# more prepare.sh
# 2014-04-30
service mysql stop
mkdir -p /data/xtrabak/prepare_`date +%F`/
tar -ixzvf /data/xtrabak/bak_`date +%F`/base.tar.gz -C /data/xtrabak/prepare_`date +%F`/
innobackupex --apply-log --user=root --defaults-file=/etc/my.cnf /data/xtrabak/prepare_`date +%F`/
innobackupex --copy-back --user=root --defaults-file=/etc/my.cnf /data/xtrabak/prepare_`date +%F`/
chown -R mysql:mysql /var/lib/mysql/
rm -rf /var/lib/mysql/xtrabackup_binlog_pos_innodb
service mysql restart

--完全備份恢復後,通過binlog進行增量恢復

[root@db test]# mysqlbinlog start-position=****** /mnt_log/mysql-binlog/192.168.3.28/mysql-bin.000052 |mysql -uroot -proot123

注意:start-position的位置可通過解壓後的備份文件查看,如下:

[root@db xtrabak]# cd prepare_2014-04-30/

[root@db prepare_2014-04-30]# more xtrabackup_binlog_info
mysql-bin.000047 1472

或者

[root@db prepare_2014-04-30]# more xtrabackup_binlog_pos_innodb
./mysql-bin.000047 1472

成功恢復後,MySQL即可正常使用。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved