mysql完全备份 二进制日志_MySQL完全备份脚本：数据+二进制日志+备份日志

1 #!/bin/bash2 #3 # Function：实现mysql完全备份4 # Notes：5

6 # 定义备份使用到的相关变量(需要根据用户实际环境做相应的修改)7 backup_data=/backup/mysql/full8 backup_binlog=/backup/mysql/logbin9 mysql_binlog=/mydata/log-bin10 mysql_bin_path=`ps -ef | grep -E "mysqld[[:space:]]+" | awk -F ' ' '{ print $8 }' | sed -r 's@[^/]+/?$@@'`11 mysql_user=root12 mysql_password=mysqldba13 logfile=/var/log/mysqldump.log14

15 # 函数定义16

17 ########################[MySQL健康检测]######################18 health_check() {19 if pgrep mysqld &> /dev/null;then20 mysqld_pid=`pgrep -l mysqld | grep -E "mysqld\>" | awk '{ print $1 }'`21 echo "$(date +"%Y-%m-%d %H:%M:%S") $mysqld_pid [Note] Server Mysql is Running ..." >> $logfile22 else23 service mysqld start &> /dev/null || echo "$(date +"%Y-%m-%d %H:%M:%S") [Warning] Server MySQL is not running, backup failed" >> $logfile && return 524 fi25 }26

27 #######################[mysqldump完全备份]###################28 full_backup(){29 local backup_name=full-`date +%F-%T`.sql30 [ ! -d $backup_data ] && mkdir -p $backup_data31 ${mysql_bin_path}mysqldump -u$mysql_user -p$mysql_password --all-databases --lock-all-tables --flush-logs --master-data=2 \32 --triggers --routines --events --set-gtid-purged=off -r ${backup_data}/$backup_name &> /dev/null33 if grep -q "CHANGE MASTER TO" ${backup_data}/$backup_name ;then34 return 035 else36 return 537 fi38 }39

40 ########################[Mysql 二进制日志备份]#####################41 bin_backup(){42 [ ! -d $backup_binlog ] && mkdir -p $backup_binlog43 cd $mysql_binlog44 tar -jcf bin-`date +"%F-%H'%M'%S"`.tar.bz2 * &> /dev/null45 \mv -f bin*.tar.bz2 $backup_binlog46 }47

48 # 主函数main49

50 health_check51 if [ $? -eq 0 ];then52 echo "$(date +"%Y-%m-%d %H:%M:%S") [Note] MySQL full backup start ..." >> $logfile53 else54 echo "$(date +"%Y-%m-%d %H:%M:%S") [Warning] Server MySQL is not running, backup failed" | mail -s "mysql backup is failed" root@`hostname`55 fi56 full_backup57 if [ $? -eq 0 ];then58 echo "$(date +"%Y-%m-%d %H:%M:%S") [Note] MySQL full backup is finished" >> $logfile59 chmod -R 600 $backup_data60 else61 echo "$(date +"%Y-%m-%d %H:%M:%S") [Warning] Function full_backup() execution failed, backup was interrupted" | cat | tee -a $logfile | mail -s "mysql backup is failed" root@`hostname`62 fi63 echo "$(date +"%Y-%m-%d %H:%M:%S") [Note] MySQL binary log file backup is started ..." >> $logfile64 bin_backup65 if [ $? -eq 0 ];then66 echo "$(date +"%Y-%m-%d %H:%M:%S") [Note] MySQL binary log file backup is finished " >> $logfile67 chmod -R 600 $backup_binlog68 else69 echo "$(date +"%Y-%m-%d %H:%M:%S") [Warning] Mysql binary log file backup is not completed" | cat | tee -a $logfile | mail -s "mysql binary log backup failed" root@`hostname`70 fi