1. set -e
    3. USER="backup"
    4. PASSWORD="backup"
    5. #定义变量=数据库数据目录
    6. DATA_DIR="/data/mysql"
    7. BIN_INDEX=$DATA_DIR"/mysql-bin.index"
    8. #定义备份目录=备份存放路径
    9. BACKUP_DIR="/data/backup/mysql"
    10. BACKUP_LOG="/var/log/mysql/backup.log"
    12. DATE=`date +"%Y%m%d"`
    13. TIME=`date +"%Y%m%d%H"`
    15. LOG_TIME=`date +"%Y-%m-%d %H:%M:%S"`
    16. DELETE_BINLOG_TIME="7 day"
    17. INCREMENT_INTERVAL="3 hour"
    19. note() {
    20. printf "[$LOG_TIME] note: $*\n" >> $BACKUP_LOG;
    21. }
    23. warning() {
    24. printf "[$LOG_TIME] warning: $*\n" >> $BACKUP_LOG;
    25. }
    27. error() {
    28. printf "[$LOG_TIME] error: $*\n" >> $BACKUP_LOG;
    29. exit 1;
    30. }
    32. full_backup() {
    33. local dbs=`ls -l $DATA_DIR | grep "^d" | awk -F " " '{print $9}'`
    35. for db in $dbs
    36. do
    37. local backup_dir=$BACKUP_DIR"/full/"$db
    38. local filename=$db"."$DATE
    39. local backup_file=$backup_dir"/"$filename".sql"
    41. if [ ! -d $backup_dir ]
    42. then
    43. mkdir -p $backup_dir || { error "创建数据库 $db 全量备份目录 $backup_dir 失败"; continue; }
    44. note "数据库 $db 全量备份目录 $backup_dir  不存在，创建完成";
    45. fi
    47. note "full backup $db start ..."
    48. mysqldump --user=${USER} --password=${PASSWORD} --flush-logs --skip-lock-tables --quick $db > $backup_file || { warning "数据库 $db 备份失败"; continue; }
    50. cd $backup_dir
    51. tar -cPzf $filename".tar.gz" $filename".sql"
    52. rm -f $backup_file
    53. chown -fR mysql:mysql $backup_dir
    55. note "数据库 $db 备份成功";
    56. note "full backup $db end."
    57. done
    58. }
    60. increment_backup() {
    61. local StartTime=`date "-d $INCREMENT_INTERVAL ago" +"%Y-%m-%d %H:%M:%S"`
    62. local DELETE_BINLOG_END_TIME=`date "-d $DELETE_BINLOG_TIME ago" +"%Y-%m-%d %H:%M:%S"`
    63. local dbs=`ls -l $DATA_DIR | grep "^d" | awk -F " " '{print $9}'`
    65. mysql -u$USER -p$PASSWORD -e "purge master logs before '$DELETE_BINLOG_END_TIME'" && note "delete $DELETE_BINLOG_TIME days before log";
    67. filename=`cat $BIN_INDEX | awk -F "/" '{print $2}'`
    68. for i in $filename
    69. do
    70. for db in $dbs
    71. do
    72. local backup_dir=$BACKUP_DIR"/increment/"$db
    73. local filename=$db"."$TIME
    74. local backup_file=$backup_dir"/"$filename".sql"
    76. if [ ! -d $backup_dir ]
    77. then
    78. mkdir -p $backup_dir || { error "创建数据库 $db 增量备份目录 $backup_dir 失败"; continue; }
    79. note "数据库 $db 增量备份目录 $backup_dir  不存在，创建完成";
    80. fi
    82. note "increment backup $db form time $StartTime start ..."
    84. mysqlbinlog -d $db --start-datetime="$StartTime" $DATA_DIR/$i >> $backup_file || { warning "数据库 $db 备份失败"; continue; }
    86. note "increment backup $db end."
    87. done
    88. done
    90. for db in $dbs
    91. do
    92. local backup_dir=$BACKUP_DIR"/increment/"$db
    93. local filename=$db"."$TIME
    94. local backup_file=$backup_dir"/"$filename".sql"
    96. cd $backup_dir
    97. tar -cPzf $filename".tar.gz" $filename".sql"
    98. rm -f $backup_file
    100. note "数据库 $db 备份成功";
    101. done
    102. }
    104. case "$1" in
    105. full)
    106. full_backup
    107. ;;
    108. increment)
    109. increment_backup
    110. ;;
    111. *)
    112. exit 2
    113. ;;
    114. esac
    116. exit 1