mysql备份脚本

配置之前查看是否含有这个配置项，mysql 5.0版本没有binlog_format配置项，启动的时候会报错

mysql>show variables like 'bin%' ;

 

Variable_name	value
binlog_cache_size	32768
binlog_direct_non_transactional_updates	OFF
binlog_format	MIXED

my.cnf

[mysqld]

datadir=/home/mysql/data #mysql主目录

log-bin=/home/binlog/mysql-bin  #binlog目录

binlog-do-db=test #备份数据库名

binlog_format=mixed #备份模式

expire_logs_days=2 #保留日志天数

#!/bin/bash
USER='root'
PASSWORD='root'
DATE= `date +"%F"`
FULL_BACKUP_DIR= "/data1/data/bak/full_backup"
INCR_BACKUP_DIR= "/data1/data/bak/incremental_backup"
DATA_DIR= "/home/mysql/data"
BINLOG_DIR= "/home/mysql/binlog"
backup_log= "/data1/data/logs/backup_$DATE.log"
gzdumpfile= "$DATE.sql.tar.gz"
dbname= "test"

#全量备份
function fullBackup(){
    if [ ! -d $FULL_BACKUP_DIR ];then
        echo "Full backup directory doesn't exist , Start creating ......" >>$backup_log
        /bin/mkdir $FULL_BACKUP_DIR
    fi

    cd $FULL_BACKUP_DIR
    echo `date +"%F %T"` "Start backup ....." >> $backup_log
    mysqldump -u$USER -p$PASSWORD --opt --flush-logs --master-data=2 --single-transaction $dbname >$dbname.$DATE.sql
    　　
   
    if [ $? = 0];then
        tar -czvf ${dbname}_full.$gzdumpfile $dbname.$DATE.sql
        echo `date +"%F %T"` Full backup MySQL succeed >>$backup_log
    else
        echo `date +"%F %T"` Full backup MySQL fail >>$backup_log
    fi

    find $FULL_BACKUP_DIR -name "*.sql.tar.gz" -mtime +32 -exec rm -f {} \;>/dev/null 2>&1
    find $FULL_BACKUP_DIR -name "*.sql"  -exec rm -f {} \;>/dev/null 2>&1
    find $FULL_BACKUP_DIR -name "*.sql.tar.gz" -mtime +32 -exec rm -f {} \;>/dev/null 2>&1
    echo `date +"%F %T"` "End of the backup !" >>$backup_log
}
#增量备份

function incrementalBackup(){
    if [ ! -d $INCR_BACKUP_DIR ];then 
        echo "Incremental backup directory doesn't exist , Start creating ......" >>$backup_log
        /bin/mkdir $INCR_BACKUP_DIR
    fi

    Time=`date -d "-10 day" +%F\ %T`
    StartTime=`date -d "-1 day" +%F\ 00:00:00`
    EndTime=`date +%F\ 00:00:00`

    mysql -u$USER -p$PASSWORD -e "purge master logs before '$Time'"&& echo echo `date +"%F %T"` delete 10 days befor log |tee -a $backup_log

    filename=`cat $BINLOG_DIR/mysql-bin.index|awk -F"/" '{print $NF}'`

    echo echo `date +"%F %T"` Start backup `date -d "-1 day" +%F` binlog >>$backup_log

    cd $INCR_BACKUP_DIR

    for i in $filename
    do
        mysqlbinlog --start-datetime="$StartTime" --stop-datetime="$EndTime" $BINLOG_DIR/$i >> $dbname.$DATE.sql
        if [ $? -eq 0 ];then
            tar -czvf ${dbname}_incr.$gzdumpfile $dbname.$DATE.sql
            echo `date +"%F %T"` $i Incremental backup MySQL succeed >>$backup_log
        else
            echo `date +"%F %T"` $i Incremental backup MySQL fail >>$backup_log
        fi
    done

find $INCR_BACKUP_DIR -name "*.sql" -exec rm -f {} \; >/dev/null 2>&1

}

day=`date +%d`

if [ $day -eq 01 ];then
    echo `date +"%F %T"` "The current date for ${day}th, Start full backup ">>$backup_log
    fullBackup
else
    echo `date +"%F %T"` "The current date for ${day}th, Start incremental backup ">>$backup_log
    incrementalBackup
fi
    

 #增加当前用户操作binlog日志文件权限，否则脚本执行时会报mysql-bin.xxxx :Permission denied:

usermod -G test,mysql test