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/
下载安装：
1）直接下载二进制包，不编译，编译需要和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 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

三使用说明:
安装完之后会生成几个工具：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)

#备份

innobackupex --user=root --password=root --host=192.168.20.5 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test /home/jacky/xtrabackup/

或者

xtrabackup --user=root --password=root --target-dir=/data/backups/ --backup // Creating a backup
xtrabackup --user=root --password=root --target-dir=/data/backups/ --copy-back // Restoring a Backup

查看备份出来的文件：
ls -lh 2017-10-11_11-56-58/
total 1.2G
-rw-r--r-- 1 root root 188 10月 11 11:56 backup-my.cnf
-rw-r----- 1 root root 1.2G 10月 11 11:57 ibdata1
-rw-r--r-- 1 root root 25 10月 11 11:57 xtrabackup_binlog_info
-rw-r----- 1 root root 95 10月 11 11:57 xtrabackup_checkpoints
-rw-r--r-- 1 root root 666 10月 11 11:57 xtrabackup_info
-rw-r----- 1 root root 2.5K 10月 11 11:57 xtrabackup_logfile
drwxr-xr-x 2 root root 4.0K 10月 11 11:57 xtra_test

/home/jacky/xtrabackup/ 备份存放的位置，备份会在该目录下生成一个按照时间命名的文件夹。用--no-timestamp参数可以指定到自己想要的备份文件夹，不受时间命名的文件夹限制。

恢复日志文件：
利用 --apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
innobackupex --user=root --password=root --apply-log /home/jacky/xtrabackup/2017-10-11_11-43-59

应用完之后再查看备份文件看是否有变化：

[root@zkteco ~] /home/jacky/xtrabackup# ls -lh 2014-10-11_11-56-58/
total 1.2G
-rw-r--r-- 1 root root  188 10月 11 11:56 backup-my.cnf
-rw-r----- 1 root root 1.2G 10月 11 12:02 ibdata1
-rw-r--r-- 1 root root 5.0M 10月 11 12:02 ib_logfile0
-rw-r--r-- 1 root root 5.0M 10月 11 12:02 ib_logfile1
-rw-r--r-- 1 root root   25 10月 11 11:57 xtrabackup_binlog_info
-rw-r--r-- 1 root root   38 10月 11 12:02 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root   95 10月 11 12:02 xtrabackup_checkpoints
-rw-r--r-- 1 root root  666 10月 11 11:57 xtrabackup_info
-rw-r----- 1 root root 2.0M 10月 11 12:02 xtrabackup_logfile
drwxr-xr-x 2 root root 4.0K 10月 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:备份的一些具体信息日志

#还原

1）删除数据库
mysql> drop database xtra_test;
Query OK, 2 rows affected (0.00 sec)

2) 删除原始目录里的文件：
[root@zkteco ~]#:/var/lib/mysql# rm ib_logfile0 ib_logfile1 ibdata1

3）关闭数据库：
1) [root@zkteco ~]#:/var/lib/mysql# /etc/init.d/mysql stop

开始恢复
2) 1：innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /home/jacky/xtrabackup/2017-10-11_11-56-58/
执行上面的命令需要保证数据库目录是空的，否则会失败，因为目录里有binlog日志，要么移走，要么就用手动复制需要的文件。所以说日志不要和数据放在一起！

或者

恢复数据文件：
innobackupex --user=root --password=root --copy-back /data/u02/2017-10-11_03-00-02/

2：cp ib_logfile0 ib_logfile1 ibdata1 /var/lib/mysql/
cp -R xtra_test /var/lib/mysql/
修改权限和开启数据库
3) [root@zkteco ~]#: /var/lib/mysql# chown -R mysql.mysql ib*
[root@zkteco ~]#: /var/lib/mysql# chown -R mysql.mysql xtra_test/
开启数据库
[root@zkteco ~]#: /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)

增量备份&还原

在做增量备份的时候需要做一次全量备份，和上面一样，只是针对所有库的备份：

[root@zkteco ~]# innobackupex --user=root --password=root --host=192.168.5.100 --defaults-file=/etc/mysql/my.cnf /home/jacky/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@zkteco ~]#:~# 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@zkteco ~]#:/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@zkteco ~]#:/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@zkteco ~]#:~# innobackupex --user=root--password=root --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --incremental --incremental-basedir=/home/jacky/increment_data/2017-10-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@zkteco ~]#:/home/jacky/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@zkteco ~]#:/home/zhoujy# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/

#第一次的增量备份应用日志,应用完日志后,将合并到全备上,恢复使用全备恢复
[root@zkteco ~]#:~# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/ --incremental-dir=/home/zhoujy/increment_data/2017-10-11_14-14-56/

#第二次的增量备份应用日志,应用完日志后,将合并到全备上,恢复使用全备恢复
[root@zkteco ~]#:~# innobackupex --apply-log /home/zhoujy/xtrabackup/2014-11-11_13-57-11/ --incremental-dir=/home/zhoujy/increment_data/2017-10-11_14-25-36/
此时两次增量备份其实都合并到全备上了,恢复是只需要使用全备进行恢复就可以了

还原操作执行完之后，查看其lsn的信息：

[root@zkteco ~]#:/home/zhoujy/xtrabackup/2017-10-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=root--password=root --host=192.168.200.51 --defaults-file=/etc/mysql/my.cnf --databases=xtra_test --stream=tar /home/jacky/xtrabackup/ 1>/home/zhoujy/xtrabackup/xtra_test.tar

拆包还原
[root@zkteco ~]#::/home/jacky/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@zkteco ~]#:/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@zkteco ~]#:/home/jacky/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
还原方法和上面一样。

全量备份脚本(生产已使用）

#!/bin/bash


DB_user="root"
DB_passwd="root"
DB1="mysql"
BK_dir="/backDB"
BK_time=`date "+%Y%m%d_%H:%M"`
log=${BK_time}"_backup.log"
DB_host="localhost"
DB_socket="/data/mysql.sock"
#判断备份目录是否存在
if [ ! -d "$BK_dir" ]
        then
                mkdir $BK_dir
        fi


#判断log目录是否存在
if [ ! -d "$BK_dir/logs" ]
        then
                mkdir $BK_dir/logs
        fi


ft=$BK_dir"/full_backuptime.log"
if [ ! -f "$ft" ]
        then
                touch $ft
	fi
# --database=$DB1
innobackupex --host=$DB_host --user=$DB_user --password=$DB_passwd --socket=$DB_socket $BK_dir/ 2>$BK_dir/logs/$log
#innobackupex   --database=$DB1 --host=$DB_host --user=$DB_user --password=$DB_passwd --socket=$DB_socket $BK_dir/ 2>$BK_dir/logs/$log


if [[ $? = 0 ]]
        then
                var=`cat $BK_dir/logs/$log | grep "Backup created"`
                echo $var |cut -d "'" -f 2 >> $ft
        else
                date "+%Y-%m-%d %H:%M" >> $BK_dir/logs/error.log
                df $BK_dir >> $BK_dir/logs/error.log
        fi

安装percona-xtrabackup备份工具

1）、安装依赖包libev.so.4， perl-DBD-MySQL

libev.so.4 下载

wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4%28%29%2864bit%29&submit=Search+...&system=&arch=

perl-DBD-MySQL 本地Yum源安装

2）安装percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm

下载地址 https://www.percona.com/downloads/XtraBackup/LATEST/

增量备份脚本（生产已使用）

#!/bin/bash

DB_user="root"
DB_passwd="root"
DB1="mysql"
BK_dir="/backDB"
BK_time=`date "+%Y%m%d_%H%M"`
DB_host="localhost"
log=${BK_time}"_inc.log"
DB_socket="/data/mysql.sock"
#֒µ½֢¸ሆۍ떻±¸·ݵŎļþ¼у
BK_fdir=`cat ${BK_dir}/full_backuptime.log | tail -f -n 1|cut -d / -f 3`

BK_inc="inc"_${BK_fdir}



if [ ! -d "$BK_dir/$BK_inc" ]
        then
                mkdir -p $BK_dir/$BK_inc
        fi


#--database=$DB1 
innobackupex --database=$DB1 --user=$DB_user --host=$DB_host --socket=$DB_socket --password=$DB_passwd --incremental --incremental-basedir=$BK_dir/$
BK_fdir/ --stream=xbstream $BK_dir/$BK_inc/ 2>$BK_dir/logs/$log | gzip > $BK_dir/$BK_inc/inc_${BK_time}.gz

FULL BACKUP：
xtrabackup --user=root --password=root --target-dir=/data/backups/ --backup   // Creating a backup
xtrabackup --user=root --password=root --target-dir=/data/backups/ --copy-back  // Restoring a Backup
恢复后master  lve复制关系没了，binlog重新开始于mysql-bin.000001

在线备份：
innobackupex --user=root --password=root  /data/backups/

注：还原数据前需要删除之前的库，停止mysql服务，删除库文件

先彻底杀掉被恢复数据的主机的相关mysql进程
[root@zkteco backups]# ps -fu mysql
UID        PID  PPID  C STIME TTY          TIME CMD
mysql     1297     1  0 00:34 ?        00:00:00 rpc.statd
[root@zkteco backups]# kill -9  1297
恢复日志文件：
innobackupex --user=root --password=root --apply-log /data/backups/2016-12-12_11-43-59
恢复数据文件：
innobackupex --user=root --password=root --copy-back /data/backups/2016-12-12_11-43-59

update mysql.user set password=password('123456') where user='root'

grant select,insert,update,delete,create on `aut`.* to 'root'@'172.31.10.%' identified by '123456';

flush privileges;
show processlist

CREATE DATABASE zkteco DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

秒客网

Xtrabackup数据库的数据备份和还原

相关文章