本文介紹使用各種方式備份和恢複MySQL資料庫及表的方法
一、備份分類
按媒體分類：
-
實體備份
指通過拷貝資料庫檔案方式完成備份，适用于資料庫很大，資料重要且需要快速恢複的資料庫。
-
邏輯備份
指通過備份資料庫的邏輯結構和資料内容的方式完成備份，适用于資料庫不是很大，或需要對導出檔案做一定修改，或重建此庫的情況。
優缺點：
- 實體備份速度快于邏輯備份，因為邏輯備份需要通路資料庫并将内容轉化成邏輯備份需要的格式
- 實體備份的備份恢複粒度範圍是整個資料庫或單個檔案，對單表是否有恢複能力取決于存儲引擎（MyISAM下每個表對應獨立檔案，可以單獨恢複；InnoDB可能使用共享資料檔案）
- 實體備份要求在資料庫關閉情況下執行，如果在運作情況下執行，要求備份期間資料庫不能修改，邏輯備份需要在資料庫運作狀态下執行
- 通常邏輯備份的檔案大小比實體備份大
- 邏輯備份不包含資料庫的配置檔案和日志檔案内容
按狀态分類：
- 線上備份
- 離線備份
按距離分類：
- 本地備份
- 遠端備份
按類型分類：
-
全量備份
指備份中包含所有資料
-
增量備份
指備份中僅包含在某個指定時間段内的變化情況，需要借助二進制日志完成
二、MySQL備份方式
（1）mysqldump
mysqldump -u 使用者名 -p 資料庫名 資料庫表 > 導出的檔案名
（2）拷貝實體表生成備份
目前存儲引擎下每個表都有自己獨立的資料檔案時可以使用這種方式。如果目前資料庫是運作狀态，則需要對此表加上一個隻讀鎖，防止備份期間的修改操作。
對InnoDB存儲引擎的表不太支援。
（3）select...into outfile
- 通過select * into outfile ‘file_name’ from tbl_name生成在伺服器上的檔案
- 通過mysqldump指令加--tab參數生成檔案
隻會生成表資料，不會生成表結構
（4）增量備份
将MySQL執行個體設定開啟log-bin參數，備份增量生成的二進制日志到指定的備份地
（5）Xtrabackup
支援全量和增量備份
三、MySQL備份示範
（1）實體備份
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
mysql> insert into students_myisam values(1,'a',1,1),(2,'b',2,2),(3,'c',3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
開始備份
[root@localhost course]# pwd
/data1/mysql/data/course
[root@localhost course]# ll students_my*
-rw-r----- 1 mysql mysql 8660 Mar 5 11:11 students_myisam.frm
-rw-r----- 1 mysql mysql 60 Mar 5 11:12 students_myisam.MYD
-rw-r----- 1 mysql mysql 5120 Mar 5 11:12 students_myisam.MYI
把這個表相關的三個檔案拷貝到另外的資料庫執行個體對應的資料庫目錄下（記得需要修改檔案權限）
[root@codis-178 cmdb_v2]# cp /home/xiaoda/students_myisam.* ./
[root@codis-178 cmdb_v2]# chown mysql:mysql students_myisam.*
mysql> select * from students_myisam;
+-----+-------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-------+--------+---------+
| 1 | a | 1 | 1 |
| 2 | b | 2 | 2 |
| 3 | c | 3 | 3 |
+-----+-------+--------+---------+
3 rows in set (0.00 sec)
對于InnoDB表來說，即使設定了innodb_file_per_table=on時，直接拷貝也不行
CREATE TABLE `students_myisam2` (
`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=InnoDB DEFAULT CHARSET=utf8
mysql> insert into students_myisam2 values(1,'a',1,1),(2,'b',2,2),(3,'c',3,3);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
開始備份
[root@codis-178 cmdb_v2]# cp /home/xiaoda/students_myisam2.* ./
[root@codis-178 cmdb_v2]# chown mysql:mysql students_myisam2.*
mysql> select * from students_myisam2;
ERROR 1146 (42S02): Table 'cmdb_v2.students_myisam2' doesn't exist
日志報錯：
180305 11:23:53 [ERROR] Cannot find or open table cmdb_v2/students_myisam2 from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.
是以對于InnoDB來說可以通過拷貝整個data目錄方式來完成備份和恢複。
（2）Mysqldump
用來生成MySQL的邏輯備份檔案，其檔案内容就是構成資料庫對象和資料内容的可重複執行的SQL語句。
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
options的關鍵參數：
-h, --host=name 要導出的目标資料庫所在主機，預設是localhost
-u, --user=name 連結目标資料庫的資料庫使用者名
-p, --password[=name] 連結目标資料庫的資料庫密碼
-P, --port=# 連結目标資料庫的端口
--add-drop-database 在使用--databases或--all-databases參數時在每個create database指令前都加上drop database指令
--add-drop-table 在每個create table指令前加上drop table指令
--default-character-set=name 指定預設的字元集，預設是UTF8
--replace 使用該指令插入資料而不是使用insert指令
--set-charset 将set names default_character_set指令寫入到導出備份檔案中，預設是開啟狀态
--dump-slave[=#] 表示從複制的slave從庫導出備份，且其中包含了change master 通語句。value參數如果不寫或=-1的情況下，則change master to語句寫入dump檔案中，設定為2則表示也寫入dump檔案中，隻是會注釋掉
--master-data[=#] 表示從複制的主庫上導出備份。value參數與--dump-slave相同。使用該參數會自動打開lock-all-table參數，除非同時使用--single-transaction參數
-T, --tab=name 表示将備份檔案以文本檔案的方式生成，并指定存放檔案路徑，每個表會生成兩個檔案，一個是.sql檔案儲存表結構，一個是.txt檔案儲存表資料資訊
-A, --all-databases 導出所有資料庫裡的所有表
-B, --databases 導出指定的一個或多個資料庫
--ignore-table=name 代表導出過程中忽略某個指定表的導出，如果要忽略多個表則這個參數使用多次
-d, --no-data 代表隻導出表結構
-R, --routines 代表導出時也要把存儲過程和函數也導出來
--triggers 代表導出時也将觸發器導出來
-w, --where=name 代表導出符合條件的資料
-x, --lock-all-tables 代表在導出過程中對每個資料庫的每個表加上一個隻讀鎖
--no-autocommit 代表對每個表的資料導出内容用set autocommit=0和commit兩個語句包裹
--single-transaction 代表将事務隔離級别設定為可重複讀并在導出開始執行start transaction開啟一個新事務，在dump執行過程中也不會阻止任何讀寫操作
例子：
導出一個資料庫
[root@localhost ~]# mysqldump -uroot -p -P3306 --databases course > backup.sql
導出多個資料庫
[root@localhost ~]# mysqldump -uroot -p -P3306 --databases course test > course.sql
[root@localhost ~]# mysqldump -uroot -p -P3306 -B course test > course.sql
導出所有資料庫
[root@localhost ~]# mysqldump -uroot -p -P3306 --all-databases > all.sql
僅導出course資料庫的資料，不包括表結構
[root@localhost ~]# mysqldump -uroot -p -P3306 --no-create-info course > course.sql
僅導出course資料庫中的students和students_myisam兩個表
[root@localhost ~]# mysqldump -uroot -p -P3306 --no-create-info course students students_myisam > students.sql
僅導出course資料庫的表結構
[root@localhost ~]# mysqldump -uroot -p -P3306 --no-data course > course.sql
導出course資料庫中除了teacher和score兩個表的其他表結構和資料
[root@localhost ~]# mysqldump -uroot -p -P3306 --ignore-table=course.teacher --ignore-table=course.score course > course.sql
導出course資料庫的表和存儲過程和觸發器
[root@localhost ~]# mysqldump -uroot -p -P3306 --routines --triggers course > course.sql
導出course資料庫中符合條件的資料
[root@localhost ~]# mysqldump -uroot -p -P3306 --where="sid in (1,2)" course students students_myisam > course.sql
遠端導出course資料庫
[root@localhost ~]# mysqldump -uroot -p -P3306 -h192.168.1.178 cmdb_v2 students_myisam > students.sql
在主庫備份
[root@codis-178 ~]# mysqldump -uroot -p -P3306 --master-data=2 --single-transctions course > course.sql
（在備份開始之初，在所有表上加一個隻讀鎖（flush table with read lock），當成功擷取該鎖并開始備份之後，此鎖就會立即釋放，後續dump過程不會影響其他的讀寫操作）
在從庫備份
[root@codis-178 ~]# mysqldump -uroot -p -P3306 --dump-slave --single-transctions test > test.sql
這裡注意，導出時報以下錯誤：
[root@codis-178 ~]# mysqldump -uroot -p -P3306 -h192.168.1.68 course > course.sql
Enter password:
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)
這是由于mysqldump版本低導緻，也就是5.5版本不能導出5.7版本
如何解決？
用5.7或更高版本的mysqldump覆寫或者指定目錄運作即可?
使用mysqldump指令導出文本檔案，通過指定--tab=dir_name參數來指定檔案路徑
添加配置
secure-file-priv=/tmp/
[root@localhost ~]# mysqldump -uroot -p -P3306 --tab=/tmp course
[root@localhost ~]# ll /tmp/course.sql
-rw-r--r-- 1 root root 1544 Mar 5 13:28 /tmp/course.sql
還可指定檔案格式
-
--fields-teminated-by=str
指定每個字段值之間的間隔符，預設是tab
-
--fields-enclosed-by=char
指定每個字段值使用什麼字元括起來，預設是沒有
-
--fields-optionsally-enclosed-by=char
指定對每個非數字類型的字段使用什麼字元括起來，預設沒有
-
--lines-terminated-by=str
指定行之間的結束符，預設是newline
[root@localhost ~]# mysqldump -uroot -p -P3306 --tab=/tmp course --fields-terminated-by=, --fields-enclosed-by="'" --lines-terminated-by="\n" course
Enter password:
[root@localhost ~]# cat /tmp/course.txt
'1','math','3'
'2','english','2'
'3','chinese','4'
'4','history','1'
'5','biology','5'
（3）select... into outfile
用來導出表中符合條件的資料到文本檔案，不導出表結構
mysql> select * from students_myisam into outfile '/tmp/students_myisam_test.txt' fields terminated by ',' enclosed by "'" lines teerminated by '\r\n';
Query OK, 3 rows affected (0.00 sec)
[root@localhost ~]# cat /tmp/students_myisam_test.txt
'1','a','1','1'
'2','b','2','2'
'3','c','3','3'
mysql> select * from students_myisam where sid in (1,2) into outfile '/tmp/students_myisam_test2.txt' fields terminated by ',' encllosed by "'" lines terminated by '\r\n';
Query OK, 2 rows affected (0.01 sec)
[root@localhost ~]# cat /tmp/students_myisam_test2.txt
'1','a','1','1'
'2','b','2','2'
參數說明：
-
terminated by
字段以什麼字元分隔
-
enclosed by
字段以什麼字元括起來
-
escaped by
轉義字元，預設是反斜杠
-
lines
每條記錄的分隔符，預設是換行符
-
local
指定從客戶主機讀檔案，沒有指定則檔案必須在伺服器上
-
replace
新行将代替有相同的唯一值的現有行
-
ignore
跳過有唯一鍵的現有行的重複行的輸入，不指定時當遇到重複行會報錯
四、備份的重要概念
（1）鎖
在執行mysqldump時，會添加flush tables with read lock（FTWRL），用于備份時擷取一緻性備份（資料與binlog位點比對）。
由于FTWRL總共需要持有兩把全局MDL鎖，并且還需要關閉所有表對象，是以這個指令殺傷力很大，執行指令時容易導緻庫hang住。
FTWEL主要包括三個步驟：
1.上全局讀鎖（lock_global_read_lock）
導緻所有更新操作都會被堵塞
2.清理表緩存（close_cached_tables）
關閉表過程中，如果有大量查詢導緻關閉表等待，那麼所有通路該表的查詢和更新都需要等待
3.上全局commit鎖（make_global_read_lock_block_commit）
會堵塞活躍事務送出
第一個session
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.15 sec)
第二個session
mysql> select * from dept;
+----+------------------+
| id | dept_name |
+----+------------------+
| 1 | Education |
| 2 | Computer Science |
| 3 | Mathematics |
+----+------------------+
3 rows in set (0.00 sec)
mysql> update dept set dept_name="Sport" where id=1;
此時寫操作會被阻止，等待逾時
第一個session
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
flush tables with read lock與lock table read local的差別
第一個session
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update dept set dept_name="Sport" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
第二個session
執行備份操作，當存在--master-data參數時，導出成功
[root@localhost ~]# mysqldump -uroot -p -P3306 --master-data course > course.sql
當普通導出時，發生鎖等待情況
[root@localhost ~]# mysqldump -uroot -p -P3306 course > course.sql
Enter password:
在第一個session中檢視
mysql> show processlist;
（2）可重複讀隔離級别
start transaction和start transaction with consistent snapshot語句的差別
-
start transaction
是第一條語句的執行時間點，就是事務開始的時間點，第一條select語句建立一緻性讀的snapshot；
-
start transaction with consistent snapshot
是立即建立事務的一緻性讀snapshot，同時開啟事務；
第一個session
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
第二個session
mysql> select * from A;
+------+-------+
| sid | score |
+------+-------+
| 8 | 94 |
+------+-------+
1 row in set (0.01 sec)
第一個session
mysql> start transaction;
Query OK, 0 rows affected (0.04 sec)
第二個session
mysql> insert into A value(9,87);
Query OK, 1 row affected (0.07 sec)
第一個session
mysql> select * from A;
+------+-------+
| sid | score |
+------+-------+
| 8 | 94 |
| 9 | 87 |
+------+-------+
2 rows in set (0.00 sec)
可以看到session2修改後的記錄
第一個session
mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
第二個session
mysql> insert into A value(10,76);
Query OK, 1 row affected (0.08 sec)
第一個session
mysql> select * from A;
+------+-------+
| sid | score |
+------+-------+
| 8 | 94 |
| 9 | 87 |
+------+-------+
2 rows in set (0.00 sec)
不可以看到session2修改後的記錄，需要送出
說明：
start transaction執行後，事務并沒有開始，是以insert發生在session1的事務開始之前，是以可以讀到修改後的值。
start transaction with consistent snapshot已經開始了事務，是以不能讀到。
五、恢複
（1）普通恢複
導入一個備份檔案
mysql -uroot -p course < course.sql
或者
進入資料庫，并切換到執行個體下
source course.sql
（2）恢複文本檔案
- 先導入表結構
- 再導入資料檔案
資料檔案導入使用mysqlimport或是load data infile
mysqlimport -uroot -p --fields-terminated-by=, --fields-enclosed-by="'" --lines-terminated-by="\n" course /tmp/course.txt
use course;
load data infile '/tmp/course.txt' into table students fields terminated by ',' enclosed by "'" lines terminated by '\r\n';
（3）全量恢複
将備份檔案中的所有資料進行恢複，恢複完成後的資料就是生成備份的那一刻的資料狀态。
（4）基于時間點的恢複
将資料庫恢複到指定的某個時間點的狀态，通常需要依賴二進制日志将指定時間點前的所有資料庫操作都重新操作一遍。
步驟：
1.通過全量備份将資料庫恢複到上一個全量恢複的時間點
2.利用二進制日志恢複到指定時間點
開啟二進制日志
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=10
測試實驗：
mysql> alter table students add tstamp timestamp;
Query OK, 0 rows affected (0.92 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.30 sec)
mysql> insert into students(sid,sname,gender,dept_id,tstamp) values(9,'Mix',1,2,now()),(10,'Tom',0,1,now());
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.24 sec)
mysql> insert into students(sid,sname,gender,dept_id,tstamp) values(11,'Luis',-1,2,now()),(12,'Sun',0,3,now());
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into students(sid,sname,gender,dept_id,tstamp) values(13,'Martis',-1,1,now()),(14,'Oifer',1,3,now());
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.24 sec)
mysql> select * from students;
+-----+--------+--------+---------+---------------------+
| sid | sname | gender | dept_id | tstamp |
+-----+--------+--------+---------+---------------------+
| 1 | abc | 1 | 1 | 2018-03-05 14:46:41 |
| 2 | Andy | -1 | 1 | 2018-03-05 14:46:41 |
| 3 | Bob | -1 | 1 | 2018-03-05 14:46:41 |
| 4 | Ruth | -1 | 2 | 2018-03-05 14:46:41 |
| 5 | Mike | -1 | 2 | 2018-03-05 14:46:41 |
| 6 | John | 0 | 3 | 2018-03-05 14:46:41 |
| 7 | Cindy | 1 | 3 | 2018-03-05 14:46:41 |
| 8 | Susan | 1 | 3 | 2018-03-05 14:46:41 |
| 9 | Mix | 1 | 2 | 2018-03-05 14:50:04 |
| 10 | Tom | 0 | 1 | 2018-03-05 14:50:04 |
| 11 | Luis | -1 | 2 | 2018-03-05 14:51:48 |
| 12 | Sun | 0 | 3 | 2018-03-05 14:51:48 |
| 13 | Martis | -1 | 1 | 2018-03-05 14:52:27 |
| 14 | Oifer | 1 | 3 | 2018-03-05 14:52:27 |
+-----+--------+--------+---------+---------------------+
14 rows in set (0.00 sec)
mysql> truncate table students;
Query OK, 0 rows affected (0.21 sec)
mysql> select * from students;
Empty set (0.00 sec)
首先恢複students表的全量備份
mysql> source backup.sql;
mysql> select * from students;
+-----+-------+--------+---------+---------------------+
| sid | sname | gender | dept_id | tstamp |
+-----+-------+--------+---------+---------------------+
| 1 | abc | 1 | 1 | 2018-03-05 14:46:41 |
| 2 | Andy | -1 | 1 | 2018-03-05 14:46:41 |
| 3 | Bob | -1 | 1 | 2018-03-05 14:46:41 |
| 4 | Ruth | -1 | 2 | 2018-03-05 14:46:41 |
| 5 | Mike | -1 | 2 | 2018-03-05 14:46:41 |
| 6 | John | 0 | 3 | 2018-03-05 14:46:41 |
| 7 | Cindy | 1 | 3 | 2018-03-05 14:46:41 |
| 8 | Susan | 1 | 3 | 2018-03-05 14:46:41 |
+-----+-------+--------+---------+---------------------+
8 rows in set (0.01 sec)
恢複某個時間點資料
[root@localhost data]# mysqlbinlog mysql-bin.000002 | mysql -uroot -p
Enter password:
mysql> select * from students;
+-----+-------+--------+---------+---------------------+
| sid | sname | gender | dept_id | tstamp |
+-----+-------+--------+---------+---------------------+
| 1 | abc | 1 | 1 | 2018-03-05 14:46:41 |
| 2 | Andy | -1 | 1 | 2018-03-05 14:46:41 |
| 3 | Bob | -1 | 1 | 2018-03-05 14:46:41 |
| 4 | Ruth | -1 | 2 | 2018-03-05 14:46:41 |
| 5 | Mike | -1 | 2 | 2018-03-05 14:46:41 |
| 6 | John | 0 | 3 | 2018-03-05 14:46:41 |
| 7 | Cindy | 1 | 3 | 2018-03-05 14:46:41 |
| 8 | Susan | 1 | 3 | 2018-03-05 14:46:41 |
| 9 | Mix | 1 | 2 | 2018-03-05 14:50:04 |
| 10 | Tom | 0 | 1 | 2018-03-05 14:50:04 |
+-----+-------+--------+---------+---------------------+
10 rows in set (0.00 sec)
[root@localhost data]# mysqlbinlog mysql-bin.000003 | mysql -uroot -p
Enter password:
mysql> select * from students;
+-----+--------+--------+---------+---------------------+
| sid | sname | gender | dept_id | tstamp |
+-----+--------+--------+---------+---------------------+
| 1 | abc | 1 | 1 | 2018-03-05 14:46:41 |
| 2 | Andy | -1 | 1 | 2018-03-05 14:46:41 |
| 3 | Bob | -1 | 1 | 2018-03-05 14:46:41 |
| 4 | Ruth | -1 | 2 | 2018-03-05 14:46:41 |
| 5 | Mike | -1 | 2 | 2018-03-05 14:46:41 |
| 6 | John | 0 | 3 | 2018-03-05 14:46:41 |
| 7 | Cindy | 1 | 3 | 2018-03-05 14:46:41 |
| 8 | Susan | 1 | 3 | 2018-03-05 14:46:41 |
| 9 | Mix | 1 | 2 | 2018-03-05 14:50:04 |
| 10 | Tom | 0 | 1 | 2018-03-05 14:50:04 |
| 11 | Luis | -1 | 2 | 2018-03-05 14:51:48 |
| 12 | Sun | 0 | 3 | 2018-03-05 14:51:48 |
| 13 | Martis | -1 | 1 | 2018-03-05 14:52:27 |
| 14 | Oifer | 1 | 3 | 2018-03-05 14:52:27 |
+-----+--------+--------+---------+---------------------+
如果恢複某個日志檔案中的一部分内容，可以通過指定--start-datetime或是--stop-datetime參數來确定開始恢複和停止的時間。
mysqlbinlog --start-datetime="2018-02-05 10:23:41" /data1/mysql/data/mysql-bin.000001 | mysql -uroot -p
mysqlbinlog --stop-datetime="2018-03-05 15:00:00" /data1/mysql/data/mysql-bin.000001 | mysql -uroot -p
六、Xtrabackup
Xtrabackup是一個對MySQL做資料備份的工具，支援線上熱備份（備份時不影響資料讀寫）。
特點：
- 備份過程快、可靠
- 備份過程不會打斷正在執行的事務
- 能夠基于壓縮等功能節約磁盤空間和流量
- 自動實作備份檢驗
- 還原速度快
[root@localhost ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/tarball/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
[root@localhost ~]# tar -zxvf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
[root@localhost ~]# cp percona-xtrabackup-2.4.9-Linux-x86_64/bin/* /usr/bin/
全量備份
[root@localhost data1]# mkdir backup
[root@localhost backup]# xtrabackup --backup --target-dir=/data1/backup/ -uroot -p'MY@)!&sql2017' -P3306 --host=127.0.0.1
...
...
...
xtrabackup: Transaction log of lsn (11460068) to (11460077) was copied.
180305 15:14:34 completed OK!
恢複
[root@localhost mysql]# mv data data_bak
[root@localhost mysql]# ls
data_bak
[root@localhost mysql]# mkdir data
[root@localhost mysql]# chown -R mysql:mysql data
首先執行prepare，将所有的資料檔案都準備到同一時間點，因為在備份過程中所有資料檔案都在不同的時間點，如果直接恢複會導緻沖突
[root@localhost mysql]# xtrabackup --prepare --target-dir=/data1/backup/
...
...
...
InnoDB: Shutdown completed; log sequence number 11461479
180305 15:16:48 completed OK!
全量恢複
[root@localhost mysql]# xtrabackup --copy-back --target-dir=/data1/backup/ --datadir=/data1/mysql/data
...
...
...
180305 15:20:17 [01] Copying ./test1/app01.ibd to /data1/mysql/data/test1/app01.ibd
180305 15:20:17 [01] ...done
180305 15:20:17 completed OK!
[root@localhost mysql]# chown -R mysql:mysql data
增量備份
[root@localhost backup]# mkdir base
[root@localhost backup]# chown -R mysql:mysql base
[root@localhost backup]# xtrabackup --backup --traget-dir=/data1/backup/base -uroot -p'MY@)!&sql2017' -P3306 --host=127.0.0.1
mysql> insert into students values(15,'aa',1,1,now());
Query OK, 1 row affected (0.07 sec)
mysql> insert into students values(16,'bb',1,2,now());
Query OK, 1 row affected (0.09 sec)
[root@localhost ~]# xtrabackup --backup --target-dir=/data1/backup/inc1 --incremental-basedir=/data1/backup/base -uroot -p'MY@)!&sql2017' -P3306 --host=127.0.0.1
mysql> insert into students values(17,'cc',0,3,now());
Query OK, 1 row affected (0.19 sec)
mysql> insert into students values(18,'dd',-1,3,now());
Query OK, 1 row affected (0.22 sec)
[root@localhost ~]# mkdir -p /data1/backup/inc2
[root@localhost ~]# chown -R mysql:mysql /data1/backup/inc2
[root@localhost ~]# xtrabackup --backup --target-dir=/data1/backup/inc2 --incremental-basedir=/data1/backup/inc1 -uroot -p'MY@)!&sql2017' -P3306 --host=127.0.0.1
增量恢複
[root@localhost mysql]# xtrabackup --prepare --apply=log-only --target-dir=/data1/backup/base --datadir=/data1/mysql/data
[root@localhost mysql]# xtrabackup --prepare --apply-log-only --target-dir=/data1/backup/base --incremental-datadir=/data1/backup/inc1 --datadir=/data1/mysql/data
[root@localhost mysql]# xtrabackup --prepare --target-dir=/data1/backup/base --incremental-datadir=/data1/backup/inc2 --datadir=/data1/mysql/data
[root@localhost mysql]# xtrabackup --copy-back --target-dir=/data1/backup/base/ --datadir=/data1/mysql/data
mysql> select * from students;
+-----+--------+--------+---------+---------------------+
| sid | sname | gender | dept_id | tstamp |
+-----+--------+--------+---------+---------------------+
| 1 | abc | 1 | 1 | 2018-03-05 14:46:41 |
| 2 | Andy | -1 | 1 | 2018-03-05 14:46:41 |
| 3 | Bob | -1 | 1 | 2018-03-05 14:46:41 |
| 4 | Ruth | -1 | 2 | 2018-03-05 14:46:41 |
| 5 | Mike | -1 | 2 | 2018-03-05 14:46:41 |
| 6 | John | 0 | 3 | 2018-03-05 14:46:41 |
| 7 | Cindy | 1 | 3 | 2018-03-05 14:46:41 |
| 8 | Susan | 1 | 3 | 2018-03-05 14:46:41 |
| 9 | Mix | 1 | 2 | 2018-03-05 14:50:04 |
| 10 | Tom | 0 | 1 | 2018-03-05 14:50:04 |
| 11 | Luis | -1 | 2 | 2018-03-05 14:51:48 |
| 12 | Sun | 0 | 3 | 2018-03-05 14:51:48 |
| 13 | Martis | -1 | 1 | 2018-03-05 14:52:27 |
| 14 | Oifer | 1 | 3 | 2018-03-05 14:52:27 |
| 15 | aa | 1 | 1 | 2018-03-05 15:34:55 |
| 16 | bb | 1 | 2 | 2018-03-05 15:35:55 |
| 17 | cc | 0 | 3 | 2018-03-05 15:42:14 |
| 18 | dd | -1 | 3 | 2018-03-05 15:42:23 |
+-----+--------+--------+---------+---------------------+
18 rows in set (0.00 sec)
![mysqldump與innobackupex備份過程知多少（二）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)