percona xtrabackup mysql数据库物理备份

xtrabackup 2.4.4 安装：

yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
yum -y install perl-Digest-MD5

innobackupex命令会在备份目录创建一个以当前日期为名称的目录，并将数据写入目录
该目录中有配置文件，xtrabackup_binlog_info中记录了binlog日志文件的位置和position位置点

全备

数据库全备过程：

1. 备份数据库

innobackupex --user=root --password=‘123456’ /backup/full

#恢复备份
恢复数据库流程：

1. 停止数据库
2. 清理环境
3. 重演回滚，恢复数据
4. 修改数据库目录权限，启动数据库服务

systemctl stop mysqld
rm -rf /var/lib/mysql/*
innobackupex --apply-log /backup/full/xxx
innobackupex --copy-back /backup/full/xxx
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld

增量备份：

备份过程

1.周一：全备

innobackupex --user=root --password=‘123456’ /backup/full

2.周二：第一次增量备份
#–incremental-basedir基于哪个目录做增量备份

innobackupex --user=root --password=‘123456’ --incremental /backup/incremental1 --incremental-basedir=/backup/full/xxx

3.周三：第二次增量备份

innobackupex --user=root --password=‘123456’ --incremental /backup/incremental2 --incremental-basedir=/backup/incremental1/xxx

恢复增量备份过程

恢复数据库流程：

1. 停止数据库，清理环境

systemctl stop mysqld
rm -rf /var/lib/mysql/*

2. 重演回滚，恢复数据

#重演redo log

#周一
innobackupex --apply-log --redo-only /xtrabackup/full/xxx

#周二
innobackupex --apply-log --redo-only /xtrabackup/full/xxx --incremental-dir=/backup/incremental1/xxx

#周三
innobackupex --apply-log --redo-only /xtrabackup/full/xxx --incremental-dir=/backup/incremental2/xxx

恢复数据

innobackupex --copy-back /backup/full/2019-01-10_09-50-11/

3. 修改数据库目录权限，启动数据库服务

chown -R mysql.mysql /app
systemctl start mysqld

错误记录

1. Can’t locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5

详细信息

[root@ceshi ~]# innobackupex --appply-log /backup/full/2019-01-09_23-23-17/
190109 23:28:41 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!".

Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 693.
BEGIN failed--compilation aborted at - line 693.
190109 23:28:41 Connecting to MySQL server host: localhost, user: (null), password: not set, port: 8017, socket: /tmp/mysql.sock
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).
[root@ceshi ~]# 

解决方法：
[root@ceshi ~]# yum -y install perl-Digest-MD5

2. version_check Connecting to MySQL server with DSN 'dbi:mysql:

详细信息

[root@ceshi ~]# innobackupex --appply-log /backup/full/2019-01-09_23-23-17/
190109 23:31:55 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!".

190109 23:31:55  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=8017;mysql_socket=/tmp/mysql.sock' (using password: NO).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=8017;mysql_socket=/tmp/mysql.sock','',...) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) at - line 1314.
190109 23:31:55 Connecting to MySQL server host: localhost, user: (null), password: not set, port: 8017, socket: /tmp/mysql.sock
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).

解决方法：
安装完perl-Digest-MD5之前的备份都不能使用，使用就会报错version_check Connecting to MySQL server with DSN 'dbi:mysql
重新备份数据就不会报错了。