配置之前查看是否含有这个配置项,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