Percona XtraBackup 备份和还原MySQL数据库

XtraBackup是Percona公司的开源项目,用以实现类似Innodb官方的热备份工具InnoDB Hot Backup的功能,它支持在线热备份(备份时不影响数据读写)。XtraBackup有很多功能和优点:例如支持全备、增量备份、部分备份;支持压缩备份;备份不影响数据读写、事务等,但是也有缺陷不足:例如不支持脱机备份、不支持直接备份到磁带设备、不支持Cloud Back,MyISAM的备份也会阻塞。不过这些小瑕疵不影响XtraBackup成为一款流行的MySQL备份工具。另外,请注意XtraBackup只支持Linux平台,不支持Windows平台。

1、Percona Xtrabackup的安装
(1)下载Xtracbackup软件(俺的OS环境是CentOS 6.5 X64)

[root@mydb01 ~]# wget http://ftp.tu-chemnitz.de/pub/linux/dag/redhat/el6/en/x86_64/rpmforge/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm

(2)安装依赖包及软件

[root@mydb01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Setting up Install Process
Package 4:perl-5.10.1-144.el6.x86_64 already installed and latest version
Package 4:perl-devel-5.10.1-144.el6.x86_64 already installed and latest version
Package libaio-0.3.107-10.el6.x86_64 already installed and latest version
Package 4:perl-Time-HiRes-1.9721-144.el6.x86_64 already installed and latest version
Package perl-DBD-MySQL-4.013-3.el6.x86_64 already installed and latest version
Resolving Dependencies
[root@mydb01 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
warning: libev-4.15-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
Preparing… ########################################### [100%]
1:libev ########################################### [100%]
[root@mydb01 ~]# ls
[root@mydb01 perconaxtrabackupForEL6]# rpm -ivh percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm
warning: percona-xtrabackup-24-2.4.12-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing… ########################################### [100%]
1:percona-xtrabackup-24 ########################################### [100%]

2、Percona Xtrabackup的备份还原数据


(1)完全备份
[root@mydb01 ~]# grep “datadir” /etc/my.cnf#查看数据目录所在的位置
datadir=/usr/local/mysql/data
[ root@mydb01~]# innobackupex –user=root –password=131468 /tmp/mysql-backup/
[ root@mydb01~]# ls /tmp/mysql-backup/#查看全备份的内容
2018-12-26_05-25-22

(2)增量备份

[root@mydb01 ~]# innobackupex –user=root –password=123 –incremental /tmp/mysql-backup/ –incremental-basedir=/tmp/mysql-backup/2018-12-26_05-25-22/
[ root@mydb01 ~]# innobackupex –user=root –password=123 –incremental /tmp/mysql-backup/ –incremental-basedir=/tmp/mysql-backup/2018-12-26_05-30-01/
[ root@mydb01 ~]# ls /tmp/mysql-backup/
2018-12-26_05-25-22 2018-12-26_05-30-01 2018-12-26_05-34-08

(3)还原数据
①停止mysql服务,然后清空数据文件目录,恢复完成后再设置权限

[ root@mydb01 ~]# service mysqld stop
[ root@mydb01 ~]# mv /var/lib/mysql /usr/lib/mysql.bak #这个是模拟不小心将数据删掉
[ root@mydb01 ~]# mkdir /var/lib/mysql
[ root@mydb01 ~]# chown mysql. /var/lib/mysql -R
②还原数据

[ root@mydb01 ~]# innobackupex –apply-log –redo-only /tmp/mysql-backup/2018-12-26_05-25-22/#读取全备份的事务日志,不回滚
[ root@mydb01 ~]# innobackupex –apply-log –redo-only /tmp/mysql-backup/2018-12-26_05-25-22/ –incremental-dir=/tmp/mysql-backup/2018-12-26_05-30-01/#将第一次增量备份的事务日志导入到读取全备份的事务日志中,不回滚
[ root@mydb01 ~]# innobackupex –apply-log –redo-only /tmp/mysql-backup/2018-12-26_05-25-22/ –incremental-dir=/tmp/mysql-backup/2018-12-26_05-34-08/#将第二次增量备份的事务日志导入到读取全备份的事务日志中,不回滚
[ root@mydb01 ~]# innobackupex –apply-log /tmp/mysql-backup/2018-12-26_05-25-22/#回滚事务日志
[ root@mydb01 ~]# innobackupex –copy-back /tmp/mysql-backup/2018-12-26_05-25-22/#还原数据
[ root@mydb01 ~]# chown mysql. /var/lib/mysql -R #修改权限
[ root@mydb01 ~]# systemctl start mysql#启动服务

 

 

以下文章点击率最高

Loading…


发表评论

邮箱地址不会被公开。 必填项已用*标注