MySQL-5.7 備份與恢復_ZenDei技術網路在線

一、備份分類按介質分類：物理備份指通過拷貝資料庫文件方式完成備份，適用於資料庫很大，數據重要且需要快速恢復的資料庫。邏輯備份指通過備份資料庫的邏輯結構和數據內容的方式完成備份，適用於資料庫不是很大，或需要對導出文件做一定修改，或重建此庫的情況。優缺點：物理備份速度快於邏輯備份，因為邏輯 ...

一、備份分類

按介質分類：

物理備份
指通過拷貝資料庫文件方式完成備份，適用於資料庫很大，數據重要且需要快速恢復的資料庫。
邏輯備份
指通過備份資料庫的邏輯結構和數據內容的方式完成備份，適用於資料庫不是很大，或需要對導出文件做一定修改，或重建此庫的情況。

優缺點：

物理備份速度快於邏輯備份，因為邏輯備份需要訪問資料庫並將內容轉化成邏輯備份需要的格式
物理備份的備份恢復粒度範圍是整個資料庫或單個文件，對單表是否有恢復能力取決於存儲引擎（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)