percona之xtrabackup备份工具

7865432

下载：wget https://www.percona.com/download ... .3-1.el6.x86_64.rpm
安装：yum -y localinstall percona-xtrabackup-24-2.4.3-1.el6.x86_64.rpm
创建一个用于备份的用户：
mysql>create user bkpuser@localhost identified by 'redhat';
mysql>grant reload,lock tables,replication client on *.* to 'bkpuser'@'localhost';
mysql>flush privileges;
生产线上可以用master来备份（架构单一情况下），如果架构是一主多从，可以单独使用一台从库进行备份
创建一个用于存放数据的目录：
mkdir /backup/{full,inc}
full：全备存放的数据；inc：增量备份存放的数据

开始备份：全备
shell>innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=redhat --no-lock /backup/
报错如下：
[iyunv@aliyun_test ~]# innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=redhat /backup/
160808 14:04:09 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!".
160808 14:04:09  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'bkpuser'  (using password: YES).

160808 14:04:09  version_check Connected to MySQL server
160808 14:04:09  version_check Executing a version check against the server...
160808 14:04:09  version_check Done.
160808 14:04:09 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 0, socket: /tmp/mysql.sock
Using server version 5.6.30-log
Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
可以看出备份没有process权限，于是删除用户重新授权

mysql>create user bkpuser@localhost identified by 'redhat';

mysql>grant reload,lock tables,process，replication client on *.* to 'bkpuser'@'localhost';

mysql>flush privileges;

shell>innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=redhat --no-lock /backup/
出现如下，表示成功：
160808 14:33:29 Backup created in directory '/backup/2016-08-08_14-33-26'
MySQL binlog position: filename 'mysql-bin.000006', position '3146'
160808 14:33:29 [00] Writing backup-my.cnf
160808 14:33:29 [00]        ...done
160808 14:33:29 [00] Writing xtrabackup_info
160808 14:33:29 [00]        ...done
xtrabackup: Transaction log of lsn (1680472) to (1680472) was copied.
160808 14:33:29 completed OK!
至此全备完全成功，然后向mysql某个库插入几条数据，然后进行增量备份
innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=redhat --socket=/tmp/mysql.sock --incremental --incremental-basedir=/backup/full/2016-08-09_09-43-34/ /backup/inc/
增量备份报错：innobackupex version 2.4.3 based on MySQL server 5.7.11 Linux (x86_64)
由于我实验用的是mysql5.6的版本，所以xtrabackup版本高了，换个低版本试试
重新安装xtrabackup工具：
wget https://www.percona.com/redir/do ... se-0.1-3.noarch.rpm
yum -y install percona-release-0.1-3.noarch.rpm
yum -y install percona-xtrabackup qpress（这里的qpress是解压缩要用到的）
全备步骤省略，继续执行增量备份

innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=redhat --socket=/tmp/mysql.sock --incremental --incremental-basedir=/backup/full/2016-08-09_09-43-34/ /backup/inc/
再次向mysql插入几条数据，继续增量备份第二次：

增备2：innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=redhat --socket=/tmp/mysql.sock --incremental --incremental-basedir=/backup/inc/2016-08-09_09-44-05/ /backup/inc/
然后进行apply-log，redo-only

innobackupex --apply-log --redo-only /backup/full/2016-08-09_09-43-34/
innobackupex --apply-log --redo-only /backup/full/2016-08-09_09-43-34 --incremental-dir=/backup/inc/2016-08-09_09-44-05
innobackupex --apply-log /backup/full/2016-08-09_09-44-05 --incremental-dir=/backup/inc/2016-08-09_09-44-08
模拟恢复：

service mysqld stop停止数据库
mv /mydata/data /mydata/data_bak
mkdir /mydata/data
innobackupex --copy-back /backup/full/2016-08-09_10-26-12/
chown -R mysql:mysql /mydata/data
service mysqld start

全量备份压缩打包：
innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=redhat --socket=/tmp/mysql.sock --compress --stream=xbstream /tmp/ > /tmp/$(date +%F).xbstream

解压恢复：
[iyunv@aliyun_test tmp]# ll
-rw-r--r-- 1 root  root  1823081 Aug 10 08:26 2016-08-10.xbstream

[iyunv@aliyun_test tmp]# mkdir 2016-08-10
[iyunv@aliyun_test tmp]# xbstream -x < 2016-08-10.xbstream -C /tmp/2016-08-10/：解开xbstream流数据

[iyunv@aliyun_test tmp]# cd 2016-08-10
[iyunv@aliyun_test 2016-08-10]# ll
total 256
-rw-r----- 1 root root    295 Aug 10 08:41 backup-my.cnf
drwx------ 2 root root   4096 Aug 10 08:41 huang
-rw-r----- 1 root root 222610 Aug 10 08:41 ibdata1.qp
drwx------ 2 root root   4096 Aug 10 08:41 mysql
drwx------ 2 root root   4096 Aug 10 08:41 performance_schema
drwx------ 2 root root   4096 Aug 10 08:41 test
-rw-r----- 1 root root     21 Aug 10 08:41 xtrabackup_binlog_info
-rw-r----- 1 root root     89 Aug 10 08:41 xtrabackup_checkpoints
-rw-r----- 1 root root    638 Aug 10 08:41 xtrabackup_info
-rw-r----- 1 root root   2560 Aug 10 08:41 xtrabackup_logfile

[iyunv@aliyun_test 2016-08-10]# innobackupex --decompress /tmp/2016-08-10
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 4011
        main::check_args() called at /usr/bin/innobackupex line 1531
innobackupex: Error: --decompress requires qpress

[iyunv@aliyun_test 2016-08-10]# innobackupex --decompress /tmp/2016-08-10：解压qp的压缩文件
成功

[iyunv@aliyun_test 2016-08-10]# ll
total 12544
-rw-r----- 1 root root      295 Aug 10 08:41 backup-my.cnf
drwx------ 2 root root     4096 Aug 10 08:57 huang
-rw-r--r-- 1 root root 12582912 Aug 10 08:57 ibdata1
-rw-r----- 1 root root   222610 Aug 10 08:41 ibdata1.qp
drwx------ 2 root root     4096 Aug 10 08:57 mysql
然后将.qp后缀的文件进行删除，删除之后恢复数据就和上面一样的步骤
[iyunv@aliyun_test 2016-08-10]# find ./ -name "*.qp" -delete：用这条命令进行删除
[iyunv@aliyun_test 2016-08-10]# ll
total 12324
-rw-r----- 1 root root      295 Aug 10 08:41 backup-my.cnf
drwx------ 2 root root     4096 Aug 10 09:01 huang
-rw-r--r-- 1 root root 12582912 Aug 10 08:57 ibdata1
drwx------ 2 root root     4096 Aug 10 09:01 mysql
innobackupex备份和恢复MySQL.pdf (654.16 KB, 下载次数: 0)

2016-8-17 10:00 上传

点击文件名下载附件

Hot Backups with Percona XtraBackup.pdf (192.9 KB, 下载次数: 0)

2016-8-17 10:00 上传

点击文件名下载附件