MySQL完全备份脚本：数据+二进制日志+备份日志
一. 脚本须知
1.mysql数据文件和二进制日志文件最好保存在不同的分区或存储设备上
2.备份完成后注意修改数据的权限以防止泄露重要信息，哪些主机哪些用户可以用来恢复
3. 查看导出的2进制日志文件看是否符合逻辑和业务，2进制日志文件只能保证物理上数据即时点还原，却保证不了数据逻辑上正确恢复（比如drop语句）
4.为了保证数据恢复没有问题建议在测试机上做一次数据恢复测试
5.恢复测试OK可以放到crontab定时任务中
1 #!/bin/bash 2 # 3 # Function：实现mysql完全备份 4 # Notes： 5 6 # 定义备份使用到的相关变量（需要根据用户实际环境做相应的修改） 7 backup_data=/backup/mysql/full 8 backup_binlog=/backup/mysql/logbin 9 mysql_binlog=/mydata/log-bin 10 mysql_bin_path=`ps -ef | grep -E "mysqld[[:space:]]+" | awk -F ' ' '{ print $8 }' | sed -r 's@[^/]+/?$@@'` 11 mysql_user=root 12 mysql_password=mysqldba 13 logfile=/var/log/mysqldump.log 14 15 # 函数定义 16 17 ########################[MySQL健康检测]###################### 18 health_check() { 19 if pgrep mysqld &> /dev/null;then 20 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 ..." >> $logfile 22 else 23 service mysqld start &> /dev/null || echo "$(date +"%Y-%m-%d %H:%M:%S") [Warning] Server MySQL is not running, backup failed" >> $logfile && return 5 24 fi 25 } 26 27 #######################[mysqldump完全备份]################### 28 full_backup(){ 29 local backup_name=full-`date +%F-%T`.sql 30 [ ! -d $backup_data ] && mkdir -p $backup_data 31 ${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/null 33 if grep -q "CHANGE MASTER TO" ${backup_data}/$backup_name ;then 34 return 0 35 else 36 return 5 37 fi 38 } 39 40 ########################[Mysql 二进制日志备份]##################### 41 bin_backup(){ 42 [ ! -d $backup_binlog ] && mkdir -p $backup_binlog 43 cd $mysql_binlog 44 tar -jcf bin-`date +"%F-%H'%M'%S"`.tar.bz2 * &> /dev/null 45 \mv -f bin*.tar.bz2 $backup_binlog 46 } 47 48 # 主函数main 49 50 health_check 51 if [ $? -eq 0 ];then 52 echo "$(date +"%Y-%m-%d %H:%M:%S") [Note] MySQL full backup start ..." >> $logfile 53 else 54 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 fi 56 full_backup 57 if [ $? -eq 0 ];then 58 echo "$(date +"%Y-%m-%d %H:%M:%S") [Note] MySQL full backup is finished" >> $logfile 59 chmod -R 600 $backup_data 60 else 61 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 fi 63 echo "$(date +"%Y-%m-%d %H:%M:%S") [Note] MySQL binary log file backup is started ..." >> $logfile 64 bin_backup 65 if [ $? -eq 0 ];then 66 echo "$(date +"%Y-%m-%d %H:%M:%S") [Note] MySQL binary log file backup is finished " >> $logfile 67 chmod -R 600 $backup_binlog 68 else 69 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
二.实例效果图
脚本执行完查看备份数据和日志
脚本执行过程跟踪
加入crontab任务
