Xtrabackup 流备份与恢复

转载

yzy121403725 2017-03-09 16:25:55 博主文章分类：mysql等关系型数据库

Xtrabackup是MySQL 数据库的备份不可多得的工具之一。提供了全备，增备，数据库级别，表级别备份等等。最牛X的还有不落盘的备份，即流备份方式。对于服务器上空间不足，或是搭建主从，直接使用流式备份大大简化了备份后的压缩复制所带来的更多开销。Xtrabackup支持tar格式以及xbstream格式的流备份。本文即是对此展开的相关描述。

1、基于tar格式备份
a、备份到本地
# innobackupex --stream=tar /tmp >/backup/bak.tar ###非压缩方式
# innobackupex --stream=tar /tmp |gzip >/backup/bakz.tar.gz ###压缩方式
# ls -hltr
total 42M
-rw-r--r-- 1 root root 39M Apr 15 17:23 bak.tar
-rw-r--r-- 1 root root 3.3M Apr 15 17:24 bakz.tar.gz

###解压备份
# mkdir bak bakz
# tar -xivf bak.tar -C /backup/bak
# tar -xizvf bakz.tar.gz -C /backup/bakz
# du -sh *
38M bak
39M bak.tar
38M bakz
3.3M bakz.tar.gz

b、备份到远程
# innobackupex --stream=tar /tmp | ssh root@192.168.1.7 \ "cat - > /backup/bak.tar" ###非压缩方式
# innobackupex --stream=tar /tmp | ssh root@192.168.1.7 \ "gzip >/backup/bak.tar.gz" ###压缩方式

2、使用xbstream格式备份
a、备份到本地
# innobackupex --stream=xbstream /tmp >/backup/bak.xbstream ###非压缩方式
# innobackupex --stream=xbstream --compress /tmp >/backup/bak_compress.xbstream ###压缩方式
# ls -hltr
total 43M
-rw-r--r-- 1 root root 37M Apr 15 17:41 bak.xbstream
-rw-r--r-- 1 root root 6.0M Apr 15 17:41 bak_compress.xbstream

###解压备份
# mkdir bk bk_compress
# xbstream -x < bak.xbstream -C /backup/bk ###解压xbstream格式

###解压xbstream格式，compress参数的备份
# xbstream -x < bak_compress.xbstream -C /backup/bk_compress                         ###首先解压xbstream
# for bf in `find . -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) && rm $bf; done ###再解压qp压缩格式
# innobackupex --decompress /backup/bk_compress     ###如果xtrabackup版本大于2.1.4，可以直接通过该方式解压

b、备份到远程
###使用压缩备份到远程并解压
# innobackupex --stream=xbstream --compress /tmp | ssh root@192.168.1.7 "xbstream -x -C /backup/stream"

加速备份进程

使用--parallel 与 –compress-threads进行加速：

当进行本地备份或使用xbstream进行流式备份时，使用--parallel 可同时拷贝多个文件，该参数指定了xtrabackup拷贝数据文件的线程数。默认情况下 xtrabackup 备份时只会开启一个进程进行数据文件的备份，若配置参数 –parallel=N 可以让 xtrabackup 开启 N 个子进程对多个数据文件进行并发备份，这样可以加快备份的速度。利用该选项特性，需要开启独立表空间（innodb_file_per_table =1）或使用共享表空间的或通过innodb_data_file_path参数将ibdata文件存储为多份不同文件。因为该特性的实现在文件级别。

innobackupex –defaults-file=/etc/mysql/my.cnf --parallel=10 --stream=tar /data/7_15 | gzip > /data/7_15.tar.gz

流式备份时使用xbstream可以通过--compress-threads选项加速压缩进程，该选项指定了xtrabackup进行并行压缩时使用的线程数。默认线程数为1。

本地流式压缩备份示例如下：

[root@localhost bin]# ./innobackupex --stream=xbstream --compress --compress-threads=16 ./ > backup.xbstream

3、流备份的全备与增备
###全备数据库，使用--extra-lsndir参数生产checkpoints文件
# innobackupex --stream=xbstream --compress --extra-lsndir=/backup/chkpoint /tmp >/backup/bak_compress.xbstream
# more /backup/chkpoint/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 8408290
last_lsn = 8408290
compact = 0

### Author : Leshami
### Blog : http://blog.csdn.net/leshami
###增备数据库，如果后续还需要再次增备，则可以再次指定--extra-lsndir，如果与上次备份指定相同的位置，该文件被覆盖
# innobackupex --compress --incremental --extra-lsndir=/backup/chkpoint --incremental-basedir=/backup/chkpoint \
> --stream=xbstream /tmp >/backup/bak_compress_inc.xbstream

4、流备份的异机恢复
###备份到异机
# innobackupex --stream=xbstream --extra-lsndir=/backup/chkpoint /tmp | ssh root@192.168.1.7 "xbstream -x -C /backup/stream"
# innobackupex --incremental --extra-lsndir=/backup/chkpoint --incremental-basedir=/backup/chkpoint --stream=xbstream \
> /tmp | ssh root@192.168.1.7 "xbstream -x -C /backup/stream_inc"

###异机恢复，copy-back及后续步骤省略
# innobackupex --apply-log --redo-only /backup/stream
# innobackupex --apply-log /backup/stream --incremental-dir=/backup/stream_inc

5、其它注意事项
a、如果使用xbstream格式异机备份时，异机未安装xbstream(封装在xtrabackup中)则出现如下错误提示。
  bash: xbstream: command not found
  xtrabackup: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)
  xb_stream_write_data() failed.
  compress: write to the destination stream failed.
  xtrabackup: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)
  xb_stream_write_data() failed.
  xtrabackup: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)
  [01] xtrabackup: Error: xtrabackup_copy_datafile() failed.
  [01] xtrabackup: Error: failed to copy datafile.
  innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2681.

b、异机备份时需要建立等效性，如下示例
  # ssh-keygen
  Generating public/private rsa key pair.
  Enter file in which to save the key (/root/.ssh/id_rsa):
  Enter passphrase (empty for no passphrase):
  Enter same passphrase again:
  Your identification has been saved in /root/.ssh/id_rsa.
  Your public key has been saved in /root/.ssh/id_rsa.pub.
  The key fingerprint is:
  29:45:ee:8d:b3:55:f1:5f:2f:da:2a:88:0c:0d:37:9f root@vdbsrv1

  ###copy 公钥到远程主机
  # ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.1.7
  21
  The authenticity of host '192.168.1.7 (192.168.1.7)' can't be established.
  RSA key fingerprint is 1d:7c:40:98:ef:de:6f:b8:8c:b2:87:72:0e:79:db:0a.
  Are you sure you want to continue connecting (yes/no)? yes
  Warning: Permanently added '192.168.1.7' (RSA) to the list of known hosts.
  root@192.168.1.7's password:
  Now try logging into the machine, with "ssh 'root@192.168.1.7'", and check in:

    .ssh/authorized_keys

  to make sure we haven't added extra keys that you weren't expecting.

  ###验证等效性是否成功
  # ssh 192.168.1.7 date;
  Wed Apr 15 17:55:31 CST 2015

c、使用tar格式远程增量备份时收到如下提示，即只支持xbstream
xtrabackup: error: streaming incremental backups are incompatible with the
'tar' streaming format. Use --stream=xbstream instead.

d、用流备份，默认的临时目录都是系统的/tmp目录，需要保证该目录有足够的空间，或指定--tmpdir选项

e、流备份日志输出
innobackupex --stream=xbstream /tmp 2>>"$backupLog" | gzip > "$backup_file" 2>>"$backupLog"