MySQL 备份 Xtrabackup-Promise(许诺)
MySQL 备份 Xtrabackup目录
[*]
[*]Xtrabackup 1
[*]1.1 Xtrabackup 简介 1
[*]1.1 Xtrabackup 原理 1
[*]1.1.1 Xtrabackup 备份过程 1
[*]1.1.2 Xtrabackup 全量备份恢复2
[*]1.1.3 Xtrabackup 增量备份原理2
[*]1.1.4 Xtrabackup 增量备份恢复2
[*]1.1.5 安装Xtrabackup 2
[*]1.1.6 检查安装结果 2
[*]
[*]使用xtrabackup实现对MySQL的备份 3
[*]2.1 基于全量备份与恢复 3
[*]2.1.1 全量备份 3
[*]2.1.2 全量备份恢复 5
[*]2.2 基于全量备份的增量备份与恢复 11
[*]2.2.1 增量备份 11
[*]2.2.2 增量恢复 12
[*]2.3 单库备份与恢复14
[*]2.4 备份文件归档压缩 15
[*]2.4.1 压缩 15
[*]2.4.2 解压 16
[*]
1.Xtrabackup
1.1 Xtrabackup 简介
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时候不影响数据读写），是商业备份工作InnoDB hotbackup的一个很好的替代品。
特点
1.无需停止数据库进行InnoDB热备
2.增量备份MySQL
3.流压缩到传输到其他服务器
4.能比较容易的创建主从同步
5.备份MySQL时不会增大服务器负载
了解备份方式
1.热备份：读写不受影响（mysqldump --> innodb）
2.温备份：仅可以执行读操作（mysqldump --> myisam）
3.冷备份：离线备份，读写都不可用
4.逻辑备份：讲数据导出文本文件中(mysqldump)
5.物理备份：讲数据文件拷贝（xtrabackup，mysqlhotcopy）
6.完成备份：备份所有数据
7.增量备份：仅备份上次完整备份或者增量备份以来变化的数据
8.差异备份：仅备份上次完整备份以来变化的数据
1.1 Xtrabackup 原理
1.1.1 Xtrabackup 备份过程
innobackupex备份过程如下图：
备份开始时首先会开启一个后台检测进程，实时检测mysql redo的变化，一旦发现redo中有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中。之后复制innodb的数据文件和系统表空间文件ibdata1，待复制结束后，执行flush tables with read lock操作，复制.frm，MYI，MYD，等文件（执行flush tableswith read lock的目的是为了防止数据表发生DDL操作，并且在这一时刻获得binlog的位置）最后会发出unlock tables，把表设置为可读可写状态，最终停止xtrabackup_log。
1.1.2 Xtrabackup 全量备份恢复
这一阶段会启动xtrabackup内嵌的innodb实例，回放xtrabackup日志xtrabackup_log，将提交的事务信息变更应用到innodb数据/表空间，同时回滚未提交的事务(这一过程类似innodb的实例恢复）。恢复过程如下图：
1.1.3 Xtrabackup 增量备份原理
innobackupex增量备份过程中的"增量"处理，其实主要是相对innodb而言，对myisam和其他存储引擎而言，它仍然是全拷贝(全备份)"。增量"备份的过程主要是通过拷贝innodb中有变更的"页"（这些变更的数据页指的是"页"的LSN大于xtrabackup_checkpoints中给定的LSN）。增量备份是基于全备的，第一次增备的数据必须要基于上一次的全备，之后的每次增备都是基于上一次的增备，最终达到一致性的增备。增量备份的过程如下，和全备的过程很类似，区别仅在第2步。
1.1.4 Xtrabackup 增量备份恢复
和全备恢复类似，也需要两步，一是数据文件的恢复，如图4，这里的数据来源由3部分组成：全备份，增量备份和xtrabackup log。二是对未提交事务的回滚，如图所示：
1.1.5 安装Xtrabackup
安装Percona XtraBackup Percona yum资源库
yum install -y \
http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum list|grep percona
yum install percona-xtrabackup-22
1.1.6 检查安装结果
# rpm -qa|grep xtrabackup
percona-xtrabackup-22-2.2.13-1.el7.x86_64
Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
常用选项:
--host 指定主机
--user 指定用户名
--password 指定密码
--port 指定端口
--databases 指定数据库
--incremental 创建增量备份
--incremental-basedir 指定包含完全备份的目录
--incremental-dir 指定包含增量备份的目录
--apply-log 对备份进行预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
--redo-only 不回滚未提交事务
--copy-back 恢复备份目录
使用innobakupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件,这些文件会被保存至一个以时间命令的目录中。
备份的同时，innobackupex还会在备份目录中创建如下文件：
(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息;每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；
(5)backup-my.cnf —— 备份命令用到的配置选项信息；
在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。
如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：
mysql> CREATE USER 'backuser'@'localhost'> mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'backuser'@'localhost'; 回收此用户所有权限
mysql> GRANT>. TO 'baskuser'@'localhost';
授权刷新、锁定表、用户查看服务器状态
mysql> FLUSH PRIVILEGES; 刷新授权表
备份过程 :
注意：备份时需启动MySQL,恢复时需关闭MySQL,清空mysql数据目录且不能重新初始化,恢复数据后应该立即进行一次完全备份
[*]使用xtrabackup实现对MySQL的备份 2.1 基于全量备份与恢复
2.1.1 全量备份
全量备份与恢复
备份
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf /opt/
恢复
innobackupex --apply-log /opt/2017-01-05_11-04-55/
innobackupex --copy-back --defaults-file=/etc/my.cnf/opt/2017-01-05_11-04-55/
完全备份：
# mysqladmin-uroot password root
Warning: Using a password on the command line interface can be insecure.
# innobackupex --user=root --password=root --defaults-file=/etc/my.cnf /opt/
#语法解释说明：
#1.--user=root 指定备份的用户
#2.--password=root指定备份用户的密码
#3.--defaults-file=/etc/my.cnf 指定的备份数据的配置文件
#4./opt/ 指定备份后的数据保存路径
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
170105 11:04:55innobackupex: Executing a version check against the server...
170105 11:04:55innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'(using password: YES).
170105 11:04:55innobackupex: Connected to MySQL server
170105 11:04:55innobackupex: Done.
170105 11:04:55innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'(using password: YES).
170105 11:04:55innobackupex: Connected to MySQL server
170105 11:04:55innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex:Using server version 5.6.35-log
innobackupex: Created backup directory /opt/2017-01-05_11-04-55
170105 11:04:55innobackupex: Starting ibbackup with command: xtrabackup--defaults-file="/etc/my.cnf"--defaults-group="mysqld" --backup --suspend-at-end --target-dir=/opt/2017-01-05_11-04-55 --innodb_log_file_size="50331648" --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp'
innobackupex: Waiting for ibbackup (pid=25098) to suspend
innobackupex: Suspend file '/opt/2017-01-05_11-04-55/xtrabackup_suspended_2'
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision> xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
> log scanned up to (1666902)
xtrabackup: Generating a list of tablespaces
Copying ./ibdata1 to /opt/2017-01-05_11-04-55/ibdata1
...done
Copying ./mysql/innodb_table_stats.ibd to /opt/2017-01-05_11-04-55/mysql/innodb_table_stats.ibd
...done
Copying ./mysql/innodb_index_stats.ibd to /opt/2017-01-05_11-04-55/mysql/innodb_index_stats.ibd
...done
Copying ./mysql/slave_relay_log_info.ibd to /opt/2017-01-05_11-04-55/mysql/slave_relay_log_info.ibd
...done
Copying ./mysql/slave_master_info.ibd to /opt/2017-01-05_11-04-55/mysql/slave_master_info.ibd
...done
Copying ./mysql/slave_worker_info.ibd to /opt/2017-01-05_11-04-55/mysql/slave_worker_info.ibd
...done
Copying ./leco/student.ibd to /opt/2017-01-05_11-04-55/leco/student.ibd
...done
> log scanned up to (1666902)
xtrabackup: Creating suspend file '/opt/2017-01-05_11-04-55/xtrabackup_suspended_2' with pid '25098'
170105 11:04:57innobackupex: Continuing after ibbackup has suspended
170105 11:04:57innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
> log scanned up to (1666902)
170105 11:04:57innobackupex: Executing FLUSH TABLES WITH READ LOCK...
170105 11:04:57innobackupex: All tables locked and flushed to disk
170105 11:04:57innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up files '/var/lib/mysql//mysql/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
innobackupex: Backing up file '/var/lib/mysql//leco/db.opt'
innobackupex: Backing up file '/var/lib/mysql//leco/student.frm'
innobackupex: Backing up files '/var/lib/mysql//performance_schema/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
170105 11:04:58innobackupex: Finished backing up non-InnoDB tables and files
170105 11:04:58innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
170105 11:04:58innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '1666902'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1666902)
xtrabackup: Creating suspend file '/opt/2017-01-05_11-04-55/xtrabackup_log_copied' with pid '25098'
xtrabackup: Transaction log of lsn (1666902) to (1666902) was copied.
170105 11:04:59innobackupex: All tables unlocked
innobackupex: Backup created in directory '/opt/2017-01-05_11-04-55'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 430
170105 11:04:59innobackupex: Connection to database server closed
170105 11:04:59innobackupex: completed OK!
说明保存成功。
在备份的同时，备份数据会在备份目录下创建一个以当前时间为名字的目录存放备份文件。
各文件说明：
（1）xtrabackup_checkpoints —-备份类型（如完全或增量）、备份状态（如是否已经没prepare状态）和LSN（日志序列号）范围信息
每个InnoDB页（通常大小为16K）都会包含一个日志序列号，即LSN，LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表名此页面最近是如何发生改变的。
backup_type = full-backuped
from_lsn = 0
to_lsn = 2097320
last_lsn = 2097320
compact = 0
（2）xtrabackup_binlog_info —-mysql服务器当前正在使用的二进制日志及备份这一刻为二进制日志事件的位置
（3）xtrabackup_pos_innodb —-二进制日志及用于InnoDB或XtraDB表的二进制日志的当前posistion。
（4）xtrabackup_binary —-备份中用到的xtrabackup的可执行文件
（5）backup-my.cnf —- 备份命令用到的配置选项信息；
在使用innobackupex命令备份时吗还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录
innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据
还需要注意，备份数据库的用户需要具有相对应的权限，如果要使用一个最小权限的用户进行备份可以使用以下参数：
mysql> GRANT>. TO ‘bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;
在使用innobackupex进行备份
# ll /opt/
total 4
drwxr-xr-x. 5 root root 4096 Jan5 11:04 2017-01-05_11-04-55
# systemctl stop mysql
# grep datadir /etc/my.cnf
datadir=/var/lib/mysql
# cd /var/lib/mysql
# rm -rf *
2.1.2 全量备份恢复
准备一个完整备份（prepare）
在使用innobackup进行备份时候，还可以使用--no-timestamp选项来阻止命令自动创建一个时间命名的目录。如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来储存备份数据在一般情况下，在备份完成后，数据且尚不能用于恢复操作，因为备份的数据中可能包含尚未提交的事物或者已经提交但尚未同步至数据文件中的事物，因此此时数据文件仍然处于不一致状态，准备的主要作用正是通过回滚未提交的事物及同步已经提交的事物至数据文件也使得数据文件处于一致状态。
Innobackupex，命令的--apply-log选项可以实现上述功能，
# innobackupex --apply-log /opt/2017-01-05_11-04-55/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
170105 11:19:14innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
170105 11:19:14innobackupex: Starting ibbackup with command: xtrabackup--defaults-file="/opt/2017-01-05_11-04-55/backup-my.cnf"--defaults-group="mysqld" --prepare --target-dir=/opt/2017-01-05_11-04-55
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision> xtrabackup: cd to /opt/2017-01-05_11-04-55
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected:> xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M: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: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool,> InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 1663530 and 1663530 in ibdata files do not match the log sequence number 1666902 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 1451, file name mysql-bin.000001
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1666902
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 1451, file name mysql-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1666912
170105 11:19:16innobackupex: Restarting xtrabackup with command: xtrabackup--defaults-file="/opt/2017-01-05_11-04-55/backup-my.cnf"--defaults-group="mysqld" --prepare --target-dir=/opt/2017-01-05_11-04-55
for creating ib_logfile*
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision> xtrabackup: cd to /opt/2017-01-05_11-04-55
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool,> InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101>
InnoDB: Setting log file ./ib_logfile1> InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1666912
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1667084
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 1451, file name mysql-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1667094
170105 11:19:18innobackupex: completed OK!
在实现准备过程中，innobackupex通常还可以使用--user-memory选项来指定其可以使用的内存大小。默认通常是100M，如果有足够的内存使用，可以多划分一些给内存准备prepare，以提高其完成速度。
从一个完整备份中恢复数据
Innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有的数据相关文件到mysql服务器的DATADIR目录来执行恢复过程，innobackupex通过backup-my.cnf来获取DATADIR目录相关信息。
开始恢复
# innobackupex --copy-back --defaults-file=/etc/my.cnf/opt/2017-01-05_11-04-55/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
170105 11:26:34innobackupex: Starting the copy-back operation
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 '/opt/2017-01-05_11-04-55'
innobackupex: back to original data directory '/var/lib/mysql'
innobackupex: Copying '/opt/2017-01-05_11-04-55/xtrabackup_info' to '/var/lib/mysql/xtrabackup_info'
innobackupex: Copying '/opt/2017-01-05_11-04-55/xtrabackup_binlog_pos_innodb' to '/var/lib/mysql/xtrabackup_binlog_pos_innodb'
innobackupex: Creating directory '/var/lib/mysql/mysql'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/innodb_table_stats.ibd' to '/var/lib/mysql/mysql/innodb_table_stats.ibd'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/innodb_index_stats.ibd' to '/var/lib/mysql/mysql/innodb_index_stats.ibd'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/slave_relay_log_info.ibd' to '/var/lib/mysql/mysql/slave_relay_log_info.ibd'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/slave_master_info.ibd' to '/var/lib/mysql/mysql/slave_master_info.ibd'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/slave_worker_info.ibd' to '/var/lib/mysql/mysql/slave_worker_info.ibd'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/db.frm' to '/var/lib/mysql/mysql/db.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/db.MYI' to '/var/lib/mysql/mysql/db.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/db.MYD' to '/var/lib/mysql/mysql/db.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/user.frm' to '/var/lib/mysql/mysql/user.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/user.MYI' to '/var/lib/mysql/mysql/user.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/user.MYD' to '/var/lib/mysql/mysql/user.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/func.frm' to '/var/lib/mysql/mysql/func.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/func.MYI' to '/var/lib/mysql/mysql/func.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/func.MYD' to '/var/lib/mysql/mysql/func.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/plugin.frm' to '/var/lib/mysql/mysql/plugin.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/plugin.MYI' to '/var/lib/mysql/mysql/plugin.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/plugin.MYD' to '/var/lib/mysql/mysql/plugin.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/servers.frm' to '/var/lib/mysql/mysql/servers.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/servers.MYI' to '/var/lib/mysql/mysql/servers.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/servers.MYD' to '/var/lib/mysql/mysql/servers.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/tables_priv.frm' to '/var/lib/mysql/mysql/tables_priv.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/tables_priv.MYI' to '/var/lib/mysql/mysql/tables_priv.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/tables_priv.MYD' to '/var/lib/mysql/mysql/tables_priv.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/columns_priv.frm' to '/var/lib/mysql/mysql/columns_priv.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/columns_priv.MYI' to '/var/lib/mysql/mysql/columns_priv.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/columns_priv.MYD' to '/var/lib/mysql/mysql/columns_priv.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_topic.frm' to '/var/lib/mysql/mysql/help_topic.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_topic.MYI' to '/var/lib/mysql/mysql/help_topic.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_topic.MYD' to '/var/lib/mysql/mysql/help_topic.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_category.frm' to '/var/lib/mysql/mysql/help_category.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_category.MYI' to '/var/lib/mysql/mysql/help_category.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_category.MYD' to '/var/lib/mysql/mysql/help_category.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_relation.frm' to '/var/lib/mysql/mysql/help_relation.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_relation.MYI' to '/var/lib/mysql/mysql/help_relation.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_relation.MYD' to '/var/lib/mysql/mysql/help_relation.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_keyword.frm' to '/var/lib/mysql/mysql/help_keyword.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_keyword.MYI' to '/var/lib/mysql/mysql/help_keyword.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/help_keyword.MYD' to '/var/lib/mysql/mysql/help_keyword.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_name.frm' to '/var/lib/mysql/mysql/time_zone_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_name.MYI' to '/var/lib/mysql/mysql/time_zone_name.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_name.MYD' to '/var/lib/mysql/mysql/time_zone_name.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone.frm' to '/var/lib/mysql/mysql/time_zone.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone.MYI' to '/var/lib/mysql/mysql/time_zone.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone.MYD' to '/var/lib/mysql/mysql/time_zone.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_transition.frm' to '/var/lib/mysql/mysql/time_zone_transition.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_transition.MYI' to '/var/lib/mysql/mysql/time_zone_transition.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_transition.MYD' to '/var/lib/mysql/mysql/time_zone_transition.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_transition_type.frm' to '/var/lib/mysql/mysql/time_zone_transition_type.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_transition_type.MYI' to '/var/lib/mysql/mysql/time_zone_transition_type.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_transition_type.MYD' to '/var/lib/mysql/mysql/time_zone_transition_type.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_leap_second.frm' to '/var/lib/mysql/mysql/time_zone_leap_second.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_leap_second.MYI' to '/var/lib/mysql/mysql/time_zone_leap_second.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/time_zone_leap_second.MYD' to '/var/lib/mysql/mysql/time_zone_leap_second.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/proc.frm' to '/var/lib/mysql/mysql/proc.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/proc.MYI' to '/var/lib/mysql/mysql/proc.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/proc.MYD' to '/var/lib/mysql/mysql/proc.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/procs_priv.frm' to '/var/lib/mysql/mysql/procs_priv.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/procs_priv.MYI' to '/var/lib/mysql/mysql/procs_priv.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/procs_priv.MYD' to '/var/lib/mysql/mysql/procs_priv.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/general_log.frm' to '/var/lib/mysql/mysql/general_log.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/general_log.CSM' to '/var/lib/mysql/mysql/general_log.CSM'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/general_log.CSV' to '/var/lib/mysql/mysql/general_log.CSV'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/slow_log.frm' to '/var/lib/mysql/mysql/slow_log.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/slow_log.CSM' to '/var/lib/mysql/mysql/slow_log.CSM'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/slow_log.CSV' to '/var/lib/mysql/mysql/slow_log.CSV'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/event.frm' to '/var/lib/mysql/mysql/event.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/event.MYI' to '/var/lib/mysql/mysql/event.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/event.MYD' to '/var/lib/mysql/mysql/event.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/ndb_binlog_index.frm' to '/var/lib/mysql/mysql/ndb_binlog_index.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/ndb_binlog_index.MYI' to '/var/lib/mysql/mysql/ndb_binlog_index.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/ndb_binlog_index.MYD' to '/var/lib/mysql/mysql/ndb_binlog_index.MYD'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/innodb_table_stats.frm' to '/var/lib/mysql/mysql/innodb_table_stats.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/innodb_index_stats.frm' to '/var/lib/mysql/mysql/innodb_index_stats.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/slave_relay_log_info.frm' to '/var/lib/mysql/mysql/slave_relay_log_info.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/slave_master_info.frm' to '/var/lib/mysql/mysql/slave_master_info.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/slave_worker_info.frm' to '/var/lib/mysql/mysql/slave_worker_info.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/proxies_priv.frm' to '/var/lib/mysql/mysql/proxies_priv.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/proxies_priv.MYI' to '/var/lib/mysql/mysql/proxies_priv.MYI'
innobackupex: Copying '/opt/2017-01-05_11-04-55/mysql/proxies_priv.MYD' to '/var/lib/mysql/mysql/proxies_priv.MYD'
innobackupex: Creating directory '/var/lib/mysql/leco'
innobackupex: Copying '/opt/2017-01-05_11-04-55/leco/student.ibd' to '/var/lib/mysql/leco/student.ibd'
innobackupex: Copying '/opt/2017-01-05_11-04-55/leco/db.opt' to '/var/lib/mysql/leco/db.opt'
innobackupex: Copying '/opt/2017-01-05_11-04-55/leco/student.frm' to '/var/lib/mysql/leco/student.frm'
innobackupex: Creating directory '/var/lib/mysql/performance_schema'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/db.opt' to '/var/lib/mysql/performance_schema/db.opt'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/cond_instances.frm' to '/var/lib/mysql/performance_schema/cond_instances.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_waits_current.frm' to '/var/lib/mysql/performance_schema/events_waits_current.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_waits_history.frm' to '/var/lib/mysql/performance_schema/events_waits_history.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_waits_history_long.frm' to '/var/lib/mysql/performance_schema/events_waits_history_long.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_waits_summary_by_instance.frm' to '/var/lib/mysql/performance_schema/events_waits_summary_by_instance.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_waits_summary_by_host_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_waits_summary_by_host_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_waits_summary_by_user_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_waits_summary_by_user_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_waits_summary_by_account_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_waits_summary_by_account_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_waits_summary_by_thread_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_waits_summary_by_thread_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_waits_summary_global_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_waits_summary_global_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/file_instances.frm' to '/var/lib/mysql/performance_schema/file_instances.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/file_summary_by_event_name.frm' to '/var/lib/mysql/performance_schema/file_summary_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/file_summary_by_instance.frm' to '/var/lib/mysql/performance_schema/file_summary_by_instance.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/socket_instances.frm' to '/var/lib/mysql/performance_schema/socket_instances.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/socket_summary_by_instance.frm' to '/var/lib/mysql/performance_schema/socket_summary_by_instance.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/socket_summary_by_event_name.frm' to '/var/lib/mysql/performance_schema/socket_summary_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/host_cache.frm' to '/var/lib/mysql/performance_schema/host_cache.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/mutex_instances.frm' to '/var/lib/mysql/performance_schema/mutex_instances.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/objects_summary_global_by_type.frm' to '/var/lib/mysql/performance_schema/objects_summary_global_by_type.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/performance_timers.frm' to '/var/lib/mysql/performance_schema/performance_timers.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/rwlock_instances.frm' to '/var/lib/mysql/performance_schema/rwlock_instances.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/setup_actors.frm' to '/var/lib/mysql/performance_schema/setup_actors.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/setup_consumers.frm' to '/var/lib/mysql/performance_schema/setup_consumers.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/setup_instruments.frm' to '/var/lib/mysql/performance_schema/setup_instruments.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/setup_objects.frm' to '/var/lib/mysql/performance_schema/setup_objects.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/setup_timers.frm' to '/var/lib/mysql/performance_schema/setup_timers.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/table_io_waits_summary_by_index_usage.frm' to '/var/lib/mysql/performance_schema/table_io_waits_summary_by_index_usage.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/table_io_waits_summary_by_table.frm' to '/var/lib/mysql/performance_schema/table_io_waits_summary_by_table.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/table_lock_waits_summary_by_table.frm' to '/var/lib/mysql/performance_schema/table_lock_waits_summary_by_table.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/threads.frm' to '/var/lib/mysql/performance_schema/threads.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_stages_current.frm' to '/var/lib/mysql/performance_schema/events_stages_current.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_stages_history.frm' to '/var/lib/mysql/performance_schema/events_stages_history.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_stages_history_long.frm' to '/var/lib/mysql/performance_schema/events_stages_history_long.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_stages_summary_by_thread_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_stages_summary_by_thread_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_stages_summary_by_host_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_stages_summary_by_host_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_stages_summary_by_user_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_stages_summary_by_user_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_stages_summary_by_account_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_stages_summary_by_account_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_stages_summary_global_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_stages_summary_global_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_statements_current.frm' to '/var/lib/mysql/performance_schema/events_statements_current.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_statements_history.frm' to '/var/lib/mysql/performance_schema/events_statements_history.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_statements_history_long.frm' to '/var/lib/mysql/performance_schema/events_statements_history_long.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_statements_summary_by_thread_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_statements_summary_by_thread_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_statements_summary_by_host_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_statements_summary_by_host_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_statements_summary_by_user_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_statements_summary_by_user_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_statements_summary_by_account_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_statements_summary_by_account_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_statements_summary_global_by_event_name.frm' to '/var/lib/mysql/performance_schema/events_statements_summary_global_by_event_name.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/hosts.frm' to '/var/lib/mysql/performance_schema/hosts.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/users.frm' to '/var/lib/mysql/performance_schema/users.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/accounts.frm' to '/var/lib/mysql/performance_schema/accounts.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/events_statements_summary_by_digest.frm' to '/var/lib/mysql/performance_schema/events_statements_summary_by_digest.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/session_connect_attrs.frm' to '/var/lib/mysql/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/opt/2017-01-05_11-04-55/performance_schema/session_account_connect_attrs.frm' to '/var/lib/mysql/performance_schema/session_account_connect_attrs.frm'
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/opt/2017-01-05_11-04-55'
innobackupex: back to original InnoDB data directory '/var/lib/mysql'
innobackupex: Copying '/opt/2017-01-05_11-04-55/ibdata1' to '/var/lib/mysql/ibdata1'
innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/opt/2017-01-05_11-04-55'
innobackupex: back to '/var/lib/mysql'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/opt/2017-01-05_11-04-55'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Copying '/opt/2017-01-05_11-04-55/ib_logfile1' to '/var/lib/mysql/ib_logfile1'
innobackupex: Copying '/opt/2017-01-05_11-04-55/ib_logfile0' to '/var/lib/mysql/ib_logfile0'
innobackupex: Finished copying back files.
170105 11:26:35innobackupex: completed OK!
恢复成功。
# ll
total 110608
-rw-r--r--. 1 root root 12582912 Jan5 11:26 ibdata1
-rw-r--r--. 1 root root 50331648 Jan5 11:26 ib_logfile0
-rw-r--r--. 1 root root 50331648 Jan5 11:26 ib_logfile1
drwxr-xr-x. 2 root root 55 Jan5 11:26 leco
drwxr-xr-x. 2 root root 4096 Jan5 11:26 mysql
drwxr-xr-x. 2 root root 4096 Jan5 11:26 performance_schema
-rw-r--r--. 1 root root 22 Jan5 11:26 xtrabackup_binlog_pos_innodb
-rw-r--r--. 1 root root 584 Jan5 11:26 xtrabackup_info
#此时数据恢复到DATADIR目录后，还要确保所有数据文件属性为正确用户和组，如MySQL，否则启动不了。
# chown -R mysql.mysql .
# ll
total 110608
-rw-r--r--. 1 mysql mysql 12582912 Jan5 11:26 ibdata1
-rw-r--r--. 1 mysql mysql 50331648 Jan5 11:26 ib_logfile0
-rw-r--r--. 1 mysql mysql 50331648 Jan5 11:26 ib_logfile1
drwxr-xr-x. 2 mysql mysql 55 Jan5 11:26 leco
drwxr-xr-x. 2 mysql mysql 4096 Jan5 11:26 mysql
drwxr-xr-x. 2 mysql mysql 4096 Jan5 11:26 performance_schema
-rw-r--r--. 1 mysql mysql 22 Jan5 11:26 xtrabackup_binlog_pos_innodb
-rw-r--r--. 1 mysql mysql 584 Jan5 11:26 xtrabackup_info
# systemctlstart mysql
# ll
total 110620
-rw-rw----. 1 mysql mysql 56 Jan5 11:28 auto.cnf
-rw-r--r--. 1 mysql mysql 12582912 Jan5 11:28 ibdata1
-rw-r--r--. 1 mysql mysql 50331648 Jan5 11:28 ib_logfile0
-rw-r--r--. 1 mysql mysql 50331648 Jan5 11:26 ib_logfile1
drwxr-xr-x. 2 mysql mysql 55 Jan5 11:26 leco
drwxr-xr-x. 2 mysql mysql 4096 Jan5 11:26 mysql
-rw-rw----. 1 mysql mysql 120 Jan5 11:28 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 Jan5 11:28 mysql-bin.index
srwxrwxrwx. 1 mysql mysql 0 Jan5 11:28 mysql.sock
drwxr-xr-x. 2 mysql mysql 4096 Jan5 11:26 performance_schema
-rw-r--r--. 1 mysql mysql 22 Jan5 11:26 xtrabackup_binlog_pos_innodb
-rw-r--r--. 1 mysql mysql 584 Jan5 11:26 xtrabackup_info
登陆测试看看没有没恢复过来
# mysql -uroot -proot
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection> Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| leco |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.02 sec)
mysql> use leco;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_leco |
+----------------+
| student |
+----------------+
1 row in set (0.02 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|> | name| char(20) | NO | | NULL | |
| age | tinyint(2)| NO | | 0 | |
| dept| varchar(16) | YES| | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> select * from student;
+----+------+-----+------+
|> +----+------+-----+------+
|1 | good |15 | NULL |
|2 | hehe |18 | NULL |
|1 | good |15 | NULL |
|2 | hehe |18 | NULL |
+----+------+-----+------+
4 rows in set (0.02 sec)
到现在全备已经测试OK
先插入几个数据，为增量备份做准备
mysql> insert into student(id,name,age) values(2,'beijing',100);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student(id,name,age) values(2,'cmz',30);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+---------+-----+------+
|> +----+---------+-----+------+
|1 | good |15 | NULL |
|2 | hehe |18 | NULL |
|1 | good |15 | NULL |
|2 | hehe |18 | NULL |
|2 | beijing | 100 | NULL |
|2 | cmz |30 | NULL |
+----+---------+-----+------+
6 rows in set (0.00 sec)
2.2 基于全量备份的增量备份与恢复
2.2.1 增量备份
每个InnoDB的页面都会包含一个LSN信息，每当相关数据发生改变，相关页面的LSN也会自动增长，这正是InnoDB表尅有进行增量备份的基础，即innobackupex通过上次完全备份之后发生改变的页面来实现。在进行增量备份时，首先要进行一次全量备份，第一次增量备份是基于全备的，之后的增量备份是基于上一次的增量备份，以此类推。
要先实现第一次增量备份。可以实现下命令进行
基于全量备份的增量备份与恢复
做一次增量备份（基于当前最新的全量备份）
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental /opt/ --incremental-basedir=/opt/2017-01-05_11-04-55/
[*]准备基于全量 innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /opt/2017-01-05_11-04-55/
[*]准备基于增量 innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /opt/2017-01-05_11-04-55/ --incremental-dir=/opt/2017-01-05_11-44-22/
[*]恢复 innobackupex --copy-back --defaults-file=/etc/my.cnf /opt/2017-01-05_11-04-55/
解释：
1.2017-01-05_11-04-55指的是完全备份所在的目录。
2.2017-01-05_11-44-22指定是第一次基于2017-01-05_11-04-55增量备份的目录，其他类似以此类推，即如果有多次增量备份。每一次都要执行如上操作。
# innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental /opt/ --incremental-basedir=/opt/2017-01-05_11-04-55/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
170105 11:44:22innobackupex: Executing a version check against the server...
170105 11:44:22innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'(using password: YES).
170105 11:44:22innobackupex: Connected to MySQL server
170105 11:44:22innobackupex: Done.
170105 11:44:22innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'(using password: YES).
170105 11:44:22innobackupex: Connected to MySQL server
170105 11:44:22innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex:Using server version 5.6.35-log
innobackupex: Created backup directory /opt/2017-01-05_11-44-22
170105 11:44:22innobackupex: Starting ibbackup with command: xtrabackup--defaults-file="/etc/my.cnf"--defaults-group="mysqld" --backup --suspend-at-end --target-dir=/opt/2017-01-05_11-44-22 --innodb_log_file_size="50331648" --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='/opt/2017-01-05_11-04-55/'
innobackupex: Waiting for ibbackup (pid=27861) to suspend
innobackupex: Suspend file '/opt/2017-01-05_11-44-22/xtrabackup_suspended_2'
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision> incremental backup from 1666902 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
> log scanned up to (1669207)
xtrabackup: Generating a list of tablespaces
xtrabackup: using the full scan for incremental backup
Copying ./ibdata1 to /opt/2017-01-05_11-44-22/ibdata1.delta
...done
Copying ./mysql/innodb_table_stats.ibd to /opt/2017-01-05_11-44-22/mysql/innodb_table_stats.ibd.delta
...done
Copying ./mysql/innodb_index_stats.ibd to /opt/2017-01-05_11-44-22/mysql/innodb_index_stats.ibd.delta
...done
Copying ./mysql/slave_relay_log_info.ibd to /opt/2017-01-05_11-44-22/mysql/slave_relay_log_info.ibd.delta
...done
Copying ./mysql/slave_master_info.ibd to /opt/2017-01-05_11-44-22/mysql/slave_master_info.ibd.delta
...done
Copying ./mysql/slave_worker_info.ibd to /opt/2017-01-05_11-44-22/mysql/slave_worker_info.ibd.delta
...done
Copying ./leco/student.ibd to /opt/2017-01-05_11-44-22/leco/student.ibd.delta
...done
> log scanned up to (1669207)
xtrabackup: Creating suspend file '/opt/2017-01-05_11-44-22/xtrabackup_suspended_2' with pid '27861'
170105 11:44:24innobackupex: Continuing after ibbackup has suspended
170105 11:44:24innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
> log scanned up to (1669207)
170105 11:44:24innobackupex: Executing FLUSH TABLES WITH READ LOCK...
170105 11:44:24innobackupex: All tables locked and flushed to disk
170105 11:44:24innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up files '/var/lib/mysql//mysql/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
innobackupex: Backing up file '/var/lib/mysql//leco/db.opt'
innobackupex: Backing up file '/var/lib/mysql//leco/student.frm'
innobackupex: Backing up files '/var/lib/mysql//performance_schema/.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
170105 11:44:25innobackupex: Finished backing up non-InnoDB tables and files
170105 11:44:25innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
170105 11:44:25innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '1669207'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1669207)
xtrabackup: Creating suspend file '/opt/2017-01-05_11-44-22/xtrabackup_log_copied' with pid '27861'
xtrabackup: Transaction log of lsn (1669207) to (1669207) was copied.
170105 11:44:26innobackupex: All tables unlocked
innobackupex: Backup created in directory '/opt/2017-01-05_11-44-22'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 595
170105 11:44:26innobackupex: Connection to database server closed
170105 11:44:26innobackupex: completed OK!
# cat xtrabackup_checkpoints#全量备份点
backup_type = full-prepared
from_lsn = 0
to_lsn = 1666902
last_lsn = 1666902
compact = 0
# cd ../2017-01-05_11-44-22/
# ls
backup-my.cnfibdata1.metamysql xtrabackup_binlog_infoxtrabackup_info
ibdata1.deltaleco performance_schemaxtrabackup_checkpointsxtrabackup_logfile
# cat xtrabackup_checkpoints#增量备份点
backup_type = incremental
from_lsn = 1666902
to_lsn = 1669207
last_lsn = 1669207
compact = 0
其中BASEDIR指的是完全备份所在的目录，此命令结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据，另外在执行过程增量备份只有在进行一次增量备份时，其中--incremental-basedir
应该指向上一次的增量备份所在的目录，需要注意的是增量备份仅仅用于InnoDB或者XtraDB表,对于MyISAM表而言，执行增量备份时候其实进程的完全备份。
2.2.2 增量恢复
准备（prepare）增量备份与整体完全备份有一些不同，尤其要注意的是，
1.需要在每个备份（包括完全和各个增量备份）上，将已经提交的事物进行，重放。重放之后，所有的备份数据将合并到完全备份上。
2.基于所有的备份将未提交的事物进程回滚
# systemctl stop mysql
# cd /var/lib/mysql/
leco/ mysql/ performance_schema/
# cd /var/lib/mysql/
# ls
auto.cnfib_logfile0leco mysql-bin.000001performance_schemaxtrabackup_binlog_pos_innodb
ibdata1 ib_logfile1mysqlmysql-bin.index select xtrabackup_info
# rm -rf *
指定全量备份
# innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /opt/2017-01-05_11-04-55/
指定增量备份
# innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /opt/2017-01-05_11-04-55/ --incremental-dir=/opt/2017-01-05_11-44-22/
恢复
# innobackupex --copy-back --defaults-file=/etc/my.cnf /opt/2017-01-05_11-04-55/
解释：
[*]2017-01-05_11-04-55/ 是一次全量备份
[*]2017-01-05_11-44-22 是一次基于2017-01-05_11-04-55的增量备份
[*]恢复的时候要指定那个全量的目录路径。
# ls
ibdata1ib_logfile0ib_logfile1lecomysqlperformance_schemaxtrabackup_info
# chown -R mysql.mysql * #此时一定要先授权在启动mysql
# ll
total 110604
-rw-r--r--. 1 mysql mysql 12582912 Jan5 13:24 ibdata1
-rw-r--r--. 1 mysql mysql 50331648 Jan5 13:24 ib_logfile0
-rw-r--r--. 1 mysql mysql 50331648 Jan5 13:24 ib_logfile1
drwxr-xr-x. 2 mysql mysql 55 Jan5 13:24 leco
drwxr-xr-x. 2 mysql mysql 4096 Jan5 13:24 mysql
-rw-rw----. 1 mysql mysql 0 Jan5 13:26 mysql-bin.index
drwxr-xr-x. 2 mysql mysql 4096 Jan5 13:24 performance_schema
-rw-r--r--. 1 mysql mysql 652 Jan5 13:24 xtrabackup_info
# systemctl start mysql
# ps aux|grep mysql
mysql 334222.00.1 1132481604 ? Ss 13:26 0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
root 334230.00.1 1131161456 ? Ss 13:26 0:00 /bin/bash /usr/bin/mysql-systemd-start post
mysql 33599 21.0 11.1 475116 111352 ? Sl 13:26 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysqlmysql.sock
root 336120.00.0 112648 952 pts/3 S+ 13:26 0:00 grep --color=auto mysql
# mysql -uroot -proot
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection> Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| leco |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)
mysql> use leco;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_leco |
+----------------+
| student |
+----------------+
1 row in set (0.00 sec)
mysql> select * from student;
+----+---------+-----+------+
|> +----+---------+-----+------+
|1 | good |15 | NULL |
|2 | hehe |18 | NULL |
|1 | good |15 | NULL |
|2 | hehe |18 | NULL |
|2 | beijing | 100 | NULL |
|2 | cmz |30 | NULL |
+----+---------+-----+------+
6 rows in set (0.00 sec)
到增量备份已经OK。
2.2.3 多次增量备份
2.2.4 多次增量恢复
2.3 单库备份与恢复
# innobackupex --user=root -password=root --defaults-file=/etc/my.cnf --database=leco /tmp/
解释：
1.要加参数 --database
2.要指定库名字
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
170105 13:58:45innobackupex: Executing a version check against the server...
170105 13:58:45innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'(using password: YES).
170105 13:58:45innobackupex: Connected to MySQL server
170105 13:58:45innobackupex: Done.
170105 13:58:45innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'(using password: YES).
170105 13:58:45innobackupex: Connected to MySQL server
170105 13:58:45innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex:Using server version 5.6.35-log
innobackupex: Created backup directory /tmp/2017-01-05_13-58-45
170105 13:58:45innobackupex: Starting ibbackup with command: xtrabackup--defaults-file="/etc/my.cnf"--defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp/2017-01-05_13-58-45 --innodb_log_file_size="50331648" --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --databases='leco'
innobackupex: Waiting for ibbackup (pid=35053) to suspend
innobackupex: Suspend file '/tmp/2017-01-05_13-58-45/xtrabackup_suspended_2'
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision> xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
> log scanned up to (1667104)
xtrabackup: Generating a list of tablespaces
Copying ./ibdata1 to /tmp/2017-01-05_13-58-45/ibdata1
...done
Copying ./leco/student.ibd to /tmp/2017-01-05_13-58-45/leco/student.ibd
...done
> log scanned up to (1667104)
xtrabackup: Creating suspend file '/tmp/2017-01-05_13-58-45/xtrabackup_suspended_2' with pid '35053'
170105 13:58:47innobackupex: Continuing after ibbackup has suspended
170105 13:58:47innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
170105 13:58:47innobackupex: Executing FLUSH TABLES WITH READ LOCK...
170105 13:58:47innobackupex: All tables locked and flushed to disk
170105 13:58:47innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up file '/var/lib/mysql//leco/db.opt'
> log scanned up to (1667104)
innobackupex: Backing up file '/var/lib/mysql//leco/student.frm'
170105 13:58:47innobackupex: Finished backing up non-InnoDB tables and files
170105 13:58:47innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
170105 13:58:47innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '1667104'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1667104)
xtrabackup: Creating suspend file '/tmp/2017-01-05_13-58-45/xtrabackup_log_copied' with pid '35053'
xtrabackup: Transaction log of lsn (1667104) to (1667104) was copied.
170105 13:58:48innobackupex: All tables unlocked
innobackupex: Backup created in directory '/tmp/2017-01-05_13-58-45'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
170105 13:58:48innobackupex: Connection to database server closed
170105 13:58:48innobackupex: completed OK!
# ll /tmp/2017-01-05_13-58-45
total 12308
-rw-r--r--. 1 root root 295 Jan5 13:58 backup-my.cnf
-rw-r-----. 1 root root 12582912 Jan5 13:58 ibdata1
drwx------. 2 root root 55 Jan5 13:58 leco
-rw-r--r--. 1 root root 21 Jan5 13:58 xtrabackup_binlog_info
-rw-r-----. 1 root root 89 Jan5 13:58 xtrabackup_checkpoints
-rw-r--r--. 1 root root 600 Jan5 13:58 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan5 13:58 xtrabackup_logfile
2.4 备份文件归档压缩
2.4.1 压缩
# mkdir /tmp/db
# innobackupex --user=root -password=root --defaults-file=/etc/my.cnf --stream=tar /tmp/db | gzip >/tmp/db/date +%F.tar.gz
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
170105 14:05:59innobackupex: Executing a version check against the server...
170105 14:05:59innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'(using password: YES).
170105 14:05:59innobackupex: Connected to MySQL server
170105 14:05:59innobackupex: Done.
170105 14:05:59innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'(using password: YES).
170105 14:05:59innobackupex: Connected to MySQL server
170105 14:05:59innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex:Using server version 5.6.35-log
innobackupex: Created backup directory /tmp/db
170105 14:05:59innobackupex: Starting ibbackup with command: xtrabackup--defaults-file="/etc/my.cnf"--defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp --innodb_log_file_size="50331648" --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --stream=tar
innobackupex: Waiting for ibbackup (pid=35685) to suspend
innobackupex: Suspend file '/tmp/xtrabackup_suspended_2'
xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86_64) (revision> xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
> log scanned up to (1667104)
xtrabackup: Generating a list of tablespaces
Streaming ./ibdata1
...done
Streaming ./mysql/innodb_table_stats.ibd
...done
Streaming ./mysql/innodb_index_stats.ibd
...done
Streaming ./mysql/slave_relay_log_info.ibd
...done
Streaming ./mysql/slave_master_info.ibd
...done
Streaming ./mysql/slave_worker_info.ibd
...done
Streaming ./leco/student.ibd
...done
> log scanned up to (1667104)
xtrabackup: Creating suspend file '/tmp/xtrabackup_suspended_2' with pid '35685'
170105 14:06:01innobackupex: Continuing after ibbackup has suspended
170105 14:06:01innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
170105 14:06:01innobackupex: Executing FLUSH TABLES WITH READ LOCK...
170105 14:06:01innobackupex: All tables locked and flushed to disk
170105 14:06:01innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up files '/var/lib/mysql//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
> log scanned up to (1667104)
innobackupex: Backing up file '/var/lib/mysql//leco/db.opt'
innobackupex: Backing up file '/var/lib/mysql//leco/student.frm'
innobackupex: Backing up files '/var/lib/mysql//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
170105 14:06:02innobackupex: Finished backing up non-InnoDB tables and files
170105 14:06:02innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
170105 14:06:02innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '1667104'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1667104)
xtrabackup: Creating suspend file '/tmp/xtrabackup_log_copied' with pid '35685'
xtrabackup: Transaction log of lsn (1667104) to (1667104) was copied.
170105 14:06:03innobackupex: All tables unlocked
innobackupex: Backup created in directory '/tmp/db'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
170105 14:06:03innobackupex: Connection to database server closed
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.
170105 14:06:03innobackupex: completed OK!
# ll /tmp/db/2017-01-05.tar.gz
-rw-r--r--. 1 root root 285805 Jan5 14:06 /tmp/db/2017-01-05.tar.gz
2.4.2 解压
# tar -izxvf date +%F.tar.gz
#注意解压的方式不同，不能直接像平时解压文件那样。
./backup-my.cnf
ibdata1
mysql/innodb_table_stats.ibd
mysql/innodb_index_stats.ibd
mysql/slave_relay_log_info.ibd
mysql/slave_master_info.ibd
mysql/slave_worker_info.ibd
leco/student.ibd
mysql/db.frm
mysql/db.MYI
mysql/db.MYD
mysql/user.frm
mysql/user.MYI
mysql/user.MYD
mysql/func.frm
mysql/func.MYI
mysql/func.MYD
mysql/plugin.frm
mysql/plugin.MYI
mysql/plugin.MYD
mysql/servers.frm
mysql/servers.MYI
mysql/servers.MYD
mysql/tables_priv.frm
mysql/tables_priv.MYI
mysql/tables_priv.MYD
mysql/columns_priv.frm
mysql/columns_priv.MYI
mysql/columns_priv.MYD
mysql/help_topic.frm
mysql/help_topic.MYI
mysql/help_topic.MYD
mysql/help_category.frm
mysql/help_category.MYI
mysql/help_category.MYD
mysql/help_relation.frm
mysql/help_relation.MYI
mysql/help_relation.MYD
mysql/help_keyword.frm
mysql/help_keyword.MYI
mysql/help_keyword.MYD
mysql/time_zone_name.frm
mysql/time_zone_name.MYI
mysql/time_zone_name.MYD
mysql/time_zone.frm
mysql/time_zone.MYI
mysql/time_zone.MYD
mysql/time_zone_transition.frm
mysql/time_zone_transition.MYI
mysql/time_zone_transition.MYD
mysql/time_zone_transition_type.frm
mysql/time_zone_transition_type.MYI
mysql/time_zone_transition_type.MYD
mysql/time_zone_leap_second.frm
mysql/time_zone_leap_second.MYI
mysql/time_zone_leap_second.MYD
mysql/proc.frm
mysql/proc.MYI
mysql/proc.MYD
mysql/procs_priv.frm
mysql/procs_priv.MYI
mysql/procs_priv.MYD
mysql/general_log.frm
mysql/general_log.CSM
mysql/general_log.CSV
mysql/slow_log.frm
mysql/slow_log.CSM
mysql/slow_log.CSV
mysql/event.frm
mysql/event.MYI
mysql/event.MYD
mysql/ndb_binlog_index.frm
mysql/ndb_binlog_index.MYI
mysql/ndb_binlog_index.MYD
mysql/innodb_table_stats.frm
mysql/innodb_index_stats.frm
mysql/slave_relay_log_info.frm
mysql/slave_master_info.frm
mysql/slave_worker_info.frm
mysql/proxies_priv.frm
mysql/proxies_priv.MYI
mysql/proxies_priv.MYD
leco/db.opt
leco/student.frm
performance_schema/db.opt
performance_schema/cond_instances.frm
performance_schema/events_waits_current.frm
performance_schema/events_waits_history.frm
performance_schema/events_waits_history_long.frm
performance_schema/events_waits_summary_by_instance.frm
performance_schema/events_waits_summary_by_host_by_event_name.frm
performance_schema/events_waits_summary_by_user_by_event_name.frm
performance_schema/events_waits_summary_by_account_by_event_name.frm
performance_schema/events_waits_summary_by_thread_by_event_name.frm
performance_schema/events_waits_summary_global_by_event_name.frm
performance_schema/file_instances.frm
performance_schema/file_summary_by_event_name.frm
performance_schema/file_summary_by_instance.frm
performance_schema/socket_instances.frm
performance_schema/socket_summary_by_instance.frm
performance_schema/socket_summary_by_event_name.frm
performance_schema/host_cache.frm
performance_schema/mutex_instances.frm
performance_schema/objects_summary_global_by_type.frm
performance_schema/performance_timers.frm
performance_schema/rwlock_instances.frm
performance_schema/setup_actors.frm
performance_schema/setup_consumers.frm
performance_schema/setup_instruments.frm
performance_schema/setup_objects.frm
performance_schema/setup_timers.frm
performance_schema/table_io_waits_summary_by_index_usage.frm
performance_schema/table_io_waits_summary_by_table.frm
performance_schema/table_lock_waits_summary_by_table.frm
performance_schema/threads.frm
performance_schema/events_stages_current.frm
performance_schema/events_stages_history.frm
performance_schema/events_stages_history_long.frm
performance_schema/events_stages_summary_by_thread_by_event_name.frm
performance_schema/events_stages_summary_by_host_by_event_name.frm
performance_schema/events_stages_summary_by_user_by_event_name.frm
performance_schema/events_stages_summary_by_account_by_event_name.frm
performance_schema/events_stages_summary_global_by_event_name.frm
performance_schema/events_statements_current.frm
performance_schema/events_statements_history.frm
performance_schema/events_statements_history_long.frm
performance_schema/events_statements_summary_by_thread_by_event_name.frm
performance_schema/events_statements_summary_by_host_by_event_name.frm
performance_schema/events_statements_summary_by_user_by_event_name.frm
performance_schema/events_statements_summary_by_account_by_event_name.frm
performance_schema/events_statements_summary_global_by_event_name.frm
performance_schema/hosts.frm
performance_schema/users.frm
performance_schema/accounts.frm
performance_schema/events_statements_summary_by_digest.frm
performance_schema/session_connect_attrs.frm
performance_schema/session_account_connect_attrs.frm
./xtrabackup_binlog_info
xtrabackup_logfile
xtrabackup_checkpoints
./xtrabackup_info
# ls
2017-01-05.tar.gzibdata1mysql xtrabackup_binlog_infoxtrabackup_info
backup-my.cnf leco performance_schemaxtrabackup_checkpointsxtrabackup_logfile
#
页:
[1]