一、 Xtrabackup 安装使用

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数据表

Innobackupex完整备份后生成了几个重要的文件：

xtrabackup_binlog_info：记录当前最新的LOG Position

xtrabackup_binlog_pos_innodb:innodb log postion

xtrabackup_checkpoints: 存放备份的起始位置beginlsn和结束位置endlsn,增量备份需要这个lsn[增量备份可以在这里面看from和to两个值的变化]

Xtrabackup特点：

(1)备份过程快速、可靠

(2)备份过程不会打断正在执行的事务

(3)能够基于压缩等功能节约磁盘空间和流量

(4)自动实现备份检验

(5)还原速度快

二安装: http://www.percona.com/downloads/

下载安装：

直接下载二进制包，不编译，编译需要和MySQL源码包配合。

wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.9/binary/Linux/x86_64/percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz

1、解压 tar 包
 tar zxvf percona-xtrabackup-2.1.9-744-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

Ubuntu 下的安装:http://www.percona.com/doc/percona-xtrabackup/2.1/installation/apt_repo.html

1：执行
apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

2：在 vi /etc/apt/sources.list 添加
deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main

3：安装
$ apt-get update
$ apt-get install percona-xtrabackup

支持的版本有：

Debian

6.0 (squeeze)

7.0 (wheezy)

Ubuntu

10.04LTS (lucid)

12.04LTS (precise)

12.10 (quantal)

13.04 (raring)

13.10 (saucy)

CentoS

CentoS5.x

CentoS6.x

CentoS7.x

RedHat 5.x

RedHat 6.x

RedHat 7.x

把2中的 VERSION 按照自己的系统把上面的代入。

deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

# precise是Ubuntu-12.04的版本代号，如果是其它系统版本，需要更换。

三使用说明:

安装完之后会生成几个工具：http://www.percona.com/doc/percona-xtrabackup/2.1/manual.html

innobackupex：这个是其实是下面三个工具的一个perl脚本封装，可以备份MyISAM, InnoDB, XtraDB表。但在处理Myisam时需要加一个读锁。

xtrabackup：一个由C编译而来的二进制文件，只能备份InnoDB和XtraDB数据。

xbcrypt：用来加密或解密备份的数据。

xbstream：用来解压或压缩xbstream格式的压缩文件。

innobackupex ：

xtrabackup命令只备份数据文件,并不备份数据表结构(.frm)，所以使用xtrabackup恢复的时候必须有对应表结构文件(.frm)。用innobackupex命令，此命令相当于冷备份，复制数据目录的索引，数据，结构文件，但会有短暂的锁表（时间依赖于MyISAM大小）。

参数：

--defaults-file：指定my.cnf参数文件的位置[此配置文件里必须指定datadir]
--apply-log：同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--remote-host=HOSTNAME： 通过ssh将备份数据存储到进程服务器上
--stream=[tar]：备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会撑满你的/tmp目录,可以通过参数--tmpdir指定目录来解决这个问题.
--tmpdir=DIRECTORY：当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir
--redo-only --apply-log：强制备份日志时只redo,跳过rollback,这在做增量备份时非常必要
--use-memory=*：该参数在prepare的时候使用,控制prepare时innodb实例使用的内存
--databases=LIST：列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份
--slave-info：备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0
--socket=SOCKET：指定mysql.sock所在位置，以便备份进程登录mysql.

更多参数见：http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html

四测试：

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

posted @ 2019-05-04 21:46 Boks 阅读(361) 评论(0) 收藏举报

刷新页面返回顶部

tddey

一、 Xtrabackup 安装使用

公告

tddey

一 、 Xtrabackup 安装使用

公告

一、 Xtrabackup 安装使用