整合了網(wǎng)上的一些資料，結(jié)合自己的理解，并進(jìn)行了實(shí)驗(yàn)驗(yàn)證
在太平等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供網(wǎng)站設(shè)計(jì)制作、網(wǎng)站設(shè)計(jì) 網(wǎng)站設(shè)計(jì)制作定制網(wǎng)站開發(fā),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),高端網(wǎng)站設(shè)計(jì),成都全網(wǎng)營銷,外貿(mào)網(wǎng)站建設(shè),太平網(wǎng)站建設(shè)費(fèi)用合理。
理解一：
1，Xtrabackup是什么
Xtrabackup是一個(gè)對InnoDB做數(shù)據(jù)備份的工具，支持在線熱備份(備份時(shí)不影響數(shù)據(jù)讀寫)，是商業(yè)備份工具InnoDB Hotbackup的一個(gè)很好的替代品。
Xtrabackup有兩個(gè)主要的工具：xtrabackup、innobackupex
（1）、xtrabackup只能備份InnoDB和XtraDB兩種數(shù)據(jù)表，而不能備份MyISAM數(shù)據(jù)表
（2）、 innobackupex是參考了InnoDB Hotbackup的innoback腳本修改而來的.innobackupex是一個(gè)perl腳本封裝，封裝了xtrabackup。主要是為了方便的 同時(shí)備份InnoDB和MyISAM引擎的表，但
在處理myisam時(shí)需要加一個(gè)讀鎖。并且加入了一些使用的選項(xiàng)。如slave-info可以記錄備份恢 復(fù)后，作為slave需要的一些信息，根據(jù)這些信息，可以很方便的利用備份來重做slave。
2，Xtrabackup可以做什么 :
在線(熱)備份整個(gè)庫的InnoDB、 XtraDB表
在xtrabackup的上一次整庫備份基礎(chǔ)上做增量備份(innodb only)
以流的形式產(chǎn)生備份，可以直接保存到遠(yuǎn)程機(jī)器上(本機(jī)硬盤空間不足時(shí)很有用)
MySQL數(shù)據(jù)庫本身提供的工具并不支持真正的增量備份，二進(jìn)制日志恢復(fù)是point-in-time(時(shí)間點(diǎn))的恢復(fù)而不是增量備份。
Xtrabackup工具支持對InnoDB存儲引擎的增量備份，工作原理如下：
(1)首先完成一個(gè)完全備份，并記錄下此時(shí)檢查點(diǎn)的LSN(Log Sequence Number)。
(2)在進(jìn)程增量備份時(shí)，比較表空間中每個(gè)頁的LSN是否大于上次備份時(shí)的LSN，如果是，則備份該頁，同時(shí)記錄當(dāng)前檢查點(diǎn)的LSN。
首 先，在xtrabackup_checkpoints中找到并記錄最后一個(gè)checkpoint(“l(fā)ast checkpoint LSN”)，然后開始從LSN的位置開始拷貝InnoDB的xtrabackup_checkpoints到xtrabackup_logfile；
接著，開始拷貝全部的數(shù)據(jù)文 件.ibd；在拷貝全部數(shù)據(jù)文件結(jié)束之后，才停止拷貝logfile。
因?yàn)閘ogfile里面記錄全部的數(shù)據(jù)修改情況，所以，即時(shí)在備份過程中數(shù)據(jù)文件被修改過了，恢復(fù)時(shí)仍然能夠通過解析xtrabackup_logfile保持?jǐn)?shù)據(jù)的一致。
理解二：
在備份的時(shí)候，備份工具主要執(zhí)行兩個(gè)任務(wù)來完成備份：
① 在后臺啟動一個(gè)日志拷貝線程。這個(gè)線程會監(jiān)視InnoDB日志文件，當(dāng)日志文件發(fā)生改變時(shí)，這個(gè)線程會將發(fā)生變化的數(shù)據(jù)塊拷貝到備份目錄下一個(gè)名為xtrabackup_logfile的文件中。這個(gè)
操作是必要的，因?yàn)閭浞菘赡軙掷m(xù)很長時(shí)間，在數(shù)據(jù)庫恢復(fù)時(shí)，需要所有從備份開始到結(jié)束的這些日志文件。
② 拷貝InnoDB數(shù)據(jù)文件到指定備份目錄下。這不是一個(gè)簡單的拷貝，備份工具打開并讀取文件的方式類似InnoDB，通過讀取文件目錄并以頁（page）為單位進(jìn)行拷貝。
理解三：
backup的恢復(fù)過程中包括恢復(fù)和還原兩個(gè)部分。
先來看看完全備份集的恢復(fù)。
在InnoDB表的備份或者更直接的說ibd數(shù)據(jù)文件復(fù)制的過程中，數(shù)據(jù)庫處于不一致的狀態(tài)，所以要將xtraback_logfile中尚未提交的事務(wù)進(jìn)行回滾，以及將已經(jīng)提交的事務(wù)進(jìn)行前滾，使各個(gè)數(shù)
據(jù)文件處于一個(gè)一致性狀態(tài)，這個(gè)過程叫做“準(zhǔn)備(prepare)”。
如果你是在一個(gè)從庫上執(zhí)行的備份，那說明你沒有東西需要回滾，只是簡單的apply redo log就可以了。另外在prepare過程中可以使用參數(shù)--use-memory增大使用系統(tǒng)內(nèi)存量從而提高恢復(fù)速度。
對于增量備份的恢復(fù)過程，與完全備份集的恢復(fù)類似，只是有少許不同：
1)、恢復(fù)過程需要使用完全備份集和各個(gè)增量備份集，各個(gè)備份集的恢復(fù)與前面說的一樣（前滾和回滾），之后各個(gè)增量備份集的redo log都會應(yīng)用到完全備份集中；
2)、對于完全備機(jī)集之后產(chǎn)生的新表，要有特殊處理方式，以便恢復(fù)后不丟表；
3)、要以完全備份集為基礎(chǔ)，然后按順序應(yīng)用各個(gè)增量備份集。
理解四：
完整備份的原理：
對于InnoDB，XtraBackup基于InnoDB的crash-recovery功能進(jìn)行備份。
crash-recovery是這樣的：InnoDB維護(hù)了一個(gè)redo log，又稱為 transaction log，也叫事務(wù)日志，它包含了InnoDB數(shù)據(jù)的所有改動情況。InnoDB啟動的時(shí)候先去檢查datafile和transaction log，然后應(yīng)用所有已提交的事務(wù)并回滾所有未提交的事務(wù)。
XtraBackup在備份的時(shí)候并不鎖定表，而是一頁一頁地復(fù)制InnoDB的數(shù)據(jù)，與此同時(shí)，XtraBackup還有另外一個(gè)線程監(jiān)視著transactions log，一旦log發(fā)生變化，就把變化過的log pages復(fù)制走(因?yàn)閠ransactions log文件大小有限，寫滿之后，就會從頭再開始寫，新數(shù)據(jù)可能會覆蓋到舊的數(shù)據(jù)，所以一旦變化就要立刻復(fù)制走）。在全部數(shù)據(jù)文件復(fù)制完成之后，停止復(fù)制logfile。
XtraBackup采用了其內(nèi)置的InnoDB庫以read-write模式打開InnoDB的數(shù)據(jù)文件，然后每次讀寫1MB(1MB/16KB=64page)的數(shù)據(jù)，一頁一頁地遍歷，同時(shí)用InnoDB的buf_page_is_corrupted()函數(shù)檢查此頁的數(shù)據(jù)是否正常，如果正常則進(jìn)行復(fù)制，如不正常則重新讀取，最多重讀10次，如果還是失敗，則備份失敗退出。復(fù)制transactions log的原理也是一樣的，只不過每次讀寫512KB(512KB/16KB=32page)的數(shù)據(jù)。
由于XtraBackup其內(nèi)置的InnoDB庫打開文件的時(shí)候是rw的，所以運(yùn)行XtraBackup的用戶，必須對InnoDB的數(shù)據(jù)文件具有讀寫權(quán)限。
由于XtraBackup要從文件系統(tǒng)中復(fù)制大量的數(shù)據(jù)，所以它盡可能地使用posix_fadvise()，來告訴OS不要緩存讀取到的數(shù)據(jù)(因?yàn)檫@些數(shù)據(jù)不會重用到了)，從而提升性能。如果要緩存的話，大
量的數(shù)據(jù)會對OS的虛擬內(nèi)存造成很大的壓力，其它進(jìn)程（如mysqld）很有可能會被swap出去，這樣就出問題了。同時(shí)，XtraBackup在讀取數(shù)據(jù)的時(shí)候還盡可能地預(yù)讀。
由于不鎖表，所以復(fù)制出來的數(shù)據(jù)是不一致的，數(shù)據(jù)的一致性是在恢復(fù)的時(shí)候使用crash-recovery進(jìn)行實(shí)現(xiàn)的。
對于MyISAM,XtraBackup還是首先鎖定所有的表，然后復(fù)制所有文件。
理解五：
增量備份的原理：
在完整備份和增量備份文件中都有一個(gè)文件xtrabackup_checkpoints會記錄備份完成時(shí)檢查點(diǎn)的LSN。在進(jìn)行新的增量備份時(shí)，XtraBackup會比較表空間中每頁的LSN是否大于上次備份完成的
LSN，如果是，則備份該頁，并記錄當(dāng)前檢查點(diǎn)的LSN。
innobackupex --apply-log：同xtrabackup的--prepare參數(shù),一般情況下,在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作，因?yàn)閭浞莸臄?shù)據(jù)中可能會包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步
至數(shù)據(jù)文件中的事務(wù)。因此，此時(shí)數(shù)據(jù) 文件仍處理不一致狀態(tài)。--apply-log的作用是通過回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件使數(shù)據(jù)文件處于一致性狀態(tài)。
1、全備：
innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=leyou /home/data/backup/full
innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=leyou --host=172.30.1.110 /home/data/backup/full （可選，備份遠(yuǎn)程主機(jī)）
root@debian:/home/data/backup/full# innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=leyou /home/data/backup/full
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
170306 16:16:49 innobackupex: Executing a version check against the server...
170306 16:16:49 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup' as 'root'
(using password: YES).
170306 16:16:49 innobackupex: Connected to MySQL server
170306 16:16:49 innobackupex: Done.
170306 16:16:49 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup' as 'root'
(using password: YES).
170306 16:16:49 innobackupex: Connected to MySQL server
170306 16:16:49 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using server version 5.5.47-0+deb7u1-log
innobackupex: Created backup directory /home/data/backup/full/2017-03-06_16-16-49
170306 16:16:49 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/etc/mysql/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --
target-dir=/home/data/backup/full/2017-03-06_16-16-49 --innodb_log_file_size="5242880" --innodb_data_file_path="ibdata1:10M:autoextend" --tmpdir=/tmp --extra-
lsndir='/tmp'
innobackupex: Waiting for ibbackup (pid=10845) to suspend
innobackupex: Suspend file '/home/data/backup/full/2017-03-06_16-16-49/xtrabackup_suspended_2'
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
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 = 2
xtrabackup: innodb_log_file_size = 5242880
>> log scanned up to (31407794483)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /home/data/backup/full/2017-03-06_16-16-49/ibdata1
[01] ...done
[01] Copying ./ZLECUBE/PO_Reverse_Box_Product_Relation.ibd to /home/data/backup/full/2017-03-06_16-16-49/ZLECUBE/PO_Reverse_Box_Product_Relation.ibd
[01] ...done
[01] Copying ./ZLECUBE/PO_Box_RecvSend_Action_Item.ibd to /home/data/backup/full/2017-03-06_16-16-49/ZLECUBE/PO_Box_RecvSend_Action_Item.ibd
...
...
...
xtrabackup: Creating suspend file '/home/data/backup/full/2017-03-06_16-16-49/xtrabackup_suspended_2' with pid '10846'
170306 16:18:13 innobackupex: Continuing after ibbackup has suspended
170306 16:18:13 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
170306 16:18:13 innobackupex: Executing FLUSH TABLES WITH READ LOCK...
170306 16:18:13 innobackupex: All tables locked and flushed to disk
170306 16:18:13 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up files '/var/lib/mysql//ZLECUBE/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (234 files)
>> log scanned up to (31407794483)
innobackupex: Backing up files '/var/lib/mysql//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
>> log scanned up to (31407794483)
innobackupex: Backing up file '/var/lib/mysql//test/bb.frm'
innobackupex: Backing up file '/var/lib/mysql//test/db.opt'
innobackupex: Backing up file '/var/lib/mysql//test/aa.frm'
innobackupex: Backing up files '/var/lib/mysql//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
170306 16:18:14 innobackupex: Finished backing up non-InnoDB tables and files
170306 16:18:14 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
170306 16:18:14 innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '31407794483'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (31407794483)
xtrabackup: Creating suspend file '/home/data/backup/full/2017-03-06_16-16-49/xtrabackup_log_copied' with pid '10846'
xtrabackup: Transaction log of lsn (31407794483) to (31407794483) was copied.
170306 16:18:15 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/data/backup/full/2017-03-06_16-16-49'
170306 16:18:15 innobackupex: Connection to database server closed
170306 16:18:15 innobackupex: completed OK!
可以看到在備份完innodb的表之后，也開始備份非innodb的表，且同時(shí)會lock table。
root@debian:/home/data/backup/full/2017-03-06_16-16-49# ls -l
total 18508
-rw-r--r-- 1 root root 188 Mar 6 16:16 backup-my.cnf
-rw-r----- 1 root root 18874368 Mar 6 16:16 ibdata1
drwxr-xr-x 2 root root 4096 Mar 6 16:18 log
drwxr-xr-x 2 root root 4096 Mar 6 16:18 mysql
drwxr-xr-x 2 root root 4096 Mar 6 16:18 performance_schema
drwx------ 2 root root 4096 Mar 6 16:18 test
-rw-r----- 1 root root 97 Mar 6 16:18 xtrabackup_checkpoints
-rw-r--r-- 1 root root 579 Mar 6 16:18 xtrabackup_info
-rw-r----- 1 root root 2560 Mar 6 16:18 xtrabackup_logfile
drwx------ 2 root root 40960 Mar 6 16:18 ZLECUBE
(1)xtrabackup_checkpoints —— 備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為prepared狀態(tài)）和LSN(日志序列號)范圍信息；
每個(gè)InnoDB頁(通常為16k大小)都會包含一個(gè)日志序列號，即LSN。LSN是整個(gè)數(shù)據(jù)庫系統(tǒng)的系統(tǒng)版本號，每個(gè)頁面相關(guān)的LSN能夠表明此頁面最近是如何發(fā)生改變的。
(2) xtrabackup_binlog_info —— mysql服務(wù)器當(dāng)前正在使用的二進(jìn)制日志文件及至備份這一刻為止二進(jìn)制日志事件的位置。
(若系統(tǒng)沒開binlog則不會有這個(gè)文件)
(3)xtrabackup_binlog_pos_innodb —— 二進(jìn)制日志文件及用于InnoDB或XtraDB表的二進(jìn)制日志文件的當(dāng)前position。
(4)xtrabackup_binary —— 備份中用到的xtrabackup的可執(zhí)行文件；
(5)backup-my.cnf —— 備份命令用到的配置選項(xiàng)信息；
在使用innobackupex進(jìn)行備份時(shí)，還可以使用--no-timestamp選項(xiàng)來阻止命令自動創(chuàng)建一個(gè)以時(shí)間命名的目錄；如此一來，innobackupex命令將會創(chuàng)建一個(gè)BACKUP-DIR目錄來存儲備份數(shù)據(jù)。
2.全備恢復(fù)： 準(zhǔn)備(prepare)一個(gè)完全備份,
之后，我們就可以根據(jù)backup-my.cnf中的配置把數(shù)據(jù)文件復(fù)制回對應(yīng)的目錄了，當(dāng)然你也可以自己復(fù)制回去，但innobackupex都會幫我們完成。在這里，對于InnoDB表來說是完成“后準(zhǔn)備”
動作，我們稱之為“恢復(fù)(recovery)”，而對于MyISAM表來說由于備份時(shí)是采用鎖表方式復(fù)制的，所以此時(shí)只是簡單的復(fù)制回來，不需要apply log，這個(gè)我們稱之為“還原(restore)”。
注：本文檔里之所以使用恢復(fù)和還原，也是和其他數(shù)據(jù)庫比如Oracle看起來一樣。
需要停庫，可以備份一下之前損壞的數(shù)據(jù)庫。
cd /var/lib
mv mysql mysql.old
mkdir mysql
chown mysql.mysql mysql
一般情況下，在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作，因?yàn)閭浞莸臄?shù)據(jù)中可能會包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)。因此，此時(shí)數(shù)據(jù)文件仍處理不一致狀態(tài)
?！皽?zhǔn)備”的主要作用正是通過回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件也使得數(shù)據(jù)文件處于一致性狀態(tài)。
innobakupex命令的--apply-log選項(xiàng)可用于實(shí)現(xiàn)上述功能。如下面的命令：
innobackupex --defaults-file=/etc/mysql/my.cnf --apply-log /home/data/backup/full/2017-03-06_16-16-49
在實(shí)現(xiàn)“準(zhǔn)備”的過程中，innobackupex通常還可以使用--use-memory選項(xiàng)來指定其可以使用的內(nèi)存的大小，默認(rèn)通常為100M。如果有足夠的內(nèi)存可用，可以多劃分一些內(nèi)存給prepare的過程，以提高其完成速度。
innobackupex --defaults-file=/etc/mysql/my.cnf --apply-log --use-memory=5G /home/data/backup/full/2017-03-06_16-16-49
innobackupex --defaults-file=/etc/mysql/my.cnf --apply-log --use-memory=5G --host=172.30.1.110 /home/data/backup/full/2017-03-06_16-16-49 （可選）
170213 17:14:06 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/home/data/backup/2017-02-13_17-11-22/backup-my.cnf" --defaults-
group="mysqld" --prepare --target-dir=/home/data/backup/2017-02-13_17-11-22 --apply-log-only --use-memory=5G
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 5368709120 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 5.0G
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 5 MB
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=31405539763
InnoDB: Highest supported file format is Barracuda.
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 31405539852
170213 17:14:07 innobackupex: completed OK!
使用copy-back來恢復(fù)數(shù)據(jù)文件。
innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /home/data/backup/full/2017-03-06_15-31-05/
可以看到innobackupex在復(fù)制備份的文件到數(shù)據(jù)庫的數(shù)據(jù)目錄中
12g的文件，很快就完成了，1分鐘左右。
innobackupex: Copying '/home/data/backup/full/2017-03-06_15-31-05/ZLECUBE/BTC_Order_Base_Info.ibd' to '/var/lib/mysql/ZLECUBE/BTC_Order_Base_Info.ibd'
innobackupex: Copying '/home/data/backup/full/2017-03-06_15-31-05/ZLECUBE/Rbac_base_role.ibd' to '/var/lib/mysql/ZLECUBE/Rbac_base_role.ibd'
innobackupex: Copying '/home/data/backup/full/2017-03-06_15-31-05/ZLECUBE/Rbac_p_power_area_province_group.frm' to
'/var/lib/mysql/ZLECUBE/Rbac_p_power_area_province_group.frm'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/home/data/backup/full/2017-03-06_15-31-05'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Copying '/home/data/backup/full/2017-03-06_15-31-05/ib_logfile0' to '/var/lib/mysql/ib_logfile0'
innobackupex: Copying '/home/data/backup/full/2017-03-06_15-31-05/ib_logfile1' to '/var/lib/mysql/ib_logfile1'
innobackupex: Finished copying back files.
170306 16:07:10 innobackupex: completed OK!
最后修改權(quán)限：
chown -R mysql.mysql /var/lib/mysql
啟動數(shù)據(jù)庫之后就可以了。
3 對完全備份的后數(shù)據(jù)庫更改進(jìn)行二進(jìn)制日志增量備份：
3.1查看全備：
cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 31407798454
last_lsn = 31407798454
compact = 0
3.2 模擬數(shù)據(jù)庫修改：
mysql -uroot -pleyou test
mysql> drop table aa;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table bb;
Query OK, 0 rows affected (0.00 sec)
3.3 增量備份數(shù)據(jù)庫：
innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=leyou --incremental /home/data/backup/incr/ --incremental-basedir=/home/data/backup/full/2017-
03-06_16-44-09/ --parallel=2
root@debian:~# innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=leyou --incremental /home/data/backup/incr/ --incremental-
basedir=/home/data/backup/full/2017-03-06_16-44-09/ --parallel=2
170306 17:04:08 innobackupex: Executing a version check against the server...
170306 17:04:08 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup' as 'root'
(using password: YES).
170306 17:04:08 innobackupex: Connected to MySQL server
170306 17:04:08 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using server version 5.5.47-0+deb7u1-log
innobackupex: Created backup directory /home/data/backup/incr/2017-03-06_17-04-08
170306 17:04:08 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/etc/mysql/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --
target-dir=/home/data/backup/incr/2017-03-06_17-04-08 --innodb_log_file_size="5242880" --innodb_data_file_path="ibdata1:10M:autoextend" --tmpdir=/tmp --extra-
lsndir='/tmp' --incremental-basedir='/home/data/backup/full/2017-03-06_16-44-09/' --parallel=2
innobackupex: Waiting for ibbackup (pid=21857) to suspend
innobackupex: Suspend file '/home/data/backup/incr/2017-03-06_17-04-08/xtrabackup_suspended_2'
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 70f4be3)
incremental backup from 31407798454 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
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 = 2
xtrabackup: innodb_log_file_size = 5242880
>> log scanned up to (31407804447)
xtrabackup: Generating a list of tablespaces
xtrabackup: using the full scan for incremental backup
xtrabackup: Starting 2 threads for parallel data files transfer
[01] Copying ./ibdata1 to /home/data/backup/incr/2017-03-06_17-04-08/ibdata1.delta
[02] Copying ./ZLECUBE/GA_GoodsArea_Info.ibd to /home/data/backup/incr/2017-03-06_17-04-08/ZLECUBE/GA_GoodsArea_Info.ibd.delta
[02] ...done
[01] ...done
[02] Copying ./ZLECUBE/SYS_Access_Authority_Info.ibd to /home/data/backup/incr/2017-03-06_17-04-08/ZLECUBE/SYS_Access_Authority_Info.ibd.delta
[02] ...done
xtrabackup: Creating suspend file '/home/data/backup/incr/2017-03-06_17-04-08/xtrabackup_log_copied' with pid '21858'
xtrabackup: Transaction log of lsn (31407804447) to (31407804447) was copied.
170306 17:04:33 innobackupex: All tables unlocked
...
...
...
innobackupex: Backup created in directory '/home/data/backup/incr/2017-03-06_17-04-08'
170306 17:04:33 innobackupex: Connection to database server closed
170306 17:04:33 innobackupex: completed OK!
root@debian:~#
du -m -s *
1 backup-my.cnf
4 ibdata1.delta
1 ibdata1.meta
1 log
2 mysql
1 performance_schema
1 test
1 xtrabackup_checkpoints
1 xtrabackup_info
1 xtrabackup_logfile
8 ZLECUBE
可以看到，增量備份的數(shù)據(jù)量很小。
root@debian:/home/data/backup/incr/2017-03-06_17-04-08# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 31407798454
to_lsn = 31407804447
last_lsn = 31407804447
compact = 0
3.4 修改數(shù)據(jù)庫，然后創(chuàng)建增量備份2（這次是基于上次的增量備份）
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=leyou --incremental /home/data/backup/incr/ --incremental-basedir=/home/data/backup/incr/2017-
03-06_17-04-08/ --parallel=2
xtrabackup: Creating suspend file '/home/data/backup/incr/2017-03-06_17-14-16/xtrabackup_log_copied' with pid '23367'
xtrabackup: Transaction log of lsn (31407804447) to (31407804447) was copied.
170306 17:14:25 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/data/backup/incr/2017-03-06_17-14-16'
170306 17:14:25 innobackupex: Connection to database server closed
170306 17:14:25 innobackupex: completed OK!
也可以刪除第二個(gè)備份，基于/home/data/backup/incr/2017-03-06_17-04-08/重新備份
root@debian:/home/data/backup/incr# ls -lrt
total 8
drwxr-xr-x 7 root root 4096 Mar 6 17:04 2017-03-06_17-04-08
drwxr-xr-x 8 root root 4096 Mar 6 17:14 2017-03-06_17-14-16
root@debian:/home/data/backup/incr#
root@debian:/home/data/backup/incr#
root@debian:/home/data/backup/incr# rm -rf 2017-03-06_17-14-16
root@debian:/home/data/backup/incr#
mysql> drop database test2;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
root@debian:/home/data/backup/incr/2017-03-06_17-17-54# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 31407804447
to_lsn = 31407804447
last_lsn = 31407804447
compact = 0
3.5 增量備份恢復(fù)
增量備份的恢復(fù)大體為3個(gè)步驟
*恢復(fù)完全備份
*恢復(fù)增量備份到完全備份（開始恢復(fù)的增量備份要添加--redo-only參數(shù)，到最后一次增量備份去掉--redo-only參數(shù)）
*對整體的完全備份進(jìn)行恢復(fù)，回滾那些未提交的數(shù)據(jù)
恢復(fù)完全備份（注意這里一定要加--redo-only參數(shù)，該參數(shù)的意思是只應(yīng)用xtrabackup日志中已提交的事務(wù)數(shù)據(jù)，不回滾還未提交的數(shù)據(jù)）
innobackupex --defaults-file=/etc/mysql/my.cnf --apply-log --redo-only /home/data/backup/full/2017-03-06_16-44-09/
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 31407802010
170306 17:30:37 innobackupex: completed OK!
將增量備份1應(yīng)用到完全備份
innobackupex --defaults-file=/etc/mysql/my.cnf --apply-log --redo-only /home/data/backup/full/2017-03-06_16-44-09/ --incremental-dir=/home/data/backup/incr/2017-03-06_17-04-08/mary_by_event_name.frm'
innobackupex: Copying '/home/data/backup/incr/2017-03-06_17-04-08/performance_schema/events_waits_history.frm' to '/home/data/backup/full/2017-03-06_16-44-09/performance_schema/events_waits_history.frm'
innobackupex: Copying '/home/data/backup/incr/2017-03-06_17-04-08/performance_schema/file_instances.frm' to '/home/data/backup/full/2017-03-06_16-44-09/performance_schema/file_instances.frm'
innobackupex: Copying '/home/data/backup/incr/2017-03-06_17-04-08/performance_schema/mutex_instances.frm' to '/home/data/backup/full/2017-03-06_16-44-09/performance_schema/mutex_instances.frm'
170306 17:33:13 innobackupex: completed OK!
將增量備份2應(yīng)用到完全備份（注意恢復(fù)最后一個(gè)增量備份時(shí)需要去掉--redo-only參數(shù)，回滾xtrabackup日志中那些還未提交的數(shù)據(jù)）
innobackupex --defaults-file=/etc/mysql/my.cnf --apply-log /home/data/backup/full/2017-03-06_16-44-09/ --incremental-dir=/home/data/backup/incr/2017-03-06_17-17-54/
innobackupex: Copying '/home/data/backup/incr/2017-03-06_17-17-54/performance_schema/file_summary_by_event_name.frm' to '/home/data/backup/full/2017-03-06_16-44-
09/performance_schema/file_summary_by_event_name.frm'
innobackupex: Copying '/home/data/backup/incr/2017-03-06_17-17-54/performance_schema/events_waits_history.frm' to '/home/data/backup/full/2017-03-06_16-44-
09/performance_schema/events_waits_history.frm'
innobackupex: Copying '/home/data/backup/incr/2017-03-06_17-17-54/performance_schema/file_instances.frm' to '/home/data/backup/full/2017-03-06_16-44-
09/performance_schema/file_instances.frm'
innobackupex: Copying '/home/data/backup/incr/2017-03-06_17-17-54/performance_schema/mutex_instances.frm' to '/home/data/backup/full/2017-03-06_16-44-
09/performance_schema/mutex_instances.frm'
170306 17:37:42 innobackupex: completed OK!
此時(shí)兩次增量備份其實(shí)都合并到全備上了,恢復(fù)是只需要使用全備進(jìn)行恢復(fù)就可以了
模擬數(shù)據(jù)故障[刪除數(shù)據(jù)庫的數(shù)據(jù)目錄，執(zhí)行如下命令還原]
innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /home/data/backup/full/2017-03-06_16-44-09/
innobackupex: Starting to copy files in '/home/data/backup/full/2017-03-06_16-44-09'
innobackupex: back to original data directory '/var/lib/mysql'
innobackupex: Copying '/home/data/backup/full/2017-03-06_16-44-09/xtrabackup_info' to '/var/lib/mysql/xtrabackup_info'
innobackupex: Creating directory '/var/lib/mysql/ZLECUBE'
innobackupex: Copying '/home/data/backup/full/2017-03-06_16-44-09/ZLECUBE/Rbac_base_role_privilege_Backup_20160625.frm' to
'/var/lib/mysql/ZLECUBE/Rbac_base_role_privilege_Backup_20160625
...
...
...
ce.frm'
innobackupex: Copying '/home/data/backup/full/2017-03-06_16-44-09/performance_schema/file_summary_by_event_name.frm' to
'/var/lib/mysql/performance_schema/file_summary_by_event_name.frm'
innobackupex: Copying '/home/data/backup/full/2017-03-06_16-44-09/performance_schema/events_waits_history.frm' to
'/var/lib/mysql/performance_schema/events_waits_history.frm'
innobackupex: Copying '/home/data/backup/full/2017-03-06_16-44-09/performance_schema/file_instances.frm' to '/var/lib/mysql/performance_schema/file_instances.frm'
innobackupex: Copying '/home/data/backup/full/2017-03-06_16-44-09/performance_schema/mutex_instances.frm' to '/var/lib/mysql/performance_schema/mutex_instances.frm'
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/home/data/backup/full/2017-03-06_16-44-09'
innobackupex: back to original InnoDB data directory '/var/lib/mysql'
innobackupex: Copying '/home/data/backup/full/2017-03-06_16-44-09/ibdata1' to '/var/lib/mysql/ibdata1'
innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/home/data/backup/full/2017-03-06_16-44-09'
innobackupex: back to '/var/lib/mysql'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/home/data/backup/full/2017-03-06_16-44-09'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Copying '/home/data/backup/full/2017-03-06_16-44-09/ib_logfile0' to '/var/lib/mysql/ib_logfile0'
innobackupex: Copying '/home/data/backup/full/2017-03-06_16-44-09/ib_logfile1' to '/var/lib/mysql/ib_logfile1'
innobackupex: Finished copying back files.
170307 15:11:51 innobackupex: completed OK!
root@debian:/var/lib#
chown -R mysql:mysql /var/lib/mysql/
root@debian:/var/lib# /etc/init.d/mysql restart
root@debian:/var/lib# mysql -uroot -pleyou
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ZLECUBE |
| log |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql>
重慶分公司
重慶分公司
