适用版本MySQL<=5.7 全备脚本
#!/bin/bash export MYSQL_BASE=/usr/local/mysql/mysql57 export PATH=$MYSQL_BASE/bin:$PATH MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_BACKUP_USER=backup MYSQL_BACKUP_PASS='backup' MYSQL_CONFIG=/etc/my.cnf BACKUP_DIR=/backup BACKUP_TMP_DIR=/backup/tmp BACKUP_PREFIX_NAME=mysql_backup BACKUP_SUFFIX_NAME=`date +%Y-%m-%d_%H-%M-%S-%N` if [ ! -d "$BACKUP_DIR" ];then mkdir -p $BACKUP_DIR fi if [ ! -d "$BACKUP_TMP_DIR" ];then mkdir -p $BACKUP_TMP_DIR fi CHECK_MYSQL_IS_OK() { CHECK_MYSQL_AVAILABLE=`mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_BACKUP_USER -p$MYSQL_BACKUP_PASS -ss -e "select @@version;"` if [ `echo $?` -ne 0 ]; then echo -e "\033[31mCan Not Connecting To MySQL Server,Please Check Again!! \033[0m" echo " HOST=$MYSQL_HOST PORT=$MYSQL_PORT USER=$MYSQL_BACKUP_USER PASS='***********'" exit 1 else echo -e "\033[32m ------------->>>>>>>> MySQL Database Connection Successfully !!! \033[0m" fi } FULL_MYSQL_BACKUP() { ### create full backup directory mkdir -p $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME echo -e "\033[32m ------------->>>>>>>> Backup Begin Time: `date +'%Y-%m-%d %H:%M:%S'` \033[0m" ### backup database innobackupex --defaults-file=$MYSQL_CONFIG --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_BACKUP_USER --password=$MYSQL_BACKUP_PASS --tmpdir=$BACKUP_TMP_DIR --extra-lsndir=$BACKUP_TMP_DIR --slave-info --parallel=4 --no-timestamp --compress --compress-threads=4 --kill-long-queries-timeout=120 --kill-long-query-type=select --ftwrl-wait-timeout=120 --ftwrl-wait-threshold=120 --throttle=200 --safe-slave-backup --safe-slave-backup-timeout=180 --ftwrl-wait-query-type=all --stream=xbstream $BACKUP_TMP_DIR >$BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME/$BACKUP_SUFFIX_NAME 2>>$BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME/$BACKUP_SUFFIX_NAME'.log' if [ `echo $?` -ne 0 ]; then echo -e "\033[31m ------------->>>>>>>> Xtrabackup Backup Failed!!! Please Check Backup Log \033[0m" mv $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME'_FAILED' echo ' '$BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME'_FAILED'/$BACKUP_SUFFIX_NAME'.log' exit 1 else echo -e "\033[32m ------------->>>>>>>> Backup End Time: `date +'%Y-%m-%d %H:%M:%S'` \033[0m" echo -e "\033[32m ------------->>>>>>>> Xtrabackup Executed Successfully !!! \033[0m" echo ' ------------->>>>>>>> 'Backup Directory: $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME fi ### backup MySQL config file cp $MYSQL_CONFIG $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME/my.cnf if [ `echo $?` -ne 0 ]; then echo -e "\033[31m ------------->>>>>>>> Backup MySQL Config File Failed \033[0m" exit 1 else echo -e "\033[32m ------------->>>>>>>> MySQL Config File Backup Successfully !!! \033[0m" echo ' ------------->>>>>>>> 'Backup Config File: $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME/my.cnf fi } GEN_BACKUP_INDEX_FILE() { TO_LSN=`cat $BACKUP_TMP_DIR/xtrabackup_checkpoints |grep to_lsn |awk -F ' = ' '{print $2}'` echo "$BACKUP_SUFFIX_NAME:$TO_LSN" >$BACKUP_TMP_DIR/index cp $BACKUP_TMP_DIR/* $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME cd $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME && md5sum $BACKUP_SUFFIX_NAME > backup_piece.md5sum.base if [ `echo $?` -ne 0 ]; then echo -e "\033[31m ------------->>>>>>>> Generate Backup [Index File] And [md5sum] Infomation Failed \033[0m" exit 1 else echo -e "\033[32m ------------->>>>>>>> Generate Backup [Index File] And [md5sum] Infomation Successfully !!! \033[0m" echo ' ------------->>>>>>>> 'Backup Index File: $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME/index echo ' ------------->>>>>>>> 'Backup md5sum File: $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME/backup_piece.md5sum.base fi } MARK_AS_EXPIRED() { ### Delete Old Backup echo -e "\033[32m ------------->>>>>>>> The Following Backups Will Have Expired And You Can Manually Delete[Default Reserved for 30 days]\033[0m" #for i in `find $BACKUP_DIR -mtime +180 -name "$BACKUP_PREFIX_NAME*"` for i in `find $BACKUP_DIR -mtime +14 -name "$BACKUP_PREFIX_NAME*"` do echo -e "\033[31m ------------->>>>>>>> $i \033[0m" done } MAIN() { CHECK_MYSQL_IS_OK FULL_MYSQL_BACKUP GEN_BACKUP_INDEX_FILE MARK_AS_EXPIRED } MAIN echo -e "\033[32m ------------->>>>>>>> Backup Execution Completed, Please Check The Backup Log\033[0m" echo ' ------------->>>>>>>> 'Backup Log File: $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME/$BACKUP_SUFFIX_NAME'.log'增备脚本
#!/bin/bash export MYSQL_BASE=/usr/local/mysql/mysql57 export PATH=$MYSQL_BASE/bin:$PATH #Define Database Connection Information MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_BACKUP_USER=backup MYSQL_BACKUP_PASS='backup' MYSQL_CONFIG=/etc/my.cnf #Define Backup Directory Information BACKUP_DIR=/backup BACKUP_TMP_DIR=/backup/tmp BACKUP_PREFIX_NAME=mysql_backup BACKUP_SUFFIX_NAME=`date +%Y-%m-%d_%H-%M-%S-%N` #FULL_BACKUP_DIR=$BACKUP_PREFIX_NAME'_'`head -n1 $BACKUP_TMP_DIR/index |awk -F ':' '{print $1}'` INCREMENTAL_LSN=`tail -n1 $BACKUP_TMP_DIR/index |awk -F ':' '{print $2}'` if [ ! -d "$BACKUP_DIR" ];then mkdir -p $BACKUP_DIR fi CHECK_MYSQL_IS_OK() { CHECK_MYSQL_AVAILABLE=`mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_BACKUP_USER -p$MYSQL_BACKUP_PASS -ss -e "select @@version;"` if [ `echo $?` -ne 0 ]; then echo -e "\033[31mCan Not Connecting To MySQL Server,Please Check Again!! \033[0m" echo " HOST=$MYSQL_HOST PORT=$MYSQL_PORT USER=$MYSQL_BACKUP_USER PASS='***********'" exit 1 else echo -e "\033[32m ------------->>>>>>>> MySQL Database Connection Successfully !!! \033[0m" fi } INCR_MYSQL_BACKUP() { ## Get Last Full Backup Base Directory if [ -f $BACKUP_TMP_DIR/index ];then FULL_BACKUP_DIR=$BACKUP_PREFIX_NAME'_'`head -n1 $BACKUP_TMP_DIR/index |awk -F ':' '{print $1}'` echo -e "\033[32m ------------->>>>>>>> Find Last Backup Set: $FULL_BACKUP_DIR\033[0m" else echo -e "\033[31mCan Not Find Last Backup Index File On [$BACKUP_TMP_DIR],Please Check !!! \033[0m" exit 1 fi echo -e "\033[32m ------------->>>>>>>> Backup Begin Time: `date +'%Y-%m-%d %H:%M:%S'` \033[0m" innobackupex --defaults-file=$MYSQL_CONFIG --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_BACKUP_USER --password=$MYSQL_BACKUP_PASS --tmpdir=$BACKUP_TMP_DIR --extra-lsndir=$BACKUP_TMP_DIR --slave-info --parallel=4 --no-timestamp --compress --compress-threads=4 --kill-long-queries-timeout=120 --kill-long-query-type=select --ftwrl-wait-timeout=120 --ftwrl-wait-threshold=120 --throttle=200 --safe-slave-backup --safe-slave-backup-timeout=180 --ftwrl-wait-query-type=all --stream=xbstream --incremental --incremental-lsn=$INCREMENTAL_LSN $BACKUP_TMP_DIR >$BACKUP_DIR/$FULL_BACKUP_DIR/$BACKUP_SUFFIX_NAME 2>>$BACKUP_DIR/$FULL_BACKUP_DIR/$BACKUP_SUFFIX_NAME'.log' if [ `echo $?` -ne 0 ]; then echo -e "\033[31m ------------->>>>>>>> Xtrabackup Backup Failed!!! Please Check Backup Log \033[0m" mv $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME $BACKUP_DIR/$BACKUP_PREFIX_NAME'_'$BACKUP_SUFFIX_NAME'_FAILED' echo ' '$BACKUP_DIR/$FULL_BACKUP_DIR/$BACKUP_SUFFIX_NAME'_FAILED'/$BACKUP_SUFFIX_NAME'.log' exit 1 else echo -e "\033[32m ------------->>>>>>>> Backup End Time: `date +'%Y-%m-%d %H:%M:%S'` \033[0m" echo -e "\033[32m ------------->>>>>>>> Xtrabackup Executed Successfully !!! \033[0m" echo ' ------------->>>>>>>> 'Backup Directory: $BACKUP_DIR/$FULL_BACKUP_DIR fi ### backup MySQL config file cp $MYSQL_CONFIG $BACKUP_DIR/$FULL_BACKUP_DIR/my.cnf if [ `echo $?` -ne 0 ]; then echo -e "\033[31m ------------->>>>>>>> Backup MySQL Config File Failed \033[0m" exit 1 else echo -e "\033[32m ------------->>>>>>>> MySQL Config File Backup Successfully !!! \033[0m" echo ' ------------->>>>>>>> 'Backup Config File: $BACKUP_DIR/$FULL_BACKUP_DIR/my.cnf fi } GEN_BACKUP_INDEX_FILE() { TO_LSN=`cat $BACKUP_TMP_DIR/xtrabackup_checkpoints |grep to_lsn |awk -F ' = ' '{print $2}'` echo "$BACKUP_SUFFIX_NAME:$TO_LSN" >>$BACKUP_TMP_DIR/index cp $BACKUP_TMP_DIR/* $BACKUP_DIR/$FULL_BACKUP_DIR/ cd $BACKUP_DIR/$FULL_BACKUP_DIR && md5sum $BACKUP_SUFFIX_NAME >> backup_piece.md5sum.base if [ `echo $?` -ne 0 ]; then echo -e "\033[31m ------------->>>>>>>> Generate Backup [Index File] And [md5sum] Infomation Failed \033[0m" exit 1 else echo -e "\033[32m ------------->>>>>>>> Generate Backup [Index File] And [md5sum] Infomation Successfully !!! \033[0m" echo ' ------------->>>>>>>> 'Backup Index File: $BACKUP_DIR/$FULL_BACKUP_DIR/index echo ' ------------->>>>>>>> 'Backup md5sum File: $BACKUP_DIR/$FULL_BACKUP_DIR/backup_piece.md5sum.base fi } MAIN() { CHECK_MYSQL_IS_OK INCR_MYSQL_BACKUP GEN_BACKUP_INDEX_FILE } MAIN echo -e "\033[32m ------------->>>>>>>> Backup Execution Completed, Please Check The Backup Log\033[0m" echo ' ------------->>>>>>>> 'Backup Log File: $BACKUP_DIR/$FULL_BACKUP_DIR/$BACKUP_SUFFIX_NAME'.log'binglog备份
#!/bin/sh #BINLOGDIR请手动指定,数据库用户需要REPLICATION SLAVE 权限 LOCAL_BACKUP_DIR=/data/mysql57/binlog/ BACKUP_LOG=/backup/binlog/backuplog BINLOGDIR=/backup/binlog/ REMOTE_HOST=127.0.0.1 REMOTE_PORT=3306 REMOTE_USER=binlog REMOTE_PASS=binlog FIRST_BINLOG=binlog.000001 SLEEP_SECONDS=10 if [ ! -d "$LOCAL_BACKUP_DIR" ];then mkdir -p $LOCAL_BACKUP_DIR fi cd ${LOCAL_BACKUP_DIR} ## 运行while循环，连接断开后等待指定时间，重新连接 while : do if [ `ls -A "${LOCAL_BACKUP_DIR}" |wc -l` -eq 2 ];then LAST_FILE=${FIRST_BINLOG} else LAST_FILE=`ls -l ${LOCAL_BACKUP_DIR} | grep -v binlog.index |tail -n 1 |awk '{print $9}'` fi mysqlbinlog --raw --read-from-remote-server --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} --result-file=${BINLOGDIR} ${LAST_FILE} echo "`date +"%Y/%m/%d %H:%M:%S"` mysqlbinlog停止，返回代码：$?" >> ${BACKUP_LOG} echo "${SLEEP_SECONDS}秒后再次连接并继续备份" >>${BACKUP_LOG} sleep ${SLEEP_SECONDS} done