XtraBackup

XtraBackup是什么？

   XtraBackup是Percona提供的一款备份工具，支持InnoDB,MyISAM, XtraDB(Percona研发的引擎)，是一款开源免费的Mysql备份工具；支持全量备份，增量备份，增量备份只有Innodeb支持，MyISAM不支持，因为Innodb有LSN(Log Sequence Number)，xtrabackup依靠检测全量的LSN号，只备份比其大的，就是增量备份；

Xtrabackup有如下特点:

• 备份集高效，完整，可用
• 备份任务执行过程中不会阻塞事物
• 节省空间，降低宽带
• 备份集自动验证机制
• 恢复速度块

 

安装XtraBackup：

创建repo文件，内容如下：

[root@localhost yum.repos.d]# pwd
/etc/yum.repos.d
[root@localhost yum.repos.d]# 
[root@localhost yum.repos.d]# cat Percona.repo 
[percona]
name = CentOS $releasever - Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
gpgcheck = 1
[root@localhost yum.repos.d]# 

下载并保存key：

#wget http://www.percona.com/redir/downloads/percona-release/RPM-GPG-KEY-percona

#cp RPM-GPG-KEY-percona /etc/pki/rpm-gpg/RPM-GPG-KEY-percona

yum安装： 

#yum -y install percona-xtrabackup

#yum -y install percona-toolkit

Xtrabackup使用：

xtrabacup 仅用于备份Innodb

   --defaults-file=/etc/my.cnf : 指定my.cnf文件，而且必须要放在第一个位置；主要为了获取datadir位置

  --backup | --prepare  两者选其一，前者为了备份，后者为了恢复  --target-dir   制定目标路径，即备份文件存储的路径

  --incremental-dir 恢复的时候，制定恢复增量备份路径

 innobackupex的使用

innobackupex可以备份Myisam，备份Innodb的时候会自动调用xtrabackup，所以我们直接用innobackupex的比较多，两种引擎都能用

 --incremental 进行增量备份，工具针对Innodb有增量，针对MyISAM自动进行全量；

 --incremental-basedir 指定一个全量备份作为基数，在此基础上进行增量备份

--apply-log 用于准备恢复

--defaults-file 指定配置文件

--user 指定用户名，要有足够的权限，我这里直接all privileges了

--password 密码

--port 端口

--socket socket文件路径

--slave-info 备份完毕xtrabackup_info中会含有position

--copy-back 待数据准备好后，将备份文件copy至数据目录下

 --redo-only  准备恢复的时候，如果后续还有增量，则必须跟上该参数；如果该备份机已经是最后一个，则不用加

 --incremental-dir 恢复的时候，制定恢复增量备份路径

--stream=tar | gzip  采用流模式，将所有的备份文件流入到标准输出的格式，支持tar和xbstream(xtrabackup专用格式)

--slave-info 输出信息中含有CHANGE MASTER TO...

异地备份

innobackupex --user root --password "rootpassword"  --slave-info --defaults-file=/etc/my.cnf --stream=tar ./ | ssh root@ipaddress  "cat - > /data/mysql/dbbak/backup.tar"

全量备份：

 innobackupex  --defaults-file=/etc/my.cnf  --user=llj --password=llj --port=3306 --socket=/tmp/mysql.sock --slave-info /export/data/dbbak/

[root@localhost dbbak]# ls
2018-09-02_22-34-32
[root@localhost dbbak]# ls 2018-09-02_22-34-32/
apple  backup-my.cnf  ibdata1  llj  mysql  performance_schema  test  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile
[root@localhost dbbak]# ls 2018-09-02_22-34-32/xtrabackup_info 
2018-09-02_22-34-32/xtrabackup_info
[root@localhost dbbak]# cat 2018-09-02_22-34-32/xtrabackup_info
uuid = 4fefba18-aebd-11e8-8b66-000c29081196
name = 
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --user=llj --password=... --port=3306 --socket=/tmp/mysql.sock --slave-info /export/data/dbbak/
tool_version = 2.3.10
ibbackup_version = 2.3.10
server_version = 5.6.36-log
start_time = 2018-09-02 22:34:32
end_time = 2018-09-02 22:34:34
lock_time = 0
binlog_pos = filename 'mysql-bin.000001', position '209'
innodb_from_lsn = 0
innodb_to_lsn = 1666702
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
[root@localhost dbbak]# 

 

备份指定的库：

innobackupex  --defaults-file=/etc/my.cnf  --databases=apple --user=llj --password=llj --port=3306 --socket=/tmp/mysql.sock --slave-info /export/data/dbbak/

--databases=库名  指定要备份的库，也支持表粒度备份，库名.表名即可，多个库中间空格隔开

                    "databasename1[.table_name1] databasename2[.table_name2] . . ."

[root@localhost dbbak]# innobackupex  --defaults-file=/etc/my.cnf  --databases=apple --user=llj --password=llj --port=3306 --socket=/tmp/mysql.sock --slave-info /export/data/dbbak/
180902 22:31:42 innobackupex: 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!".

180902 22:31:42  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'llj'  (using password: YES).
180902 22:31:42  version_check Connected to MySQL server
180902 22:31:42  version_check Executing a version check against the server...
180902 22:31:42  version_check Done.
180902 22:31:42 Connecting to MySQL server host: localhost, user: llj, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 5.6.36-log
innobackupex version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /export/data/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
180902 22:31:42 >> log scanned up to (1666702)
xtrabackup: Generating a list of tablespaces
Skipping db: ./mysql
Skipping db: ./test
Skipping db: ./performance_schema
Skipping db: ./llj
180902 22:31:42 [01] Copying ./ibdata1 to /export/data/dbbak/2018-09-02_22-31-42/ibdata1
180902 22:31:43 [01]        ...done
180902 22:31:43 [01] Copying ./apple/a.ibd to /export/data/dbbak/2018-09-02_22-31-42/apple/a.ibd
180902 22:31:43 [01]        ...done
180902 22:31:43 [01] Copying ./apple/b.ibd to /export/data/dbbak/2018-09-02_22-31-42/apple/b.ibd
180902 22:31:43 [01]        ...done
180902 22:31:43 >> log scanned up to (1666702)
180902 22:31:43 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180902 22:31:43 Executing FLUSH TABLES WITH READ LOCK...
180902 22:31:43 Starting to backup non-InnoDB tables and files
Skipping db: ./mysql
Skipping db: ./test
Skipping db: ./performance_schema
180902 22:31:43 [01] Copying ./apple/db.opt to /export/data/dbbak/2018-09-02_22-31-42/apple/db.opt
180902 22:31:43 [01]        ...done
180902 22:31:43 [01] Copying ./apple/a.frm to /export/data/dbbak/2018-09-02_22-31-42/apple/a.frm
180902 22:31:43 [01]        ...done
180902 22:31:43 [01] Copying ./apple/b.frm to /export/data/dbbak/2018-09-02_22-31-42/apple/b.frm
180902 22:31:43 [01]        ...done
Skipping db: ./llj
180902 22:31:43 Finished backing up non-InnoDB tables and files
Failed to get master binlog coordinates from SHOW SLAVE STATUS
This means that the server is not a replication slave. Ignoring the --slave-info option
180902 22:31:43 [00] Writing xtrabackup_binlog_info
180902 22:31:43 [00]        ...done
180902 22:31:43 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1666702'
xtrabackup: Stopping log copying thread.
.180902 22:31:43 >> log scanned up to (1666702)

180902 22:31:44 Executing UNLOCK TABLES
180902 22:31:44 All tables unlocked
180902 22:31:44 Backup created in directory '/export/data/dbbak/2018-09-02_22-31-42/'
MySQL binlog position: filename 'mysql-bin.000001', position '209'
180902 22:31:44 [00] Writing backup-my.cnf
180902 22:31:44 [00]        ...done
180902 22:31:44 [00] Writing xtrabackup_info
180902 22:31:44 [00]        ...done
xtrabackup: Transaction log of lsn (1666702) to (1666702) was copied.
180902 22:31:44 completed OK!

可以看到只有apple一个库文件夹 

[root@localhost dbbak]# ls
2018-09-02_22-31-42
[root@localhost dbbak]# cd 2018-09-02_22-31-42/
[root@localhost 2018-09-02_22-31-42]# ls
apple  backup-my.cnf  ibdata1  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile
[root@localhost 2018-09-02_22-31-42]# 

实现远程备份：

方法一：安装nc, pigz（并发的gzip）

先在远端机器上操作，执行后，系统会一直监听；

nc -l 1234 | gunzip | tar ixvf - &> xtrabackup.log
  然后在本地mysql上执行备份命令：

（这种方法不错是不错，就是无法挂到后台执行，nohup就报错，不明原因；数据量大要执行好几个小时的时候，网络一断的话就呵呵了）

[root@bogon dbbak]# nc -l 1234 | gunzip | tar ixvf - &> xtrabackup.log
[root@bogon dbbak]# 
[root@bogon dbbak]# ls
1.sh  apple  backup-my.cnf  ibdata1  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup.log  xtrabackup_logfile
[root@bogon dbbak]# 

 innobackupex  --defaults-file=/etc/my.cnf  --databases=apple --user=llj --password=llj --port=3306 --socket=/tmp/mysql.sock --slave-info --stream=tar ./ | pigz | nc 192.168.199.134 1234

[root@localhost dbbak]# 
[root@localhost dbbak]# innobackupex  --defaults-file=/etc/my.cnf  --databases=apple --user=llj --password=llj --port=3306 --socket=/tmp/mysql.sock --slave-info --stream=tar ./ | pigz | nc 192.168.199.134 1234
180902 22:43:10 innobackupex: 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!".

180902 22:43:10  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'llj'  (using password: YES).
180902 22:43:10  version_check Connected to MySQL server
180902 22:43:10  version_check Executing a version check against the server...
180902 22:43:10  version_check Done.
180902 22:43:10 Connecting to MySQL server host: localhost, user: llj, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 5.6.36-log
innobackupex version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /export/data/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
180902 22:43:11 >> log scanned up to (1666702)
xtrabackup: Generating a list of tablespaces
Skipping db: ./mysql
Skipping db: ./test
Skipping db: ./performance_schema
Skipping db: ./llj
180902 22:43:11 [01] Streaming ./ibdata1
180902 22:43:11 [01]        ...done
180902 22:43:11 [01] Streaming ./apple/a.ibd
180902 22:43:11 [01]        ...done
180902 22:43:11 [01] Streaming ./apple/b.ibd
180902 22:43:11 [01]        ...done
180902 22:43:12 >> log scanned up to (1666702)
180902 22:43:12 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180902 22:43:12 Executing FLUSH TABLES WITH READ LOCK...
180902 22:43:12 Starting to backup non-InnoDB tables and files
Skipping db: ./mysql
Skipping db: ./test
Skipping db: ./performance_schema
180902 22:43:12 [01] Streaming ./apple/db.opt to <STDOUT>
180902 22:43:12 [01]        ...done
180902 22:43:12 [01] Streaming ./apple/a.frm to <STDOUT>
180902 22:43:12 [01]        ...done
180902 22:43:12 [01] Streaming ./apple/b.frm to <STDOUT>
180902 22:43:12 [01]        ...done
Skipping db: ./llj
180902 22:43:12 Finished backing up non-InnoDB tables and files
Failed to get master binlog coordinates from SHOW SLAVE STATUS
This means that the server is not a replication slave. Ignoring the --slave-info option
180902 22:43:12 [00] Streaming xtrabackup_binlog_info
180902 22:43:12 [00]        ...done
180902 22:43:12 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1666702'
xtrabackup: Stopping log copying thread.
.180902 22:43:12 >> log scanned up to (1666702)

180902 22:43:12 Executing UNLOCK TABLES
180902 22:43:12 All tables unlocked
180902 22:43:12 Backup created in directory '/export/data/dbbak/'
MySQL binlog position: filename 'mysql-bin.000001', position '209'
180902 22:43:12 [00] Streaming backup-my.cnf
180902 22:43:12 [00]        ...done
180902 22:43:12 [00] Streaming xtrabackup_info
180902 22:43:12 [00]        ...done
xtrabackup: Transaction log of lsn (1666702) to (1666702) was copied.
180902 22:43:12 completed OK!
[root@localhost dbbak]# 

方法二：异地备份也可以设置免密登陆，然后通过ssh的方式复制过去

ssh-keygen
ssh-copy-id -i mysql@127.0.0.1

innobackupex  --defaults-file=/etc/my.cnf  --databases=apple --user=llj --password=llj --port=3306 --socket=/tmp/mysql.sock --slave-info /export/data/dbbak/  --stream=tar   | gzip | ssh root@192.168.199.134  "cat  -> /export/data/dbbak/bak.tar" 
 

挂到后台备份： 如果要保存到远端主机，用setsid比较好，我用nohup的tar文件解压的时候会报错，1T多，复制了好几个小时啊，解压就报错，都是泪；

 setsid innobackupex  --defaults-file=/etc/my.cnf  --user=llj --password=llj --port=3306 --socket=/tmp/mysql.sock --slave-info /export/data/dbbak/  --stream=tar   | gzip | ssh root@192.168.199.134  "cat  -> /export/data/dbbak/bak.tar"   &
 

 

然后是准备恢复：

[root@192.168.10.13 dbbak]#innobackupex --defaults-file=/etc/my.cnf --apply-log /export/data/dbbak/2018-08-25_01-27-26/

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

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



180825 01:29:17  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/export/data/dbbak/2018-08-25_01-27-26 --tmpdir=/export/data/mysqltmp

xtrabackup_56 version 2.1.9 for MySQL server 5.6.17 Linux (x86_64) (revision id: 746)
xtrabackup: cd to /export/data/dbbak/2018-08-25_01-27-26
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1610164)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend:max:200M
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:100M;ibdata2:100M:autoextend:max:200M
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.
...................
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1614888
180825 01:29:25  innobackupex: completed OK!

最后是执行恢复

执行前，将原数据文件删除或者备份；删除ib_logfile0  ib_logfile1 文件，删除 ibdata1  ibdata2文件

然后执行恢复

innobackupex --defaults-file=/etc/my.cnf --copy-back /export/data/dbbak/2018-08-25_01-27-26/

[root@192.168.10.13 mysql]#innobackupex --defaults-file=/etc/my.cnf --copy-back /export/data/dbbak/2018-08-25_01-27-26/

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

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 '/export/data/dbbak/2018-08-25_01-27-26'
innobackupex: back to original data directory '/export/data/mysql'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/xtrabackup_binlog_pos_innodb' to '/export/data/mysql/xtrabackup_binlog_pos_innodb'
innobackupex: Creating directory '/export/data/mysql/test'
innobackupex: Creating directory '/export/data/mysql/performance_schema'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_waits_summary_by_thread_by_event_name.frm' to '/export/data/mysql/performance_schema/events_waits_summary_by_thread_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/threads.frm' to '/export/data/mysql/performance_schema/threads.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_waits_current.frm' to '/export/data/mysql/performance_schema/events_waits_current.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_statements_summary_by_account_by_event_name.frm' to '/export/data/mysql/performance_schema/events_statements_summary_by_account_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/table_io_waits_summary_by_index_usage.frm' to '/export/data/mysql/performance_schema/table_io_waits_summary_by_index_usage.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/table_lock_waits_summary_by_table.frm' to '/export/data/mysql/performance_schema/table_lock_waits_summary_by_table.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_statements_summary_by_user_by_event_name.frm' to '/export/data/mysql/performance_schema/events_statements_summary_by_user_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_statements_current.frm' to '/export/data/mysql/performance_schema/events_statements_current.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/rwlock_instances.frm' to '/export/data/mysql/performance_schema/rwlock_instances.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_stages_summary_global_by_event_name.frm' to '/export/data/mysql/performance_schema/events_stages_summary_global_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/socket_summary_by_event_name.frm' to '/export/data/mysql/performance_schema/socket_summary_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/db.opt' to '/export/data/mysql/performance_schema/db.opt'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_stages_summary_by_host_by_event_name.frm' to '/export/data/mysql/performance_schema/events_stages_summary_by_host_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_waits_summary_by_account_by_event_name.frm' to '/export/data/mysql/performance_schema/events_waits_summary_by_account_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_waits_summary_by_user_by_event_name.frm' to '/export/data/mysql/performance_schema/events_waits_summary_by_user_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/setup_objects.frm' to '/export/data/mysql/performance_schema/setup_objects.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_statements_history_long.frm' to '/export/data/mysql/performance_schema/events_statements_history_long.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_waits_summary_by_host_by_event_name.frm' to '/export/data/mysql/performance_schema/events_waits_summary_by_host_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/file_summary_by_instance.frm' to '/export/data/mysql/performance_schema/file_summary_by_instance.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_statements_history.frm' to '/export/data/mysql/performance_schema/events_statements_history.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/objects_summary_global_by_type.frm' to '/export/data/mysql/performance_schema/objects_summary_global_by_type.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_stages_summary_by_thread_by_event_name.frm' to '/export/data/mysql/performance_schema/events_stages_summary_by_thread_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_statements_summary_by_host_by_event_name.frm' to '/export/data/mysql/performance_schema/events_statements_summary_by_host_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/hosts.frm' to '/export/data/mysql/performance_schema/hosts.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_waits_history.frm' to '/export/data/mysql/performance_schema/events_waits_history.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_statements_summary_by_thread_by_event_name.frm' to '/export/data/mysql/performance_schema/events_statements_summary_by_thread_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/session_connect_attrs.frm' to '/export/data/mysql/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/file_summary_by_event_name.frm' to '/export/data/mysql/performance_schema/file_summary_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/cond_instances.frm' to '/export/data/mysql/performance_schema/cond_instances.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/session_account_connect_attrs.frm' to '/export/data/mysql/performance_schema/session_account_connect_attrs.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/setup_instruments.frm' to '/export/data/mysql/performance_schema/setup_instruments.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_statements_summary_by_digest.frm' to '/export/data/mysql/performance_schema/events_statements_summary_by_digest.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_stages_current.frm' to '/export/data/mysql/performance_schema/events_stages_current.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/socket_instances.frm' to '/export/data/mysql/performance_schema/socket_instances.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_stages_summary_by_user_by_event_name.frm' to '/export/data/mysql/performance_schema/events_stages_summary_by_user_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_statements_summary_global_by_event_name.frm' to '/export/data/mysql/performance_schema/events_statements_summary_global_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/setup_actors.frm' to '/export/data/mysql/performance_schema/setup_actors.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/users.frm' to '/export/data/mysql/performance_schema/users.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/setup_timers.frm' to '/export/data/mysql/performance_schema/setup_timers.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/socket_summary_by_instance.frm' to '/export/data/mysql/performance_schema/socket_summary_by_instance.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/performance_timers.frm' to '/export/data/mysql/performance_schema/performance_timers.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_waits_summary_by_instance.frm' to '/export/data/mysql/performance_schema/events_waits_summary_by_instance.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/setup_consumers.frm' to '/export/data/mysql/performance_schema/setup_consumers.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/host_cache.frm' to '/export/data/mysql/performance_schema/host_cache.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_waits_history_long.frm' to '/export/data/mysql/performance_schema/events_waits_history_long.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_stages_history.frm' to '/export/data/mysql/performance_schema/events_stages_history.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/mutex_instances.frm' to '/export/data/mysql/performance_schema/mutex_instances.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_stages_summary_by_account_by_event_name.frm' to '/export/data/mysql/performance_schema/events_stages_summary_by_account_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/file_instances.frm' to '/export/data/mysql/performance_schema/file_instances.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/accounts.frm' to '/export/data/mysql/performance_schema/accounts.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/table_io_waits_summary_by_table.frm' to '/export/data/mysql/performance_schema/table_io_waits_summary_by_table.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_waits_summary_global_by_event_name.frm' to '/export/data/mysql/performance_schema/events_waits_summary_global_by_event_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/performance_schema/events_stages_history_long.frm' to '/export/data/mysql/performance_schema/events_stages_history_long.frm'
innobackupex: Creating directory '/export/data/mysql/mysql'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/plugin.frm' to '/export/data/mysql/mysql/plugin.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/event.MYI' to '/export/data/mysql/mysql/event.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_relation.MYD' to '/export/data/mysql/mysql/help_relation.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/slow_log.CSV' to '/export/data/mysql/mysql/slow_log.CSV'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_name.MYI' to '/export/data/mysql/mysql/time_zone_name.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_category.frm' to '/export/data/mysql/mysql/help_category.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/procs_priv.frm' to '/export/data/mysql/mysql/procs_priv.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/slow_log.frm' to '/export/data/mysql/mysql/slow_log.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_name.frm' to '/export/data/mysql/mysql/time_zone_name.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/event.frm' to '/export/data/mysql/mysql/event.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_keyword.MYD' to '/export/data/mysql/mysql/help_keyword.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/innodb_index_stats.ibd' to '/export/data/mysql/mysql/innodb_index_stats.ibd'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_keyword.frm' to '/export/data/mysql/mysql/help_keyword.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/user.frm' to '/export/data/mysql/mysql/user.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_transition_type.MYD' to '/export/data/mysql/mysql/time_zone_transition_type.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/innodb_index_stats.frm' to '/export/data/mysql/mysql/innodb_index_stats.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/func.MYD' to '/export/data/mysql/mysql/func.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/servers.frm' to '/export/data/mysql/mysql/servers.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_transition.MYI' to '/export/data/mysql/mysql/time_zone_transition.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone.MYD' to '/export/data/mysql/mysql/time_zone.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_transition.MYD' to '/export/data/mysql/mysql/time_zone_transition.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/proc.MYI' to '/export/data/mysql/mysql/proc.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/procs_priv.MYD' to '/export/data/mysql/mysql/procs_priv.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/ndb_binlog_index.MYI' to '/export/data/mysql/mysql/ndb_binlog_index.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/func.MYI' to '/export/data/mysql/mysql/func.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/tables_priv.MYD' to '/export/data/mysql/mysql/tables_priv.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_transition_type.MYI' to '/export/data/mysql/mysql/time_zone_transition_type.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/columns_priv.frm' to '/export/data/mysql/mysql/columns_priv.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/ndb_binlog_index.frm' to '/export/data/mysql/mysql/ndb_binlog_index.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/proxies_priv.frm' to '/export/data/mysql/mysql/proxies_priv.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_topic.frm' to '/export/data/mysql/mysql/help_topic.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_leap_second.MYI' to '/export/data/mysql/mysql/time_zone_leap_second.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/slave_relay_log_info.frm' to '/export/data/mysql/mysql/slave_relay_log_info.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/procs_priv.MYI' to '/export/data/mysql/mysql/procs_priv.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/proc.frm' to '/export/data/mysql/mysql/proc.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/slave_master_info.ibd' to '/export/data/mysql/mysql/slave_master_info.ibd'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/slave_relay_log_info.ibd' to '/export/data/mysql/mysql/slave_relay_log_info.ibd'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_topic.MYD' to '/export/data/mysql/mysql/help_topic.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/slave_worker_info.ibd' to '/export/data/mysql/mysql/slave_worker_info.ibd'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/servers.MYD' to '/export/data/mysql/mysql/servers.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/db.frm' to '/export/data/mysql/mysql/db.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/general_log.frm' to '/export/data/mysql/mysql/general_log.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/tables_priv.MYI' to '/export/data/mysql/mysql/tables_priv.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone.frm' to '/export/data/mysql/mysql/time_zone.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_name.MYD' to '/export/data/mysql/mysql/time_zone_name.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone.MYI' to '/export/data/mysql/mysql/time_zone.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_relation.frm' to '/export/data/mysql/mysql/help_relation.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_transition_type.frm' to '/export/data/mysql/mysql/time_zone_transition_type.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/proc.MYD' to '/export/data/mysql/mysql/proc.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/user.MYD' to '/export/data/mysql/mysql/user.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/plugin.MYD' to '/export/data/mysql/mysql/plugin.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/proxies_priv.MYD' to '/export/data/mysql/mysql/proxies_priv.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/slow_log.CSM' to '/export/data/mysql/mysql/slow_log.CSM'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/tables_priv.frm' to '/export/data/mysql/mysql/tables_priv.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_keyword.MYI' to '/export/data/mysql/mysql/help_keyword.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/proxies_priv.MYI' to '/export/data/mysql/mysql/proxies_priv.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/columns_priv.MYD' to '/export/data/mysql/mysql/columns_priv.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/db.MYD' to '/export/data/mysql/mysql/db.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_category.MYI' to '/export/data/mysql/mysql/help_category.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_relation.MYI' to '/export/data/mysql/mysql/help_relation.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/db.MYI' to '/export/data/mysql/mysql/db.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/innodb_table_stats.ibd' to '/export/data/mysql/mysql/innodb_table_stats.ibd'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/slave_master_info.frm' to '/export/data/mysql/mysql/slave_master_info.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_category.MYD' to '/export/data/mysql/mysql/help_category.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/event.MYD' to '/export/data/mysql/mysql/event.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/plugin.MYI' to '/export/data/mysql/mysql/plugin.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/func.frm' to '/export/data/mysql/mysql/func.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_leap_second.MYD' to '/export/data/mysql/mysql/time_zone_leap_second.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_transition.frm' to '/export/data/mysql/mysql/time_zone_transition.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/general_log.CSV' to '/export/data/mysql/mysql/general_log.CSV'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/ndb_binlog_index.MYD' to '/export/data/mysql/mysql/ndb_binlog_index.MYD'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/time_zone_leap_second.frm' to '/export/data/mysql/mysql/time_zone_leap_second.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/columns_priv.MYI' to '/export/data/mysql/mysql/columns_priv.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/slave_worker_info.frm' to '/export/data/mysql/mysql/slave_worker_info.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/user.MYI' to '/export/data/mysql/mysql/user.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/innodb_table_stats.frm' to '/export/data/mysql/mysql/innodb_table_stats.frm'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/general_log.CSM' to '/export/data/mysql/mysql/general_log.CSM'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/servers.MYI' to '/export/data/mysql/mysql/servers.MYI'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/mysql/help_topic.MYI' to '/export/data/mysql/mysql/help_topic.MYI'
innobackupex: Creating directory '/export/data/mysql/apple'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/apple/db.opt' to '/export/data/mysql/apple/db.opt'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/apple/a.frm' to '/export/data/mysql/apple/a.frm'

innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/export/data/dbbak/2018-08-25_01-27-26'
innobackupex: back to original InnoDB data directory '/export/data/dbdata'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/ibdata1' to '/export/data/dbdata/ibdata1'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/ibdata2' to '/export/data/dbdata/ibdata2'

innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/export/data/dbbak/2018-08-25_01-27-26'
innobackupex: back to '/export/data/dbdata'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/export/data/dbbak/2018-08-25_01-27-26'
innobackupex: back to original InnoDB log directory '/export/data/dblog'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/ib_logfile1' to '/export/data/dblog/ib_logfile1'
innobackupex: Copying '/export/data/dbbak/2018-08-25_01-27-26/ib_logfile0' to '/export/data/dblog/ib_logfile0'
innobackupex: Finished copying back files.

180825 01:45:10  innobackupex: completed OK!

工具会将数据文件copy回datadir路径；

最后还有一步，就是修改data权限，属主和属组改为mysql

chown -R mysql.mysql /export/data/

最后开启服务，登陆进去校验建的表都正常

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| apple              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.03 sec)

mysql> show tables from apple;
+-----------------+
| Tables_in_apple |
+-----------------+
| a               |
+-----------------+
1 row in set (0.01 sec)

mysql> select * from apple.a limit 1 \G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
        

     

如果想要保险些，可以做下全表的check： mysqlcheck -u root -p123456 -A -c 

最后如果需要作为Slave挂在到Master上面怎么办呢？挂在需要知道binlog file 和position才可以；没关系，Xtrabackup都帮我们记着呢；在备份中有xtrabackup_binlog_info文件，里面有记录；然后根据这2个参数挂在即可；

root@192.168.10.13 2018-08-25_03-08-46]#ls
apple          ibdata1  ib_logfile0  mysql               test     undo002  undo004            xtrabackup_binlog_info        xtrabackup_checkpoints
backup-my.cnf  ibdata2  ib_logfile1  performance_schema  undo001  undo003  xtrabackup_binary  xtrabackup_binlog_pos_innodb  xtrabackup_logfile
[root@192.168.10.13 2018-08-25_03-08-46]#cat  xtrabackup_binlog_info
mysql-bin.000003	3226
[root@192.168.10.13 2018-08-25_03-08-46]#

等到主从同步了以后，可以再做checksum，进行一致性校验就更完美了；