xtrabackup第三方备份工具

Xtrabackup 是percona公司的开源项目，用以实现类似innodb官方的热备份工具InnoDB Hot Backup的功能，能够非常快速地备份与恢复mysql数据库。 Xtrabackup中包含两个工具：
xtrabackup是用于热备份innodb, xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

下载地址

http://www.percona.com/software/percona-xtrabackup

下载安装xtrabackup

下载libev-4.15-1.el6.rf.x86_64.rpm 包
https://centos.pkgs.org/6/repoforge-x86_64/libev-4.15-1.el6.rf.x86_64.rpm.html
[root@www.cndba.cn software]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
配置yum源 安装perl-Digest-MD5包
参考yum 配置方法：http://www.cndba.cn/dave/article/154
[root@www.cndba.cn software]# yum -y install perl-Digest-MD5
安装percona xtrabackup
[root@www.cndba.cn software]# rpm -ivh percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

为备份建立一个只有备份权限的用户

MariaDB [(none)]> create user 'dbbackup'@'localhost' identified by '123456';
MariaDB [(none)]> grant all privileges on *.* to 'dbbackup'@'localhost';
MariaDB [(none)]> flush privileges;

查看cndba数据库所有表

[root@www.cndba.cn mysql]# mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or /g.
Your MariaDB connection id is 10
Server version: 10.2.10-MariaDB-log MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

MariaDB [(none)]> use cndba
Database changed
MariaDB [cndba]> show tables;
+-----------------+
| Tables_in_cndba |
+-----------------+
| depart_pos      |
| department      |
| staffer         |
| student         |
| test            |
| test12          |
| test123         |
| test2           |
+-----------------+
9 rows in set (0.00 sec)

全量备份数据库

innobackupex --host=localhost --user=dbbackup --password=123456 /backup/
…
171213 17:49:47 Backup created in directory '/backup/2017-12-13_17-49-45/'
MySQL binlog position: filename 'mysql-bin.000016', position '14912', GTID of the last change '0-67-162'
171213 17:49:47 [00] Writing /backup/2017-12-13_17-49-45/backup-my.cnf
171213 17:49:47 [00]        ...done
171213 17:49:47 [00] Writing /backup/2017-12-13_17-49-45/xtrabackup_info
171213 17:49:47 [00]        ...done
xtrabackup: Transaction log of lsn (2008920) to (2008929) was copied.
171213 17:49:47 completed OK!

创建cndba 表

MariaDB [cndba]> create table cndba (id int);
Query OK, 0 rows affected (0.03 sec)

增量备份数据库

[root@www.cndba.cn backup]# innobackupex --host=localhost --user=dbbackup --password=123456  --incremental --incremental-basedir=/backup/2017-12-13_17-49-45/ /backup/incremental/
171213 22:31:47 Executing UNLOCK TABLES
171213 22:31:47 All tables unlocked
171213 22:31:47 [00] Copying ib_buffer_pool to /backup/incremental/2017-12-13_22-31-44/ib_buffer_pool
171213 22:31:47 [00]        ...done
171213 22:31:47 Backup created in directory '/backup/incremental/2017-12-13_22-31-44/'
MySQL binlog position: filename 'mysql-bin.000001', position '465', GTID of the last change '0-67-1'
171213 22:31:47 [00] Writing /backup/incremental/2017-12-13_22-31-44/backup-my.cnf
171213 22:31:47 [00]        ...done
171213 22:31:47 [00] Writing /backup/incremental/2017-12-13_22-31-44/xtrabackup_info
171213 22:31:47 [00]        ...done
xtrabackup: Transaction log of lsn (2013218) to (2013227) was copied.
171213 22:31:48 completed OK!
--incremental 指定是增量备份
--incremental-basedir 指定基于哪个完整备份做增量备份，最后是增量备份保存的目录

恢复数据库

关闭mysql 服务
[root@www.cndba.cn /]# service mysql stop
删除mysql目录（测试用的，生产环境请勿操作）
[root@www.cndba.cn /]# rm -rf /data/mysql
通过准备阶段使备份达到一致的状态
[root@www.cndba.cn /]# innobackupex --apply-log --redo-only /backup/2017-12-13_17-49-45/
在所有的增量备份上（除最后一个增量备份）上执行
因为此次试验只做一次增量，这一步可不执行，直接执行下一步
[root@www.cndba.cn /]#innobackupex --apply-log --redo-only --incremental-dir=/backup/incremental/2017-12-13_22-31-44/ /backup/2017-12-13_17-49-45/
在最后一个增量备份上执行
[root@www.cndba.cn /]#innobackupex --apply-log --incremental-dir=/backup/incremental/2017-12-13_22-31-44/ /backup/2017-12-13_17-49-45/
恢复全量及增量备份
[root@www.cndba.cn /]#innobackupex --copy-back /backup/2017-12-13_17-49-45/
目录授权mysql权限
[root@www.cndba.cn /]#chown -R mysql:mysql /data/mysql
启动数据库
[root@www.cndba.cn /]#service mysql start

查看增量表cndba 被恢复

MariaDB [cndba]> show tables;
+-----------------+
| Tables_in_cndba |
+-----------------+
| depart_pos      |
| department      |
| staffer         |
| student         |
| test            |
| test12          |
| test123         |
| test2           |
+-----------------+
9 rows in set (0.00 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。