XtraBackup全备与增量备份

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

一、XtraBackup 安装

下载地址：http://www.percona.com/downloads/XtraBackup/XtraBackup-2.2.8/source/

XtraBackup 全备与增量备份

安装步骤：

 ================================
How to build XtraBackup on Linux
================================
 
Prerequisites
-------------$ yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses5-devel
 
Compiling with CMake
--------------------
$ cmake -DBUILD_CONFIG=xtrabackup_release && make -j4
Installation
------------
$ make install

will install all XtraBackup binaries, the innobackupex script and tests to /usr/local/xtrabackup. You can override this either with“make DESTDIR=… install”or by changing the installation layout with“cmake -DINSTALL_LAYOUT=…”.

如果出现下面报错，需要 yum install crypt* ; yum install libgcrypt*

 CMake Error at cmake/gcrypt.cmake:25 (MESSAGE):
  Cannot find gcrypt.h in /usr/include;/usr/local/include;/opt/local/include.
  You can use libgcrypt-config --cflags to get the necessary path and pass it
  to CMake with -DGCRYPT_INCLUDE_PATH=<path>
Call Stack (most recent call first):
  storage/innobase/xtrabackup/src/CMakeLists.txt:20 (FIND_GCRYPT)
 
 
CMake Error at cmake/gcrypt.cmake:36 (MESSAGE):
  Cannot find libgcrypt shared libraries in
  /usr/lib;/usr/local/lib;/opt/local/lib.  You can use libgcrypt-config
  --libs to get the necessary path and pass it to CMake with
  -DGCRYPT_LIB_PATH=<path>
Call Stack (most recent call first):
  storage/innobase/xtrabackup/src/CMakeLists.txt:20 (FIND_GCRYPT)

二、xtrabackup 全备测试

普通备份 (全量备份)
[root@server mysql]# mkdir /data/mysql/databak
[root@server mysql]# xtrabackup –defaults-file=/usr/local/mysql/my.cnf –backup –target-dir=/data/mysql/databak/
[root@server mysql]# cp -rp data/test/students.frm databak/test/
注意：xtrabackup 只备份数据文件，并不备份数据表结构(.frm)，所以这里要手动备份一下，以便 xtrabackup 恢复的时候使用，如果是做了分表分文件保存的话，就需要挑出.frm 表结构，表数据文件.ibd 不需要复制

全量备份恢复
实施对备份文件进行恢复前的准备
[root@server mysql]# xtrabackup –defaults-file=/usr/local/mysql/my.cnf –prepare –target-dir=/data/mysql/databak/
从备份目录复制对应数据库表结构到默认的数据目录 [如果是做了分表分文件，则需要全部复制到默认的数据目录]
cp -r /data/mysql/databak/test /data/mysql/data/
删除默认数据目录中对应的数据文件并复制备份的数据文件到默认数据目录
rm /data/mysql/data/ib*
cp /data/mysql/databak/ib* /data/mysql/data/
修改数据目录权限 [注意细节：mysql 库的所有者是 mysql，组是 root]
chown -R mysql:mysql /data/mysql/data
重启 MySQL
[root@server data]# /etc/init.d/mysqld restart

三、xtrabackup 增量备份测试

1. 全备
[root@server databak]# mkdir /data/mysql/databak/
[root@server mysql]# xtrabackup –defaults-file=/usr/local/mysql/my.cnf –backup –target-dir=/data/mysql/databak/
[root@server mysql]# cp -rp data/test/students.frm databak/test/
2. 增量备份
[root@server databak]# mkdir /data/mysql/databak/delta/
[root@server databak]# xtrabackup –defaults-file=/usr/local/mysql/my.cnf –backup –incremental-basedir=/data/mysql/databak/ –target-dir=/data/mysql/databak/delta/

3. 备份恢复准备[这里没有完成增量备份，增量备份数据恢复失败]

[root@server data]# xtrabackup –defaults-file=/usr/local/mysql/my.cnf –prepare –target-dir=/data/mysql/databak/
[root@server data]# xtrabackup –defaults-file=/usr/local/mysql/my.cnf –prepare –target-dir=/data/mysql/databak/ –incremental-dir/=/data/mysql/databak/delta/

4. 恢复数据库

从备份目录复制对应数据库表结构到默认的数据目录 [如果是做了分表分文件，则需要全部复制到默认的数据目录]
cp -r /data/mysql/databak/test /data/mysql/data/
删除默认数据目录中对应的数据文件并复制备份的数据文件到默认数据目录
rm /data/mysql/data/ib*
cp /data/mysql/databak/ib* /data/mysql/data/
修改数据目录权限[注意细节：mysql 库的所有者是 mysql，组是 root]
chown -R mysql:mysql /data/mysql/data

四、用 innobackupex 执行全备与增量备份

innobackupex 全备和增量备份
1. 全备

 [root@server mysql]# innobackupex --user=root --password=RedHat /data/mysql/databak/
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3637
main::init() called at /usr/bin/innobackupex line 1557
innobackupex: Error: option 'datadir' has different values:
'/var/lib/mysql' in defaults file
'/data/mysql/data/' in SHOW VARIABLES

执行失败，需要指定配置文件路径：

 [root@server xtrabackup]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --password=redhat /data/mysql/databak/
 
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.
 
150204 19:00:03  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/usr/local/mysql/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
150204 19:00:03  innobackupex: Connected to MySQL server
150204 19:00:03  innobackupex: Executing a version check against the server...
150204 19:00:03  innobackupex: Done.
150204 19:00:03  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!".
 
innobackupex:  Using server version 5.5.30
 
innobackupex: Created backup directory /data/mysql/databak/2015-02-04_19-00-03
 
150204 19:00:03  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/usr/local/mysql/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/mysql/databak/2015-02-04_19-00-03 --tmpdir=/tmp --extra-lsndir='/tmp'
innobackupex: Waiting for ibbackup (pid=19209) to suspend
innobackupex: Suspend file '/data/mysql/databak/2015-02-04_19-00-03/xtrabackup_suspended_2'
 
xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = /data/mysql/data
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
xtrabackup: using O_DIRECT
>> log scanned up to (1602080)
xtrabackup: Generating a list of tablespaces
[01] Copying /data/mysql/data/ibdata1 to /data/mysql/databak/2015-02-04_19-00-03/ibdata1
>> log scanned up to (1602080)
>> log scanned up to (1602080)
>> log scanned up to (1602080)
>> log scanned up to (1602080)
>> log scanned up to (1602080)
>> log scanned up to (1602080)
>> log scanned up to (1602080)
>> log scanned up to (1602080)
>> log scanned up to (1602080)
>> log scanned up to (1602080)
>> log scanned up to (1602080)
[01]        ...done
>> log scanned up to (1602080)
[01] Copying ./test/students.ibd to /data/mysql/databak/2015-02-04_19-00-03/test/students.ibd
[01]        ...done
>> log scanned up to (1602080)
xtrabackup: Creating suspend file '/data/mysql/databak/2015-02-04_19-00-03/xtrabackup_suspended_2' with pid '19209'
 
150204 19:00:17  innobackupex: Continuing after ibbackup has suspended
150204 19:00:17  innobackupex: Executing FLUSH TABLES WITH READ LOCK...
150204 19:00:17  innobackupex: All tables locked and flushed to disk
 
150204 19:00:17  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/data/mysql/data/'
innobackupex: Backing up files '/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
>> log scanned up to (1602080)
innobackupex: Backing up file '/data/mysql/data//test/students.frm'
innobackupex: Backing up files '/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
150204 19:00:17  innobackupex: Finished backing up non-InnoDB tables and files
 
150204 19:00:17  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
150204 19:00:17  innobackupex: Waiting for log copying to finish
 
xtrabackup: The latest check point (for incremental): '1602080'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1602080)
 
xtrabackup: Creating suspend file '/data/mysql/databak/2015-02-04_19-00-03/xtrabackup_log_copied' with pid '19209'
xtrabackup: Transaction log of lsn (1602080) to (1602080) was copied.
150204 19:00:18  innobackupex: All tables unlocked
 
innobackupex: Backup created in directory '/data/mysql/databak/2015-02-04_19-00-03'
150204 19:00:18  innobackupex: Connection to database server closed
150204 19:00:18  innobackupex: completed OK!

2. 第一次增量备份
#–incremental：增量备份的文件夹
#–incremental-dir：针对哪个做增量备份

 [root@server databak]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --password=redhat --incremental  /data/mysql/databak/ --incremental-dir /data/mysql/databak/2015-02-04_19-00-03/
 
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.
 
150204 19:03:58  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/usr/local/mysql/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
150204 19:03:58  innobackupex: Connected to MySQL server
150204 19:03:58  innobackupex: Executing a version check against the server...
150204 19:03:58  innobackupex: Done.
150204 19:03:58  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!".
 
innobackupex:  Using server version 5.5.30
 
innobackupex: Created backup directory /data/mysql/databak/2015-02-04_19-03-58
 
150204 19:03:58  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/usr/local/mysql/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/mysql/databak/2015-02-04_19-03-58 --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='/data/mysql/databak/2015-02-04_19-00-03'
innobackupex: Waiting for ibbackup (pid=19349) to suspend
innobackupex: Suspend file '/data/mysql/databak/2015-02-04_19-03-58/xtrabackup_suspended_2'
 
xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 1602080 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = /data/mysql/data
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
xtrabackup: using O_DIRECT
>> log scanned up to (1602919)
xtrabackup: Generating a list of tablespaces
xtrabackup: using the full scan for incremental backup
[01] Copying /data/mysql/data/ibdata1 to /data/mysql/databak/2015-02-04_19-03-58/ibdata1.delta
>> log scanned up to (1602919)
>> log scanned up to (1602919)
>> log scanned up to (1602919)
>> log scanned up to (1602919)
>> log scanned up to (1602919)
[01]        ...done
[01] Copying ./test/students.ibd to /data/mysql/databak/2015-02-04_19-03-58/test/students.ibd.delta
[01]        ...done
>> log scanned up to (1602919)
xtrabackup: Creating suspend file '/data/mysql/databak/2015-02-04_19-03-58/xtrabackup_suspended_2' with pid '19349'
 
150204 19:04:05  innobackupex: Continuing after ibbackup has suspended
150204 19:04:05  innobackupex: Executing FLUSH TABLES WITH READ LOCK...
150204 19:04:05  innobackupex: All tables locked and flushed to disk
 
150204 19:04:05  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/data/mysql/data/'
innobackupex: Backing up files '/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
>> log scanned up to (1602919)
innobackupex: Backing up file '/data/mysql/data//test/students.frm'
innobackupex: Backing up files '/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
150204 19:04:06  innobackupex: Finished backing up non-InnoDB tables and files
 
150204 19:04:06  innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
150204 19:04:06  innobackupex: Waiting for log copying to finish
 
xtrabackup: The latest check point (for incremental): '1602919'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1602919)
 
xtrabackup: Creating suspend file '/data/mysql/databak/2015-02-04_19-03-58/xtrabackup_log_copied' with pid '19349'
xtrabackup: Transaction log of lsn (1602919) to (1602919) was copied.
150204 19:04:07  innobackupex: All tables unlocked
 
innobackupex: Backup created in directory '/data/mysql/databak/2015-02-04_19-03-58'
150204 19:04:07  innobackupex: Connection to database server closed
150204 19:04:07  innobackupex: completed OK!
[root@server databak]# ls
2015-02-04_19-00-03  2015-02-04_19-03-58
[root@server databak]# ll
total 8
drwxr-xr-x. 5 root root 4096 Feb  4 19:00 2015-02-04_19-00-03
drwxr-xr-x. 5 root root 4096 Feb  4 19:04 2015-02-04_19-03-58
[root@server databak]# du -sh *
1.1G    2015-02-04_19-00-03
1.5M    2015-02-04_19-03-58

3. 模拟数据丢失

 mysql> drop database test;
Query OK, 1 row affected (0.14 sec)
 
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

4. 将全备数据执行 prepare

 [root@server 2015-02-04_19-03-58]# innobackupex --apply-log --redo-only /data/mysql/databak/2015-02-04_19-00-03/
 
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.
 
150204 19:10:15  innobackupex: 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!".
 
 
150204 19:10:15  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/data/mysql/databak/2015-02-04_19-00-03/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/mysql/databak/2015-02-04_19-00-03 --apply-log-only
 
xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to /data/mysql/databak/2015-02-04_19-00-03
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1602080)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:1G: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:1G: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.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 1602070 and 1602070 in ibdata files do not match the log sequence number 1602080 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...
 
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
 
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1602080
150204 19:10:16  innobackupex: completed OK!

5. 将增量备份数据合并到全备中

 [root@server 2015-02-04_19-03-58]# innobackupex --apply-log --redo-only --incremental /data/mysql/databak/2015-02-04_19-00-03/ --incremental-dir=/data/mysql/databak/2015-02-04_19-03-58/
 
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.
 
150204 19:10:19  innobackupex: 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!".
 
 
150204 19:10:19  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/data/mysql/databak/2015-02-04_19-00-03/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/mysql/databak/2015-02-04_19-00-03 --apply-log-only --incremental-dir=/data/mysql/databak/2015-02-04_19-03-58/
 
xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 1602080 is enabled.
xtrabackup: cd to /data/mysql/databak/2015-02-04_19-00-03
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1602919)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = /data/mysql/databak/2015-02-04_19-03-58/
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Generating a list of tablespaces
xtrabackup: page size for /data/mysql/databak/2015-02-04_19-03-58//ibdata1.delta is 16384 bytes
Applying /data/mysql/databak/2015-02-04_19-03-58//ibdata1.delta to ./ibdata1...
xtrabackup: page size for /data/mysql/databak/2015-02-04_19-03-58//test/students.ibd.delta is 16384 bytes
Applying /data/mysql/databak/2015-02-04_19-03-58//test/students.ibd.delta to ./test/students.ibd...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup:   innodb_log_group_home_dir = /data/mysql/databak/2015-02-04_19-03-58/
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.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 1602070 and 1602070 in ibdata files do not match the log sequence number 1602919 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...
 
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
 
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1602919
innobackupex: Starting to copy non-InnoDB files in '/data/mysql/databak/2015-02-04_19-03-58/'
innobackupex: to the full backup directory '/data/mysql/databak/2015-02-04_19-00-03'
innobackupex: Copying '/data/mysql/databak/2015-02-04_19-03-58/xtrabackup_info' to '/data/mysql/databak/2015-02-04_19-00-03/xtrabackup_info'
innobackupex: Copying '/data/mysql/databak/2015-02-04_19-03-58/performance_schema/db.opt' to '/data/mysql/databak/2015-02-04_19-00-03/performance_schema/db.opt'
innobackupex: Copying '/data/mysql/databak/2015-02-04_19-03-58/performance_schema/events_waits_summary_by_thread_by_event_name.frm' to '/data/mysql/databak/2015-02-04_19-00-03/performance_schema/events_waits_summary_by_thread_by_event_name.frm'
innobackupex: Copying '/data/mysql/databak/2015-02-04_19-03-58/performance_schema/file_summary_by_instance.frm' to '/data/mysql/databak/2015-02-04_19-00-03/performance_schema/file_summary_by_instance.frm'..........
150204 19:10:20  innobackupex: completed OK!

6. 停止 mysqld 服务，恢复数据

 [root@server 2015-02-04_19-03-58]# /etc/init.d/mysqld stop
Shutting down MySQL...                                     [OK]
[root@server data]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --copy-back /data/mysql/databak/2015-02-04_19-00-03/
 
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.
 
150204 19:13:47  innobackupex: 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 '/data/mysql/databak/2015-02-04_19-00-03'.........
 
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/data/mysql/databak/2015-02-04_19-00-03'
innobackupex: back to original InnoDB data directory '/data/mysql/data'
innobackupex: Copying '/data/mysql/databak/2015-02-04_19-00-03/ibdata1' to '/data/mysql/data/ibdata1'
 
innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/data/mysql/databak/2015-02-04_19-00-03'
innobackupex: back to '/data/mysql/data/'
 
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/data/mysql/databak/2015-02-04_19-00-03'
innobackupex: back to original InnoDB log directory '/data/mysql/data/'
innobackupex: Finished copying back files.
 
150204 19:13:53  innobackupex: completed OK!

7. 对恢复的文件修改权限

 [root@server data]# chown -R mysql:mysql *
[root@server data]# chown -R mysql:root mysql/

8. 启动 mysql 服务

 [root@server data]# /etc/init.d/mysqld start
Starting MySQL...                                          [OK]

done ^_^

更多 XtraBackup 相关教程见以下内容：

MySQL 管理之使用 XtraBackup 进行热备 http://www.linuxidc.com/Linux/2014-04/99671.htm

MySQL 开源备份工具 Xtrabackup 备份部署 http://www.linuxidc.com/Linux/2013-06/85627.htm

MySQL Xtrabackup 备份和恢复 http://www.linuxidc.com/Linux/2011-12/50275.htm

用 XtraBackup 实现 MySQL 的主从复制快速部署【主不锁表】http://www.linuxidc.com/Linux/2012-10/71919p2.htm

安装和使用 Percona 推出的 Xtrabackup 备份 MySQL http://www.linuxidc.com/Linux/2011-10/44451.htm

XtraBackup 的详细介绍：请点这里
XtraBackup 的下载地址：请点这里

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-08/134474.htm

XtraBackup全备与增量备份

开源堡垒机JumpServer配置教程：使用步骤与配置

申请腾讯混元的API Key并且使用LobeChat调用混元AI

【开源安全保护】如何安装JumpServer堡垒机

基于Docker快速搭建一个开源的IT人员在线工具箱-it-tools

Docker部署搭建一个开源强大的图书管理系统

Centos之赛门铁克存储搭建

腾讯云2024双11优惠活动，云服务器28元1年起，先领代金券！

SSH命令使用小节

简单了解dd、ext3grep、extundelete与linux数据恢复

zabbix监控运维使用zatree扩展

	================================
	How to build XtraBackup on Linux
	================================

	Prerequisites
	-------------$ yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses5-devel

	Compiling with CMake
	--------------------
	$ cmake -DBUILD_CONFIG=xtrabackup_release && make -j4
	Installation
	------------
	$ make install

	CMake Error at cmake/gcrypt.cmake:25 (MESSAGE):
	Cannot find gcrypt.h in /usr/include;/usr/local/include;/opt/local/include.
	You can use libgcrypt-config --cflags to get the necessary path and pass it
	to CMake with -DGCRYPT_INCLUDE_PATH=<path>
	Call Stack (most recent call first):
	storage/innobase/xtrabackup/src/CMakeLists.txt:20 (FIND_GCRYPT)


	CMake Error at cmake/gcrypt.cmake:36 (MESSAGE):
	Cannot find libgcrypt shared libraries in
	/usr/lib;/usr/local/lib;/opt/local/lib. You can use libgcrypt-config
	--libs to get the necessary path and pass it to CMake with
	-DGCRYPT_LIB_PATH=<path>
	Call Stack (most recent call first):
	storage/innobase/xtrabackup/src/CMakeLists.txt:20 (FIND_GCRYPT)

	[root@server mysql]# innobackupex --user=root --password=RedHat /data/mysql/databak/
	innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3637
	main::init() called at /usr/bin/innobackupex line 1557
	innobackupex: Error: option 'datadir' has different values:
	'/var/lib/mysql' in defaults file
	'/data/mysql/data/' in SHOW VARIABLES

	[root@server xtrabackup]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --password=redhat /data/mysql/databak/

	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.

	150204 19:00:03 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/usr/local/mysql/my.cnf;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
	150204 19:00:03 innobackupex: Connected to MySQL server
	150204 19:00:03 innobackupex: Executing a version check against the server...
	150204 19:00:03 innobackupex: Done.
	150204 19:00:03 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!".

	innobackupex: Using server version 5.5.30

	innobackupex: Created backup directory /data/mysql/databak/2015-02-04_19-00-03

	150204 19:00:03 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/usr/local/mysql/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/mysql/databak/2015-02-04_19-00-03 --tmpdir=/tmp --extra-lsndir='/tmp'
	innobackupex: Waiting for ibbackup (pid=19209) to suspend
	innobackupex: Suspend file '/data/mysql/databak/2015-02-04_19-00-03/xtrabackup_suspended_2'

	xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
	xtrabackup: uses posix_fadvise().
	xtrabackup: cd to /data/mysql/data/
	xtrabackup: open files limit requested 0, set to 1024
	xtrabackup: using the following InnoDB configuration:
	xtrabackup: innodb_data_home_dir = /data/mysql/data
	xtrabackup: innodb_data_file_path = ibdata1:1G:autoextend
	xtrabackup: innodb_log_group_home_dir = ./
	xtrabackup: innodb_log_files_in_group = 2
	xtrabackup: innodb_log_file_size = 5242880
	xtrabackup: using O_DIRECT
	>> log scanned up to (1602080)
	xtrabackup: Generating a list of tablespaces
	[01] Copying /data/mysql/data/ibdata1 to /data/mysql/databak/2015-02-04_19-00-03/ibdata1
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	>> log scanned up to (1602080)
	[01] ...done
	>> log scanned up to (1602080)
	[01] Copying ./test/students.ibd to /data/mysql/databak/2015-02-04_19-00-03/test/students.ibd
	[01] ...done
	>> log scanned up to (1602080)
	xtrabackup: Creating suspend file '/data/mysql/databak/2015-02-04_19-00-03/xtrabackup_suspended_2' with pid '19209'

	150204 19:00:17 innobackupex: Continuing after ibbackup has suspended
	150204 19:00:17 innobackupex: Executing FLUSH TABLES WITH READ LOCK...
	150204 19:00:17 innobackupex: All tables locked and flushed to disk

	150204 19:00:17 innobackupex: Starting to backup non-InnoDB tables and files
	innobackupex: in subdirectories of '/data/mysql/data/'
	innobackupex: Backing up files '/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
	>> log scanned up to (1602080)
	innobackupex: Backing up file '/data/mysql/data//test/students.frm'
	innobackupex: Backing up files '/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
	150204 19:00:17 innobackupex: Finished backing up non-InnoDB tables and files

	150204 19:00:17 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
	150204 19:00:17 innobackupex: Waiting for log copying to finish

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

	xtrabackup: Creating suspend file '/data/mysql/databak/2015-02-04_19-00-03/xtrabackup_log_copied' with pid '19209'
	xtrabackup: Transaction log of lsn (1602080) to (1602080) was copied.
	150204 19:00:18 innobackupex: All tables unlocked

	innobackupex: Backup created in directory '/data/mysql/databak/2015-02-04_19-00-03'
	150204 19:00:18 innobackupex: Connection to database server closed
	150204 19:00:18 innobackupex: completed OK!

	[root@server databak]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --password=redhat --incremental /data/mysql/databak/ --incremental-dir /data/mysql/databak/2015-02-04_19-00-03/

	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.

	150204 19:03:58 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/usr/local/mysql/my.cnf;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
	150204 19:03:58 innobackupex: Connected to MySQL server
	150204 19:03:58 innobackupex: Executing a version check against the server...
	150204 19:03:58 innobackupex: Done.
	150204 19:03:58 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!".

	innobackupex: Using server version 5.5.30

	innobackupex: Created backup directory /data/mysql/databak/2015-02-04_19-03-58

	150204 19:03:58 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/usr/local/mysql/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/mysql/databak/2015-02-04_19-03-58 --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='/data/mysql/databak/2015-02-04_19-00-03'
	innobackupex: Waiting for ibbackup (pid=19349) to suspend
	innobackupex: Suspend file '/data/mysql/databak/2015-02-04_19-03-58/xtrabackup_suspended_2'

	xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
	incremental backup from 1602080 is enabled.
	xtrabackup: uses posix_fadvise().
	xtrabackup: cd to /data/mysql/data/
	xtrabackup: open files limit requested 0, set to 1024
	xtrabackup: using the following InnoDB configuration:
	xtrabackup: innodb_data_home_dir = /data/mysql/data
	xtrabackup: innodb_data_file_path = ibdata1:1G:autoextend
	xtrabackup: innodb_log_group_home_dir = ./
	xtrabackup: innodb_log_files_in_group = 2
	xtrabackup: innodb_log_file_size = 5242880
	xtrabackup: using O_DIRECT
	>> log scanned up to (1602919)
	xtrabackup: Generating a list of tablespaces
	xtrabackup: using the full scan for incremental backup
	[01] Copying /data/mysql/data/ibdata1 to /data/mysql/databak/2015-02-04_19-03-58/ibdata1.delta
	>> log scanned up to (1602919)
	>> log scanned up to (1602919)
	>> log scanned up to (1602919)
	>> log scanned up to (1602919)
	>> log scanned up to (1602919)
	[01] ...done
	[01] Copying ./test/students.ibd to /data/mysql/databak/2015-02-04_19-03-58/test/students.ibd.delta
	[01] ...done
	>> log scanned up to (1602919)
	xtrabackup: Creating suspend file '/data/mysql/databak/2015-02-04_19-03-58/xtrabackup_suspended_2' with pid '19349'

	150204 19:04:05 innobackupex: Continuing after ibbackup has suspended
	150204 19:04:05 innobackupex: Executing FLUSH TABLES WITH READ LOCK...
	150204 19:04:05 innobackupex: All tables locked and flushed to disk

	150204 19:04:05 innobackupex: Starting to backup non-InnoDB tables and files
	innobackupex: in subdirectories of '/data/mysql/data/'
	innobackupex: Backing up files '/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
	>> log scanned up to (1602919)
	innobackupex: Backing up file '/data/mysql/data//test/students.frm'
	innobackupex: Backing up files '/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
	150204 19:04:06 innobackupex: Finished backing up non-InnoDB tables and files

	150204 19:04:06 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
	150204 19:04:06 innobackupex: Waiting for log copying to finish

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

	xtrabackup: Creating suspend file '/data/mysql/databak/2015-02-04_19-03-58/xtrabackup_log_copied' with pid '19349'
	xtrabackup: Transaction log of lsn (1602919) to (1602919) was copied.
	150204 19:04:07 innobackupex: All tables unlocked

	innobackupex: Backup created in directory '/data/mysql/databak/2015-02-04_19-03-58'
	150204 19:04:07 innobackupex: Connection to database server closed
	150204 19:04:07 innobackupex: completed OK!
	[root@server databak]# ls
	2015-02-04_19-00-03 2015-02-04_19-03-58
	[root@server databak]# ll
	total 8
	drwxr-xr-x. 5 root root 4096 Feb 4 19:00 2015-02-04_19-00-03
	drwxr-xr-x. 5 root root 4096 Feb 4 19:04 2015-02-04_19-03-58
	[root@server databak]# du -sh *
	1.1G 2015-02-04_19-00-03
	1.5M 2015-02-04_19-03-58

	mysql> drop database test;
	Query OK, 1 row affected (0.14 sec)

	mysql> show databases;
	+--------------------+
	\| Database \|
	+--------------------+
	\| information_schema \|
	\| mysql \|
	\| performance_schema \|
	+--------------------+
	3 rows in set (0.00 sec)

	[root@server 2015-02-04_19-03-58]# innobackupex --apply-log --redo-only /data/mysql/databak/2015-02-04_19-00-03/

	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.

	150204 19:10:15 innobackupex: 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!".


	150204 19:10:15 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/data/mysql/databak/2015-02-04_19-00-03/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/data/mysql/databak/2015-02-04_19-00-03 --apply-log-only

	xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
	xtrabackup: cd to /data/mysql/databak/2015-02-04_19-00-03
	xtrabackup: This target seems to be not prepared yet.
	xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1602080)
	xtrabackup: using the following InnoDB configuration for recovery:
	xtrabackup: innodb_data_home_dir = ./
	xtrabackup: innodb_data_file_path = ibdata1:1G: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:1G: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.3
	InnoDB: Using CPU crc32 instructions
	InnoDB: Initializing buffer pool, size = 100.0M
	InnoDB: Completed initialization of buffer pool
	InnoDB: Highest supported file format is Barracuda.
	InnoDB: The log sequence numbers 1602070 and 1602070 in ibdata files do not match the log sequence number 1602080 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...

	[notice (again)]
	If you use binary log and don't use any hack of group commit,
	the binary log position seems to be:

	xtrabackup: starting shutdown with innodb_fast_shutdown = 1
	InnoDB: Starting shutdown...
	InnoDB: Shutdown completed; log sequence number 1602080
	150204 19:10:16 innobackupex: completed OK!

	[root@server 2015-02-04_19-03-58]# /etc/init.d/mysqld stop
	Shutting down MySQL... [OK]
	[root@server data]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --copy-back /data/mysql/databak/2015-02-04_19-00-03/

	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.

	150204 19:13:47 innobackupex: 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 '/data/mysql/databak/2015-02-04_19-00-03'.........

	innobackupex: Starting to copy InnoDB system tablespace
	innobackupex: in '/data/mysql/databak/2015-02-04_19-00-03'
	innobackupex: back to original InnoDB data directory '/data/mysql/data'
	innobackupex: Copying '/data/mysql/databak/2015-02-04_19-00-03/ibdata1' to '/data/mysql/data/ibdata1'

	innobackupex: Starting to copy InnoDB undo tablespaces
	innobackupex: in '/data/mysql/databak/2015-02-04_19-00-03'
	innobackupex: back to '/data/mysql/data/'

	innobackupex: Starting to copy InnoDB log files
	innobackupex: in '/data/mysql/databak/2015-02-04_19-00-03'
	innobackupex: back to original InnoDB log directory '/data/mysql/data/'
	innobackupex: Finished copying back files.

	150204 19:13:53 innobackupex: completed OK!

	[root@server data]# chown -R mysql:mysql *
	[root@server data]# chown -R mysql:root mysql/

	[root@server data]# /etc/init.d/mysqld start
	Starting MySQL... [OK]