xtrabackup的初使用

- 日理万妓 2022-06-16 22:03 113阅读 0赞

这里参考了jeanron大师的文章：[http://chuansong.me/n/1696438151334][http_chuansong.me_n_1696438151334]  
经过前边恶心的安装以后，接下来尝试一番xtrabackup的备份功能。  
xtrabackup主要是用于热备份innodb,或者是 xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；  
innobackupex是将xtrabackup进行封装的perl脚本，可以备份和恢复MyISAM表以及数据表结构。  
\*\*

## 1、备份的解析 ##

\*\*  
从innobackupex –help命令可以看到innobackupex 的参数有很多，下面尝试做一个全备。

[root@cxqtest ~]# cd /data/backup/0408/
    [root@cxqtest 0408]# innobackupex -S /data/3306/mysql.sock /data/backup/0408/ --no-timestamp --no-lock --throttle=100
    170304 11:00:41 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!". 170304 11:00:42 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' (using password: NO). Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock','',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at - line 1314 170304 11:00:43 Connecting to MySQL server host: localhost, user: not set, password: not set, port: 3306, socket: /data/3306/mysql.sock Failed to connect to MySQL server: Access denied for user 'root'@'localhost' (using password: NO). [root@cxqtest 0408]# innobackupex -S /data/3306/mysql.sock /data/backup/0408/ --no-timestamp --no-lock --throttle=100 -uroot -p innobackupex: [ERROR] innobackupex: option '-p' requires an argument

报错了，原因是没有写上用户名密码

[root@cxqtest 0408]# innobackupex -S /data/3306/mysql.sock /data/backup/0408/ --no-timestamp --no-lock --throttle=100 -uroot -pmysql123
    170304 11:01:24 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!".
    
    170304 11:01:24  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
    170304 11:01:24  version_check Connected to MySQL server
    170304 11:01:24  version_check Executing a version check against the server...
    170304 11:01:24  version_check Done.
    170304 11:01:24 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /data/3306/mysql.sock
    Using server version 5.6.30-log
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /data/3306/data
    xtrabackup: open files limit requested 65535, set to 65535
    xtrabackup: using the following InnoDB configuration:
    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 = 3
    xtrabackup:   innodb_log_file_size = 524288000
    xtrabackup: using O_DIRECT
    InnoDB: Number of pools: 1
    170304 11:01:25 >> log scanned up to (1626008)
    xtrabackup: Generating a list of tablespaces
    InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
    170304 11:01:25 [01] Copying ./ibdata1 to /data/backup/0408/ibdata1
    170304 11:01:26 >> log scanned up to (1626008)
    170304 11:01:27 >> log scanned up to (1626008)
    .....
    xtrabackup: The latest check point (for incremental): '1626008'
    xtrabackup: Stopping log copying thread.
    .170304 11:02:00 >> log scanned up to (1626008)
    
    170304 11:02:01 Backup created in directory '/data/backup/0408/'
    MySQL binlog position: filename 'mybinlog.000006', position '191', GTID of the last change '82f3c6ed-007a-11e7-9b50-000c298ee31c:1-7'
    170304 11:02:01 [00] Writing backup-my.cnf
    170304 11:02:01 [00]        ...done
    170304 11:02:01 [00] Writing xtrabackup_info
    170304 11:02:01 [00]        ...done
    xtrabackup: Transaction log of lsn (1626008) to (1626008) was copied.
    170304 11:02:01 completed OK!

经过一段时间的备份，可以看到备份完成了  
让我们查看一下备份文件如下：

[root@cxqtest 0408]# du -sh 
    1.1G    .
    [root@cxqtest 0408]# du -sh ./*
    4.0K    ./backup-my.cnf
    1.1G    ./ibdata1
    1.7M    ./mysql
    636K    ./performance_schema
    4.0K    ./test
    4.0K    ./xtrabackup_binlog_info
    4.0K    ./xtrabackup_checkpoints
    4.0K    ./xtrabackup_info
    4.0K    ./xtrabackup_logfile

可以看到备份了my.cnf，ibdata1，各种数据库，这时候还多出来几个xtrabackup打头的文件  
查看这几个文件的内容如下：  
–xtrabackup\_binlog\_info文件记录了对应当下binlog的信息

[root@cxqtest 0408]# more xtrabackup_binlog_info
    more xtrabackup_binlog_info
    mybinlog.000006 191 82f3c6ed-007a-11e7-9b50-000c298ee31c:1-7

–xtrabackup\_checkpoints记录了备份的类型是全备，是从0到1626008的最新的全备

[root@cxqtest 0408]# more xtrabackup_checkpoints
    backup_type = full-backupedf
    rom_lsn = 0
    to_lsn = 1626008
    last_lsn = 1626008
    compact = 0
    recover_binlog_info = 0

–xtrabackup\_info记录的是命令的信息

[root@cxqtest 0408]# more xtrabackup_info
    uuid = f0083067-0086-11e7-8435-000c298ee31c
    name = innobackupextool
    command = -S /data/3306/mysql.sock /data/backup/0408/ --no-timestamp --no-lock --throttle=100 -uroot -pmysql123
    version = 2.4.6
    ibbackup_version = 2.4.6
    server_version = 5.6.30
    logstart_time = 2017-03-04 11:01:24
    end_time = 2017-03-04 11:02:01
    lock_time = 1488596521
    binlog_pos = filename 'mybinlog.000006', position '191', 
    GTID of the last change '82f3c6ed-007a-11e7-9b50-000c298ee31c:1-7'
    innodb_from_lsn = 0
    innodb_to_lsn = 1626008
    partial = N
    incremental = N
    format = file
    compact = N
    compressed = N
    encrypted = N

–xtrabackup\_logfile 看到logfile无法用more查看，是一个二进制日志文件格式，所以使用strings进行查看如下：  
记录的是备份的时间

[root@cxqtest 0408]# more xtrabackup_logfile
    --More--(88%)
    
    ?庰掟栶氿烉︷
    [root@cxqtest 0408]# XshellXshellXshellXshellXshellXshellXshellXshellXshellXshell -bash: XshellXshellXshellXshellXshellXshellXshellXshellXshellXshell: command not found
    [root@cxqtest 0408]#
     [root@cxqtest 0408]# strings xtrabackup_log
     filextrabkup 170304 11:01:25

## **2、全备恢复测试** ##

数据的恢复还是使用innobackupex这个工具  
这里的数据恢复分为两个步骤，prepare和还原恢复，prepare意义是如果我们备份数据的时候，存在未提交的事务，但是数据却存在于备份中，这样就是一个数据不一致的状态，在启动数据库的时候需要走一个前滚，然后是一个回滚的操作。这个体现主要就在于logfile和ibdata。是使用apply-log这个选项实现的。  
前提需要先将之前的目录删除，停掉mysql服务。

[root@cxqtest 0408]# /etc/init.d/mysqld stop
    Shutting down MySQL...                                     [  OK  ]
    [root@cxqtest 0408]# 
    [root@cxqtest 0408]# cd 
    [root@cxqtest ~]# 
    [root@cxqtest 3306]# ls
    data
    [root@cxqtest 3306]# mv data data.bak
    [root@cxqtest 3306]# ls
    data.bak
    [root@cxqtest 3306]# mkdir data

可以使用如下命令进行恢复操作：  
第一步：执行innobackupex –defaults-file=/data/backup/0408/backup-my.cnf -uroot -pmysql123 –apply-log /data/backup/0408/

[root@cxqtest 0408]# innobackupex --defaults-file=/data/backup/0408/backup-my.cnf -uroot -pmysql123 --apply-log /data/backup/0408/
    170304 12:31:01 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!".
    
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    xtrabackup: cd to /data/backup/0408/
    xtrabackup: This target seems to be not prepared yet.
    InnoDB: Number of pools: 1
    xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1626008)
    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 = 8388608
    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 = 8388608
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: PUNCH HOLE support available
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Uses event mutexes
    InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    InnoDB: Compressed tables use zlib 1.2.3
    InnoDB: Number of pools: 1
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    InnoDB: Completed initialization of buffer pool
    InnoDB: page_cleaner coordinator priority: -20
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: The log sequence number 1625998 in the system tablespace does not match the log sequence number 1626008 in the ib_logfiles!
    InnoDB: Database was not shutdown normally!
    InnoDB: Starting crash recovery.
    InnoDB: Doing recovery: scanned up to log sequence number 1626008 (0%)
    InnoDB: Creating shared tablespace for temporary tables
    InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    InnoDB: File './ibtmp1' size is now 12 MB.
    InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
    InnoDB: 32 non-redo rollback segment(s) are active.
    InnoDB: Waiting for purge to start
    InnoDB: 5.7.13 started; log sequence number 1626008
    
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: FTS optimize thread exiting.
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 1626027
    InnoDB: Number of pools: 1
    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 = 3
    xtrabackup:   innodb_log_file_size = 524288000
    InnoDB: PUNCH HOLE support available
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Uses event mutexes
    InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    InnoDB: Compressed tables use zlib 1.2.3
    InnoDB: Number of pools: 1
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    InnoDB: Completed initialization of buffer pool
    InnoDB: page_cleaner coordinator priority: -20
    InnoDB: Setting log file ./ib_logfile101 size to 500 MB
    InnoDB: Progress in MB:
     100 200 300 400 500
    InnoDB: Setting log file ./ib_logfile1 size to 500 MB
    InnoDB: Progress in MB:
     100 200 300 400 500
    InnoDB: Setting log file ./ib_logfile2 size to 500 MB
    InnoDB: Progress in MB:
     100 200 300 400 500
    InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
    InnoDB: New log files created, LSN=1626027
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 1626124
    InnoDB: Doing recovery: scanned up to log sequence number 1626133 (0%)
    InnoDB: Doing recovery: scanned up to log sequence number 1626133 (0%)
    InnoDB: Database was not shutdown normally!
    InnoDB: Starting crash recovery.
    InnoDB: Removed temporary tablespace data file: "ibtmp1"
    InnoDB: Creating shared tablespace for temporary tables
    InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    InnoDB: File './ibtmp1' size is now 12 MB.
    InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
    InnoDB: 32 non-redo rollback segment(s) are active.
    InnoDB: Waiting for purge to start
    InnoDB: page_cleaner: 1000ms intended loop took 54592ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
    InnoDB: 5.7.13 started; log sequence number 1626133
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: FTS optimize thread exiting.
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 1626152
    170304 12:32:00 completed OK!

然后执行如下：

[root@cxqtest 0408]# innobackupex --defaults-file=/data/backup/0408/backup-my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
    170304 12:32:42 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 version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    Error: datadir must be specified.

报错要执行datadir，原因是我指定的参数文件是备份的backup-my.cnf内容如下：

[root@cxqtest 0408]# cat backup-my.cnf 
    
    [mysqld]
    innodb_checksum_algorithm=innodb
    innodb_log_checksum_algorithm=innodb
    innodb_data_file_path=ibdata1:1G:autoextend
    innodb_log_files_in_group=3
    innodb_log_file_size=524288000
    innodb_fast_checksum=false
    innodb_page_size=16384
    innodb_log_block_size=512
    innodb_undo_directory=.
    innodb_undo_tablespaces=0
    server_id=3306
    
    redo_log_version=0

由于没有指定datadir导致报错，所以报错，这里直接指定原来的/etc/my.cnf

[root@cxqtest 3306]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
    170304 12:47:01 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 version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    170304 12:47:01 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0
    170304 12:47:22 [01]        ...done
    170304 12:47:22 [01] Copying ib_logfile1 to /data/3306/data/ib_logfile1
    170304 12:47:54 [01]        ...done
    170304 12:47:55 [01] Copying ib_logfile2 to /data/3306/data/ib_logfile2
    innobackupex: Error writing file '/data/3306/data/ib_logfile2' (Errcode: 28 - No space left on device)
    [01] Error: copy_file() failed.

再一次报错：不过这次报错很明显  
innobackupex: Error writing file ‘/data/3306/data/ib\_logfile2’ (Errcode: 28 - No space left on device)  
然后查看df -h:空间满了，无奈删除了一些其他的文件

[root@cxqtest 3306]# df -h
    Filesystem      Size  Used Avail Use% Mounted on
    /dev/sda2 36G 36G 0 100% /
    tmpfs           1.2G   68K  1.2G   1% /dev/shm
    /dev/sda1       194M   34M  150M  19% /boot
    [root@cxqtest 0408]# df -h
    Filesystem      Size  Used Avail Use% Mounted on
    /dev/sda2 36G 28G 5.6G 84% /
    tmpfs           1.2G   68K  1.2G   1% /dev/shm
    /dev/sda1       194M   34M  150M  19% /boot

然后继续如上的步骤：

[root@cxqtest 3306]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
    170304 12:50:52 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 version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    Original data directory /data/3306/data is not empty!

又报错！！！原因是第一次copyback回去了部分数据，目录不为空导致无法进行，然后将对应目录清空，继续进行如上copy-back操作

[root@cxqtest data]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
    170304 12:51:57 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 version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    170304 12:51:57 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0
    170304 12:52:18 [01]        ...done
    170304 12:52:18 [01] Copying ib_logfile1 to /data/3306/data/ib_logfile1
    170304 12:52:42 [01]        ...done
    170304 12:52:42 [01] Copying ib_logfile2 to /data/3306/data/ib_logfile2
    170304 12:53:06 [01]        ...done
    170304 12:53:07 [01] Copying ibdata1 to /data/3306/data/ibdata1
    170304 12:53:54 [01]        ...done
    .......省略一堆的copy输出日志
    170304 12:53:56 [01] Copying ./xtrabackup_info to /data/3306/data/xtrabackup_info
    170304 12:53:56 [01]        ...done
    170304 12:53:56 completed OK!
    [root@cxqtest data]# 
    [root@cxqtest data]# 
    [root@cxqtest data]# ls
    ibdata1  ib_logfile0  ib_logfile1  ib_logfile2  ibtmp1  mysql  performance_schema  test  xtrabackup_info
    [root@cxqtest data]# pwd
    /data/3306/data

终于将3306恢复回来了  
然后将对应的属组修改回MySQL，默认是root。启动MySQL服务登陆正常

[root@cxqtest data]# ll
    total 2596892
    -rw-r----- 1 root root 1073741824 Mar 4 12:53 ibdata1
    -rw-r----- 1 root root 524288000 Mar 4 12:52 ib_logfile0
    -rw-r----- 1 root root 524288000 Mar 4 12:52 ib_logfile1
    -rw-r----- 1 root root 524288000 Mar 4 12:53 ib_logfile2
    -rw-r----- 1 root root 12582912 Mar 4 12:53 ibtmp1
    drwxr-x--- 2 root root 4096 Mar 4 12:53 mysql
    drwxr-x--- 2 root root 4096 Mar 4 12:53 performance_schema
    drwxr-x--- 2 root root 4096 Mar 4 12:53 test
    -rw-r----- 1 root root 606 Mar 4 12:53 xtrabackup_info
    [root@cxqtest data]# chown -R mysql.mysql /data/3306/data
    [root@cxqtest data]# ll
    total 2596892
    -rw-r----- 1 mysql mysql 1073741824 Mar 4 12:53 ibdata1
    -rw-r----- 1 mysql mysql 524288000 Mar 4 12:52 ib_logfile0
    -rw-r----- 1 mysql mysql 524288000 Mar 4 12:52 ib_logfile1
    -rw-r----- 1 mysql mysql 524288000 Mar 4 12:53 ib_logfile2
    -rw-r----- 1 mysql mysql 12582912 Mar 4 12:53 ibtmp1
    drwxr-x--- 2 mysql mysql 4096 Mar 4 12:53 mysql
    drwxr-x--- 2 mysql mysql 4096 Mar 4 12:53 performance_schema
    drwxr-x--- 2 mysql mysql 4096 Mar 4 12:53 test
    -rw-r----- 1 mysql mysql 606 Mar 4 12:53 xtrabackup_info
    [root@cxqtest data]# /etc/init.d/mysqld start
    Starting MySQL................                             [  OK  ]
    [root@cxqtest data]# 
    [root@cxqtest data]# 
    [root@cxqtest data]# mysql -uroot -p
    mysEnter password: 
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    [root@cxqtest data]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.6.30-log Source distribution
    
    Copyright (c) 2000, 2013, 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.
    
    root@localhost:mysql.sock  12:56:04 [(none)]>
    root@localhost:mysql.sock  12:56:05 [(none)]>
    root@localhost:mysql.sock  12:56:05 [(none)]>
    root@localhost:mysql.sock  12:56:05 [(none)]>show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.14 sec)
    
    root@localhost:mysql.sock  12:56:08 [(none)]>
    root@localhost:mysql.sock  12:56:09 [(none)]>
    root@localhost:mysql.sock  12:56:09 [(none)]>exit

## **3、增量的备份恢复操作** ##

然后进行一下增量备份恢复的测试工作：  
首先在test数据库下创建一张测试表：

root@localhost:mysql.sock  13:15:47 [(none)]>use test;
    Database changed
    root@localhost:mysql.sock  13:15:49 [test]>
    root@localhost:mysql.sock  13:15:49 [test]>
    root@localhost:mysql.sock  13:15:49 [test]>
    root@localhost:mysql.sock  13:15:49 [test]>show tables;
    Empty set (0.05 sec)
    
    root@localhost:mysql.sock  13:15:56 [test]>
    root@localhost:mysql.sock  13:15:57 [test]>
    root@localhost:mysql.sock  13:15:57 [test]>create table t(id int ,name varchar(10));
    Query OK, 0 rows affected (0.80 sec)
    
    root@localhost:mysql.sock  13:16:28 [test]>insert into t values(1,'a'),(2,'b'),(3,'c'),(4,'d');
    Query OK, 4 rows affected (0.07 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    root@localhost:mysql.sock  13:17:52 [test]>select * from t;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | a    |
    |    2 | b    |
    |    3 | c    |
    |    4 | d    |
    +------+------+
    4 rows in set (0.00 sec)

由于之前已经做过了全备了，所以这里就直接可以做增量备份。  
使用如下命令进行增量备份：

innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --incremental-basedir=/data/backup/0408/incr --incremental /data/backup/0408/incr

很不幸失败了：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --incremental-basedir=/data/backup/0408/incr --incremental /data/backup/0408/incr
    170304 13:21:17 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!".
    
    xtrabackup: Error: cannot open /data/backup/0408/incr//xtrabackup_checkpoints xtrabackup: error: failed to read metadata from /data/backup/0408/incr//xtrabackup_checkpoints

原因嘛，也给出来了

error: failed to read metadata from /data/backup/0408/incr//xtrabackup_checkpoints

当然，这个错误很明显，就是要找到之前全备的一个基点，也就是增量备的起始点lsn，这样才能进行增量备份，但是这里我直接指定了增量备份的basedir目录/data/backup/0408/incr，所以导致报了错，然后修改回来就没有问题了。这里引用jeanron大师的总结，很精辟：  
原因就在于里面的一个关键文件 \_checkpoints  
使用增备得有一个参考点，从哪里开始，即从哪个LSN开始，这个LSN在指定的参数–incremental-basedir=/data/backup/0408/incr下不存在，因为这个是一个新目录，所以需要指向全库备份的目录。  
然后修复后备份就没问题了，英为有了这个参考点LSN，所以需要要说明的是这个备份其实有累计增量和差异增量了。  
这个怎么理解呢，比如周日做一个全备，周一做一个增备，周二做一个周日全备到周二的一个增备，这就是一个累计增量备份，而周三的时候做一个周二至周三数据变化的备份，就是一个差异增量备份。

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --incremental-basedir=/data/backup/0408/ --incremental /data/backup/0408/incr
    170304 13:21:52 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!".
    
    170304 13:21:53  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
    170304 13:21:53  version_check Connected to MySQL server
    170304 13:21:53  version_check Executing a version check against the server...
    170304 13:21:53  version_check Done.
    170304 13:21:53 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /data/3306/mysql.sock
    Using server version 5.6.30-log
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    incremental backup from 1626008 is enabled.
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /data/3306/data
    xtrabackup: open files limit requested 65535, set to 65535
    xtrabackup: using the following InnoDB configuration:
    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 = 3
    xtrabackup:   innodb_log_file_size = 524288000
    xtrabackup: using O_DIRECT
    InnoDB: Number of pools: 1
    170304 13:21:53 >> log scanned up to (1634925)
    xtrabackup: Generating a list of tablespaces
    InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
    xtrabackup: using the full scan for incremental backup
    170304 13:21:54 >> log scanned up to (1634925)
    170304 13:21:55 [01] Copying ./ibdata1 to /data/backup/0408/incr/2017-03-04_13-21-52/ibdata1.delta
    170304 13:21:55 >> log scanned up to (1634925)
    .....
    170304 13:22:14 [01]        ...done
    ......
    170304 13:22:18 Finished backing up non-InnoDB tables and files
    170304 13:22:18 [00] Writing xtrabackup_binlog_info
    170304 13:22:18 [00]        ...done
    170304 13:22:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '1634925'
    xtrabackup: Stopping log copying thread.
    .170304 13:22:18 >> log scanned up to (1634925)
    
    170304 13:22:18 Executing UNLOCK TABLES
    170304 13:22:18 All tables unlocked
    170304 13:22:18 Backup created in directory '/data/backup/0408/incr/2017-03-04_13-21-52/'
    MySQL binlog position: filename 'mybinlog.000001', position '574', GTID of the last change 'd26bc1be-0096-11e7-9c08-000c298ee31c:1-2'
    170304 13:22:18 [00] Writing backup-my.cnf
    170304 13:22:18 [00]        ...done
    170304 13:22:18 [00] Writing xtrabackup_info
    170304 13:22:18 [00]        ...done
    xtrabackup: Transaction log of lsn (1634925) to (1634925) was copied.
    170304 13:22:18 completed OK!
    [root@cxqtest incr]# ls
    2017-03-04_13-21-17  2017-03-04_13-21-52

为了区别两次增量的不同，继续插入

root@localhost:mysql.sock  13:31:16 [test]>insert into t values(5,'e'),(6,'f');
    Query OK, 2 rows affected (0.03 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    root@localhost:mysql.sock  13:31:36 [test]>select * from t;
    +------+------+
    | id | name | +------+------+
    |    1 | a    |
    |    2 | b    |
    |    3 | c    |
    |    4 | d    |
    |    5 | e    |
    | 6 | f | +------+------+
    6 rows in set (0.00 sec)

再次进行基于上一次增量备份的增量备份：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --incremental-basedir=/data/backup/0408/incr/2017-03-04_13-21-52 --incremental /data/backup/0408/incr
    170304 13:35:29 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!".
    
    170304 13:35:29  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
    170304 13:35:29  version_check Connected to MySQL server
    170304 13:35:29  version_check Executing a version check against the server...
    170304 13:35:29  version_check Done.
    170304 13:35:29 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /data/3306/mysql.sock
    Using server version 5.6.30-log
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    incremental backup from 1634925 is enabled.
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /data/3306/data
    xtrabackup: open files limit requested 65535, set to 65535
    xtrabackup: using the following InnoDB configuration:
    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 = 3
    xtrabackup:   innodb_log_file_size = 524288000
    xtrabackup: using O_DIRECT
    InnoDB: Number of pools: 1
    170304 13:35:29 >> log scanned up to (1639553)
    ......
    170304 13:35:49 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '1639553'
    xtrabackup: Stopping log copying thread.
    .170304 13:35:49 >> log scanned up to (1639553)
    
    170304 13:35:49 Executing UNLOCK TABLES
    170304 13:35:49 All tables unlocked
    170304 13:35:49 Backup created in directory '/data/backup/0408/incr/2017-03-04_13-35-29/'
    MySQL binlog position: filename 'mybinlog.000001', position '821', GTID of the last change 'd26bc1be-0096-11e7-9c08-000c298ee31c:1-3'
    170304 13:35:49 [00] Writing backup-my.cnf
    170304 13:35:49 [00]        ...done
    170304 13:35:49 [00] Writing xtrabackup_info
    170304 13:35:49 [00]        ...done
    xtrabackup: Transaction log of lsn (1639553) to (1639553) was copied.
    170304 13:35:50 completed OK!
    [root@cxqtest incr]# 
    [root@cxqtest incr]# ls
    2017-03-04_13-21-17  2017-03-04_13-21-52  2017-03-04_13-35-29

然后删除test数据库中的表t

root@localhost:mysql.sock  13:37:48 [(none)]>use test;
    Database changed
    root@localhost:mysql.sock  13:37:50 [test]>
    root@localhost:mysql.sock  13:37:50 [test]>
    root@localhost:mysql.sock  13:37:50 [test]>show tables;
    +----------------+
    | Tables_in_test | +----------------+
    | t | +----------------+
    1 row in set (0.00 sec)
    
    root@localhost:mysql.sock  13:37:53 [test]>drop table t;
    Query OK, 0 rows affected (0.13 sec)

然后我们对比一下全备，第一次增备，第二次增备的checkpoints文件内容：

[root@cxqtest incr]# cat ../xtrabackup_checkpoints ---全备
    backup_type = full-prepared
    from_lsn = 0
    to_lsn = 1626008
    last_lsn = 1626008
    compact = 0
    recover_binlog_info = 0
    [root@cxqtest incr]# cat 2017-03-04_13-21-52/xtrabackup_checkpoints --第一次增备
    backup_type = incremental
    from_lsn = 1626008
    to_lsn = 1634925
    last_lsn = 1634925
    compact = 0
    recover_binlog_info = 0
    [root@cxqtest incr]# cat 2017-03-04_13-35-29/xtrabackup_checkpoints --第二次增备
    backup_type = incremental
    from_lsn = 1634925
    to_lsn = 1639553
    last_lsn = 1639553
    compact = 0
    recover_binlog_info = 0

lsn值逐渐递增。  
增量还原分为两个步骤  
a.prepare

innobackupex –apply-log /path/to/BACKUP-DIR

此时数据可以被程序访问使用；可使用—use-memory选项指定所用内存以加快进度，默认100M；  
b.recover

innobackupex –copy-back /path/to/BACKUP-DIR

从my.cnf读取datadir/innodb\_data\_home\_dir/innodb\_data\_file\_path等变量

先复制MyISAM表，然后是innodb表，最后为logfile；

开始合并操做：  
第一次是全备的redo apply

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --apply-log --redo-only /data/backup/0408/
    170304 13:46:27 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!".
    
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    xtrabackup: cd to /data/backup/0408/
    xtrabackup: This target seems to be already prepared.
    InnoDB: Number of pools: 1
    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:1G:autoextend
    xtrabackup:   innodb_log_group_home_dir = .
    xtrabackup:   innodb_log_files_in_group = 3
    xtrabackup:   innodb_log_file_size = 524288000
    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 = 3
    xtrabackup:   innodb_log_file_size = 524288000
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: PUNCH HOLE support available
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Uses event mutexes
    InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    InnoDB: Compressed tables use zlib 1.2.3
    InnoDB: Number of pools: 1
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    InnoDB: Completed initialization of buffer pool
    InnoDB: page_cleaner coordinator priority: -20
    InnoDB: Highest supported file format is Barracuda.
    
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 1626237
    InnoDB: Number of pools: 1
    170304 13:46:28 completed OK!

第二次是第一次增备的redo apply

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --apply-log --redo-only /data/backup/0408/ --incremental-dir=/data/backup/0408/incr/2017-03-04_13-21-52
    170304 13:51:13 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!".
    
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    incremental backup from 1626008 is enabled.
    xtrabackup: cd to /data/backup/0408/
    xtrabackup: This target seems to be already prepared with --apply-log-only.
    InnoDB: Number of pools: 1
    xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1634925)
    .....
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: PUNCH HOLE support available
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Uses event mutexes
    InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    InnoDB: Compressed tables use zlib 1.2.3
    InnoDB: Number of pools: 1
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    InnoDB: Completed initialization of buffer pool
    InnoDB: page_cleaner coordinator priority: -20
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: The log sequence number 1626228 in the system tablespace does not match the log sequence number 1634925 in the ib_logfiles!
    InnoDB: Database was not shutdown normally!
    InnoDB: Starting crash recovery.
    InnoDB: Doing recovery: scanned up to log sequence number 1634925 (0%)
    InnoDB: xtrabackup: Last MySQL binlog file position 574, file name mybinlog.000001
    InnoDB: xtrabackup: Last MySQL binlog file position 574, file name mybinlog.000001
    170304 13:51:17 completed OK!

其实整个过程就是一个merge的过程，可以看到全备的checkpoint中的信息已经发生了变化。

[root@cxqtest 0408]# cat xtrabackup_checkpoints 
    backup_type = log-applied
    from_lsn = 0
    to_lsn = 1634925
    last_lsn = 1634925
    compact = 0
    recover_binlog_info = 0
    [root@cxqtest 2017-03-04_13-21-52]# cat xtrabackup_checkpoints 
    backup_type = incremental
    from_lsn = 1626008
    to_lsn = 1634925
    last_lsn = 1634925
    compact = 0
    recover_binlog_info = 0

然后进行第一次还原操作：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/incr/2017-03-04_13-21-52
    170304 13:58:13 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 version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    Original data directory /data/3306/data is not empty!

报错了，需要清空对应的数据文件目录，清空目录继续进行操作：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/incr/2017-03-04_13-21-52
    170304 14:00:31 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 version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    innobackupex: File 'ibdata1' not found (Errcode: 2 - No such file or directory)
    InnoDB: Operating system error number 2 in a file operation.
    InnoDB: The error means the system cannot find the path specified.
    [01] error: cannot open file ibdata1
    [01] Error: copy_file() failed.

又报错，说找不到ibdata1，其实这时候也应该明白了，其实merge合并之后到的是全备的checkpoint文件，所以要恢复的指定的应该是全备的目录才对。  
再次修改目录进行恢复，成功：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
    170304 14:02:30 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 version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    .....
    170304 14:03:59 completed OK!

然后赋予相应的mysql权限  
注意：此次没有关闭mysql服务  
登陆：

root@localhost:mysql.sock  14:14:27 [(none)]>use test;
    Database changed
    root@localhost:mysql.sock  14:14:32 [test]>show tables;
    +----------------+
    | Tables_in_test | +----------------+
    | t | +----------------+
    1 row in set (0.00 sec)
    
    root@localhost:mysql.sock  14:14:35 [test]>select * from t;
    ERROR 1146 (42S02): Table 'test.t' doesn't exist

竟然报错了，明明显示有表啊，为什么会报表不存在？  
重新启动mysql失败

[root@cxqtest incr]# /etc/init.d/mysqld restart
    MySQL server PID file could not be found!                  [FAILED]
    Starting MySQL......
    ...................The server quit without updating PID fil[FAILED]/3306/data/mysql.pid).

报错的原因就是找不到pid，幸好，清空目录的时候只是进行了mv操作，所以讲原pid再拷贝到data目录下，启动MySQL服务

[root@cxqtest incr]# /etc/init.d/mysqld start
    Starting MySQL                                             [  OK  ]
    [root@cxqtest incr]# 
    [root@cxqtest incr]# 
    [root@cxqtest incr]# mysql -uroot -p
    Enter password: 
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/3306/mysql.sock' (2)

但是很遗憾，虽然启动成功了，但是找不到sock，登陆不了MySQL数据库，这就尴尬了  
然后想着能不能再关闭重启一下是否可以

[root@cxqtest incr]# /etc/init.d/mysqld stop
    Shutting down MySQL..                                      [  OK  ]
    [root@cxqtest incr]# /etc/init.d/mysqld start
    Starting MySQL                                             [  OK  ]
    [root@cxqtest incr]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.6.30-log Source distribution
    
    Copyright (c) 2000, 2013, 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.
    
    root@localhost:mysql.sock  14:20:35 [(none)]>
    root@localhost:mysql.sock  14:20:36 [(none)]>
    root@localhost:mysql.sock  14:20:36 [(none)]>use test;
    Database changed
    root@localhost:mysql.sock  14:20:39 [test]>show tables;
    +----------------+
    | Tables_in_test | +----------------+
    | t | +----------------+
    1 row in set (0.00 sec)
    
    root@localhost:mysql.sock  14:20:41 [test]>select * from t;
    +------+------+
    | id | name | +------+------+
    |    1 | a    |
    |    2 | b    |
    |    3 | c    |
    | 4 | d | +------+------+
    4 rows in set (0.11 sec)

终于恢复回来了，好不容易！！  
TIPS：在进行恢复的过程当中还是需要关闭服务再恢复，再启动服务，不然的话，还是会出现上述的报错，还挺麻烦。  
这个过程我们相当于完成了一个全备+一个增备的数据恢复过程。  
而我们在一个增备之后又插入了一些数据，这个怎么继续恢复呢，还是prepare的过程。这个路径需要注意，还是merge到全备中。  
tips：在prepare阶段，不能关闭MySQL的服务，在copy的阶段再进行关闭MySQL服务的操作

innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --apply-log --redo-only /data/backup/0408/ --incremental-dir=/data/backup/0408/incr/2017-03-04_13-35-29

查看xtrabackup\_checkpoints

[root@cxqtest 0408]# cat xtrabackup_checkpoints 
    backup_type = log-applied
    from_lsn = 0
    to_lsn = 1634925
    last_lsn = 1634925
    compact = 0
    recover_binlog_info = 0
    [root@cxqtest 0408]# 
    [root@cxqtest 0408]# 
    [root@cxqtest 0408]# cat xtrabackup_checkpoints 
    backup_type = log-applied
    from_lsn = 0
    to_lsn = 1639553
    last_lsn = 1639553
    compact = 0
    recover_binlog_info = 0

已经更新到了最新的lsn号  
如下，进行恢复–ps：这次进行MySQL服务停止尝试一下  
同样，清空data目录

[root@cxqtest 3306]# cd data/
    [root@cxqtest data]# ls
    auto.cnf     ib_logfile1  innodb_status.12062  mysql               test
    error.log    ib_logfile2  mybinlog.000001      mysql.pid           xtrabackup_binlog_pos_innodb
    ibdata1      ibtmp1       mybinlog.000002      performance_schema  xtrabackup_info
    ib_logfile0  incr         mybinlog.index       slow.log
    [root@cxqtest data]# /etc/init.d/mysqld stop
    Shutting down MySQL..                                      [  OK  ]
    [root@cxqtest data]# 
    [root@cxqtest data]# mv * ../bak/
    [root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
    170304 14:35:05 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 version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    170304 14:35:05 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0
    170304 14:37:00 completed OK!

恢复完成，重复赋予恢复文件的MySQL属组权限，启动MySQL服务：  
如果没有服务权限就会报如下的错：

[root@cxqtest data]# /etc/init.d/mysqld start
    Starting MySQL..The server quit without updating PID file ([FAILED]06/data/mysql.pid).
    [root@cxqtest data]# /etc/init.d/mysqld start
    Starting MySQL                                             [  OK  ]
    [root@cxqtest data]# 
    [root@cxqtest data]# /etc/init.d/mysqld stop
    Shutting down MySQL...The server quit without updating PID [FAILED]ata/3306/data/mysql.pid).
    [root@cxqtest data]# /etc/init.d/mysqld start
    Starting MySQL                                             [  OK  ]
    [root@cxqtest data]# /etc/init.d/mysqld stop
    MySQL server PID file could not be found!                  [FAILED]
    [root@cxqtest data]# 
    [root@cxqtest data]# ls
    2017-03-04_14-29-25  ib_logfile0  ibtmp1               innodb_status.17127  performance_schema  xtrabackup_binlog_pos_innodb
    error.log            ib_logfile1  incr                 mybinlog.index       slow.log            xtrabackup_info
    ibdata1              ib_logfile2  innodb_status.14679  mysql                test
    [root@cxqtest data]# ll
    total 2596920
    drwxr-x--- 2 root root 4096 Mar 4 14:36 2017-03-04_14-29-25
    -rw-r----- 1 mysql root 14301 Mar 4 14:43 error.log
    -rw-r----- 1 root root 1073741824 Mar 4 14:36 ibdata1
    -rw-r----- 1 root root 524288000 Mar 4 14:35 ib_logfile0
    -rw-r----- 1 root root 524288000 Mar 4 14:35 ib_logfile1
    -rw-r----- 1 root root 524288000 Mar 4 14:36 ib_logfile2
    -rw-r----- 1 root root 12582912 Mar 4 14:37 ibtmp1
    drwxr-x--- 2 root root 4096 Mar 4 14:36 incr
    -rw-rw---- 1 mysql mysql 0 Mar 4 14:39 mybinlog.index
    drwxr-x--- 2 root root 4096 Mar 4 14:36 mysql
    drwxr-x--- 2 root root 4096 Mar 4 14:37 performance_schema
    -rw-rw---- 1 mysql mysql 543 Mar 4 14:43 slow.log
    drwxr-x--- 2 root root 4096 Mar 4 14:36 test
    -rw-r----- 1 root root 20 Mar 4 14:37 xtrabackup_binlog_pos_innodb
    -rw-r----- 1 root root 625 Mar 4 14:37 xtrabackup_info
    [root@cxqtest data]# pwd
    /data/3306/data
    [root@cxqtest data]# chown -R mysql.mysql *
    [root@cxqtest data]# 
    [root@cxqtest data]# 
    [root@cxqtest data]# /etc/init.d/mysqld start
    Starting MySQL..                                           [  OK  ]
    [root@cxqtest data]# 
    [root@cxqtest data]# ls
    2017-03-04_14-29-25  ib_logfile0  incr                 mysql               test
    auto.cnf             ib_logfile1  innodb_status.19581  mysql.pid           xtrabackup_binlog_pos_innodb
    error.log            ib_logfile2  mybinlog.000001      performance_schema  xtrabackup_info
    ibdata1              ibtmp1       mybinlog.index       slow.log

然后进行还原恢复：

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf -uroot -pmysql123 --copy-back /data/backup/0408/
    170304 15:02:39 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 version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    170304 15:02:39 [01] Copying ib_logfile0 to /data/3306/data/ib_logfile0
    170304 15:04:03 [01] Copying ./xtrabackup_info to /data/3306/data/xtrabackup_info
    170304 15:04:03 [01]        ...done
    170304 15:04:03 completed OK!

恢复完成，然后在data目录下赋予MySQL属组权限，启动MySQL服务：

[root@cxqtest bak]# /etc/init.d/mysqld start
    Starting MySQL                                             [  OK  ]
    [root@cxqtest bak]# 
    [root@cxqtest bak]# 
    [root@cxqtest bak]# /etc/init.d/mysqld stop
    Shutting down MySQL..                                      [  OK  ]
    [root@cxqtest bak]# /etc/init.d/mysqld start
    Starting MySQL..                                           [  OK  ]

然后登陆数据库进行查询操作：

root@localhost:mysql.sock  15:11:42 [(none)]>use test;
    Database changed
    root@localhost:mysql.sock  15:11:44 [test]>
    root@localhost:mysql.sock  15:11:44 [test]>
    root@localhost:mysql.sock  15:11:45 [test]>
    root@localhost:mysql.sock  15:11:45 [test]>show tables;
    +----------------+
    | Tables_in_test | +----------------+
    | t | +----------------+
    1 row in set (0.00 sec)
    
    root@localhost:mysql.sock  15:11:48 [test]>select * from t;
    +------+------+
    | id | name | +------+------+
    |    1 | a    |
    |    2 | b    |
    |    3 | c    |
    |    4 | d    |
    |    5 | e    |
    | 6 | f | +------+------+
    6 rows in set (0.03 sec)

可以看到第二次增量备添加的数据被恢复的回来。

## **4、备份中的常用选项和场景** ##

innobackupex中的选项很多，常用的比如stream选项，–slave-info选项能够方便搭建从库，生成偏移量的信息，比如并行–parallel等，还可以根据LSN来备份，选项是–incremental-lsn  
对于stream选项，默认是打包，可以结合管道来实现压缩，比如：  
innobackupex –defaults-file=/etc/my.cnf –user=root –stream=tar /data/backup/0408/ | gzip > /data/backup/0408/0408.tar.gz  
很多时候其实我不想备份整个库，我只想备份一个表，那么这个操作如何来实现呢。  
innobackupex –defaults-file=/etc/my.cnf –user=root -pn–include=’test.t’ /data/backup/0408  
这里有几点需要注意，工具还是会逐个去扫描，只是那些不符合的会被忽略掉，也就意味着备份出来的情况和全备的目录结构是一样的，但是指定的表会备份出ibd，frm文件。

[root@cxqtest bak]# ll
    total 1036
    -rw-r--r-- 1 mysql mysql    8556 Mar 22 18:34 t.frm
    -rw-r--r-- 1 root  root  1048576 Mar 22 19:26 t.ibd
    [root@cxqtest bak]# cd ..

而这种情况下，ibdata也会完整备份出来，如果这个文件很大，那就相当不给力了。  
不过有一个场景还是很实用的。那就是迁移表。

## **5、迁移表实验** ##

如果我们还有一个实例3307的数据库，想把3306库中的test.t表导入到3307的test数据库中我们可以使用Innobackupex来做物理备份，然后还原导入，达到迁移的目的。

[root@cxqtest incr]# innobackupex --defaults-file=/etc/my.cnf --user=root -pmysql123 --include='test.t' /data/backup/0408
    170304 15:21:16 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!".
    
    170304 15:21:16  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/3306/mysql.sock' as 'root'  (using password: YES).
    170304 15:21:16  version_check Connected to MySQL server
    xtrabackup: Transaction log of lsn (1626267) to (1626267) was copied.
    170304 15:21:51 completed OK!
    [root@cxqtest 0408]# cd 2017-03-04_15-21-16
    [root@cxqtest 2017-03-04_15-21-16]# ls
    2017-03-04_14-29-25  2017-03-04_14-56-20  ibdata1  test                    xtrabackup_checkpoints  xtrabackup_logfile
    2017-03-04_14-53-36  backup-my.cnf        incr     xtrabackup_binlog_info  xtrabackup_info
    [root@cxqtest 2017-03-04_15-21-16]# cat xtrabackup_checkpoints 
    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 1626267
    last_lsn = 1626267
    compact = 0
    recover_binlog_info = 0

下面的命令会声明指定目录下的备份需要导出对象。

[root@cxqtest 2017-03-04_15-21-16]# ls
    2017-03-04_14-29-25  2017-03-04_14-56-20  ibdata1  test                    xtrabackup_checkpoints  xtrabackup_logfile
    2017-03-04_14-53-36  backup-my.cnf        incr     xtrabackup_binlog_info  xtrabackup_info
    [root@cxqtest 2017-03-04_15-21-16]# cd test/
    [root@cxqtest test]# ll
    total 108
    -rw-r----- 1 root root 8586 Mar 4 15:21 t.frm
    -rw-r----- 1 root root 98304 Mar 4 15:21 t.ibd
    [root@cxqtest incr]# innobackupex --apply-log --export /data/backup/0408/2017-03-04_15-21-16
    170304 15:31:06 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!".
    
    innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    xtrabackup: auto-enabling --innodb-file-per-table due to the --export option
    xtrabackup: cd to /data/backup/0408/2017-03-04_15-21-16/
    xtrabackup: This target seems to be not prepared yet.
    InnoDB: Number of pools: 1
    xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1626267)
    .....
    InnoDB: Shutdown completed; log sequence number 1626664
    170304 15:31:52 completed OK!

直接结果就是多了如下的文件：

[root@cxqtest test]# ll
    total 128
    -rw-r--r-- 1 root root 420 Mar 4 15:31 t.cfg -rw-r----- 1 root root 16384 Mar 4 15:31 t.exp -rw-r----- 1 root root 8586 Mar 4 15:21 t.frm -rw-r----- 1 root root 98304 Mar 4 15:21 t.ibd

然后在新的3307的test数据库中创建表t，并且对表t信息做截断：

root@localhost:mysql.sock  15:35:24 [(none)]>use test;
    Database changed
    root@localhost:mysql.sock  15:35:26 [test]>
    root@localhost:mysql.sock  15:35:26 [test]>
    root@localhost:mysql.sock  15:51:16 [test]>create table t(id int ,name varchar(10));
    Query OK, 0 rows affected (0.34 sec)
    root@localhost:mysql.sock  15:35:26 [test]>alter table t discard tablespace;
    Query OK, 0 rows affected (0.26 sec)

然后将exp和ibd文件拷贝到目标目录然后修改属组导入即可（如是导入到mysql5.6拷贝*.cfg，而不是*.exp）：

[root@cxqtest test]# cp t.exp /data/3307/data/test/
    [root@cxqtest test]# cp t.ibd /data/3307/data/test/
    [root@cxqtest test]# cp t.cfg /data/3307/data/test/
    [root@cxqtest test]# cd /data/3307/data/test/
    [root@cxqtest test]# ll
    total 128
    -rw-r--r-- 1 root root 420 Mar 4 15:47 t.cfg
    -rw-r----- 1 root root 16384 Mar 4 15:38 t.exp
    -rw-r----- 1 root root 98304 Mar 4 15:39 t.ibd
    [root@cxqtest test]# chown mysql.mysql t.*
    [root@cxqtest test]# lltotal 128[root@cxqtest test]# ll
    total 116
    -rw-r--r-- 1 mysql mysql 420 Mar 4 15:47 t.cfg
    -rw-r----- 1 mysql mysql 16384 Mar 4 15:38 t.exp
    -rw-r----- 1 mysql mysql 98304 Mar 4 15:39 t.ibd

然后再在test数据库中对表t进行导入操作：

root@localhost:mysql.sock  15:53:10 [test]>alter table t import tablespace;
    Query OK, 0 rows affected (0.13 sec)
    root@localhost:mysql.sock  15:53:41 [test]>show tables;
    +----------------+
    | Tables_in_test | +----------------+
    | t | +----------------+
    1 row in set (0.00 sec)
    
    root@localhost:mysql.sock  15:53:46 [test]>select * from t;
    +------+------+
    | id | name | +------+------+
    |    1 | a    |
    |    2 | b    |
    |    3 | c    |
    |    4 | d    |
    |    5 | e    |
    | 6 | f | +------+------+
    6 rows in set (0.00 sec)

可以看到数据从3306的test数据库中导入到了3307的数据库当中。  
如果不是按如上步骤进行操作会报如下的错误：  
原因就是，1、在没有对表进行创建和discard之前就将文件拷贝到了test目录下；2、表创建完成以后，没有将对应的文件拷贝到test目录下，导致数据库无法找到对应的文件

root@localhost:mysql.sock  15:49:44 [test]>alter table t import tablespace;
    ERROR 1146 (42S02): Table 'test.t' doesn't exist root@localhost:mysql.sock 15:49:47 [test]>alter table test.t import tablespace; ERROR 1146 (42S02): Table 'test.t' doesn't exist
    root@localhost:mysql.sock  15:49:53 [test]>
    root@localhost:mysql.sock  15:49:54 [test]>
    root@localhost:mysql.sock  15:49:54 [test]>
    root@localhost:mysql.sock  15:50:31 [test]>
    root@localhost:mysql.sock  15:50:32 [test]>create table t(id int ,name varchar(10));
    ERROR 1813 (HY000): Tablespace for table '`test`.`t`' exists. Please DISCARD the tablespace before IMPORT.
    root@localhost:mysql.sock  15:50:44 [test]>alter table test2 discard tablespace;
    ERROR 1146 (42S02): Table 'test.test2' doesn't exist root@localhost:mysql.sock 15:51:07 [test]>alter table t discard tablespace; ERROR 1146 (42S02): Table 'test.t' doesn't exist

有另外一点值得说的是，这个.exp文件是不是必须的，其实也不是。  
我们只拷贝.ibd文件也照样可以。可能在新版本中会有一些警告提示，我们重新来做一下。

root@localhost:mysql.sock  15:50:32 [test]>alter table t discard tablespace;
    Query OK, 0 rows affected (0.03 sec)

同时删除刚刚拷贝过来的.exp文件。  
然后拷贝ibd文件到指定目录，赋权限  
导入表空间信息。

root@localhost:mysql.sock  15:50:32 [test]> alter table t import tablespace; 
    Query OK, 0 rows affected (0.00 sec)

[http_chuansong.me_n_1696438151334]: http://chuansong.me/n/1696438151334