MySQL 备份与恢复详解

共计 16000 个字符，预计需要花费 40 分钟才能阅读完成。

备份类型可以分为物理备份和逻辑备份两种

物理备份是指通过拷贝数据库文件的方式完成备份，这种备份方式适用于数据库很大，数据重要且需要快速恢复的数据库

逻辑备份是指通过备份数据库的逻辑结构（create database/table 语句)和数据内容（insert 语句或者文本文件）的方式完成备份。这种备份方式适用于数据库不是很大，或者你
需要对导出的文件做一定的修改，又或者是希望在另外的不同类型服务器上重新建立此数据库的情况

物理备份通常要求在数据库关闭的情况下执行，但如果是在数据库运行情况下执行，则要求备份期间数据库不能修改

逻辑备份的速度要慢于物理备份，是因为 逻辑备份需要访问数据库并将内容转化成逻辑备份需要的格式 ；通常输出的备份文件大小也要比物理备份大；另外逻辑备份也不包含数据库的
配置文件和日志文件内容；备份和恢复的粒度可以是所有数据库，也可以是单个数据库，也可以是单个表；逻辑备份需要再数据库运行的状态下执行；它的执行工具可以是 MySQLdump 或者是 select … into outfile 两种方式

备份又可以分为在线备份和离线备份两种
在线备份是指在数据库运行的状态下执行的备份
而离线备份是指在数据库关闭情况下执行的备份

备份还可以分为本地备份和远程备份两种

本地备份是指备份是在和当前 MySQL 运行的相同主机上发起和执行
而离线备份是指在数据库关闭情况下执行的备份

备份还可以分为本地备份和远程备份两种
本地备份是指备份是在和当前 MySQL 运行的相同主机上发起和执行

远程备份是指备份是在和当前 MySQL 运行的不同主机上发起和执行
比如 mysqldump 命令可以连接本机 MySQL，也可以连接远程 MySQL；在比如 select …into outfile 命令可以通过本地或者远程的 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 工具执行全量备份或增量备份

对 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.*

MySQL 备份与恢复详解

对 InnoDB 表来说：即使是 innodb_file_per_table=on 的情况下直接拷贝文件也不行

把这个表相关的两个文件 students.frm，students.ibd 文件拷贝到另外的数据库实例对应的数据库目录下

MySQL 备份与恢复详解

查看错误日志信息：
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 工具用来生成 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

此处含有隐藏内容，需要正确输入密码后可见！

指定链接目标数据库的数据库密码
–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

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

MySQL 备份与恢复详解

–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

MySQL 备份与恢复详解

针对文本文件 (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 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;

MySQL 备份与恢复详解

导入过程中如果碰到唯一性约束冲突则执行替换语句：

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';`

恢复类型分为全量恢复和基于时间点恢复两种
全量恢复是指将备份文件中所有的数据都进行恢复，恢复完成之后的数据就是为生成备份的那一刻的数据状态
而基于时间点的恢复是指将数据库恢复到指定的某个时间点的状态，通常需要依赖二进制日志 将指定时间点前的所有数据库操作都重新操作一遍

基于时间点的恢复是将数据库恢复到指定的时间点状态，通常是先通过全量恢复的方式先将数据库恢复到上一个全量

恢复的时间点，然后再利用二进制日志恢复到指定的时间点
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;

MySQL 备份与恢复详解

删除表里全部数据，进行模拟恢复
第一个的 flush logs 的时候 alter table

MySQL 备份与恢复详解

插入 a,b 的那次数据的二进制日志文件

MySQL 备份与恢复详解

恢复第一个二进制日志文件：插入 a,b

mysql> truncate table students; ## 先删除表里的全部数据

mysql> desc students;

恢复第一个二进制日志文件：插入 a,b

可以通过 mysqlbinlog 查询一下二进制文件看看里面的具体内容

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

MySQL 备份与恢复详解

恢复第二个二进制日志文件：

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

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

Enter password:

MySQL 备份与恢复详解

如果是恢复某个日志文件中的一部分内容，可以通过指定—start-datetime 或者是—stop-datetime 参数来确定开始恢复时间和停止恢复时间

比如将数据库恢复到 2018-09-19 19:06:08 的时刻：
涉及到 mysql-bin.000048 文件内容

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

MySQL 备份与恢复详解

基于时间点的恢复也可以指定日志文件中的位置 ，通过设置–start-position 和–stop-position 两个参数

mysql> truncate table students;

mysql> select * from students;

Empty set (0.00 sec)

看一下二进制的具体内容

MySQL 备份与恢复详解

从一个完整的日志位置恢复或则 commit 提交以后–stop-position 位置恢复

也就是
–start-position=’298’–stop-position=’460’#只恢复某个具体的 binlog 内容
或则
–stop-posionion=’460’#恢复 460 日志位置之前的 binlog 的内容

1 2	`[root@master01 data]# mysqlbinlog -v --start-position='298'` `--stop-position='460'` `mysql-bin.000046 \| mysql -u root -p` `Enter password:`

MySQL 备份与恢复详解

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

1 2	`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`

MySQL 备份与恢复详解

恢复过程中首先要执行 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 备份与恢复详解

进行模拟数据增加，进行增量备份

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 备份与恢复详解

在加数据，进行第二次增量备份

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

MySQL 备份与恢复详解

恢复第一次插入的数据

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

MySQL 备份与恢复详解

恢复第二次插入的数据

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;

MySQL 备份与恢复详解

在准备增量备份时，必须跳过未提交事务的回滚，因为备份时未提交的事务可能正在进行，并且可能会在下一次增量备份中提交。使用–apply-log-only 选项来防止回滚阶段。
最后一次恢复的时候不用加–apply-log-only

：