概述

物理备份:二进制文件，不可编辑，数据库变化，数据库的一个副本（逻辑备份是SQL文件，可编辑）。

特点:

1)由数据文件和目录组成，是mysql数据目录的部拷贝/部分拷贝。

2〉速度快,文件复制

3) 备份粒度小

4）除了备份数据以外，还可以备份日志和配置文件

适合场景：最合适大数据量的备份，比如百GB,TB。

物理备份方法:

1) xtrabackup(percona公司开源免费的，早期对应的官方收费版本innobackup)

2) mysql enterprise backup(MEB/mysqlbackup),官方收费

一 xtrabackup工具介绍

目前2018年5月的版本:2.4.11,在2.3.3之前备份会产生死锁，在2.3.3之后就不再死锁了。

支持官方mysql、 Percona、MariaDB，支持在线热备，备份速度快，支持压缩，支持加密，支持自动实现备份验证，恢复速度快，支持在线迁移表，支持创建一个slave从库。支持并行，支持全备/增量备份/部分备份。

XtraBackup产品有两个工具: innobackupex（老） ,xtrbackup（新）

二 xtrabackup安装

首先查看本操作系统支持的加密协议，在下载对应的软件。

[root@mysqldb ~]# rpm -qa | grep libgcrypt

libgcrypt-1.4.5-12.el6_8.x86_64

老版本2.4.20最合适

tar -zxvf percona-xtrabackup-2.4.20-Linux-x86_64.el6.libgcrypt145.tar.gz

/mysql/app/xtrabackup/bin

vim /root/.bash_profile

vim ~/.bash_profile

PATH=$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin

source ~/.bash_profile

[root@mysqldb bin]# xtrabackup --version

xtrabackup: recognized server arguments:

xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)

三 xtrabackup的相关参数介绍

3.1xtrabackup的相关参数介绍

--apply-log-only：prepare备份的时候只执行redo阶段，用于增量备份。这个选项使在准备备份(prepare)时，只执行重做(redo)阶段，这对于增量备份非常重要。

--backup：创建备份并且放入--target-dir目录中

--close-files：不保持文件打开状态，xtrabackup打开表空间的时候通常不会关闭文件句柄，目的是为了正确处理DDL操作。如果表空间数量非常巨大并且不适合任何限制，一旦文件不在被访问的时候这个选项可以关闭文件句柄.打开这个选项会产生不一致的备份。

--compact：创建一份没有辅助索引的紧凑备份

--compress：压缩所有输出数据，包括事务日志文件和元数据文件，通过指定的压缩算法，目前唯一支持的算法是quicklz.结果文件是qpress归档格式，每个xtrabackup创建的*.qp文件都可以通过qpress程序提取或者解压缩

--compress-chunk-size=#：压缩线程工作buffer的字节大小，默认是64K

--compress-threads=#：xtrabackup进行并行数据压缩时的worker线程的数量，该选项默认值是1，并行压缩（'compress-threads'）可以和并行文件拷贝('parallel')一起使用。例如:'--parallel=4 --compress --compress-threads=2'会创建4个IO线程读取数据并通过管道传送给2个压缩线程。

--create-ib-logfile：这个选项目前还没有实现，目前创建Innodb事务日志，你还是需要prepare两次。

--datadir=DIRECTORY：backup的源目录，mysql实例的数据目录。从my.cnf中读取，或者命令行指定。

--defaults-extra-file=[MY.CNF]：在global files文件之后读取，必须在命令行的第一选项位置指定。

--defaults-file=[MY.CNF]：唯一从给定文件读取默认选项，必须是个真实文件，必须在命令行第一个选项位置指定。

--defaults-group=GROUP-NAME：从配置文件读取的组，innobakcupex多个实例部署时使用。

--export：为导出的表创建必要的文件

--extra-lsndir=DIRECTORY：(for --bakcup):在指定目录创建一份xtrabakcup_checkpoints文件的额外的备份。

--incremental-basedir=DIRECTORY：创建一份增量备份时，这个目录是增量别分的一份包含了full bakcup的Base数据集。

--incremental-dir=DIRECTORY：prepare增量备份的时候，增量备份在DIRECTORY结合full backup创建出一份新的full backup。

--incremental-force-scan：创建一份增量备份时，强制扫描所有增在备份中的数据页即使完全改变的page bitmap数据可用。

--incremetal-lsn=LSN：创建增量备份的时候指定lsn。

--innodb-log-arch-dir：指定包含归档日志的目录。只能和xtrabackup --prepare选项一起使用。

--innodb-miscellaneous：从My.cnf文件读取的一组Innodb选项。以便xtrabackup以同样的配置启动内置的Innodb。通常不需要显示指定。

--log-copy-interval=#：这个选项指定了log拷贝线程check的时间间隔（默认1秒）。

--log-stream：xtrabakcup不拷贝数据文件，将事务日志内容重定向到标准输出直到--suspend-at-end文件被删除。这个选项自动开启--suspend-at-end。

--no-defaults：不从任何选项文件中读取任何默认选项,必须在命令行第一个选项。

--databases=#：指定了需要备份的数据库和表。，格式为：--database="db1[.tb1] db2[.tb2]" 多个库之间以空格隔开，如果此选项不被指定，将会备份所有的数据库。

--database-file=#：指定包含数据库和表的文件格式为databasename1.tablename1为一个元素，一个元素一行。

--parallel=#：指定备份时拷贝多个数据文件并发的进程数，默认值为1。

--prepare：xtrabackup在一份通过--backup生成的备份执行还原操作，以便准备使用。

--print-default：打印程序参数列表并退出，必须放在命令行首位。

--print-param：使xtrabackup打印参数用来将数据文件拷贝到datadir并还原它们。

--rebuild_indexes：在apply事务日志之后重建innodb辅助索引，只有和--prepare一起才生效。

--rebuild_threads=#：在紧凑备份重建辅助索引的线程数，只有和--prepare和rebuild-index一起才生效。

--stats：xtrabakcup扫描指定数据文件并打印出索引统计。

--stream=name：将所有备份文件以指定格式流向标准输出，目前支持的格式有xbstream和tar。

--suspend-at-end：使xtrabackup在--target-dir目录中生成xtrabakcup_suspended文件。在拷贝数据文件之后xtrabackup不是退出而是继续拷贝日志文件并且等待知道xtrabakcup_suspended文件被删除。这项可以使xtrabackup和其他程序协同工作。

--tables=name：正则表达式匹配database.tablename。备份匹配的表。

--tables-file=name：指定文件，一个表名一行。

--target-dir=DIRECTORY：指定backup的目的地，如果目录不存在，xtrabakcup会创建。如果目录存在且为空则成功。不会覆盖已存在的文件。

--throttle=#：指定每秒操作读写对的数量。

--tmpdir=name：当使用--print-param指定的时候打印出正确的tmpdir参数。

--to-archived-lsn=LSN：指定prepare备份时apply事务日志的LSN，只能和xtarbackup --prepare选项一起用。

--user-memory = #：通过--prepare prepare备份时候分配多大内存，目的像innodb_buffer_pool_size。默认值100M如果你有足够大的内存。1-2G是推荐值，支持各种单位(1MB,1M,1GB,1G)。

--version：打印xtrabackup版本并退出。

--xbstream：支持同时压缩和流式化。需要客服传统归档tar,cpio和其他不允许动态streaming生成的文件的限制，例如动态压缩文件，xbstream超越其他传统流式/归档格式的的优点是，并发stream多个文件并且更紧凑的数据存储（所以可以和--parallel选项选项一起使用xbstream格式进行streaming）。

1.xtrabackup常用参数

基本命令：

--defaults-file:指明服务器的配置文件，此参数必须作为innobackupex的第一个参数，否则报错

--host:指明主机

--user:指明备份的用户名

--password:指明备份用户名的密码

--backup:指明为备份，此参数可以忽略

/tmp/backup:备份的目录

--backup:指明为备份

--apply-log:指明为重做日志,//应用 BACKUP-DIR 中的 xtrabackup_logfile 事务日志文件。一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件使得数据文件处于一致性状态。

--copy-back:指明为恢复,//拷贝先前备份所有文件到它们的原始路径。但原路径下不能有任何文件或目录，除非指定 --force-non-empty-directories 选项。

--slave-info:备份从库的show slave status信息，仅用于在备份从库时使用

--no-lock:不锁表，仅适用于存储引擎为innodb，并且不在乎备份位置点时使用

增量备份：

--incremental://这个选项告诉 xtrabackup 创建一个增量备份，而不是完全备份。它传递到 xtrabackup 子进程。当指定这个选项，可以设置 --incremental-lsn 或 --incremental-basedir。如果这2个选项都没有被指定，--incremental-basedir 传递给 xtrabackup 默认值，默认值为：基础备份目录的第一个时间戳备份目录。

--incremental-basedir:指明此次的增量备份是基于之前的哪一次备份，增量备份仅适用于innodb和xtradb引擎

--redo-only //在“准备基本完整备份” 和 “合并所有的增量备份(除了最后一个增备)”时使用此选项。它直接传递给xtrabackup的 xtrabackup --apply-log-only 选项，使xtrabackup跳过"undo"阶段，只做"redo"操作。如果后面还有增量备份应用到这个全备,这是必要的。有关详细信息,请参阅xtrabackup文档。

--incremental-dir:恢复增量备份时，指明增量备份的目录

部分备份：

--include:使用正则表达式备份匹配到的内容；例：备份mysql库中的user表，--include='^mysql[.]user'

--table-files:如果要备份多个表，可以把多个表以”数据库名.表名“的方式写到文件中，备份时指定此文件

--databases:可以备份整个库，也可以备份库中的某个表，备份表时用”库名.表名“的方式

--export:使用部分备份恢复时用此选项，这和完整备份和增量备份有所区别

高级命令：

xtrabackup在执行备份时，会等待获得全局锁，对于长时间执行的语句，会引发锁争用的问题！

--lock-wait-timeout:指明执行flush tables with read lock前的等待时间，0表示不等待直接执行锁表命令，单位是s，若超过此参数设置的时间后还存在长时间执行的查询，则xtrabackup终止运行

--lock-wait-query-type:可以为all和update；若设置为all，表示等待所有类型的语句执行完成后再执行flush tables with read lock; 若设置update，则表示等待除了select之外sql语句执行完成，再执行锁表操作

--kill-long-queries-timeout:执行flush tables with read lock后等待的秒数

--kill-long-query-type:可以为all和select; 若为all，表示超过等待的秒数，强制终止所有的查询；若为select,表示强制终止select类型的查询

四 xtrabackup备份与恢复-案例

4.1 xtrabackup备份与恢复-所有库

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --target-dir=/mysql/backup/alldb --parallel=6

4.2 破坏数据

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

4.3 准备perpar（--apply-log）

用--apply-log的作用是使数据文件处理一致状态。

执行后，xtrabackup_checkpoints文件backup_type = full-backuped 变为 full-prepare

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --prepare --user-memory=2G --target-dir=/mysql/backup/alldb/ --parallel=6

chown -R mysql:mysql /mysql/data/3306/data

service mysql restart

4.4 执行数据文件还原，需要原来的数据文件为空

rsync参数:

-a归档模式，递归方式传输文件，保持原有的文件属性。

-p 传输进度

-v 传输时的进度信息

-r递归

--exclude='xtrbackup*' 去除不用信息文件

rsync -avrp /mysql/backup/alldb/* --exclude='xtrbackup*' /mysql/data/3306/data

五 xtrabackup备份与恢复-单数据库-单表

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `syjdb` /*!40100 DEFAULT CHARACTER SET utf8 */

5.1 备份部分数据库

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --target-dir=/mysql/backup/db --databases="syj syjdb" --parallel=2

5.2 准备perpar

service mysql stop

mv /mysql/data/3306/data/syjdb /mysql/data/3306/data/syjdbbak

mkdir /mysql/data/3306/data/syjdb

chown -R mysql:mysql /mysql/data/3306/data/syjdb

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --prepare --user-memory=2G --target-dir=/mysql/backup/db/ --parallel=6

5.3 恢复数据库

cp /mysql/backup/db/ibdata* /mysql/data/3306/data/

cp /mysql/backup/db/syjdb/* /mysql/data/3306/data/syjdb

chown -R mysql:mysql /mysql/data/3306/data/

5.4 备份不同数据库的不同表

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --target-dir=/mysql/backup/tdb --databases="syj.gw syjdb.gwdb" --parallel=2

不能有空格，回车。

vim tabname

syj.gw

syjdb.gwdb

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --target-dir=/mysql/backup/txtdb --tables-file=/mysql/backup/tabname --parallel=2

5.5 准备与恢复

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --prepare --user-memory=2G --target-dir=/mysql/backup/tdb --parallel=6

cp -rp /mysql/backup/tdb/syjdb/gwdb* /mysql/data/3306/data/syjdb/

cp -rp /mysql/backup/tdb/ibdata* /mysql/data/3306/data/

chown -R mysql:mysql *

六 xtrabackup备份与恢复-增量

1 全备+业务变化

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --target-dir=/mysql/backup/alldb --parallel=2

210308 21:38:29 completed OK!

select now()

2021-03-08 21:41:49

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `syjdb` /*!40100 DEFAULT CHARACTER SET utf8 */

CREATE table syjdb.gwdb select * from syj.gw;

2 增量备份1

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --target-dir=/mysql/backup/1db --incremental-basedir=/mysql/backup/alldb --parallel=2

3 业务操作2

CREATE table syjdb.m1db select * from syj.m1;

select now()

2021-03-08 21:47:41

4 增量备份2

特别恶心，不是累积增量，特别恶心，追加增量。

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --target-dir=/mysql/backup/2db --incremental-basedir=/mysql/backup/1db --parallel=2

5 增量恢复1-恢复全备

删除数据库目录

--注意:--redo-only，只做redo，不做undo

xtrabackup --prepare --apply-log-only --target-dir=/mysql/backup/alldb/

6 增量恢复2-恢复增量至全备

xtrabackup --prepare --apply-log-only --target-dir=/mysql/backup/alldb --incremental-dir=/mysql/backup/1db/

7 增量恢复3-对最后的全备进行恢复

最后一次不需要--apply-log-only

xtrabackup --prepare --target-dir=/mysql/backup/alldb --incremental-dir=/mysql/backup/2db

8 数据一致性

xtrabackup --prepare --target-dir=/mysql/backup/alldb

9 COPY数据文件还原

--rsync是用来加速恢复的，最小化flush tables with read lock的阻塞时间，用rsync来拷贝非innodb的文件

--rsync如果有myisam的业务表,建议加上。

mkdir -p /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

rsync -avrP /mysql/backup/edb/* --exclude='xtrabackup_*' /mysql/data/3306/data/

chown -R mysql:mysql /mysql/data/3306/data

select count(*) from syjdb.m1db;

10000 OK

5.5 增量备份其他类型

个别数据库增量备份与恢复

区别:备份的时候上面是针对全库，如果是个别数据库，需要用下面的参数:--databases="syj syjdb"

表的增量备份与恢复

区别:备份的时候上面是针对表,最后一步需要手工拷文件增量上只不过袭的参数如下:

--databases="syj.i11"

--tables-file=”mysql/backup/tbname.txt"

--include=’syj.11,syjdb.12'

七 xtrabackup备份与恢复-压缩-打包-加密

7.1 压缩备份与恢复

1 压缩备份

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --compress --compress-threads=2 --backup --target-dir=/mysql/backup/alldb --parallel=3

2 破坏数据

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

3 解压与恢复

tar vxf qpress-11-linux-x64.tar

qpress

cp qpress /usr/bin/

xtrabackup --decompress --user-memory=2G --target-dir=/mysql/backup/alldb/

xtrabackup --prepare --user-memory=2G --target-dir=/mysql/backup/alldb/

rsync -avrP /mysql/backup/alldb/* --exclude='xtrabackup_*' /mysql/data/3306/data/

chown -R mysql:mysql /mysql/data/3306/data

7.2 打包备份与恢复

1 两种打包方式

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --stream=tar --target-dir=/mysql/backup/tdb | gzip >/mysql/backup/tardb.tar.gz

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --stream=xbstream --target-dir=/mysql/backup/stdb --parallel=4 1>/mysql/backup/stdb.xbstream

2 破坏数据

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

3 恢复数据

tar izxvf /mysql/backup/tardb.tar.gz -C t1

xtrabackup --prepare --target-dir=/mysql/backup/t1

mkdir -p /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

rsync -avrP /mysql/backup/t1/* --exclude='xtrabackup_*' /mysql/data/3306/data/

chown -R mysql:mysql /mysql/data/3306/data

cp -rpv stdb.xbstream /mysql/backup/stdb

xbstream -x < /mysql/backup/stdb.xbstream

xtrabackup --prepare --target-dir=

xtrabackup --prepare --target-dir=/mysql/backup/stdb/

mkdir -p /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

rsync -avrP /mysql/backup/stdb/* --exclude='xtrabackup_*' /mysql/data/3306/data/

chown -R mysql:mysql /mysql/data/3306/data

7.3 加密-解密

1 加密-备份

openssl rand -base64 24

SUbP/rYpUcE/wmEG9t+wTbtdAA5bgR3S

echo -n "SUbP/rYpUcE/wmEG9t+wTbtdAA5bgR3S" >/mysql/app/xtrabackupkeyfile

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --encrypt=AES256 --encrypt-key-file=/mysql/app/xtrabackupkeyfile --encrypt-threads=2 --backup --target-dir=/mysql/backup/edb --parallel=3

2 解密-恢复

xtrabackup --decrypt=AES256 --encrypt-key-file=/mysql/app/xtrabackupkeyfile --target-dir=/mysql/backup/edb --remove-original

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

xtrabackup --prepare --target-dir=/mysql/backup/edb/

rsync -avrP /mysql/backup/edb/* --exclude='xtrabackup_*' /mysql/data/3306/data/

八 innobackupex备份与恢复-bin日志

8.1 备份与建立增量数据

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=backup --password=backup --backup --target-dir=/mysql/backup/alldb --parallel=6

cat /mysql/backup/alldb/xtrabackup_binlog_info

binlog.000082 154

purge binary logs to 'binlog.000082';

show binlog events in 'binlog.000082';

| binlog.000082 | 4 | Format_desc | 1313306 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |

| binlog.000082 | 123 | Previous_gtids | 1313306 | 154 |

create table syjdb.dd select * from syj.dd;

create table syjdb.sales select * from syj.sales;

8.2 破坏数据与日志备份

service mysql stop

rm -rf /mysql/data/3306/data_bak

mv /mysql/data/3306/data /mysql/data/3306/data_bak

mkdir /mysql/data/3306/data

chown -R mysql:mysql /mysql/data/3306/data

8.3 恢复数据与日志恢复

xtrabackup --defaults-file=/mysql/data/3306/my.cnf --prepare --user-memory=2G --target-dir=/mysql/backup/alldb --parallel=6

xtrabackup --prepare --user-memory=2G --target-dir=/mysql/backup/alldb/

rsync -avrP /mysql/backup/alldb/* --exclude='xtrabackup_*' /mysql/data/3306/data/

chown -R mysql:mysql /mysql/data/3306/data

cd /mysql/log/3306/

mysqlbinlog binlog.000082 >82.sql

mysql -uroot -proot <82.sql

8.4 报错修改

ERROR 1153 (08S01) at line 185787: Got a packet bigger than 'max_allowed_packet' bytes

可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。

max_allowed_packet = 100M

九 innobackupex生产自动化脚本

[root@mysqldb script]# cat backup-xtrabackup-full.sh

#!/bin/bash

# script from Full backup mysql data per day!

BAKDIR=/mysql/backup/

XTRABACKUPDIR=/mysql/app/xtrabackup/bin/xtrabackup

BAKFILEPRE=xtrfullbackup

BAKFILE=$BAKDIR/$BAKFILEPRE-`date +%Y%m%d`

LOGFILE=$BAKDIR/$BAKFILEPRE-`date +%Y%m%d`.log

BAKDIR1=$BAKDIR/$BAKFILEPRE

BAKDIR2=$BAKFILE

REMOTE_HOST=192.168.1.51

REMOTE_BAKDIR=/mysql/backup/backup-db

MYCNF=/mysql/data/3306/my.cnf

BAKMYCNF=$BAKDIR/my-`date +%Y%m%d`.cnf

USER=backup

PWD=backup

PARALLEL=2

#innobackupex --defaults-file=$MYCNF --user=$USER --password=$PWD --stream=tar $BAKDIR 2> $LOGFILE |ssh $REMOTE_HOST "gzip ->$REMOTE_BAKDIR/$BAKFILE.tar.gz"

$XTRABACKUPDIR --defaults-file=$MYCNF --user=$USER --password=$PWD --backup --parallel=$PARALLEL --stream=tar --target-dir=$BAKDIR1 1> $BAKDIR2.tar 2> $LOGFILE

gzip -c $BAKDIR2.tar > $BAKDIR2.tar.gz

cp $MYCNF $BAKMYCNF

# check backup log

CHECKOK=`tail -1 $LOGFILE | grep "completed OK\!" | wc -l`

if [ $CHECKOK -ne 1 ]

then

echo "[ WARNING ] Backup failed!"

exit

#ssh $REMOTE_HOST "find $REMOTE_BAKDIR/$BAKFILEPRE* -mtime +14 -type f -maxdepth 1 | xargs rm -rf {}"

find $BAKDIR -mtime +3 -name "$BAKFILEPRE*.tar" -exec rm -f {} \;

find $BAKDIR -mtime +10 -name "$BAKFILEPRE*.gz" -exec rm -f {} \;

find $BAKDIR -mtime +10 -name "$BAKFILEPRE*.log" -exec rm -f {} \;