mysql增量备份 percona-xtrabackup
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql增量备份 percona-xtrabackup，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10418字，纯文字阅读大概需要15分钟。
内容图文
-
先说下实际环境
-
阿里云买的
ESC
跑的
mysql
服务,目前由于数据量过大,数据库目前有
800
多
GB
,每次全备需要等
20
多个小时才能够完成,然后就想到做增量备份,写下此文档
一、测试环境
-
[
root@localhost
~]#
cat
/
etc
/
redhat
-
release
-
CentOS
release
6.2
(
Final
)
-
[
root@localhost
~]#
mysql
--
version
-
mysql
Ver
14.14
Distrib
5.5
.
20
,
for
Linux
(
x86_64
)
using
readline
5.1
二、安装软件及其作用
-
软件名：
percona
-
xtrabackup
-
作用：针对
Mysql
数据库进行热备份，及增量备份，主要应用于
innodb
引擎
三、安装步骤
-
yum
-
y install cmake gcc gcc
-
c
++
libaio libaio
-
devel automake autoconf bzr bison libtool ncurses5
-
devel
#安装依赖包
-
-
yum
-
y install http
:
//www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm #安装percona YUM库
-
-
yum
-
y install percona
-
xtrabackup
.
x86_64
#安装percona-xtrabackup
四、备份及恢复命令
1）完整备份
-
innobackupex
--
defaults
-
file
=
/etc/
my
.
cnf
--
user
=
root
--
password
=
PASSWORD
--
database
=
DATE BACK_DIR_PATH
-
# PASSWORD 数据库密码
-
# DATE 数据库名
-
# --database=DATE 针对所有库做备份去掉该选项即可
-
# BACK_DIR_PATH 完全备份目录位置
-
-
注：该命令执行后会在备份目录位置以当前时间为命名生成一个目录，备份的文件会备份到该目录中。例如
2014
-
12
-
06
_16
-
00
-
15
2）完整备份的恢复
-
1.
首先需要备份数据库
-
2.
停止数据库运行
-
3.
删除数据库目录内所有文件
-
4.
然后执行下列命令进行恢复
-
innobackupex
--
defaults
-
file
=
/etc/
my
.
cnf
--
user
=
root
--
password
=
PASSWORD
--
database
=
DATE
--
apply
-
log BACK_DIR_PATH
/
2014
-
12
-
06
_16
-
00
-
15
-
# PASSWORD 数据库密码
-
# DATE 数据库名
-
# BACK_DIR_PATH 完全备份目录位置
3）增量备份
-
innobackupex
--
defaults
-
file
=
/etc/
my
.
cnf
--
user
=
root
--
password
=
PASSWORD
--
database
=
DATE
--
incremental
-
basedir
=
BACK_DIR_PATH
/
2014
-
12
-
06
_16
-
00
-
15
--
incremental BACK_DIR_ZENG_1
-
# PASSWORD 数据库密码
-
# DATE 数据库名
-
# BACK_DIR_PATH 完全备份目录位置
-
# BACK_DIR_ZENG_1 第一次增量备份目录位置
-
# --incremental-basedir 该选项后面写的为第一次完整备份的路径
-
-
注：此次增量备份是在第一次完整备份的基础之上进行的增量备份
4）第二次增量备份
-
innobackupex
--
defaults
-
file
=
/etc/
my
.
cnf
--
user
=
root
--
password
=
PASSWORD
--
database
=
DATE
--
incremental
-
basedir
=
BACK_DIR_ZENG_1
--
incremental BACK_DIR_ZENG_2
-
# PASSWORD 数据库密码
-
# DATE 数据库名
-
# BACK_DIR_PATH 完全备份目录位置
-
# BACK_DIR_ZENG_1 第一次增量备份目录位置
-
# BACK_DIR_ZENG_2 第二次增量备份目录位置
-
# --incremental-basedir 该选项后面写的为第一次增量备份的路径
-
注：此次增量备份是在第一次增量备份的基础之上进行的第二次增量备份
5）第三次增量备份
-
同第二次增量备份
6）多次增量备份之后的恢复
-
1
、首先需要备份数据库
-
2
、停止数据库运行
-
3
、删除数据库目录内所有文件
-
4
、然后执行下列命令进行恢复
-
innobackupex
--
apply
-
log
--
redo
-
only BACK_DIR_PATH
-
innobackupex
--
apply
-
log
--
redo
-
only BACK_DIR_PATH
--
incremental
-
dir
=
BACK_DIR_ZENG_1
-
innobackupex
--
apply
-
log BACK_DIR_PATH
--
incremental
-
dir
=
BACK_DIR_ZENG_2
-
# BACK_DIR_PATH 完全备份目录位置
-
# BACK_DIR_ZENG_1 第一次增量备份目录位置
-
# BACK_DIR_ZENG_2 第二次增量备份目录位置
-
-
注：
如果只有
2
次增量备份则执行此条命令，如果有多次增量命令，则参照上一条命令
五、脚本
-
此脚本为一次全备+
6
次增备轮询
-
#!/bin/sh
-
-
DATE
=
`date +%Y-%m-%d`
-
BACKUP_DIR_PATH
=
/backup/
data
-
FILE_CNF
=
/etc/
my
.
cnf
-
USER_NAME
=
root
-
PASSWORD
=
root
-
DATEBASE
=
test
-
BACK_LOG
=
$BACKUP_DIR_PATH
/
backup_cost_time
-
OUT_LOG
=
Xtrabackup_$DATE
.
log
-
-
-
if
[
-
d
"$BACKUP_DIR_PATH/rec6"
];
-
then
-
mv $BACKUP_DIR_PATH $BACKUP_DIR_PATH
-
bak
-
mkdir $BACKUP_DIR_PATH
-
fi
-
-
-
if
[
!
-
d
"$BACKUP_DIR_PATH/full"
];
-
then
-
-
START_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
mkdir $BACKUP_DIR_PATH
/
full
-
echo
"###start full backup at $START_DATE to directory full"
>>
$BACK_LOG
-
echo
"###start full backup at $START_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
innobackupex
--
defaults
-
file
=
$FILE_CNF
--
user
=
$USER_NAME
--
password
=
$PASSWORD
--
database
=
$DATEBASE $BACKUP_DIR_PATH
/
full
2
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
STOP_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
echo
"###stop full backup at $STOP_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
"###stop full backup at $STOP_DATE to directory full"
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
break
;
-
-
-
elif
[
!
-
d
"$BACKUP_DIR_PATH/rec1"
];
-
then
-
-
BACKUP_FULL_PATH
=
`ls -l $BACKUP_DIR_PATH/full |awk ‘/^d/ {print $NF}‘`
-
START_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
mkdir $BACKUP_DIR_PATH
/
rec1
-
echo
"###start 1 incremental backup at $START_DATE to directory full"
>>
$BACK_LOG
-
echo
"###start 1 incremental backup at $START_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
innobackupex
--
defaults
-
file
=
$FILE_CNF
--
user
=
$USER_NAME
--
password
=
$PASSWORD
--
database
=
$DATEBASE
--
incremental
-
basedir
=
$BACKUP_DIR_PATH
/
full
/
$BACKUP_FULL_PATH
--
incremental $BACKUP_DIR_PATH
/
rec1
2
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
STOP_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
echo
"###stop 1 incremental backup at $STOP_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
"###stop 1 incremental backup at $STOP_DATE to directory full"
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
break
;
-
-
-
elif
[
!
-
d
"$BACKUP_DIR_PATH/rec2"
];
-
then
-
-
BACKUP_REC1_PATH
=
`ls -l $BACKUP_DIR_PATH/rec1 |awk ‘/^d/ {print $NF}‘`
-
START_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
mkdir $BACKUP_DIR_PATH
/
rec2
-
echo
"###start 2 incremental backup at $START_DATE to directory full"
>>
$BACK_LOG
-
echo
"###start 2 incremental backup at $START_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
innobackupex
--
defaults
-
file
=
$FILE_CNF
--
user
=
$USER_NAME
--
password
=
$PASSWORD
--
database
=
$DATEBASE
--
incremental
-
basedir
=
$BACKUP_DIR_PATH
/
rec1
/
$BACKUP_REC1_PATH
--
incremental $BACKUP_DIR_PATH
/
rec2
2
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
STOP_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
echo
"###stop 2 incremental backup at $STOP_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
"###stop 2 incremental backup at $STOP_DATE to directory full"
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
break
;
-
-
-
-
elif
[
!
-
d
"$BACKUP_DIR_PATH/rec3"
];
-
then
-
-
BACKUP_REC2_PATH
=
`ls -l $BACKUP_DIR_PATH/rec2 |awk ‘/^d/ {print $NF}‘`
-
START_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
mkdir $BACKUP_DIR_PATH
/
rec3
-
echo
"###start 3 incremental backup at $START_DATE to directory full"
>>
$BACK_LOG
-
echo
"###start 3 incremental backup at $START_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
innobackupex
--
defaults
-
file
=
$FILE_CNF
--
user
=
$USER_NAME
--
password
=
$PASSWORD
--
database
=
$DATEBASE
--
incremental
-
basedir
=
$BACKUP_DIR_PATH
/
rec2
/
$BACKUP_REC2_PATH
--
incremental $BACKUP_DIR_PATH
/
rec3
2
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
STOP_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
echo
"###stop 3 incremental backup at $STOP_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
"###stop 3 incremental backup at $STOP_DATE to directory full"
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
break
;
-
-
-
-
elif
[
!
-
d
"$BACKUP_DIR_PATH/rec4"
];
-
then
-
-
BACKUP_REC3_PATH
=
`ls -l $BACKUP_DIR_PATH/rec3 |awk ‘/^d/ {print $NF}‘`
-
START_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
mkdir $BACKUP_DIR_PATH
/
rec4
-
echo
"###start 4 incremental backup at $START_DATE to directory full"
>>
$BACK_LOG
-
echo
"###start 4 incremental backup at $START_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
innobackupex
--
defaults
-
file
=
$FILE_CNF
--
user
=
$USER_NAME
--
password
=
$PASSWORD
--
database
=
$DATEBASE
--
incremental
-
basedir
=
$BACKUP_DIR_PATH
/
rec3
/
$BACKUP_REC3_PATH
--
incremental $BACKUP_DIR_PATH
/
rec4
2
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
STOP_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
echo
"###stop 4 incremental backup at $STOP_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
"###stop 4 incremental backup at $STOP_DATE to directory full"
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
break
;
-
-
-
-
elif
[
!
-
d
"$BACKUP_DIR_PATH/rec5"
];
-
then
-
-
BACKUP_REC4_PATH
=
`ls -l $BACKUP_DIR_PATH/rec4 |awk ‘/^d/ {print $NF}‘`
-
START_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
mkdir $BACKUP_DIR_PATH
/
rec5
-
echo
"###start 5 incremental backup at $START_DATE to directory full"
>>
$BACK_LOG
-
echo
"###start 5 incremental backup at $START_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
innobackupex
--
defaults
-
file
=
$FILE_CNF
--
user
=
$USER_NAME
--
password
=
$PASSWORD
--
database
=
$DATEBASE
--
incremental
-
basedir
=
$BACKUP_DIR_PATH
/
rec4
/
$BACKUP_REC4_PATH
--
incremental $BACKUP_DIR_PATH
/
rec5
2
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
STOP_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
echo
"###stop 5 incremental backup at $STOP_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
"###stop 5 incremental backup at $STOP_DATE to directory full"
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
break
;
-
-
-
-
elif
[
!
-
d
"$BACKUP_DIR_PATH/rec6"
];
-
then
-
-
BACKUP_REC5_PATH
=
`ls -l $BACKUP_DIR_PATH/rec5 |awk ‘/^d/ {print $NF}‘`
-
START_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
mkdir $BACKUP_DIR_PATH
/
rec6
-
echo
"###start 6 incremental backup at $START_DATE to directory full"
>>
$BACK_LOG
-
echo
"###start 6 incremental backup at $START_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
innobackupex
--
defaults
-
file
=
$FILE_CNF
--
user
=
$USER_NAME
--
password
=
$PASSWORD
--
database
=
$DATEBASE
--
incremental
-
basedir
=
$BACKUP_DIR_PATH
/
rec5
/
$BACKUP_REC5_PATH
--
incremental $BACKUP_DIR_PATH
/
rec6
2
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
STOP_DATE
=
`date +%Y-%m-%d_%H:%M:%S`
-
-
echo
"###stop 6 incremental backup at $STOP_DATE to directory full"
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
"###stop 6 incremental backup at $STOP_DATE to directory full"
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACK_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
echo
""
>>
$BACKUP_DIR_PATH
/
$OUT_LOG
-
-
break
;
-
fi
原文：http://www.cnblogs.com/CongZhang/p/5300699.html
内容总结
以上是互联网集市为您收集整理的mysql增量备份 percona-xtrabackup全部内容，希望文章能够帮你解决mysql增量备份 percona-xtrabackup所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】