MySQL分库备份

公司一直是全备+binlog，但是如果单个库出现问题怎么办，在进行完整恢复的话，第一会耽误时间，第二会影响其它数据库，第三如果数据量大，对于库的恢复也是问题，所以我提出了，除了全备，还要做分库备份，只要更灵活，出现问题，直接恢复单库（方便、快捷）

下面直接上脚本

1，将MySQL自带库外的其他库进行备份

#!/bin/bash#---------------------------------------------------------# $Name:         Store_backup.sh# $Version:      v1.0# $Author:       邱月涛# $organization: www.yi******.cn# $Create Date:  2017-06-03# $Description:  MySQL store Backup #用于MySQL 分库备份,并保留30天数据#---------------------------------------------------------#source /etc/init.d/functionsDAY=`date +%F`BACK_DIR="/home/backup/mysqlbackup"DBUSER="root"DBPASSWD="你自己的password"BIN_PATH="/usr/bin"LOG="/home/backup/mysqlbackup/mysqlback.log"## Close all tables and refresh log ####"$BIN_PATH"/mysql -u "$DBUSER" -p"$DBPASSWD" -e "flush logs"#"$BIN_PATH"/mysql -u "$DBUSER" -p"$DBPASSWD" -e "flush tables with read lock"echo "mysqldump start `date +%F\ %H\:%M\:%S`" >> "$LOG"## Each backup database to the target location ####for i in `"$BIN_PATH"/mysql -u "$DBUSER" -p"$DBPASSWD" -e "show databases" |grep -vE "Database|information_schema"`  do        if [ ! -d "$BACK_DIR"/"$i" ]; then                mkdir -p  "$BACK_DIR"/"$i"           fi"$BIN_PATH"/mysqldump -u "$DBUSER" -p"$DBPASSWD" --default-character-set=utf8 --opt --lock-tables -f --log-error="$LOG" "$i" > "$BACK_DIR"/"$i"/"$DAY".sql   doneecho "mysqldump stop `date +%F\ %H\:%M\:%S`" >> "$LOG"echo "" >>"$LOG"## Delete 30 days ago backup files ####for RM in `"$BIN_PATH"/mysql -u "$DBUSER" -p"$DBPASSWD" -e "show databases" |grep -vE "Database|information_schema"`   dorm -rf "$BACK_DIR"/"$RM"/$(date +%F --date='30 days ago').sql    done

效果如下：

[root@files mysqlbackup]# tree -L 1 /home/backup/mysqlbackup/home/backup/mysqlbackup├──  luence├──  hpi├──  h├── my├── mysql├── mysqlback.log├── ypp└──  tao

将脚本加入crontab定时任务即可；

[root@files mysqlbackup]# crontab -l0  4 * * * /root/shell/mysqlbackup.sh