运维
by smallnest

[转]Xtrabackup全量备份/增量备份脚本

本文收集了几篇关于使用 Xtrabackup/innobackupex进行mysql数据库全量和增量备份的脚本，学习之用。搜集的几篇都是转载，非本文原创。

Xtrabackup全量备份/增量备份脚本

这篇转载的文章源于 http://powermichael.blog.51cto.com/12450987/1954307，但目前这篇原始文章已经无法访问了。

全量备份脚本

全量备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
#!/bin/bash
#Description:xtrabackup complete
#Author:created by michael
#2017-08-07 v0.1
#
USER=root
PASSWD=123456
BACKUP_DIR=/backup/mysql/complete
DATE=$(date +"%F_%T")
[[ -d $BACKUP_DIR ]] || mkdir $DATE_DIR
innobackupex --user=$USER --password=$PASSWD $BACKUP_DIR &> /tmp/mysql/"$DATE".txt
egrep ".* Backup created in directory.*" /tmp/mysql/"$DATE".txt >> $BACKUP_DIR/complete.info
rm -rf /tmp/mysql/"$DATE".txt

启动crond以及开机自启动crond

1
2
systemctl start crond
systemctl enable crond

授予执行权限

1
chmod 755 /root/script/backup_complete.sh

每周六的凌晨4点整定时执行全量备份

1
2
3
4
[root@michaelos complete]# crontab -e
crontab: installing new crontab
[root@michaelos complete]# crontab -l
0 4 * * 6 /root/script/backup_complete.sh

增量备份脚本

增量备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@michaelos script]# cat backup_increment.sh 
#!/bin/bash 
#Description: mysql backup incremention
#Author:michael
#2017-08-07 v0.1
#
USER=root
PASSWORD=123456
BACKUP_DIR=/backup/mysql/increment
DATE=$(date +"%F_$T")
BASE_DIR=$(tail -1 /backup/mysql/complete/complete.info | cut -d\' -f2)
[[ -d $BACKUP_DIR ]] || mkdir $BACKUP_DIR
innobackupex --user=$USER --password=$PASSWORD --incremental $BACKUP_DIR --incremental-basedir=$BASE_DIR &> /tmp/mysql/"$DATE".txt
egrep ".*Backup created in directory.*" /tmp/mysql/"$DATE".txt >> $BACKUP_DIR/backup.info
rm -rf /tmp/mysql/"$DATE".txt

授予执行权限

1
chmod 755 backup_increment.sh

每周二、四、日的凌晨2点执行增量备份

1
2
3
[root@michaelos script]# crontab -l
0 4 * * 6 /root/script/backup_complete.sh
0 2 * * 2,4,7 /root/script/backup_increment.sh

xtrabackup全量增量备份脚本

这篇文章来自 segmentfault的linda玲。

全量备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
#!/bin/sh
# add ling
 
INNOBACKUPEX=innobackupex
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX
TODAY=`date +%Y%m%d%H%M`
YESTERDAY=`date -d"yesterday" +%Y%m%d%H%M`
USEROPTIONS="--user=user --password=123456"
TMPFILE="/logs/mysql/innobackup_$TODAY.$$.tmp"
MYCNF=/etc/my.cnf
MYSQL=/usr/local/mariadb/bin/mysql
MYSQLADMIN=/usr/local/mariadb/bin/mysqladmin
BACKUPDIR=/backup/mysql # 备份的主目录
FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录
INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录
KEEP=1 # 保留几个全库备份
 
# Grab start time
#############################################################################
# Display error message and exit
#############################################################################
error()
{
    echo "$1" 1>&2
    exit 1
}
 
# Check options before proceeding
if [ ! -x $INNOBACKUPEXFULL ]; then
  error "$INNOBACKUPEXFULL does not exist."
fi
 
if [ ! -d $BACKUPDIR ]; then
  error "Backup destination folder: $BACKUPDIR does not exist."
fi
 
if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then
 error "HALTED: MySQL does not appear to be running."
fi
 
if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then
 error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)."
fi
 
# Some info output
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date`"
echo
 
# Create full and incr backup directories if they not exist.
for i in $FULLBACKUPDIR $INCRBACKUPDIR
do
        if [ ! -d $i ]; then
                mkdir -pv $i
        fi
done
 
# 压缩上传前一天的备份
echo "压缩前一天的备份，scp到远程主机"
cd $BACKUPDIR
tar -zcvf $YESTERDAY.tar.gz ./full/ ./incr/
scp -P 8022 $YESTERDAY.tar.gz root@192.168.10.46:/data/backup/mysql/
if [ $? = 0 ]; then
  rm -rf $BACKUPDIR/full $BACKUPDIR/incr
  echo "Running new full backup."
  innobackupex --defaults-file=$MYCNF $USEROPTIONS $FULLBACKUPDIR > $TMPFILE 2>&1
else
  echo "Error with scp."
fi
 
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
 echo "$INNOBACKUPEX failed:"; echo
 echo "---------- ERROR OUTPUT from $INNOBACKUPEX ----------"
# cat $TMPFILE
# rm -f $TMPFILE
 exit 1
fi
# 这里获取这次备份的目录 
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`
echo "THISBACKUP=$THISBACKUP"
#rm -f $TMPFILE
echo "Databases backed up successfully to: $THISBACKUP"
# Cleanup
echo "delete tar files of 3 days ago"
find $BACKUPDIR/ -mtime +3 -name "*.tar.gz"  -exec rm -rf {} \;
 
echo
echo "completed: `date`"
exit 0

增量备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
#!/bin/sh
# add ling
 
INNOBACKUPEX=innobackupex
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX
TODAY=`date +%Y%m%d%H%M`
USEROPTIONS="--user=user --password=123456"
TMPFILE="/logs/mysql/incr_$TODAY.$$.tmp"
MYCNF=/etc/my.cnf
MYSQL=/usr/local/mariadb/bin/mysql
MYSQLADMIN=/usr/local/mariadb/bin/mysqladmin
BACKUPDIR=/backup/mysql # 备份的主目录
FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录
INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录
#############################################################################
# Display error message and exit
#############################################################################
error()
{
    echo "$1" 1>&2
    exit 1
}
 
# Check options before proceeding
if [ ! -x $INNOBACKUPEXFULL ]; then
  error "$INNOBACKUPEXFULL does not exist."
fi
 
if [ ! -d $BACKUPDIR ]; then
  error "Backup destination folder: $BACKUPDIR does not exist."
fi
 
if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then
 error "HALTED: MySQL does not appear to be running."
fi
 
if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then
 error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)."
fi
 
# Some info output
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date`"
echo
 
# Create full and incr backup directories if they not exist.
for i in $FULLBACKUPDIR $INCRBACKUPDIR
do
        if [ ! -d $i ]; then
                mkdir -pv $i
        fi
done
 
# Find latest full backup
LATEST_FULL=`find $FULLBACKUPDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n"`
echo "LATEST_FULL=$LATEST_FULL" 
# Run an incremental backup if latest full is still valid.
# Create incremental backups dir if not exists.
TMPINCRDIR=$INCRBACKUPDIR/$LATEST_FULL
mkdir -p $TMPINCRDIR
BACKTYPE="incr"
# Find latest incremental backup.
LATEST_INCR=`find $TMPINCRDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`
echo "LATEST_INCR=$LATEST_INCR"
  # If this is the first incremental, use the full as base. Otherwise, use the latest incremental as base.
if [ ! $LATEST_INCR ] ; then
  INCRBASEDIR=$FULLBACKUPDIR/$LATEST_FULL
else
  INCRBASEDIR=$LATEST_INCR
fi
echo "Running new incremental backup using $INCRBASEDIR as base."
innobackupex --defaults-file=$MYCNF $USEROPTIONS --incremental $TMPINCRDIR --incremental-basedir $INCRBASEDIR > $TMPFILE 2>&1
 
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
 echo "$INNOBACKUPEX failed:"; echo
 echo "---------- ERROR OUTPUT from $INNOBACKUPEX ----------"
 exit 1
fi
# 这里获取这次备份的目录 
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`
echo "THISBACKUP=$THISBACKUP"
echo
echo "Databases backed up successfully to: $THISBACKUP"
echo
echo "incremental completed: `date`"
exit 0

分别基于innobackupex备份工具的mysql全量备份和增量备份shell脚本

这是另一篇mysql全量和增量备份的脚本， 来自 21运维。

之前有过一个基于innobackupex备份mysql文章，不是很简单实用。这里是一个更为简单的脚本，可以实现分别基于innobackupex备份工具的mysql全量备份和增量备份。
注意：
1，线上用的时候请注意将自己的用户名和密码都设置对，这里password密码我暂时用xxxxxx代替。当然，你也可以建立专用于备份用的数据库用户并设置权限。
2，根据情况设置一个任务计划。

mysql全量备份脚本如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#!/bin/bash
#author:21yunwei
logfile="/home/mysqlbak/mysql_full_bak_time.log"
backuplogfile="/home/mysqlbak/mysqlbackup.log"
mysql_bakdir="/home/mysqlbak/"
user=root
password=xxxxxx
 
[ ! -d $mysql_bakdir  ] &&  mkdir -p $mysql_bakdir
[ ! -f  $logfile ] && touch  $logfile
 
innobackupex --defaults-file=/etc/my.cnf --user=${user} --password=${password} --compress –-parallel=4 –-throttle=400 --stream=tar ${mysql_bakdir} 2>&1 >> $backuplogfile
reset=$?
finishtime=`date +%Y%m%d_%H%M%S`
[ ${reset} -eq 0 ] && echo  "Mysql fullbakup  finished, time: $finishtime">>  $logfile || echo "Mysql fullbakup failed,please check time:$finishtime" >>  $logfile

mysql增量备份脚本如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#!/bin/bash
#author:21yunwei
time=`date -d '-3 day' +%Y-%m-%d`
logfile="/home/mysqlbak/mysql_full_bak_time.log"
mysql_bakdir="/home/mysqlbak/"
user=root
password=xxxxxx
lastfullbakdir=`ls /home/mysqlbak/ |grep ${time}`
 
[ ! -d $mysql_bakdir  ] &&  mkdir -p $mysql_bakdir
[ ! -d  $logfile ] && touch  $logfile
 
innobackupex --defaults-file=/etc/my.cnf --user=${user} --password=${password} --incremental-basedir=${mysql_bakdir}/${lastfullbakdir}  --incremental ${mysql_bakdir}
finishtime=`date +%Y%m%d_%H%M%S`
[ $? -eq 0 ] && echo  "Mysql fullbakup  finished, time: $finishtime">>   $logfile || echo "Mysql fullbakup failed,please check time:$finishtime">>  $logfile