使用perconna xtrabackup备份脚本
使用percona xtrabackup对两个数据库实例进行备份，备份的脚本如下所示：
1 #!/bin/bash 2 BASEDIR="/usr/local/mysql" 3 BIN="$BASEDIR/bin" 4 MYDUMPE="/usr/local/bin/mydumper" 5 XTRABACKUP="/usr/bin/xtrabackup" 6 DATA1_HOME="$BASEDIR/mysqldata/data1" 7 DATA1_MYCNF="$DATA1_HOME/my.cnf" 8 DATA1_SOCK=`grep socket $DATA1_MYCNF|awk '{print $3}'` 9 DATA1_PORT=`grep port $DATA1_MYCNF|awk '{print $3}'` 10 DATA1_IP=`grep bind-address $DATA1_MYCNF|awk '{print $3}'` 11 12 DATA2_HOME="$BASEDIR/mysqldata/data2" 13 DATA2_MYCNF="$DATA2_HOME/my.cnf" 14 DATA2_SOCK=`grep socket $DATA2_MYCNF|awk '{print $3}'` 15 DATA2_PORT=`grep port $DATA2_MYCNF|awk '{print $3}'` 16 DATA2_IP=`grep bind-address $DATA2_MYCNF|awk '{print $3}'` 17 18 USER="root" 19 PASSWORD="123456" 20 BACKUPPATH_LOCAL="/usr/local/mysql/mysqldata/databak" 21 BACKUPPATH_REMOTE="/usr/local/mysql/databak" 22 #get the first day of one week,all backup directory is named by date,the incremental-basedir is the first day of the week 23 DATE="`date +%F`" 24 # get the day number of today in this week,monday is 1,sunday is 7 as the first day of this week 25 WEEKDAY="`date +%u`" 26 AGO="$WEEKDAY days ago" 27 WEEKNUM=`date +%U` 28 #get the first day of one week,as the incremental_back_base 29 WEEK_FIRST_DAY=`date -d "$AGO" +%F` 30 TODAY_DIR="$BACKUPPATH_LOCAL/today" 31 32 # the full backup function 33 full_backup(){ 34 local DEFAULTS_FILE=$1 35 local USER=$2 36 local PASSWORD=$3 37 local SOCKET=$4 38 local TARGET_DIR=$5 39 local LOG_FILE=$6 40 $BIN/mysql --user=$USER --password=$PASSWORD -S $SOCKET -e "stop slave;" 41 $XTRABACKUP --defaults-file=$DEFAULTS_FILE --backup --user=$USER --password=$PASSWORD --socket=$SOCKET --target-dir=$TARGET_DIR 1>$LOG_FILE 2>&1 42 $BIN/mysql --user=$USER --password=$PASSWORD -S $SOCKET -e "start slave;" 43 44 } 45 # the incremental backup function 46 incremental_backup(){ 47 local DEFAULTS_FILE=$1 48 local USER=$2 49 local PASSWORD=$3 50 local SOCKET=$4 51 local TARGET_DIR=$5 52 local LOG_FILE=$6 53 local BASE_DIR=$7 54 local NAME=$8 55 $BIN/mysql --user=$USER --password=$PASSWORD -S $SOCKET -e "stop slave;" 56 $XTRABACKUP --defaults-file=$DEFAULTS_FILE --backup --user=$USER --password=$PASSWORD --socket=$SOCKET --incremental-basedir=$BASE_DIR --target-dir=$TARGET_DIR 1>$LOG_FILE 2>&1 57 #$XTRABACKUP --defaults-file=$DEFAULTS_FILE --backup --user=$USER --password=$PASSWORD --socket=$SOCKET --compression --stream=xbstream --incremental-basedir=$BASE_DIR --target-dir=$TARGET_DIR >$DATE-$NAME.tar 58 $BIN/mysql --user=$USER --password=$PASSWORD -S $SOCKET -e "start slave;" 59 } 60 # check the mysql is running or not,if mysql is not running ,the backup will cancel 61 mysql_check(){ 62 local SOCKET=$1 63 local IP=$2 64 local PORT=$3 65 local NAME=$4 66 if [ `ps -ef|grep $SOCKET|grep -Ev grep|wc -l` -ge 1 ] && [ `netstat -ltnp|grep $IP:$PORT|grep -Ev grep|wc -l` -eq 1 ] 67 then 68 echo " THE MYSQL SERVER $NAME IS RUNNING" 69 return 1 70 else 71 echo "THE MYSQL SERVER $NAME IS NOT RUNNING" 72 return 0 73 fi 74 } 75 76 # main 77 # delete yesterday backup in TODAY directory 78 /bin/mv $TODAY_DIR/* $BACKUPPATH_LOCAL 79 80 [ -e $TODAY_DIR ] || mkdir -p $TODAY_DIR 81 [ -e "$BACKUPPATH_LOCAL/$DATE" ] || mkdir -p "$BACKUPPATH_LOCAL/$DATE" 82 cd "$BACKUPPATH_LOCAL/$DATE" 83 84 85 TARGET_DIR="$BACKUPPATH_LOCAL/$DATE" 86 87 # if today is sunday,the backup type is full_backup,else the backup type is delta backup on the sunday full backup 88 if [ $WEEKDAY -eq 7 ] 89 then 90 mysql_check $DATA1_SOCK $DATA1_IP $DATA1_PORT "DATA1" 91 [ $? -eq 1 ] && full_backup $DATA1_MYCNF $USER $PASSWORD $DATA1_SOCK $TARGET_DIR/data1 "$TARGET_DIR/data1.log" 92 mysql_check $DATA2_SOCK $DATA2_IP $DATA2_PORT "DATA2" 93 #echo $TARGET_DIR 94 [ $? -eq 1 ] && full_backup $DATA2_MYCNF $USER $PASSWORD $DATA2_SOCK $TARGET_DIR/data2 "$TARGET_DIR/data2.log" 95 else 96 BASE_DIR=$BACKUPPATH_LOCAL/$WEEK_FIRST_DAY 97 mysql_check $DATA1_SOCK $DATA1_IP $DATA1_PORT "DATA1" 98 [ $? -eq 1 ] && incremental_backup $DATA1_MYCNF $USER $PASSWORD $DATA1_SOCK $TARGET_DIR/data1 $TARGET_DIR/data1.log $BASE_DIR/data1 data1 99 mysql_check $DATA2_SOCK $DATA2_IP $DATA2_PORT "DATA2" 100 [ $? -eq 1 ] && incremental_backup $DATA2_MYCNF $USER $PASSWORD $DATA2_SOCK $TARGET_DIR/data2 $TARGET_DIR/data2.log $BASE_DIR/data2 data2 101 fi 102 # copy today backup to TODAY directory,the NBU server will catch TODAY directory 103 #/bin/mv -ar $TARGET_DIR $TODAY_DIR 104 105 /bin/mv $TARGET_DIR $TODAY_DIR
主要实现的功能是，每周日早上进行一次全备份操作，然后周内的每天，基于周日的全备，做一个增量备份的操作，每个实例备份的结果，放在以实例命名的目录下，每天忙备份的结果，放在以当前日期命名的目录下。
备份脚本对应的删除脚本，实现的功能是这样的，每周日，也就是全备的时候，去删除上上周的所有备份，保留上周的所有备份，这个时候要判断上上周的日期，并且找到所有这些目录，实现的脚本如下：
1 # delete the backup ,delete 14 days ago full backup and the incremental backup based on that full backup 2 3 BACKUPPATH=/usr/local/mysql/mysqldata/databak 4 DATE=`date +%F` 5 6 DELETE_DATE_SUNDAY=`date -d "14 days ago" +%F` 7 for index in {-1,-2,-3,-4,-5,-6} 8 do 9 DELETE_DATE_WEEKDAY=`date -d "$index days ago $DELETE_DATE_SUNDAY" +%F` 10 DELETE_DATE_DIR=$BACKUPPATH/$DELETE_DATE_WEEKDAY 11 [ -e $DELETE_DATE_DIR ] && rm -rf $DELETE_DATE_DIR 12 done 13 14 [ -e $BACKUPPATH/$DELETE_DATE_SUNDAY ] && rm -rf $BACKUPPATH/$DELETE_DATE_SUNDAY
在做主从备份的同时，需要对主服务器上面的binlog进行实时同步到从服务器上，因为主服务器上的binlog会被覆盖掉，所以需要在备份的从服务器上保留完整的binlog信息。具体的操作命令是
/usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=*.*.*.* --port=**** --user=replication --password=**** mysql-bin.000021 --result-file=/usr/local/mysql/mysqldata/master_binlog_bak/data1/back- &
这行命令的主要功能是从远方的服务器上同步binlog，-raw是指binlog 的格式，stop-never是指不中断的去同步，host和port、user和passeord是有replication权限的用户，后面要跟一个binlog 名称，也就是master主机上的binlog.index中存在的binlog文件名，result-file是要写一个目录名，如果除了目录名，还有其他的字段，那就是定义了目标文件的命名规则。比如/usr/local/mysql/mysqldata/databak/back-，则文件名称为back-mysql-bin.0000*.
