涂抹mysql笔记-数据备份和恢复

<>物理备份和逻辑备份
<>联机备份和脱机备份
<>本地备份和远程备份
<>完整备份和增量备份
<>完整恢复和增量恢复
<>复制表相关文件方式创建备份：
MyISAM引擎表关联的文件有三类：.frm .MYD .MYI只要将这些文件复制保存就相当于对表进行了备份。
MyISAM的表在备份操作前需要先持有这类对象的只读锁定：flush tables tb1_list with read lock;然后cp相关文件即可，完成后释放锁unlock tables;

<>使用mysqlhotcopy，命令行工具创建备份。只能创建本地备份，并且在联机情况下创建备份
$mysqlhotcopy -u system -p'oralinux' -S /mysql/conf/mysql.sock 5ienet /mysql/tmp flushed 5 tables with read lock('5ienet'.'t1','5ienet'.'t2','5ienet'.'t3','5ienet'.'t4','5ienet'.'t5')
mysqlhotcopy只用于MyISAM和Archive引擎的表对象。不适用InnoDB表对象。mysqlhotcopy是per编写的脚本。在使用该命令时需要先安装perl语言的运行环境以及DBI/DBD等依赖包，否则执行时可能会抛出异常。

<>使用mysqldump命令行工具创建逻辑备份：mysqldump属于逻辑备份，既可以在本地执行也可以在远端操作。
mysqldump可以备份整个mysql服务也可以只备份某几个db，或者指定只备份某个或某几个表对象，或者表中某些符合条件的记录。其输出结果集一种是表中的sql语句，一种是将数据按照指定分隔符输出成定界格式的平面文件。
[mysql@linux01 tmp]$ mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
连接相关的参数：
-u,--user：指定连接的用户名
-p,--password：指定连接用户的密码
-S,--socket：指定连接到socket文件
-h,--host：指定目标数据库的IP地址
-P,--port：指定目标数据库服务的端口
导出jason数据库中的所有对象：
[mysql@linux01 mysql]$ mysqldump -usystem -p'oralinux' -S /mysql/conf/mysql.sock jason >/mysql/backup/jason_fulldb.sql
Warning: Using a password on the command line interface can be insecure.
[mysql@linux01 mysql]$
[mysql@linux01 backup]$ ll /mysql/backup/
总用量: 158528
-rw-rw-r--. 1 mysql mysql 162329443 4月 13 16:46 jason_fulldb.sql

备份多个数据库：备份jason和5ienet库：
[mysql@linux01 backup]$ mysqldump -usystem -p'oralinux' -S /mysql/conf/mysql.sock --databases jason 5ienet >/mysql/backup/db_jason_5ienet_fulldb.sql
Warning: Using a password on the command line interface can be insecure.
[mysql@linux01 backup]$
[mysql@linux01 backup]$ ls -ltrh /mysql/backup/ |grep db_
-rw-rw-r--. 1 mysql mysql 158M 4月 13 16:52 db_jason_5ienet_fulldb.sql
全库备份：
[mysql@linux01 backup]$ mysqldump -usystem -p'oralinux' -S /mysql/conf/mysql.sock --all-databases >/mysql/backup/alldb_fulldb.sql
Warning: Using a password on the command line interface can be insecure.
[mysql@linux01 backup]$
[mysql@linux01 backup]$ ls -ltrh /mysql/backup/ |grep alldb
-rw-rw-r--. 1 mysql mysql 158M 4月 13 16:55 alldb_fulldb.sql
以上备份锁库锁表。

输出定界格式文件备份jason库：
[mysql@linux01 backup]$ mysqldump -usystem -p'oralinux' -S /mysql/conf/mysql.sock --tab=/mysql/backup/ jason
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
改为：
[mysql@linux01 backup]$ mysqldump -usystem -p'oralinux' -S /mysql/conf/mysql.sock --tab=/mysql/tmp/ jason
Warning: Using a password on the command line interface can be insecure.
[mysql@linux01 backup]$
[mysql@linux01 backup]$ ls -ltrh /mysql/tmp/
-rw-rw-rw-. 1 mysql mysql 175 4月 13 17:03 ld_csv1.txt
-rw-rw-r--. 1 mysql mysql 1.4K 4月 13 17:03 ld_csv1.sql
-rw-rw-rw-. 1 mysql mysql 71 4月 13 17:03 ld_cmd.txt
-rw-rw-r--. 1 mysql mysql 1.4K 4月 13 17:03 ld_cmd.sql
-rw-rw-r--. 1 mysql mysql 2.5K 4月 13 17:03 t_idb_big.sql
-rw-rw-rw-. 1 mysql mysql 141 4月 13 17:03 ld_sql.txt
-rw-rw-r--. 1 mysql mysql 1.4K 4月 13 17:03 ld_sql.sql
-rw-rw-rw-. 1 mysql mysql 125M 4月 13 17:03 t_idb_big.txt
-rw-rw-rw-. 1 mysql mysql 0 4月 13 17:03 users.txt
-rw-rw-r--. 1 mysql mysql 1.5K 4月 13 17:03 users.sql
-rw-rw-rw-. 1 mysql mysql 25 4月 13 17:03 test1.txt
-rw-rw-r--. 1 mysql mysql 1.3K 4月 13 17:03 test1.sql
定义界限选项：
--fields-terminated-by
--fields-enclosed-by
--fields-optionally-enclosed-by
--fields-escaped-by
--lines-terminated-by
定界格式备份表：
[mysql@linux01 tmp]$ mysqldump -usystem -p'oralinux' -S /mysql/conf/mysql.sock --tab=/mysql/tmp/ jason ld_sql --fields-terminated-by=',' --fields-enclosed-by='"'
Warning: Using a password on the command line interface can be insecure.
[mysql@linux01 tmp]$ ls -ltr /mysql/tmp/ |grep ld_sql
-rw-rw-rw-. 1 mysql mysql 173 4月 13 17:25 ld_sql.txt
-rw-rw-r--. 1 mysql mysql 1405 4月 13 17:25 ld_sql.sql
[mysql@linux01 tmp]$ cat ld_sql.txt
"1000002","景十","开封","jingshi@data.com"
"1000003","景百","洛阳","jingbai@data.com"
"1000004","景千","许昌","jingqian@data.com"
"1000005","景万","漯河","jingwan@data.com"
[mysql@linux01 tmp]$ cat ld_sql.sql
-- MySQL dump 10.13 Distrib 5.6.34, for Linux (x86_64)
--
-- Host: localhost Database: jason
-- ------------------------------------------------------
-- Server version 5.6.34-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `ld_sql`
--

DROP TABLE IF EXISTS `ld_sql`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ld_sql` (
`id` int(11) DEFAULT '0',
`username` varchar(5) DEFAULT NULL,
`city` varchar(6) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-04-13 17:25:20
[mysql@linux01 tmp]$
恢复mysqldump创建的备份集：
定界符格式恢复：
system@jason>drop table ld_sql;
Query OK, 0 rows affected (0.01 sec)
首先执行ld_sql.sql文件将表恢复到jason库中执行mysql命令如下：
[mysql@linux01 tmp]$ mysql -usystem -p'oralinux' -S /mysql/conf/mysql.sock jason </mysql/tmp/ld_sql.sql
Warning: Using a password on the command line interface can be insecure.
[mysql@linux01 tmp]$
system@jason>select * from ld_sql;
Empty set (0.00 sec)

system@jason>select * from ld_sql;
+---------+----------+------+-------------------+
| id | username | city | email |
+---------+----------+------+-------------------+
| 1000002 | 景十 | 开封 | jingshi@data.com |
| 1000003 | 景百 | 洛阳 | jingbai@data.com |
| 1000004 | 景千 | 许昌 | jingqian@data.com |
| 1000005 | 景万 | 漯河 | jingwan@data.com |
+---------+----------+------+-------------------+
4 rows in set (0.00 sec)

SQL格式恢复：
使用mysql命令行工具:mysql -usystem -p'oralinux' -S /mysql/conf/mysql.sock jason </mysql/backup/jason_fulldb.sql

使用mysql命令行工具提供的source命令：source命令不是标准的sql语句二是由mysql命令行工具提供的客户端命令，该命令的功能就是执行指定的sql脚本，用来恢复mysqldump创建的备份机真实再恰当不过了：use jason; source /mysql/backup/jason_fulldb.sql
system@jason>drop database jason;
Query OK, 6 rows affected (0.11 sec)

system@(none)>
system@(none)>create database jason;
Query OK, 1 row affected (0.00 sec)

system@(none)>use jason
Database changed
system@jason>source /mysql/backup/jason_fulldb.sql
Query OK, 5684 rows affected (0.21 sec)
Records: 5684 Duplicates: 0 Warnings: 0

Query OK, 5602 rows affected (0.25 sec)
Records: 5602 Duplicates: 0 Warnings: 0

Query OK, 5569 rows affected (0.22 sec)
Records: 5569 Duplicates: 0 Warnings: 0

Query OK, 5641 rows affected (0.25 sec)
Records: 5641 Duplicates: 0 Warnings: 0
.....
.....

Query OK, 0 rows affected (0.00 sec)

system@jason>

mysqldump常用参数：
mysqldump --help查看所有参数
-?,--help：显示帮助信息
-u,--user：指定连接的用户名
-p,--password：指定用户的密码
-S,--socket：指定socket文件连接，本地登录才会使用
-h,--host：指定连接的服务器名
-P,--port：指定连接的服务器端口
以上是连接参数。
--default-character_set：设置字符集默认是UTF8
-A,--all-databases：导出所有数据库，默认情况下information_schema库是不会被导出的。
-B,--databases：导出指定的某个或某几个数据库，这个参数导出库中数据时输出的内容包含建库脚本。只备一个库不写--databases则没有建库脚本
--tables：导出指定的表对象，参数值的格式为“库名表名”，默认其将覆盖--databases（-B）参数。
-w,--where：只导出符合条件的记录。
上面这几个参数控制导出粒度越来越细
-l,--lock-tables：锁定读取的表对象，想导出一致性备份的话最好启用本参数，不指定默认是在启用状态。
--single-transaction：对于支持对版本的存储引擎(InnoDB)在导出时会建立一致性快照，也就是该表对象导出操作将在同一个事务中。在保证导出数据的一致性的前提下，又不会阻塞其他会话的读写操作，相比--lock-tables参数来说锁粒度要细，造成的影响也要小很多，遗憾的是它只能支持有限的存储引擎。另外需要注意，指定了这个参数后其他连接不能执行alter table、drop table、rename table、truncate table这类语句， -l和--single-transaction这两个参数是最常用的。

-d,--no-data：只导出对象结构不导出数据
-t,--no-create-info：与前面的参数功能相反，只导出数据而不添加create table语句
-f,--force：即使遇到sql错误也继续执行。
-F,--flush-logs：在执行导出前先刷新日志文件，视操作场景，有可能会触发多次刷新日志文件。一般来说如果是全库导出建议先刷新日志文件，否则就不用了。

不常用参数：
-T,--tab
--fields-terminated-by
--fields-enclosed-by
--fields-optionally-enclosed-by
--fields-escaped-by
--lines-terminated-by
-e,--extended-insert
--dump-slave=[1or2]
--include-master-host-port
--master-data[=#]
-x,--lock-all-tables
-K,--disable-keys
--max-allowed-packet
--net-buffer-lenth
--no-autocommit
-n,--no-create-db
-R,routines：导出存储过程、函数等定义好的mysql程序
--flush-privileges：导出mysql库后执行一条flushprivileges语句
--ignore-table：指定的表对象不做导出，参数值得格式为db_name.tablename，注意每次只能指定一个值，如果有多个表对象都不进行导出操作的话，那就需要指定多个--ignore-table参数。

最不常用
-Y,--all-tablespaces
-y,--no-tablespaces
--log-error=name
--add-drop-database
--add-drop-table
--add-drop-trigger
--add-locks
--allow-keywords
--apply-slave-statemets
--bind-address=IPADDR
--character-sets_dir=name
-i,--comments
--comments=name
--compact
-c,--complete-insert
-C,--compress
-a,--create-option
--debug-check
--debug-info
--delayed-insert
--delete-master-logs
--hex-blob
--insert-igonre
-E,--event
--set-charset
--dump-date
--triggers
-N,--no-set-names
--opt
--skip-opt
-Q,--quote-names
-order-by-primary
--protocol
-q,--quick
--replace
-r,--result-file
--tz-utc
-v,--verbose
-V,--version
-X,--xml

<>自动化备份策略
1、常用库级备份命令：
[mysql@linux01 backup]$ mysqldump -usystem -p'oralinux' -S /mysql/conf/mysql.sock -A -R -x --default-character_set=utf8 | gzip > /mysql/backup/dbfullbak_`date +%F`.sql.gz
Warning: Using a password on the command line interface can be insecure.
[mysql@linux01 backup]$ ls -ltr |grep .gz
-rw-rw-r--. 1 mysql mysql 12749189 4月 14 09:49 dbfullbak_2017-04-14.sql.gz

这里使用了-x参数，也就是说在执行完整备份期间，数据库处于锁定状态，若希望尽量减少备份期间对前段读写请求的影响，可以考虑将-x参数替换为 --single-transaction -l两个参数。这样的话对于InnoDB引擎对象来说备份时不会影响读写，而对于MyISAM引擎对象来说尽管仍会有锁定，但也是备份哪个对象时之锁定那一个对象，影响面积会小很多。所以我们可以把语句改成：
mysqldump -usystem -p'oralinux' -S /mysql/conf/mysql.sock -A -R --single-transaction -l --default-character_set=utf8 | gzip > /mysql/backup/dbfullbak_`date +%F`.sql.gz

创建备份脚本mysql_full_backup.sh

#!/bin/sh
#Created by jason 20170414

source /mysql/scripts/mysql_env.ini

BACKUP_PATH=/mysql/backup
BACKUP_FILE=${BACKUP_PATH}/dbfullbak_`date +%F`.sql.gz
BACKUPLOG_FILE=${BACKUP_PATH}/dbfullbak_`date +%F`.log
MYSQL_PATH=/mysql/bin
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -S /mysql/conf/mysql.sock -A -R --single-transaction -l --default-character_set=utf8"

echo > $BACKUPLOG_FILE
echo -e "===Jobs started at `date +%F' '%T' '%w`===\n" >> $BACKUPLOG_FILE
echo -e "****Executed command:${MYSQL_DUMP} | gzip > $BACKUP_FILE" >> $BACKUPLOG_FILE
${MYSQL_DUMP} | gzip > $BACKUP_FILE
echo -e "****Executed finished at `date +%F' '%T' '%w` ===" >> $BACKUPLOG_FILE
echo -e "****Backup file size: `du -sh $BACKUP_FILE` ===\n" >> $BACKUPLOG_FILE
echo -e "---Find expired backup and delete those files---" >> $BACKUPLOG_FILE
for tfile in $(/usr/bin/find $BACKUP_PATH/ -mtime +6)
do
if [ -d $tfile ] ; then
rmdir $tfile
elif [ -f $tfile ] ; then
rm -f $tfile
fi
echo -e "---Delete file:$tfile---" >> $BACKUPLOG_FILE
done
echo -e "\n===Job ended at `date +%F' '%T' '%w`===\n" >> $BACKUPLOG_FILE

2.分表备份：创建mysql_full_backupbytable.sh

#!/bin/sh
#Created by jason 20170414

source /mysql/scripts/mysql_env.ini

BACKUP_PATH=/mysql/backup
BACKUP_FILE=${BACKUP_PATH}/dbfullbak_bytables_`date +%F`.sql.gz
BACKUPLOG_FILE=${BACKUP_PATH}/dbfullbak_bytables_`date +%F`.log
MYSQL_PATH=/mysql/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /mysql/conf/mysql.sock"
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -S /mysql/conf/mysql.sock --single-transaction -l --default-character_set=utf8"

echo > $BACKUPLOG_FILE
echo -e "===Jobs started at `date +%F' '%T' '%w`===\n" >> ${BACKUPLOG_FILE}

for dbs in `${MYSQL_CMD} -e "show databases" |sed '1d' |egrep -v "information_schema|mysql|performance_schema"`
do
mkdir -p ${BACKUP_PATH}/${dbs}
echo -e "****Databases:${dbs} Backup Start_Time:`date +%F' '%T' '%w` ***\n" >> ${BACKUPLOG_FILE}
for tbls in `${MYSQL_CMD} -D ${dbs} -e "show tables" |sed '1d'`
do
echo -e "###Begin ${dbs}.${tbls} Dump! Start_Time: `date +%F' '%T' '%w`" >> ${BACKUPLOG_FILE}
echo -e "Executed Command: ${MYSQL_DUMP} --tables ${dbs} ${tbls} | gzip > ${BACKUP_PATH}/${dbs}/${dbs}_${tbls}.sql.gz" >> ${BACKUPLOG_FILE}
${MYSQL_DUMP} --tables ${dbs} ${tbls} | gzip > ${BACKUP_PATH}/${dbs}/${dbs}_${tbls}.sql.gz
echo -e "###End ${dbs}.${tbls} Dump! Stop_Time: `date +%F' '%T' '%w` ===" >> ${BACKUPLOG_FILE}
echo >> ${BACKUPLOG_FILE}
done
echo -e "****Database:${dbs} Backup Stop_time:`date +%F' '%T' '%w`****" >> ${BACKUPLOG_FILE}
echo -e "****Backup file size: `du -sh ${BACKUP_PATH}/${dbs}` ***\n" >> ${BACKUPLOG_FILE}
done

echo -e "---Find expired backup and delete those files---" >> ${BACKUPLOG_FILE}
for tfile in $(/usr/bin/find ${BACKUP_PATH}/ -mtime +6)
do
if [ -d $tfile ] ; then
rmdir $tfile
elif [ -f $tfile ] ; then
rm -f $tfile
fi
echo -e "---Delete file:$tfile---" >> ${BACKUPLOG_FILE}
done
echo -e "\n===Jobs ended at `date +%F' '%T' '%w` ===\n" >> ${BACKUPLOG_FILE}

<>冷复制方式创建物理备份：停止数据库服务，将data目录复制一份到备份路径下
<>二进制日志创建增量备份
<>第三方工具创建联机备份：企业版的可以使用Mysql Enterprise Backup包含增量备份和完整备份。InnoDB表使用热备份机制，其他存储引擎表使用温备份机制备份。

冷备、增量备份和恢复策略

<>创建冷备份：
1、关闭数据库
2、备份mysql数据目录
3、启动数据库
<>创建增量备份：
增量备份及恢复通过二进制日志实现，故开启binlog。
[mysql@linux01 5ienet]$ cat /mysql/conf/my.cnf |grep binlog
log-bin=/mysql/binlog/mysql-bin
binlog_cache_size=32M
max_binlog_cache_size=512M
max_binlog_size=512M
binlog_format=mixed
查看binlog内容：mysqlbinlog /mysql/binlog/mysql-bin.000016
加载binlog内容到mysql库 mysqlbinlog /mysql/binlog/mysql-bin.000016 | mysql -usystem -p'oralinux'

mysqlbinlog常用参数：
--base64-output：控制以何种方式解析日志文件并编码输出
never：不处理row格式日志，只处理传统的基于statement格式日志。若指定本参数，并且处理包含row格式的日志就会抛出一条警告。
decode-rows：解码处理，通常会与-v即下面那个参数组合使用。
auto：按照常规方式处理，如遇到row格式日志则输出base64编码的信息，默认是auto
-v,--verbose：重组为sql语句的输出，专门用于row格式日志文件中的实践处理，若指定两次-v，那么输出信息中还会包含列的数据类型信息。

mysqlbinlog /mysql/binlog/mysql-bin.000016这种默认解析是基于row格式记录的，完全看不懂。加上 --base64-output=decode-rows -v两个参数后相同位置的输出信息就变成能够看懂的sql语句了，若我们附加两个-v参数，连列的字段类型都标示出来了。不过通过管道符交给mysql命令的话就不用--base64-output参数了。因为mysql可以识别。

mysqlbinlog --base64-output=decode-rows -v -v /mysql/binlog/mysql-bin.000016 |more

此外下列参数使用频度也较高：
--set-charset：设置字符集，它会在输出时附加SET NAMES character_set语句，以后指定恢复操作时的字符集
-d,--database：只处理与指定数据库相关的日志。
--start-datetime：指定分析的起始时间点。
--stop-datetime：指定分析的结束时间点，可以用来做精确的时间点恢复。
-j,--start-position：指定根系的其实事件位置（数据库中的每次操作在写入二进制日志时都会有个位置，若从解析过的二进制日志中来看就是#at后面的值）
--stop-position：指定根系的结束事件位置。功能与前面的指定起始结束时间点类似，只不过一个是通过时间变量控制，另一个则是指定事件位置控制。

<>备份与恢复策略

XtraBackup联机备份
<>关于XtraBackup：Percona提供。优点：
备份集高效、完整、可用
备份任务执行过程中不会阻塞事务
节省磁盘空间，降低网络带宽占用
备份集自动验证机制
恢复更快
安装包下载地址：http://www.percona.com/downloads/XtraBackup
tar -zxvf percona-xtrabackup-2.0.7-552.tar.gz -C /mysql/
mv percona-xtrabackup-2.0.7 xtrabackup
chown -R mysql:mysql xtrabackup/
在其bin下可以看到有几个命令工具：
innobackupex：由perl脚本语言编写的工具，该工具能够备份所有使用MyISAM、InnoDB、XtraDB引擎的表对象，不过其实它自身实现的主体功能是备份MyISAM，当执行该命令备份InnoDB或XtraDB引擎数据时，它会通过调用xtrabackup命令完成相关操作。
innobackupex-1.5.1 -> innobackupex
xbstream：以专用的xbstream格式压缩xtrabackup输出的信息，可以将之理解为Linux中tar命令的增强版。
xtrabackup：用于支持mysql5.1+InnoDB插件
xtrabackup_51：用于支持mysql5.0版本和mysql5.1+内置InnoDB的环境
xtrabackup_55：用于支持mysql5.5版本
xtrabackup_56：用于支持mysql5.6版本
这主要为了保持对不同mysql版本的兼容性，为了方便调用xtrabackup命令，将xtrabackup程序保存所在目录加入到mysql用户的环境变量：
echo "export PATH=/mysql/xtrabackup/bin:\$PATH" >> /home/mysql/.bash_profile
后续所有操作如非特别注明，均在mysql用户下执行。
xtrabackup必须在mysql的服务端执行（但创建的备份集不一定是保存在本地）
为了安全我们现在数据库中创建专用备份账户，并授予相应权限：
system@(none)>create user xtrabk@'localhost' identified by 'oralinux';
Query OK, 0 rows affected (0.01 sec)
system@(none)>grant reload,lock tables, replication client,super on *.* to xtrabk@'localhost';
Query OK, 0 rows affected (0.00 sec)

<>xtrabackup命令
--backup：创建备份集，用于备份
--prepare：准备备份集，用于恢复
xtrabackup备份示例：
[mysql@linux01 ~]$ xtrabackup_56 --defaults-file=/mysql/conf/my.cnf --backup --target-dir=/mysql/backup/bak_20170414
xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
InnoDB: Allocated tablespace 38, old maximum was 0
>> log scanned up to (2132094862)
[01] Copying ./ibdata1 to /mysql/backup/bak_20170414/ibdata1
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
[01] ...done
[01] Copying ./jason/ld_sql.ibd to /mysql/backup/bak_20170414/jason/ld_sql.ibd
[01] ...done
[01] Copying ./jason/ld_cmd.ibd to /mysql/backup/bak_20170414/jason/ld_cmd.ibd
[01] ...done
[01] Copying ./jason/users.ibd to /mysql/backup/bak_20170414/jason/users.ibd
[01] ...done
[01] Copying ./jason/test1.ibd to /mysql/backup/bak_20170414/jason/test1.ibd
[01] ...done
[01] Copying ./jason/t_idb_big.ibd to /mysql/backup/bak_20170414/jason/t_idb_big.ibd
>> log scanned up to (2132094862)
>> log scanned up to (2132094862)
[01] ...done
[01] Copying ./5ienet/t2.ibd to /mysql/backup/bak_20170414/5ienet/t2.ibd
[01] ...done
[01] Copying ./5ienet/t3.ibd to /mysql/backup/bak_20170414/5ienet/t3.ibd
[01] ...done
[01] Copying ./5ienet/t1.ibd to /mysql/backup/bak_20170414/5ienet/t1.ibd
[01] ...done
[01] Copying ./5ienet/t_idb1.ibd to /mysql/backup/bak_20170414/5ienet/t_idb1.ibd
[01] ...done
[01] Copying ./mysql/slave_master_info.ibd to /mysql/backup/bak_20170414/mysql/slave_master_info.ibd
[01] ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /mysql/backup/bak_20170414/mysql/innodb_table_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_worker_info.ibd to /mysql/backup/bak_20170414/mysql/slave_worker_info.ibd
[01] ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /mysql/backup/bak_20170414/mysql/innodb_index_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /mysql/backup/bak_20170414/mysql/slave_relay_log_info.ibd
[01] ...done
>> log scanned up to (2132094862)
xtrabackup: The latest check point (for incremental): '2132094862'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2132094862)

xtrabackup: Transaction log of lsn (2132094862) to (2132094862) was copied.
说明备份完成，查看生成的文件：
[mysql@linux01 bak_20170414]$ ls -ltrh /mysql/backup/bak_20170414/
总用量 2.1G
-rw-rw----. 1 mysql mysql 2.0G 4月 14 16:01 ibdata1
drwx------. 2 mysql mysql 4.0K 4月 14 16:01 jason
drwx------. 2 mysql mysql 4.0K 4月 14 16:01 5ienet
drwx------. 2 mysql mysql 4.0K 4月 14 16:01 mysql
-rw-rw----. 1 mysql mysql 2.5K 4月 14 16:01 xtrabackup_logfile
-rw-rw----. 1 mysql mysql 83 4月 14 16:01 xtrabackup_checkpoints

指定的三个参数详解：
--backup：指定当前的操作模式，创建备份集
--target-dir：指定备份集的存储路径。如果指定的路径不存在那么就自动创建。而如果目录存在，需要确保该目录为空，否则xtrabackup在备份时若发现目标路径下存在同名文件，就会抛出文件已存在的错误信息
--default-file：从mysql的选项文件中读取参数，最重要的是获取datadir参数值。xtrabackup的参数也可以放在my.cnf这样就不用每次只需都去指定参数了。如：
[xtrabackup]
target-dir=/mysql/backup
[mysql@linux01 bak_20170414]$ ls -ltrh /mysql/backup/bak_20170414/
总用量 2.1G
-rw-rw----. 1 mysql mysql 2.0G 4月 14 16:01 ibdata1
drwx------. 2 mysql mysql 4.0K 4月 14 16:01 jason
drwx------. 2 mysql mysql 4.0K 4月 14 16:01 5ienet
drwx------. 2 mysql mysql 4.0K 4月 14 16:01 mysql
-rw-rw----. 1 mysql mysql 2.5K 4月 14 16:01 xtrabackup_logfile
-rw-rw----. 1 mysql mysql 83 4月 14 16:01 xtrabackup_checkpoints
查看备份集看到这里面只包含与InnoDB引擎相关的文件，如InnoDB系统表空间（ibdata）、日志文件（ib_logfile）、InnoDB引擎表对象数据文件等，其他如MyISAM数据、用户/权限等都不在这个备份集中。甚至连InnoDB引擎表对象定义文件.frm都没有备份，所以xtrabackup不是完整的备份集，如果要创建数据库的完整备份，那就得用innobackupex命令

<>innobackupex命令
[mysql@linux01 mysql]$ innobackupex --defaults-file=/mysql/conf/my.cnf --user=xtrabk --password='oralinux' /mysql/backup/

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

170414 18:04:59 innobackupex: Starting mysql with options: --defaults-file='/mysql/conf/my.cnf' --password=xxxxxxxx --user='xtrabk' --unbuffered --
170414 18:04:59 innobackupex: Connected to database with mysql child process (pid=1384)
170414 18:05:05 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

innobackupex: Using mysql Ver 14.14 Distrib 5.6.34, for Linux (x86_64) using EditLine wrapper
Warning: Using a password on the command line interface can be insecure.
innobackupex: Using mysql server version Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /mysql/backup/2017-04-14_18-05-05
170414 18:05:05 innobackupex: Starting mysql with options: --defaults-file='/mysql/conf/my.cnf' --password=xxxxxxxx --user='xtrabk' --unbuffered --
170414 18:05:05 innobackupex: Connected to database with mysql child process (pid=1408)
170414 18:05:07 innobackupex: Connection to database server closed

170414 18:05:07 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/conf/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/mysql/backup/2017-04-14_18-05-05 --tmpdir=/mysql/tmp
innobackupex: Waiting for ibbackup (pid=1414) to suspend
innobackupex: Suspend file '/mysql/backup/2017-04-14_18-05-05/xtrabackup_suspended'

xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
InnoDB: Allocated tablespace 38, old maximum was 0
>> log scanned up to (2132120318)
[01] Copying ./ibdata1 to /mysql/backup/2017-04-14_18-05-05/ibdata1
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
[01] ...done
[01] Copying ./jason/ld_sql.ibd to /mysql/backup/2017-04-14_18-05-05/jason/ld_sql.ibd
[01] ...done
[01] Copying ./jason/ld_cmd.ibd to /mysql/backup/2017-04-14_18-05-05/jason/ld_cmd.ibd
[01] ...done
[01] Copying ./jason/t_idb1.ibd to /mysql/backup/2017-04-14_18-05-05/jason/t_idb1.ibd
[01] ...done
[01] Copying ./jason/users.ibd to /mysql/backup/2017-04-14_18-05-05/jason/users.ibd
[01] ...done
[01] Copying ./jason/j1.ibd to /mysql/backup/2017-04-14_18-05-05/jason/j1.ibd
[01] ...done
[01] Copying ./jason/test1.ibd to /mysql/backup/2017-04-14_18-05-05/jason/test1.ibd
[01] ...done
[01] Copying ./jason/t_idb_big.ibd to /mysql/backup/2017-04-14_18-05-05/jason/t_idb_big.ibd
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
[01] ...done
[01] Copying ./5ienet/t2.ibd to /mysql/backup/2017-04-14_18-05-05/5ienet/t2.ibd
[01] ...done
[01] Copying ./5ienet/t3.ibd to /mysql/backup/2017-04-14_18-05-05/5ienet/t3.ibd
[01] ...done
[01] Copying ./5ienet/t1.ibd to /mysql/backup/2017-04-14_18-05-05/5ienet/t1.ibd
[01] ...done
[01] Copying ./5ienet/t_idb1.ibd to /mysql/backup/2017-04-14_18-05-05/5ienet/t_idb1.ibd
[01] ...done
[01] Copying ./mysql/slave_master_info.ibd to /mysql/backup/2017-04-14_18-05-05/mysql/slave_master_info.ibd
[01] ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /mysql/backup/2017-04-14_18-05-05/mysql/innodb_table_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_worker_info.ibd to /mysql/backup/2017-04-14_18-05-05/mysql/slave_worker_info.ibd
[01] ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /mysql/backup/2017-04-14_18-05-05/mysql/innodb_index_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /mysql/backup/2017-04-14_18-05-05/mysql/slave_relay_log_info.ibd
[01] ...done
>> log scanned up to (2132120318)

170414 18:05:30 innobackupex: Continuing after ibbackup has suspended
170414 18:05:30 innobackupex: Starting mysql with options: --defaults-file='/mysql/conf/my.cnf' --password=xxxxxxxx --user='xtrabk' --unbuffered --
170414 18:05:30 innobackupex: Connected to database with mysql child process (pid=1425)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
170414 18:05:32 innobackupex: Starting to lock all tables...
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
>> log scanned up to (2132120318)
170414 18:05:42 innobackupex: All tables locked and flushed to disk

170414 18:05:42 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/mysql/data'
innobackupex: Backing up files '/mysql/data/jason/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (14 files)
innobackupex: Backing up files '/mysql/data/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
innobackupex: Backing up files '/mysql/data/5ienet/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (37 files)
>> log scanned up to (2132120318)
innobackupex: Backing up file '/mysql/data/jason_mc/db.opt'
innobackupex: Backing up files '/mysql/data/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
170414 18:05:43 innobackupex: Finished backing up non-InnoDB tables and files

170414 18:05:43 innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '2132120318'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2132120318)

xtrabackup: Transaction log of lsn (2132120318) to (2132120318) was copied.
170414 18:05:46 innobackupex: All tables unlocked
170414 18:05:46 innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/mysql/backup/2017-04-14_18-05-05'
innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 120
170414 18:05:46 innobackupex: completed OK!

innobackupex备份会自动创建以日期时间组合命名的目录，备份数据在其中，如果不想让innobackupex命令创建日期+时间命令的子目录，那么可以通过--no-timestamp禁用，这样innobackupex会直接将备份集保存在指定的目录。查看生成的备份集：
[mysql@linux01 2017-04-14_18-05-05]$ pwd
/mysql/backup/2017-04-14_18-05-05
[mysql@linux01 2017-04-14_18-05-05]$ ls -ltrh
总用量 2.1G
-rw-rw-r--. 1 mysql mysql 241 4月 14 18:05 backup-my.cnf
-rw-rw----. 1 mysql mysql 2.0G 4月 14 18:05 ibdata1
-rw-rw-r--. 1 mysql mysql 23 4月 14 18:05 xtrabackup_binlog_info
drwx------. 2 mysql mysql 4.0K 4月 14 18:05 jason
drwxrwxr-x. 2 mysql mysql 4.0K 4月 14 18:05 performance_schema
drwx------. 2 mysql mysql 4.0K 4月 14 18:05 5ienet
drwxrwxr-x. 2 mysql mysql 4.0K 4月 14 18:05 jason_mc
drwx------. 2 mysql mysql 4.0K 4月 14 18:05 mysql
-rw-rw----. 1 mysql mysql 2.5K 4月 14 18:05 xtrabackup_logfile
-rw-rw----. 1 mysql mysql 83 4月 14 18:05 xtrabackup_checkpoints
-rw-rw-r--. 1 mysql mysql 13 4月 14 18:05 xtrabackup_binary
可以看出文件数比xtrabackup要多，甚至连mysql/performance_schema这类系统库也创建了备份，此外还包括多个以xtrabackup开头的文件：
xtrabackup_binary：包含备份所需的二进制信息
xtrabackup_binlog_info：记录备份时的二进制日志文件位置
xtrabackup_checkpoints：记录LSN以及备份的类型
xtrabackup_logfile：备份日志文件，里面记录备份操作过程中数据库的变更
backup-my.cnf：包含备份所必需的一些初始化选项。注意一定不要以为它是mysql服务所用的my.cnf，它里面只包含备份时与InnoDB相关的一些初始化选项，而不是根据当前的mysql服务所用的my.cnf创建的备份，my.cnf建议手动备份。如果是复制环境还包括slave相关的文件。

<>创建增量备份：对于InnoDB引擎表才有增量备份，其他如MyISAM/CSV这类引擎表对象都是完整备份。这是因为InnoDB有LSN（Log Sequence Number日志序列号类似oracle的SCN）InnoDB的每个页都保存有LSN，这个序号能够标示该页最后修改时间，增量备份正是根据LSN来的。因为每次备份Xtrabackup都会在备份集中创建一个xtrabackup_checkpoints文件，这个文件中记录了最后修改的LSN序号，那么创建增量备份时只需要从上次的备份集中找到xtrabackup_checkpoints文件，读取最新的LSN，而后在创建增量时选择LSN大于这个序号的页，以及这期间产生的二进制日志就可以了。甚至不需要对比全量备份集合当前数据库的数据文件。

示例在创建增量备份前先插入或更新一批数据，更好的对比增量备份的效果：
insert into jason.t_idb1 values(3,'a');
insert into jason.t_idb1 values(4,'b');
update jason.ld_sql set id=1000050 where id=1000005;
create table jason.j3(id int,v1 varchar(20));
create table jason.j4(id int,v1 varchar(20)) engine=myisam;
创建增量备份：
[mysql@linux01 backup]$ innobackupex --defaults-file=/mysql/conf/my.cnf --user=xtrabk --password='oralinux' --incremental --incremental-basedir=/mysql/backup/2017-04-14_18-05-05 /mysql/backup/

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

170414 18:10:23 innobackupex: Starting mysql with options: --defaults-file='/mysql/conf/my.cnf' --password=xxxxxxxx --user='xtrabk' --unbuffered --
170414 18:10:23 innobackupex: Connected to database with mysql child process (pid=1675)
170414 18:10:30 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

innobackupex: Created backup directory /mysql/backup/2017-04-14_18-10-30
170414 18:10:30 innobackupex: Starting mysql with options: --defaults-file='/mysql/conf/my.cnf' --password=xxxxxxxx --user='xtrabk' --unbuffered --
170414 18:10:30 innobackupex: Connected to database with mysql child process (pid=1701)
170414 18:10:32 innobackupex: Connection to database server closed

170414 18:10:32 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/conf/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/mysql/backup/2017-04-14_18-10-30 --tmpdir=/mysql/tmp --incremental-basedir='/mysql/backup/2017-04-14_18-05-05'
innobackupex: Waiting for ibbackup (pid=1708) to suspend
innobackupex: Suspend file '/mysql/backup/2017-04-14_18-10-30/xtrabackup_suspended'

xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)
incremental backup from 2132120318 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
InnoDB: Allocated tablespace 38, old maximum was 0
>> log scanned up to (2132133402)
[01] Copying ./ibdata1 to /mysql/backup/2017-04-14_18-10-30/ibdata1.delta
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
[01] ...done
[01] Copying ./jason/ld_sql.ibd to /mysql/backup/2017-04-14_18-10-30/jason/ld_sql.ibd.delta
[01] ...done
[01] Copying ./jason/ld_cmd.ibd to /mysql/backup/2017-04-14_18-10-30/jason/ld_cmd.ibd.delta
[01] ...done
[01] Copying ./jason/t_idb1.ibd to /mysql/backup/2017-04-14_18-10-30/jason/t_idb1.ibd.delta
[01] ...done
[01] Copying ./jason/users.ibd to /mysql/backup/2017-04-14_18-10-30/jason/users.ibd.delta
[01] ...done
[01] Copying ./jason/j3.ibd to /mysql/backup/2017-04-14_18-10-30/jason/j3.ibd.delta
[01] ...done
[01] Copying ./jason/j1.ibd to /mysql/backup/2017-04-14_18-10-30/jason/j1.ibd.delta
[01] ...done
[01] Copying ./jason/test1.ibd to /mysql/backup/2017-04-14_18-10-30/jason/test1.ibd.delta
[01] ...done
>> log scanned up to (2132133402)
[01] Copying ./jason/t_idb_big.ibd to /mysql/backup/2017-04-14_18-10-30/jason/t_idb_big.ibd.delta
>> log scanned up to (2132133402)
[01] ...done
[01] Copying ./5ienet/t2.ibd to /mysql/backup/2017-04-14_18-10-30/5ienet/t2.ibd.delta
>> log scanned up to (2132133402)
[01] ...done
[01] Copying ./5ienet/t3.ibd to /mysql/backup/2017-04-14_18-10-30/5ienet/t3.ibd.delta
[01] ...done
[01] Copying ./5ienet/t1.ibd to /mysql/backup/2017-04-14_18-10-30/5ienet/t1.ibd.delta
[01] ...done
[01] Copying ./5ienet/t_idb1.ibd to /mysql/backup/2017-04-14_18-10-30/5ienet/t_idb1.ibd.delta
[01] ...done
[01] Copying ./mysql/slave_master_info.ibd to /mysql/backup/2017-04-14_18-10-30/mysql/slave_master_info.ibd.delta
[01] ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /mysql/backup/2017-04-14_18-10-30/mysql/innodb_table_stats.ibd.delta
[01] ...done
[01] Copying ./mysql/slave_worker_info.ibd to /mysql/backup/2017-04-14_18-10-30/mysql/slave_worker_info.ibd.delta
[01] ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /mysql/backup/2017-04-14_18-10-30/mysql/innodb_index_stats.ibd.delta
[01] ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /mysql/backup/2017-04-14_18-10-30/mysql/slave_relay_log_info.ibd.delta
[01] ...done
>> log scanned up to (2132133402)

170414 18:10:42 innobackupex: Continuing after ibbackup has suspended
170414 18:10:42 innobackupex: Starting mysql with options: --defaults-file='/mysql/conf/my.cnf' --password=xxxxxxxx --user='xtrabk' --unbuffered --
170414 18:10:42 innobackupex: Connected to database with mysql child process (pid=1717)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
170414 18:10:44 innobackupex: Starting to lock all tables...
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
>> log scanned up to (2132133402)
170414 18:10:54 innobackupex: All tables locked and flushed to disk

170414 18:10:54 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/mysql/data'
innobackupex: Backing up files '/mysql/data/jason/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up files '/mysql/data/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
innobackupex: Backing up files '/mysql/data/5ienet/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (37 files)
>> log scanned up to (2132133402)
innobackupex: Backing up file '/mysql/data/jason_mc/db.opt'
innobackupex: Backing up files '/mysql/data/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
170414 18:10:56 innobackupex: Finished backing up non-InnoDB tables and files

170414 18:10:56 innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '2132133402'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2132133402)

xtrabackup: Transaction log of lsn (2132133402) to (2132133402) was copied.
170414 18:10:59 innobackupex: All tables unlocked
170414 18:10:59 innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/mysql/backup/2017-04-14_18-10-30'
innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 1503
170414 18:10:59 innobackupex: completed OK!
[mysql@linux01 backup]$

--incremental：告诉xtrabackup是要创建增量备份
--incremental-basedir：指定一个全量备份的路径作为增量备份的基础。
[mysql@linux01 backup]$ cd 2017-04-14_18-10-30
[mysql@linux01 2017-04-14_18-10-30]$ pwd
/mysql/backup/2017-04-14_18-10-30
[mysql@linux01 2017-04-14_18-10-30]$ ls -lrth
总用量 716K
-rw-rw-r--. 1 mysql mysql 241 4月 14 18:10 backup-my.cnf
-rw-rw----. 1 mysql mysql 44 4月 14 18:10 ibdata1.meta
-rw-rw----. 1 mysql mysql 672K 4月 14 18:10 ibdata1.delta
-rw-rw-r--. 1 mysql mysql 24 4月 14 18:10 xtrabackup_binlog_info
drwx------. 2 mysql mysql 4.0K 4月 14 18:10 jason
drwxrwxr-x. 2 mysql mysql 4.0K 4月 14 18:10 performance_schema
drwx------. 2 mysql mysql 4.0K 4月 14 18:10 5ienet
drwxrwxr-x. 2 mysql mysql 4.0K 4月 14 18:10 jason_mc
drwx------. 2 mysql mysql 4.0K 4月 14 18:10 mysql
-rw-rw----. 1 mysql mysql 2.5K 4月 14 18:10 xtrabackup_logfile
-rw-rw----. 1 mysql mysql 90 4月 14 18:10 xtrabackup_checkpoints
-rw-rw-r--. 1 mysql mysql 13 4月 14 18:10 xtrabackup_binary
对于MyISAM引擎的表来说表对象的数据就没变，直接全部复制了一份。而对于InnoDB引擎表多了两个扩展名为table_name.delta和table_name.meta用来记录表对象的元数据，比如页大小、是否压缩过等。

Xtrabackup备份工作机制参考书中289页内容。

执行恢复：分两个步骤
准备恢复（prepare）：由于备份集没有办法直接拿来用，因为这中间可能存在未提交或未回滚的事务、数据文件不一致、缺失相关文件等故有一个队备份集做的准备的过程。对于Xtrabackup对于的参数是--prepare。对于innobackupx对应的是--apply-log
执行恢复（copy-back）：备份集准备好后就可以执行恢复了，innobackupx提供了恢复专用参数--copy-back，它的功能是将指定的备份集恢复到指定的路径下，指定的路径就是datadir参数的参数值。
关闭mysql服务，模拟data命令丢失，mv data data_bak。启动mysql发现报错：

170414 17:35:03 mysqld_safe mysqld from pid file /mysql/conf/mysql.pid ended
170414 17:35:34 mysqld_safe Starting mysqld daemon with databases from /mysql/data
2017-04-14 17:35:35 0 [Warning] Can't create test file /mysql/data/linux01.lower-test
2017-04-14 17:35:35 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2017-04-14 17:35:35 0 [Note] /mysql/bin/mysqld (mysqld 5.6.34-log) starting as process 690 ...
2017-04-14 17:35:35 690 [Warning] Can't create test file /mysql/data/linux01.lower-test
2017-04-14 17:35:35 690 [Warning] Can't create test file /mysql/data/linux01.lower-test
/mysql/bin/mysqld: Can't change dir to '/mysql/data/' (Errcode: 2 - No such file or directory)
2017-04-14 17:35:35 690 [ERROR] Aborting

2017-04-14 17:35:35 690 [Note] Binlog end
2017-04-14 17:35:35 690 [Note] /mysql/bin/mysqld: Shutdown complete

170414 17:35:35 mysqld_safe mysqld from pid file /mysql/conf/mysql.pid ended
从日志中可以看出找不到/mysql/data目录，那么我们对数据库进行恢复：
[mysql@linux01 ~]$ innobackupex --defaults-file=/mysql/conf/my.cnf --apply-log --redo-only /mysql/backup/2017-04-14_18-05-05

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".

170414 18:19:05 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/conf/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mysql/backup/2017-04-14_18-05-05 --apply-log-only --tmpdir=/mysql/tmp

xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)
xtrabackup: cd to /mysql/backup/2017-04-14_18-05-05
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(2132120318)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: CPU supports crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 2132120308 and 2132120308 in ibdata files do not match the log sequence number 2132120318 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 162364840, file name mysql-bin.000016

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 162364840, file name mysql-bin.000016

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2132120318
170414 18:19:05 innobackupex: completed OK!

--apply-log：从指定选项文件中读取配置信息并应用日志等。这就代表要做的是对备份集的恢复的准备工作若要做恢复本参数必须指定。
--redo-only：如果进行准备工作的备份操作完成后，还有其他增量备份集待处理那么就必须指定本参数。本参数会强制Xtrabackup只应用redo而不进行回滚，转换为Xtrabackup命令的话，对应的参数就是--apply-log-only，大家可以注意看前面innobackupex命令输出的信息。如果没有增量备份，那么本参数就无需指定了。

继续执行innobackupex命令应用增量备份，这次要操作的备份集就是最后一份，不需要再指定--redo-only参数了实际执行命令如下：
[mysql@linux01 ~]$ innobackupex --defaults-file=/mysql/conf/my.cnf --apply-log /mysql/backup/2017-04-14_18-05-05 --incremental-dir=/mysql/backup/2017-04-14_18-10-30

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".

170414 18:21:41 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/conf/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mysql/backup/2017-04-14_18-05-05 --incremental-dir=/mysql/backup/2017-04-14_18-10-30 --tmpdir=/mysql/tmp

xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)
incremental backup from 2132120318 is enabled.
xtrabackup: cd to /mysql/backup/2017-04-14_18-05-05
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(2132133402)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = /mysql/backup/2017-04-14_18-10-30
xtrabackup: innodb_log_files_in_group = 1
....
....
xtrabackup: page size for /mysql/backup/2017-04-14_18-10-30/mysql/innodb_index_stats.ibd.delta is 16384 bytes
Applying /mysql/backup/2017-04-14_18-10-30/mysql/innodb_index_stats.ibd.delta to ./mysql/innodb_index_stats.ibd...
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = /mysql/backup/2017-04-14_18-10-30
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: CPU supports crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 2132120308 and 2132120308 in ibdata files do not match the log sequence number 2132133402 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 984, file name mysql-bin.000017
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2017-04-14 18:21:42 7f5fa53a0700 InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-04-14 18:21:42 7f5fa53a0700 InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-04-14 18:21:42 7f5fa53a0700 InnoDB: Warning: table '5ienet/t3'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 1.2.10 started; log sequence number 2132133402

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 984, file name mysql-bin.000017

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2132134736
innobackupex: Starting to copy non-InnoDB files in '/mysql/backup/2017-04-14_18-10-30'
innobackupex: to the full backup directory '/mysql/backup/2017-04-14_18-05-05'
innobackupex: Copying '/mysql/backup/2017-04-14_18-10-30/xtrabackup_binlog_info' to '/mysql/backup/2017-04-14_18-05-05/xtrabackup_binlog_info'
innobackupex: Copying '/mysql/backup/2017-04-14_18-10-30/jason/ld_sql.frm' to '/mysql/backup/2017-04-14_18-05-05/jason/ld_sql.frm'
innobackupex: Copying '/mysql/backup/2017-04-14_18-10-30/jason/j3.frm' to '/mysql/backup/2017-04-14_18-05-05/jason/j3.frm'
innobackupex: Copying '/mysql/backup/2017-04-14_18-10-30/jason/j4.frm' to '/mysql/backup/2017-04-14_18-05-05/jason/j4.frm'
....
....
innobackupex: Copying '/mysql/backup/2017-04-14_18-10-30/mysql/help_keyword.frm' to '/mysql/backup/2017-04-14_18-05-05/mysql/help_keyword.frm'
170414 18:21:44 innobackupex: completed OK!

incremental-dir：指定要处理的增量备份集路径。
如果操作没有报错，那么增量备份的应用就成功了，之后建议再执行一遍innobackupex --apply-log:
[mysql@linux01 ~]$ innobackupex --defaults-file=/mysql/conf/my.cnf --apply-log /mysql/backup/2017-04-14_18-05-05

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".

170414 18:40:23 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/conf/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mysql/backup/2017-04-14_18-05-05 --tmpdir=/mysql/tmp

xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)
xtrabackup: cd to /mysql/backup/2017-04-14_18-05-05
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: CPU supports crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 256 MB
InnoDB: Progress in MB: 100 200
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB: 100 200
InnoDB: Setting log file ./ib_logfile2 size to 256 MB
InnoDB: Progress in MB: 100 200
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2132134736
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2017-04-14 18:40:26 7fdbef59d700 InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-04-14 18:40:26 7fdbef59d700 InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-04-14 18:40:26 7fdbef59d700 InnoDB: Warning: table '5ienet/t3'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 1.2.10 started; log sequence number 2132134924

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 984, file name mysql-bin.000017

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2132136040

170414 18:40:27 innobackupex: Restarting xtrabackup with command: xtrabackup_56 --defaults-file="/mysql/conf/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mysql/backup/2017-04-14_18-05-05 --tmpdir=/mysql/tmp
for creating ib_logfile*

xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)
xtrabackup: cd to /mysql/backup/2017-04-14_18-05-05
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: CPU supports crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2017-04-14 18:40:28 7f3b8f59d700 InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-04-14 18:40:28 7f3b8f59d700 InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 1.2.10 started; log sequence number 2132136040

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 984, file name mysql-bin.000017

第一次准备是为了是数据文件达到一致性的状态，第二次准备则是为了创建InnoDB的专用日志文件，因为这些文件不会被备份。尽管mysql在启动过程中如果发现文件不存在也会自动创建。

现在终于可以做恢复操作了恢复时有三个参数：
--defaults-file：指定初始化选项文件
--copy-back：指明接下来要做的操作时从备份路径中，将文件复制回初始化选项指定的路径下。
[backup_dir]：指定备份文件所在的路径。

恢复命令如下：
[mysql@linux01 ~]$ innobackupex --defaults-file=/mysql/conf/my.cnf --copy-back /mysql/backup/2017-04-14_18-05-05

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".

Original data directory '/mysql/data' does not exist! at /mysql/xtrabackup/bin/innobackupex line 586.

[mysql@linux01 ~]$ mkdir -p /mysql/data

[mysql@linux01 ~]$ innobackupex --defaults-file=/mysql/conf/my.cnf --copy-back /mysql/backup/2017-04-14_18-05-05

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".

Original data directory '/mysql/data' does not exist! at /mysql/xtrabackup/bin/innobackupex line 586.
[mysql@linux01 ~]$ mkdir -p /mysql/data
[mysql@linux01 ~]$
[mysql@linux01 ~]$
[mysql@linux01 ~]$ innobackupex --defaults-file=/mysql/conf/my.cnf --copy-back /mysql/backup/2017-04-14_18-05-05

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".

innobackupex: Starting to copy files in '/mysql/backup/2017-04-14_18-05-05'
innobackupex: back to original data directory '/mysql/data'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/xtrabackup_binlog_pos_innodb' to '/mysql/data/xtrabackup_binlog_pos_innodb'
innobackupex: Creating directory '/mysql/data/jason'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/jason/ld_sql.frm' to '/mysql/data/jason/ld_sql.frm'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/jason/j3.frm' to '/mysql/data/jason/j3.frm'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/jason/j4.frm' to '/mysql/data/jason/j4.frm'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/jason/ld_sql.ibd' to '/mysql/data/jason/ld_sql.ibd'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/jason/j2.frm' to '/mysql/data/jason/j2.frm'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/jason/ld_cmd.ibd' to '/mysql/data/jason/ld_cmd.ibd'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/jason/ld_csv1.CSM' to '/mysql/data/jason/ld_csv1.CSM'
...
...
'/mysql/data/mysql/time_zone_leap_second.frm'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/mysql/help_keyword.frm' to '/mysql/data/mysql/help_keyword.frm'

innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/mysql/backup/2017-04-14_18-05-05'
innobackupex: back to original InnoDB data directory '/mysql/data'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/ibdata1' to '/mysql/data/ibdata1'

innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/mysql/backup/2017-04-14_18-05-05'
innobackupex: back to '/mysql/data'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/mysql/backup/2017-04-14_18-05-05'
innobackupex: back to original InnoDB log directory '/mysql/data'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/ib_logfile2' to '/mysql/data'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/ib_logfile0' to '/mysql/data'
innobackupex: Copying '/mysql/backup/2017-04-14_18-05-05/ib_logfile1' to '/mysql/data'
innobackupex: Finished copying back files.

170414 18:45:09 innobackupex: completed OK!

恢复完成后手动复制之前备份的my.cnf到指定目录下。

<>打包和压缩备份
[mysql@linux01 ~]$ innobackupex --defaults-file=/mysql/conf/my.cnf --user=xtrabk --password='oralinux' --stream=tar /tmp |gzip > /mysql/backup/xtra_fullbackup.tar.gz

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

170414 18:58:38 innobackupex: Starting mysql with options: --defaults-file='/mysql/conf/my.cnf' --password=xxxxxxxx --user='xtrabk' --unbuffered --
170414 18:58:38 innobackupex: Connected to database with mysql child process (pid=2893)
170414 18:58:44 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

innobackupex: Created backup directory /tmp
170414 18:58:44 innobackupex: Starting mysql with options: --defaults-file='/mysql/conf/my.cnf' --password=xxxxxxxx --user='xtrabk' --unbuffered --
170414 18:58:44 innobackupex: Connected to database with mysql child process (pid=2918)
170414 18:58:46 innobackupex: Connection to database server closed

170414 18:58:46 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/conf/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/mysql/tmp --tmpdir=/mysql/tmp --stream=tar
innobackupex: Waiting for ibbackup (pid=2924) to suspend
innobackupex: Suspend file '/mysql/tmp/xtrabackup_suspended'

xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:2048M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
InnoDB: Allocated tablespace 38, old maximum was 0
>> log scanned up to (2132138109)
[01] Streaming ./ibdata1
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
[01] ...done
[01] Streaming ./jason/ld_sql.ibd
[01] ...done
[01] Streaming ./jason/ld_cmd.ibd
[01] ...done
[01] Streaming ./jason/t_idb1.ibd
[01] ...done
[01] Streaming ./jason/users.ibd
[01] ...done
[01] Streaming ./jason/j3.ibd
[01] ...done
[01] Streaming ./jason/j1.ibd
[01] ...done
[01] Streaming ./jason/test1.ibd
[01] ...done
[01] Streaming ./jason/t_idb_big.ibd
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
[01] ...done
[01] Streaming ./5ienet/t2.ibd
[01] ...done
[01] Streaming ./5ienet/t3.ibd
[01] ...done
[01] Streaming ./5ienet/t1.ibd
[01] ...done
[01] Streaming ./5ienet/t_idb1.ibd
[01] ...done
[01] Streaming ./mysql/slave_master_info.ibd
[01] ...done
[01] Streaming ./mysql/innodb_table_stats.ibd
[01] ...done
[01] Streaming ./mysql/slave_worker_info.ibd
[01] ...done
[01] Streaming ./mysql/innodb_index_stats.ibd
[01] ...done
[01] Streaming ./mysql/slave_relay_log_info.ibd
[01] ...done
>> log scanned up to (2132138109)

170414 18:59:24 innobackupex: Continuing after ibbackup has suspended
170414 18:59:24 innobackupex: Starting mysql with options: --defaults-file='/mysql/conf/my.cnf' --password=xxxxxxxx --user='xtrabk' --unbuffered --
170414 18:59:24 innobackupex: Connected to database with mysql child process (pid=2932)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
170414 18:59:26 innobackupex: Starting to lock all tables...
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
>> log scanned up to (2132138109)
170414 18:59:36 innobackupex: All tables locked and flushed to disk

170414 18:59:36 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/mysql/data'
innobackupex: Backing up files '/mysql/data/jason/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up files '/mysql/data/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
innobackupex: Backing up files '/mysql/data/5ienet/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (37 files)
>> log scanned up to (2132138109)
innobackupex: Backing up file '/mysql/data/jason_mc/db.opt'
innobackupex: Backing up files '/mysql/data/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
>> log scanned up to (2132138109)
170414 18:59:39 innobackupex: Finished backing up non-InnoDB tables and files

170414 18:59:39 innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '2132138109'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2132138109)

xtrabackup: Streaming transaction log from a temporary file...
xtrabackup: Done.
xtrabackup: Transaction log of lsn (2132138109) to (2132138109) was copied.
170414 18:59:42 innobackupex: All tables unlocked
170414 18:59:42 innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/tmp'
innobackupex: MySQL binlog position: filename 'mysql-bin.000018', position 120
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.
170414 18:59:42 innobackupex: completed OK!

注意解包时使用-i参数
[mysql@linux01 backup]$ du -sh *
3.1G 2017-04-14_18-05-05
2.8G 2017-04-14_18-10-30
44M xtra_fullbackup.tar.gz
可以对比压缩大小。

<>自动化备份脚本

[mysql@linux01 scripts]$ cat /mysql/scripts/mysql_env.ini
#set env
MYSQL_USER=system
MYSQL_PASS='oralinux'

#check parameter
if [ $# -ne 1 ]
then
HOST_PORT=3306
else
HOST_PORT=$1
fi

新建mysql_full_backup_byxtra.sh
#!/bin/sh
#Created by jason 20170414

source /mysql/scripts/mysql_env.ini

BACKUP_PATH=/mysql/backup
BACKUP_FILE=${BACKUP_PATH}/xtra_fullbak_`date +%F`.tar.gz
BACKUPLOG_FILE=${BACKUP_PATH}/xtra_fullbak_`date +%F`.log
ORI_CONF_FILE=/mysql/conf/my.cnf
NEW_CONF_FILE=$BACKUP_PATH/my_`date +%F`.cnf
MYSQL_PATH=/mysql/xtrabackup/bin
MYSQL_CMD="${MYSQL_PATH}/innobackupex --defaults-file=$ORI_CONF_FILE --user=xtrabk --password='oralinux' --stream=tar /tmp"

echo > $BACKUPLOG_FILE
echo -e "===Jobs started at `date +%F' '%T' '%w`===\n" >> $BACKUPLOG_FILE
echo -e "===First cp my.cnf file to backup directory===" >> $BACKUPLOG_FILE
/bin/cp $ORI_CONF_FILE $NEW_CONF_FILE
echo > $BACKUPLOG_FILE

echo -e "****Executed command:${MYSQL_CMD} | gzip > $BACKUP_FILE" >> $BACKUPLOG_FILE
${MYSQL_CMD} 2>> $BACKUPLOG_FILE | gzip - > $BACKUP_FILE
echo -e "****Executed finished at `date +%F' '%T' '%w` ===" >> $BACKUPLOG_FILE
echo -e "****Backup file size: `du -sh $BACKUP_FILE` ===\n" >> $BACKUPLOG_FILE
echo -e "---Find expired backup and delete those files---" >> $BACKUPLOG_FILE
for tfile in $(/usr/bin/find $BACKUP_PATH/ -mtime +6)
do
if [ -d $tfile ] ; then
rmdir $tfile
elif [ -f $tfile ] ; then
rm -f $tfile
fi
echo -e "---Delete file:$tfile---" >> $BACKUPLOG_FILE
done
echo -e "\n===Job ended at `date +%F' '%T' '%w`===\n" >> $BACKUPLOG_FILE

巴特西

涂抹mysql笔记-数据备份和恢复

最新文章

热门文章