MySQL 備份與恢複詳解

備份類型介紹

備份類型可以分為實體備份和邏輯備份兩種

實體備份是指通過拷貝資料庫檔案的方式完成備份，

這種備份方式适用于

資料庫

很大

，資料重要且需要快速恢複的資料庫

邏輯備份是指通過備份資料庫的邏輯結構（create database/table語句)和資料内容（insert語句或者文本檔案）的方式完成備份。這種備份方式适用于資料庫不是很大，或者你

需要對導出的檔案做一定的修改，又或者是希望在另外的不同類型伺服器上重建立立此資料庫的情況

實體備份通常要求在資料庫關閉的情況下執行

，但如果是在資料庫運作情況下執行，則要求備份期間資料庫不能修改

邏輯備份的速度要慢于實體備份，是因為

邏輯備份需要通路資料庫并将内容轉化成邏輯備份需要的格式

；通常輸出的備份檔案大小也要比實體備份大；另外邏輯備份也不包含資料庫的

配置檔案和日志檔案内容；

備份和恢複的粒度可以是所有資料庫，也可以是單個資料庫，也可以是單個表；邏輯備份需要再資料庫運作的狀态下執行

；它的執行工具可以是MySQLdump或者是select … into outfile兩種方式

備份又可以分為線上備份和離線備份兩種

線上備份是指在資料庫運作的狀态下執行的備份

而離線備份是指在資料庫關閉情況下執行的備份

備份還可以分為本地備份和遠端備份兩種

本地備份是指備份是在和目前MySQL運作的相同主機上發起和執行

而離線備份是指在資料庫關閉情況下執行的備份

備份還可以分為本地備份和遠端備份兩種

本地備份是指備份是在和目前MySQL運作的相同主機上發起和執行

遠端備份是指備份是在和目前MySQL運作的不同主機上發起和執行

比如mysqldump指令可以連接配接本機MySQL，也可以連接配接遠端MySQL；在比如select …into outfile指令可以通過本地或者遠端的MySQL用戶端執行，但生成的檔案則會存放在MySQL執行個體運作的主機上

對實體備份來說啟動備份的過程是MySQL執行個體主機，但備份的位址有可能是遠端的某個

存儲

備份還可以分為全量備份和增量備份兩種

全量備份是指備份中包含所有的資料，而增量備份是指備份中僅包含在某個指定時間段内的變化情況

全量備份的方法正如之前說到的實體備份和邏輯備份方式

而增量備份的方法在MySQL中需要借助二進制日志完成

MySQL備份方法

1. mysqldump指令執行備份

2. 通過

拷貝實體表檔案生成備份

：目前存儲引擎下每個表都有自己獨立的資料檔案時就可以使用拷貝實體表檔案的方式。如果

目前資料庫是運作狀态，則需要下對此表加上一個隻讀鎖

，防止備份期間的修改操作FLUSH TABLES tbl_list WITH READ LOCK；這種表級的備份方式

對MyISAM存儲引擎支援很好

，因為MyISAM的表天生就分成了三個獨立的資料檔案（.frm, .MYD, and *.MYI），

但對InnoDB存儲引擎的表就不太支援

3. 通過select … into outfile方式生成文本檔案：第一種方式是通過SELECT * INTO OUTFILE ‘file_name’ FROM tbl_name指令生成在伺服器上的文本檔案，另一種方式是通過mysqldump指令加–tab參數生成文本檔案; 但這種方式隻會生成表資料，不會生成表結構

4. MySQL增量備份：将MySQL執行個體設定為開啟log-bin參數，備份增量生成的二進制日志到指定的備份地

5. Xtrabackup工具執行全量備份或增量備份

MySQL 實體拷貝檔案

對MyISAM表來說：如果目前此表在拷貝過程中沒有任何寫操作時，可以直接拷貝

10.0.0.201 master01

10.0.0.204 master02

沒有主從關系

建立一個myisam表插入資料進行模拟測試：

CREATE TABLE `students_myisam` (

`sid` int(11) NOT NULL,

`sname` varchar(64) DEFAULT NULL,

`gender` int(11) DEFAULT NULL,

`dept_id` int(11) DEFAULT NULL,

PRIMARY KEY (`sid`),

KEY `idx_sname` (`sname`),

KEY `idx_gender` (`gender`),

KEY `dept_id` (`dept_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into students_myisam values(1,'a',1,1),(2,'b',2,2),(3,'c',3,3);

把這個表相關的三個檔案students_myisam.frm,students_myisam.MYD, students_myisam.MYI檔案拷貝到另外

的資料庫執行個體對應的資料庫目錄下

[root@master01 course]# scp students_myisam.* 10.0.0.204:/usr/local/mysql/data/course/

注意相關目錄下的權限改成mysql

[root@master02 course]# chown mysql.mysql students_myisam.*

對InnoDB表來說：即使是innodb_file_per_table=on的情況下直接拷貝檔案也不行

把這個表相關的兩個檔案students.frm，students.ibd檔案拷貝到另外的資料庫執行個體對應的資料庫目錄下

檢視錯誤日志資訊：

2018-09-18T09:26:39.479752Z 5667 [Warning] InnoDB: Cannot open table test/students from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

有一部分放在共享表空間ibdata裡面，是以不能直接拷貝檔案

對InnoDB來說，可以通過拷貝整個data目錄的方式來完成備份和恢複

,拷貝到另外執行個體，上記得授權

Mysqldump 詳解

Mysqldump工具用來生成MySQL的邏輯備份檔案，其檔案内容就是構成資料庫對象和資料内容的可重複執行的SQL語句

Mysqldump工具使用的三種方式：

shell> mysqldump [options] db_name [tbl_name …] #備份某個庫的那些表

shell> mysqldump [options] –databases db_name … #備份那些苦

shell> mysqldump [options] –all-databases #備份所有庫

options裡的關鍵參數：

–host=host_name, -h host_name指定要導出的目标資料庫所在的主機，預設是localhost

–user=user_name, -u user_name指定連結目标資料庫的資料庫使用者名

–password[=password], -p[password]指定連結目标資料庫的資料庫密碼

–port=port_num, -P port_num指定連結目标資料庫的端口

–add-drop-database表示在使用–databases或者是–all-databases參數時在每個create database指令前都加上drop database的指令

–add-drop-table表示在每個create table指令前加上drop table指令

–add-drop-trigger表示在每個create trigger指令前加上drop trigger指令

–replace表示使用replace指令插入資料而不是使用insert指令

--default-character-set=charset_name指定預設的字元集，預設是UTF8

–set-charset表示将SET NAMES default_character_set指令寫入到導出備份檔案中，預設是開啟狀态

--dump-slave[=value]參數表示從複制的slave從庫導出備份，且其中包含了change master to語句

，則利用此備份檔案可以直接建立另一個複制從庫。

value參數如果不寫或者=1的情況下，則change master to語句寫入到dump檔案中

，如果設定=2則代表也寫入到dump檔案中，隻是會注釋掉

--master-data[=value]參數表示從複制的主庫上導出備份

，和–dump-slave參數配置類似，可以利用此備份檔案直接建立另一個複制從庫。其value的取值含義也和dump-slave相同。

使用該參數會自動打開—lock-all-table參數

，

除非同時使--single-transaction參數

–tab=dir_name, -T dir_name參數表示将備份檔案以文本檔案的方式生成，dir_name指定了存放的檔案路徑，每個表會生成兩個檔案，一個是.sql檔案儲存的表結構資訊，一個是.txt檔案儲存的表資料資訊

–all-databases, -A參數代表導出所有資料庫裡的所有的表

–databases, -B參數代表導出指定的一個或多個資料庫

–ignore-table=db_name.tbl_name參數代表導出過程中忽略某個指定表的導出，如果要忽略多個表則這個參數要使用多次

–no-data, -d參數代表不要導出資料，隻導出表結構

–routines, -R參數代表也要把存儲過程和函數導出來

–triggers參數代表也要将觸發器導出來

–where=’where_condition’, -w ‘where_condition’ 參數代表僅導出符合條件的資料，比如

–where=”user=’jimf’”

-w”userid>1”

-w”userid<1

–lock-all-tables, -x參數代表在導出過程中對每個資料庫的每個表加上一個隻讀鎖

–no-autocommit參數代表對每個表的資料導出内容用set autocommit=0和commit兩個語句包圍

–single-transaction參數代表将事務隔離級别設定為可重複讀并在導出開始時執行start transaction開啟一個新的事務，在dump的執行過程中也不會阻止任何的讀寫操作，一般都會加上改參數

資料導出：

導出一個資料庫：

[root@master01 ~]# mysqldump -u root -p -P 3306 --databases course>backup.sql

導出多個資料庫：

[root@master01 ~]# mysqldump -u root -p -P 3306 --databases course test>course.sql

#或則

[root@master01 ~]# mysqldump -u root -p -P 3306 -B course test>course.sql

導出所有資料庫：

[root@master01 ~]# mysqldump -u root -p -P 3306 --all-databases>course.sql

導出一個資料庫的某幾個表：

[root@master01 ~]# mysqldump -u root -p -P 3306 course students students_myisam>course.sql

僅導出course資料庫的資料而不包含表結構：

[root@master01 ~]# mysqldump -u root -p -P 3306 --no-create-info course>course.sql

僅導出course資料庫中的students和students_myisam兩個表的資料：

[root@master01 ~]# mysqldump -u root -p -P 3306 --no-create-info course students students_myisam>course.sq

僅導出course資料庫的表結構：

[root@master01 ~]# mysqldump -u root -p -P 3306 --no-data course>course.sql

導出course資料庫中除了teacher和score兩個表的其他表結構和資料：

[root@master01 ~]# mysqldump -u root -p -P 3306 --ignore-table=course.teacher --ignoretable=course.score course>course.sql

導出course資料庫的表和存儲過程和觸發器：

[root@master01 ~]# mysqldump -u root -p -P 3306 --routine --trigger course>course.sql

導出course資料庫中符合where條件的資料：

[root@master01 ~]# mysqldump -u root -p -P 3306 --where="sid in (1,2)" course students

students_myisam>course.sql

遠端導出course資料庫，導出檔案在發起導出指令的伺服器上：

[root@master02 data]# mysqldump -u root -p -P 3306 -h 10.0.0.201 course > course.sql

關于備份鎖表的問題

FLUSH TABLES WITH READ LOCK簡稱(FTWRL)，該指令主要用于備份工具擷取一緻性備份(資料與binlog位點比對)。由FTWRL總共需要持有兩把全局的MDL鎖，并且還需要關閉所有表對象，是以這個指令的殺傷性很大，執行指令時容易導緻庫hang住

FTWRL主要包括3個步驟：

1.上全局讀鎖(lock_global_read_lock)

2.清理表緩存(close_cached_tables)

3.上全局COMMIT鎖(make_global_read_lock_block_commit)

FTWRL每個操作的影響

上全局讀鎖會導緻所有更新操作都會被堵塞；關閉表過程中，如果有大查詢導緻關閉表等待，那麼所有通路這個表的查詢和更新都需要等待；上全局COMMIT鎖時，會堵塞活躍事務送出

從主庫備份正确方式：

[root@master ~]# mysqldump -u root -p -P 3306 –master-data=2 –single-transaction course >course.sql

此備份方式會在備份開始之初在所有表上加一個隻讀鎖(FLUSH TABLES WITH READ LOCK)，當成功擷取了該鎖并開始備份之後，此鎖就會立即釋放。而後續的dump過程中不會影響其他的讀寫操作

從主庫備份如果–dump-slave參數，則備份失敗：

[root@master01 ~]# mysqldump -u root -p -P 3306 –dump-slave=2 –single-transaction course >course.sql

Enter password:

mysqldump: Couldn’t execute ‘START SLAVE’: The server is not configured as slave; fix in config file or with CHANGE MASTER TO (1200)

在從庫上備份資料：

[root@slave1 course]# mysqldump -u root -p -P 330 –dump-slave –single-transaction test >course.sql

Mysql 導入備份檔案

格式如下

第一種mysql

1	SHELL> mysql -u root -p db_name < dbbackup.sql

第二種：

也可以使用source指令導入一個備份檔案：

在sql的備份的那裡登入

mysql > use dbname;

mysql > use dbbackup.sql;

使用mysqldump指令導出文本檔案

通過制定-tab=dir_name 參數來指定文本檔案的導出路勁

需要在my.cnf 中先配置

secure_file_priv=/usr/local/mysql/backup/

比如導出course資料庫的所有表的文本檔案到=/usr/local/mysql/backup/目錄下：

這個目錄也需要mysql 權限才能導出

[root@master01 backup]# mysqldump -u root -p –tab=/usr/local/mysql/backup course

–fields-terminated-by=str參數指定每個字段值之間的間隔符，預設是tab

–fields-enclosed-by=char參數指定每個字段值使用什麼字元括起來，預設是沒有

–fields-optionally-enclosed-by=char參數指定對每個非數字類型的字段值使用什麼字元括起來，預設沒有

–lines-terminated-by=str參數指定行之間的結束符，預設是newline

比如：

[root@master01 backup]# mysqldump -u root -p –tab=/usr/local/mysql/backup –fields-terminated-by=, –fields-enclosed-by=’”’ –lines-terminated-by=0x0d0a course

針對文本檔案(txt)到導入，分為兩步， 先是導入表結構檔案，再導入資料檔案

表結構檔案的導入可以使用mysql db1 < t1.sql或者source指令

資料檔案的導入可以使用mysqlimport或者load data infile指令

模拟用文本檔案導入資料，不删除表，删除表資料，再進行導入

mysqlimport 方式：

mysql> delete from students;

mysql> exit;

[root@master01 backup]# mysqlimport -u root -p --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a course /usr/local/mysql/backup/students.txt

Enter password:

course.students: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0

或者使用load data infile 方式

mysql> use course;

mysql> select * from students;

mysql> delete from students;

mysql> load data infile '/usr/local/mysql/backup/students.txt' into table students FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

自定義腳本定時備份

檢測所有使用者定義的資料庫，定時備份所有的資料庫，并上傳到備份伺服器

Python腳本還是shell 腳本，想怎麼寫随你

樣例腳本如下：

#!/bin/sh

####################################

##Function: mysql_dump

##Version: 1.1

# #####################################

MYUSER=system

PORT=5001

DB_DATE=$(date +%F)

DB_NAME=$(uname -n)

MYPASS=********

MYLOGIN=" /data/application/mysql/bin/mysql -u$MYUSER -p$MYPASS -P$PORT “

MYDUMP=" /data/application/mysql/bin/mysqldump -u$MYUSER -p$MYPASS -P$PORT -B “

DATABASE=" $($MYLOGIN -e "show databases;" |egrep -vi

"information_schema|database|performance_schema|mysql") “

for dbname in $DATABASE do

MYDIR=/server/backup/$dbname

[ ! -d $MYDIR ] && mkdir -p $MYDIR

$MYDUMP $dbname --ignore-table=opsys.user_action|gzip

>$MYDIR/${dbname}_${DB_NAME}_${DB_DATE}_sql.gz

Done

find /server/backup/ -type f -name "*.gz" -mtime +3|xargs rm –rf

find /server/backup/* -type d -name "*" -exec rsync -avz {} data_backup:/data/backup/ \;

Select … into outfield 備份

select … into outfile指令可以用來導出表中符合條件的資料到文本檔案，不導出表結構，僅用來導出資料

SELECT INTO…OUTFILE文法：

select * from Table into outfile '/路徑/檔案名'

fields terminated by ','

enclosed by '"'

lines terminated by ‘\r\n

outfile 輸出檔案路勁必須在my.cnf 中配置secure_file_priv=/usr/local/mysql/backup/

TERMINATED BY用來指定字段值之間的符号，例如， “TERMINATED BY ‘,’”指定了逗号作為兩個字段值之間的标志。

ENCLOSED BY子句用來指定包裹檔案中字元值的符号，例如， “ENCLOSED BY ’ ” ‘”表示檔案中字元值放在雙引号之間，若加上關鍵字OPTIONALLY表示所有的值都放在雙引号之間。

ESCAPED BY子句用來指定轉義字元，例如， “ESCAPED BY ‘‘”将“”指定為轉義字元，取代“\”，如空格将表示為“*N”。

LINES子句：在LINES子句中使用TERMINATED BY指定一行結束的标志，如“LINES TERMINATED BY ‘?’”表示一行以“?”作為結束标志

比如導出students表裡完整的資料：

rm -rf /usr/local/mysql/backup/*

比如導出students表裡完整的資料：

mysql> select * from students into outfile '/usr/local/mysql/backup/students.txt' fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

導出students表裡的部分資料：

mysql> select * from students where sid in (1,2) into outfile '/usr/local/mysql/backup/students2.txt' fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

導出表關聯之後的資料：

mysql> select * from students a inner join dept b on a.dept_>into outfile '/usr/local/mysql/backup/students3.txt' fields

terminated by ',' enclosed by '"' lines terminated by '\r\n';

對select … into outfile導出的文本檔案，可以通過load data infile指令導入到表裡

基本文法：

load data [low_priority] [local] infile 'file_name txt' [replace | ignore]

into table tbl_name

[fields

[terminated by't']

[OPTIONALLY] enclosed by '']

[escaped by'\' ]]

[lines terminated by'n']

[ignore number lines]

[(col_name, )]

terminated by分隔符：意思是以什麼字元作為分隔符

enclosed by字段括起字元

escaped by轉義字元

terminated by描述字段的分隔符，預設情況下是tab字元（\t）

enclosed by描述的是字段的括起字元。

escaped by描述的轉義字元。預設的是反斜杠（backslash： \ ）

lines 關鍵字指定了每條記錄的分隔符預設為’\n’即為換行符

如果指定local關鍵詞，則表明從客戶主機讀檔案。如果local沒指定，檔案必須位于伺服器上。

replace和ignore關鍵詞控制對現有的唯一鍵記錄的重複的處理。如果你指定replace，新行将代替有相同的唯一鍵值的現有行

。如果你指定ignore，跳過有唯一鍵的現有行的重複行的輸入。如果你不指定任何一個選項，當找到重複鍵時，出現一個錯誤

，并且文本檔案的餘下部分被忽略

比如将students.txt檔案中的資料導入到表裡：

mysql> delte from students;

mysql> load data infile '/usr/local/mysql/backup/students2.txt' into table students fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

導入過程中忽略錯誤，繼續将符合條件的資料導入：

mysql> load data infile '/usr/local/mysql/backup/students.txt' ignore into table students fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

mysql> select * from students;

導入過程中如果碰到唯一性限制沖突則執行替換語句：

1	mysql> load data infile '/usr/local/mysql/backup/students.txt' replace into table students fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

mysql 恢複

恢複類型分為全量恢複和基于時間點恢複兩種

全量恢複是指将備份檔案中所有的資料都進行恢複，恢複完成之後的資料就是為生成備份的那一刻的資料狀态

而

基于時間點的恢複是指将資料庫恢複到指定的某個時間點的狀态，通常需要依賴二進制日志

将指定時間點前的所有資料庫操作都重新操作一遍

基于時間點恢複

基于時間點的恢複是将資料庫恢複到指定的時間點狀态，通常是先通過全量恢複的方式先将資料庫恢複到上一個全量

恢複的時間點，

然後再利用二進制日志恢複到指定的時間點

Mysqlbinlog工具可以用來解析二進制日志的内容，讓日志可以被執行或者是檢視

在資料庫中重新執行某個二進制日志檔案中的内容，可以使用如下指令

shell> mysqlbinlog binlog_files | mysql -u root -p

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

或者是先把日志檔案内容解析出來，然後再執行

shell> mysqlbinlog binlog_files > tmpfile

shell> mysql -u root -p < tmpfile

模拟資料測試基于時間點恢複：

mysql> use course;

mysql> delte from students;

mysql> alter table students add tstamp timestamp;

mysql> flush logs; #重新整理一下二進制日志，新生成一個二進制日志

#插入資料，flush logs新生成一個二進制檔案

mysql> insert into students(sid,sname,gender,dept_id) values(1,'a',1,1),(2,'b',2,2);

mysql> flush logs;

mysql> insert into students(sid,sname,gender,dept_id) values(3,'c',3,3),(4,'d',4,3);

mysql> flush logs;

mysql> insert into students(sid,sname,gender,dept_id) values(5,'e',5,3),(6,'f',6,3);

mysql> flush logs;

mysql> select * from students;

删除表裡全部資料，進行模拟恢複

第一個的flush logs的時候alter table

插入a,b的那次資料的二進制日志檔案

恢複第一個二進制日志檔案：插入a,b

mysql> truncate table students; ##先删除表裡的全部資料

mysql> desc students;

恢複第一個二進制日志檔案：插入a,b

可以通過mysqlbinlog 查詢一下二進制檔案看看裡面的具體内容

[root@master01 data]# mysql -u root -p < /tmp/mysqlbinlog46.sql

恢複第二個二進制日志檔案：

[root@master01 data]# mysqlbinlog -v mysql-bin.000047 > /tmp/mysqlbinlog47.sql

[root@master01 data]# mysql -u root -p < /tmp/mysqlbinlog47.sql

Enter password:

如果是恢複某個日志檔案中的一部分内容，可以通過指定—start-datetime或者是—stop-datetime參數來确定開始恢複時間和停止恢複時間

比如将資料庫恢複到2018-09-19 19:06:08的時刻：

涉及到mysql-bin.000048檔案内容

[root@master01 data]# mysqlbinlog -v --stop-datetime="2018-09-19 19:06:46" mysql-bin.000048 | mysql -u root -p

Enter password:

基于時間點的恢複也可以指定日志檔案中的位置

，通過設定–start-position和–stop-position兩個參數

mysql> truncate table students;

mysql> select * from students;

Empty set (0.00 sec)

看一下二進制的具體内容

從一個完整的日志位置恢複或則commit 送出以後–stop-position位置恢複

也就是

–start-position=’298’ –stop-position=’460’ #隻恢複某個具體的binlog内容

或則

–stop-posionion=’460’ #恢複460日志位置之前的binlog的内容

[root@master01 data]# mysqlbinlog -v --start-position='298' --stop-position='460' mysql-bin.000046 | mysql -u root -p

Enter password:

Xtrabackup備份與恢複

Xtrabackup是一個對Mysql做資料備份的工具，支援線上熱備份（備份時不影響資料讀寫）

,Xtrabackup有兩個主要的工具：xtrabackup、 innobackupex，其中innobackupex已經逐漸被xtrabackup取代

特點：

(1)備份過程快速、可靠；

(2)備份過程不會打斷正在執行的事務；

(3)能夠基于壓縮等功能節約磁盤空間和流量；

(4)自動實作備份檢驗；

(5)還原速度快

下載下傳位址：

https://www.percona.com/downloads/XtraBackup/LATEST/

安裝：

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/RedHat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

yum -y install libev

yum -y install perl-Digest-*

rpm -ivh percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

全量備份：

mkdir -p /data/backups/mysql

[root@master01 mysql]# xtrabackup --backup --target-dir=/data/backups/mysql/ -u root -p -P 3306 --host=10.0.0.201

全量恢複

恢複過程中首先要執行prepare

，

将所有的資料檔案都準備到同一個時間點

，

因為在備份過程中所有的資料檔案備份都是在不同的時間點，如果直接啟動可能會導緻沖突

/etc/init.d/mysql stop

mv /usr/local/mysql/data /usr/local/mysql/data_bak

mkdir /usr/local/mysql/data

xtrabackup --prepare --target-dir=/data/backups/mysql

xtrabackup --copy-back --target-dir=/data/backups/mysql --datadir=/usr/local/mysql/data

chown -R mysql:mysql /usr/local/mysql/data

/etc/init.d/mysql start

增量備份：

建立一個初始完整備份

rm -rf /data/backups/mysql/*

#完整備份

[root@master01 mysql]# xtrabackup --backup --target-dir=/data/backups/mysql/base -u root -p123456 -P 3306 --host=127.0.0.1

進行模拟資料增加，進行增量備份

mysql -u root -p

use course

insert into students values(11,'aa',1,1,current_timestamp);

insert into students values(12,'bb',2,2,current_timestamp);

exit

xtrabackup --backup --target-dir=/data/backups/mysql/inc1 --incremental-basedir=/data/backups/mysql/base -u root -p123456 --host=127.0.0.1

在加資料，進行第二次增量備份

mysql -u root -p

use course;

insert into students values(13,'cc',3,3,current_timestamp);

insert into students values(14,'dd',4,3,current_timestamp);

exit

xtrabackup --backup --target-dir=/data/backups/mysql/inc2 --incremental-basedir=/data/backups/mysql/inc1 -u root -p123456 -P 3306 --host=127.0.0.1

增量備份恢複：

恢複第一次插入的資料

pkill mysql

rm -rf /usr/local/mysql/data/

#準備上一次完整備份時間點

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/base --datadir=/usr/local/mysql/data

#準備恢複第一次插入的資料

xtrabackup --prepare --target-dir=/data/backups/mysql/base --incremental-dir=/data/backups/mysql/inc1 --datadir=/usr/local/mysql/data

xtrabackup --copy-back --target-dir=/data/backups/mysql/base --datadir=/usr/local/mysql/data

chown -R mysql.mysql /usr/local/mysql/data/

/etc/init.d/mysql start

mysql -u root -p

恢複第二次插入的資料

pkill mysql

rm -rf /usr/local/mysql/data

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/base --incrementaldir=/data/backups/mysql/inc1 --datadir=/usr/local/mysql/data

xtrabackup --prepare --target-dir=/data/backups/mysql/base --incremental-dir=/data/backups/mysql/inc2 --datadir=/usr/local/mysql/data

xtrabackup --copy-back --target-dir=/data/backups/mysql/base --datadir=/usr/local/mysql/data

chown -R mysql.mysql /usr/local/mysql/data

/etc/init.d/mysql start

mysql -u root -p

use course;

select * from students;

在準備增量備份時，必須跳過未送出事務的復原，因為備份時未送出的事務可能正在進行，并且可能會在下一次增量備份中送出。使用–apply-log-only選項來防止復原階段。

最後一次恢複的時候不用加–apply-log-only