Xtrabackup数据库热备
一 简介:
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）。XtraBackup支持所有的Percona Server、MySQL、MariaDB和Drizzle。
xtrabackup有两个主要的工具：xtrabackup、innobackupex
(1).xtrabackup只能备份InnoDB和XtraDB 两种数据表 (2).innobackupex则封装了xtrabackup,同时可以备份MyISAM数据表
xtrabackup_binlog_info：记录当前最新的LOG Position xtrabackup_binlog_pos_innodb:innodb log postion xtrabackup_checkpoints: 存放备份的起始位置beginlsn和结束位置endlsn,增量备份需要这个lsn[增量备份可以在这里面看from和to两个值的变化]
(1)备份过程快速、可靠 (2)备份过程不会打断正在执行的事务 (3)能够基于压缩等功能节约磁盘空间和流量 (4)自动实现备份检验 (5)还原速度快
二 安装: http://www.percona.com/downloads/
下载安装：
1）安装依赖包
yum -y install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.* package
2）直接下载二进制包，不编译，编译需要和MySQL源码包配合。
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/tarball/percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz
1）tar zxvf percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz 2）ls -lh drwxr-xr-x 2 root root 4.0K 5月 2 2014 bin drwxr-xr-x 4 root root 4.0K 5月 2 2014 share 3） ls -lh bin/ -rwxr-xr-x 1 root root 165K 5月 2 2014 innobackupex lrwxrwxrwx 1 root root 12 5月 2 2014 innobackupex-1.5.1 -> innobackupex -rwxr-xr-x 1 root root 2.2M 5月 2 2014 xbcrypt -rwxr-xr-x 1 root root 2.2M 5月 2 2014 xbstream -rwxr-xr-x 1 root root 13M 5月 2 2014 xtrabackup -rwxr-xr-x 1 root root 16M 5月 2 2014 xtrabackup_55 -rwxr-xr-x 1 root root 79M 5月 2 2014 xtrabackup_56 4） cp innobackupex-1.5.1 /usr/bin/innobackupex cp xtrabackup_55 /usr/bin/xtrabackup #cp xtrabackup /usr/bin
三 使用说明:
innobackupex：这个是其实是下面三个工具的一个perl脚本封装，可以备份MyISAM, InnoDB, XtraDB表。但在处理Myisam时需要加一个读锁。 xtrabackup：一个由C编译而来的二进制文件，只能备份InnoDB和XtraDB数据。 xbcrypt：用来加密或解密备份的数据。 xbstream：用来解压或压缩xbstream格式的压缩文件。
innobackupex ：
xtrabackup命令只备份数据文件,并不备份数据表结构(.frm)，所以使用xtrabackup恢复的时候必须有对应表结构文件(.frm)。用innobackupex命令，此命令相当于冷备份，复制数据目录的索引，数据，结构文件，但会有短暂的锁表（时间依赖于MyISAM大小）。
参数详解：
#常用参数 #压缩参数 #加密参数 #增量备份参数 #主从 --include：该选项表示使用正则表达式匹配表的名字[db.tb]，要求为其指定匹配要备份的表的完整名称，即databasename.tablename。
--apply-log：该选项表示同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
--decompress：该选项表示解压--compress选项压缩的文件。
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir。
|
四 测试：
1)全量备份&还原
#初始化
mysql> create database xtra_test default charset utf8; Query OK, 1 row affected (0.00 sec) mysql> use xtra_test Database changed mysql> create table M(id int,name varchar(10))engine=myisam; Query OK, 0 rows affected (0.00 sec) mysql> create table I(id int,name varchar(10))engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> insert into M values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into I values(11,'A'),(22,'B'),(33,'C'),(44,'D'),(55,'E'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from M; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +------+------+ 5 rows in set (0.00 sec) mysql> select * from I; +------+------+ | id | name | +------+------+ | 11 | A | | 22 | B | | 33 | C | | 44 | D | | 55 | E | +------+------+ 5 rows in set (0.00 sec)
#备份
root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test /home/zhoujy/xtrabackup/
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
141111 11:56:58 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup;host=192.168.200.51' as 'zjy' (using password: YES).
141111 11:56:58 innobackupex: Connected to MySQL server
141111 11:56:58 innobackupex: Executing a version check against the server...
141111 11:56:58 innobackupex: Done.
141111 11:56: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.38-0ubuntu0.12.04.1-log
innobackupex: Created backup directory /home/zhoujy/xtrabackup/2014-11-11_11-56-58
141111 11:56:58 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/etc/mysql/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58 --innodb_log_file_size="5242880" --innodb_data_file_path="ibdata1:10M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --databases='xtra_test'
innobackupex: Waiting for ibbackup (pid=10539) to suspend
innobackupex: Suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_suspended_2'
xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
>> log scanned up to (7363097930)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /home/zhoujy/xtrabackup/2014-11-11_11-56-58/ibdata1
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
>> log scanned up to (7363097930)
[01] ...done
>> log scanned up to (7363097930)
xtrabackup: Creating suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_suspended_2' with pid '10540'
141111 11:57:11 innobackupex: Continuing after ibbackup has suspended
141111 11:57:11 innobackupex: Executing FLUSH TABLES WITH READ LOCK...
141111 11:57:11 innobackupex: All tables locked and flushed to disk
141111 11:57:11 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/db.opt'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.MYI'
>> log scanned up to (7363097930)
innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.frm'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/I.frm'
innobackupex: Backing up file '/var/lib/mysql//xtra_test/M.MYD'
141111 11:57:11 innobackupex: Finished backing up non-InnoDB tables and files
141111 11:57:11 innobackupex: Executing FLUSH ENGINE LOGS...
141111 11:57:11 innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '7363097930'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (7363097930)
xtrabackup: Creating suspend file '/home/zhoujy/xtrabackup/2014-11-11_11-56-58/xtrabackup_log_copied' with pid '10540'
xtrabackup: Transaction log of lsn (7363097930) to (7363097930) was copied.
141111 11:57:12 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/zhoujy/xtrabackup/2014-11-11_11-56-58'
innobackupex: MySQL binlog position: filename 'mysql-bin51.000001', position 967
141111 11:57:12 innobackupex: Connection to database server closed
141111 11:57:12 innobackupex: completed OK!
查看备份出来的文件：
ls -lh 2014-11-11_11-56-58/ total 1.2G -rw-r--r-- 1 root root 188 11月 11 11:56 backup-my.cnf -rw-r----- 1 root root 1.2G 11月 11 11:57 ibdata1 -rw-r--r-- 1 root root 25 11月 11 11:57 xtrabackup_binlog_info -rw-r----- 1 root root 95 11月 11 11:57 xtrabackup_checkpoints -rw-r--r-- 1 root root 666 11月 11 11:57 xtrabackup_info -rw-r----- 1 root root 2.5K 11月 11 11:57 xtrabackup_logfile drwxr-xr-x 2 root root 4.0K 11月 11 11:57 xtra_test
/home/zhoujy/xtrabackup/ 备份存放的位置，备份会在该目录下生成一个按照时间命名的文件夹。用--no-timestamp参数可以指定到自己想要的备份文件夹，不受时间命名的文件夹限制。
利用 --apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
root@ha1:/home/zhoujy/xtrabackup# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_11-56-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.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
141111 12:02:42 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!".
141111 12:02:42 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/home/zhoujy/xtrabackup/2014-11-11_11-56-58/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58
xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: cd to /home/zhoujy/xtrabackup/2014-11-11_11-56-58
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(7363097930)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M: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:10M: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.4
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 7363094028 and 7363094028 in ibdata files do not match the log sequence number 7363097930 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 967, file name /var/log/mysql/mysql-bin51.000001
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.21 started; log sequence number 7363097930
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 967, file name /var/log/mysql/mysql-bin51.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 7363100603
141111 12:02:44 innobackupex: Restarting xtrabackup with command: xtrabackup --defaults-file="/home/zhoujy/xtrabackup/2014-11-11_11-56-58/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/home/zhoujy/xtrabackup/2014-11-11_11-56-58
for creating ib_logfile*
xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: cd to /home/zhoujy/xtrabackup/2014-11-11_11-56-58
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
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.4
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 5 MB
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=7363100603
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.21 started; log sequence number 7363100684
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 967, file name /var/log/mysql/mysql-bin51.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 7363103417
141111 12:02:45 innobackupex: completed OK!
应用完之后再查看备份文件看是否有变化：
root@ha1:/home/zhoujy/xtrabackup# ls -lh 2014-11-11_11-56-58/ total 1.2G -rw-r--r-- 1 root root 188 11月 11 11:56 backup-my.cnf -rw-r----- 1 root root 1.2G 11月 11 12:02 ibdata1 -rw-r--r-- 1 root root 5.0M 11月 11 12:02 ib_logfile0 -rw-r--r-- 1 root root 5.0M 11月 11 12:02 ib_logfile1 -rw-r--r-- 1 root root 25 11月 11 11:57 xtrabackup_binlog_info -rw-r--r-- 1 root root 38 11月 11 12:02 xtrabackup_binlog_pos_innodb -rw-r----- 1 root root 95 11月 11 12:02 xtrabackup_checkpoints -rw-r--r-- 1 root root 666 11月 11 11:57 xtrabackup_info -rw-r----- 1 root root 2.0M 11月 11 12:02 xtrabackup_logfile drwxr-xr-x 2 root root 4.0K 11月 11 11:57 xtra_test
看到redo log已经出现。xtrabackup_开头的几个文件记录了一些日志偏移量的信息和日志名和时间等信息。在xtrabackup_checkpoints中记录了备份的模式：
backup_type = full-backuped。
xtrabackup_binlog_info：记录当前最新的LOG Position xtrabackup_binlog_pos_innodb:innodb log postion xtrabackup_checkpoints: 存放备份的起始位置beginlsn和结束位置endlsn,增量备份需要这个lsn[增量备份可以在这里面看from和to两个值的变化 xtrabackup_info:备份的一些具体信息日志
#还原
mysql> drop database xtra_test; Query OK, 2 rows affected (0.00 sec)
1) 删除原始目录里的文件：
root@ha1:/var/lib/mysql# rm ib_logfile0 ib_logfile1 ibdata1
关闭数据库：
1) root@ha1:/var/lib/mysql# /etc/init.d/mysql stop
开始恢复
2) 1：innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /home/zhoujy/xtrabackup/2014-11-11_11-56-58/
执行上面的命令需要保证数据库目录是空的，否则会失败，因为目录里有binlog日志，要么移走，要么就用手动复制需要的文件。所以说日志不要和数据放在一起！
2：cp ib_logfile0 ib_logfile1 ibdata1 /var/lib/mysql/
cp -R xtra_test /var/lib/mysql/
修改权限和开启数据库
3) root@ha1:/var/lib/mysql# chown -R mysql.mysql ib*
root@ha1:/var/lib/mysql# chown -R mysql.mysql xtra_test/
开启数据库
root@ha1:/var/lib/mysql# /etc/init.d/mysql start
...
mysql start/running, process 15937
数据已恢复：
mysql> use xtra_test Database changed mysql> select * from I; +------+------+ | id | name | +------+------+ | 11 | A | | 22 | B | | 33 | C | | 44 | D | | 55 | E | +------+------+ 5 rows in set (0.00 sec)
2)增量备份&还原
#备份
在做增量备份的时候需要做一次全量备份，和上面一样，只是针对所有库的备份：
root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf /home/zhoujy/xtrabackup/ ...
innobackupex: MySQL binlog position: filename 'mysql-bin51.000002', position 107 141111 13:57:24 innobackupex: Connection to database server closed 141111 13:57:24 innobackupex: completed OK!
增量备份这里开始
1.数据库操作：
mysql> select * from I;
+------+------+
| id | name |
+------+------+
| 11 | A |
| 22 | B |
| 33 | C |
| 44 | D |
| 55 | E |
+------+------+
5 rows in set (0.00 sec)
mysql> insert into I values(111,'A'),(222,'B'),(333,'C'),(444,'D'),(555,'E');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from M;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+------+------+
5 rows in set (0.00 sec)
mysql> update M set name = upper(name);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> create table X(name varchar(20))default charset utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into X values('zjy');
Query OK, 1 row affected (0.00 sec)
2：增量备份（基于全量的增量备份）
root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --incremental --incremental-basedir=/home/zhoujy/xtrabackup/2014-11-11_13-57-11/ /home/zhoujy/increment_data/ ... ... trabackup: Creating suspend file '/home/zhoujy/increment_data/2014-11-11_14-14-56/xtrabackup_log_copied' with pid '19492' xtrabackup: Transaction log of lsn (7363106612) to (7363106612) was copied. 141111 14:15:06 innobackupex: All tables unlocked innobackupex: Backup created in directory '/home/zhoujy/increment_data/2014-11-11_14-14-56' innobackupex: MySQL binlog position: filename 'mysql-bin51.000002', position 981 141111 14:15:06 innobackupex: Connection to database server closed 141111 14:15:06 innobackupex: completed OK!
其中，--incremental指明是增量备份，--incremental-basedir指定上次完整备份或者增量备份文件的位置。这里的增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。
增量备份的文件：
root@ha1:/home/zhoujy/increment_data# ls -lh 2014-11-11_14-14-56/ total 440K -rw-r--r-- 1 root root 188 11月 11 14:14 backup-my.cnf -rw-r----- 1 root root 400K 11月 11 14:15 ibdata1.delta -rw-r----- 1 root root 44 11月 11 14:14 ibdata1.meta drwxr-xr-x 2 root root 4.0K 11月 11 14:15 mha_test drwxr-xr-x 2 root root 4.0K 11月 11 14:15 mysql drwxr-xr-x 2 root root 4.0K 11月 11 14:15 performance_schema -rw-r--r-- 1 root root 25 11月 11 14:15 xtrabackup_binlog_info -rw-r----- 1 root root 102 11月 11 14:15 xtrabackup_checkpoints -rw-r--r-- 1 root root 738 11月 11 14:15 xtrabackup_info -rw-r----- 1 root root 2.5K 11月 11 14:15 xtrabackup_logfile drwxr-xr-x 2 root root 4.0K 11月 11 14:15 xtra_test root@ha1:/home/zhoujy/increment_data# cat 2014-11-11_14-14-56/xtrabackup_checkpoints backup_type = incremental from_lsn = 7363103427 对应增量中的to_lsn to_lsn = 7363106612 last_lsn = 7363106612 compact = 0
3:继续增量备份
数据库操作
mysql> insert into X values('dxy');
Query OK, 1 row affected (0.01 sec)
mysql> insert into X values('浙江');
Query OK, 1 row affected (0.01 sec)
增量备份（基于增量的增量备份）
root@ha1:~# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --incremental --incremental-basedir=/home/zhoujy/increment_data/2014-11-11_14-14-56/ /home/zhoujy/increment_data/ 141111 14:25:46 innobackupex: Connection to database server closed 141111 14:25:46 innobackupex: completed OK!
增量备份的文件
root@ha1:/home/zhoujy/increment_data# cat 2014-11-11_14-25-36/xtrabackup_checkpoints backup_type = incremental from_lsn = 7363106612 对应上一个备份的to_lsn to_lsn = 7363107216 last_lsn = 7363107216 compact = 0
要是在第2次做增量备份的时候 --incremental-basedir 指向全量备份，则第一次增量备份中的数据会被第2次包含，只需要还原一次就可以恢复，现在则需要还原2次增量备份。
#还原
#还原全量备份： root@ha1:/home/zhoujy# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/ #第一次的增量备份应用日志,应用完日志后,将合并到全备上,恢复使用全备恢复 root@ha1:~# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/ --incremental-dir=/home/zhoujy/increment_data/2014-11-11_14-14-56/ #第二次的增量备份应用日志,应用完日志后,将合并到全备上,恢复使用全备恢复 root@ha1:~# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/ --incremental-dir=/home/zhoujy/increment_data/2014-11-11_14-25-36/ 此时两次增量备份其实都合并到全备上了,恢复是只需要使用全备进行恢复就可以了
还原操作执行完之后，查看其lsn的信息：
root@ha1:/home/zhoujy/xtrabackup/2014-11-11_13-57-11# cat xtrabackup_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 7363107216 last_lsn = 7363107216 compact = 0
说明了，还原操作执行没问题。按照之前还原操作的步骤来恢复数据：先删除数据，再把文件复制过去，看看是否成功。测试下来，还原成功。
3）压缩备份：必须使用-i参数拆包解压
打包(Tar)备份
innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test --stream=tar /home/zhoujy/xtrabackup/ 1>/home/zhoujy/xtrabackup/xtra_test.tar
拆包还原
root@ha1:/home/zhoujy/xtrabackup# tar ixvf xtra_test.tar ./backup-my.cnf ibdata1 xtra_test/db.opt xtra_test/M.MYI xtra_test/M.frm xtra_test/I.frm xtra_test/M.MYD ./xtrabackup_binlog_info xtrabackup_logfile xtrabackup_checkpoints ./xtrabackup_info
还原方法和上面一样，就不多做说明了。
打包压缩：
root@ha1:/home/zhoujy/xtrabackup# innobackupex --user=zjy --password=123456# --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test --stream=tar /home/zhoujy/xtrabackup/ | gzip >/home/zhoujy/xtrabackup/xtra_test.tar.gz
解压：
root@ha1:/home/zhoujy/xtrabackup# tar izxvf xtra_test.tar.gz ./backup-my.cnf ibdata1 xtra_test/db.opt xtra_test/M.MYI xtra_test/M.frm xtra_test/I.frm xtra_test/M.MYD ./xtrabackup_binlog_info xtrabackup_logfile xtrabackup_checkpoints ./xtrabackup_info
还原方法和上面一样。
总结：
- 增量备份是基于增量或全备的基础之上完成的。
- 增量备份的基础是InnoDB引擎使用了LSN机制，非InnoDB引擎不存在增量备份的说法，每次都是全备。
- 对于增量备份的恢复期间需要对已提交的事务前滚，未提交的事务回滚。
- 增量备份的恢复应按照备份的顺利逐个逐个replay，需要使用--apply-log --redo-only选项。
- 仅仅最后一个增量备份不需要使用–redo-only选项。
- 如果要做完全恢复或时点恢复，需要结合binlog来实现
注意：
1.datadir目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-backup选项不会覆盖
2.在restore之前,必须shutdown MySQL实例，你不能将一个运行中的实例restore到datadir目录中
3.由于文件属性会被保留，大部分情况下你需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户
chown -R mysql:mysql /var/lib/mysql/
以上需要在用户调用Innobackupex之前完成
--force-non-empty-directories：指定该参数时候，使得innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败。
浙公网安备 33010602011771号