mysql数据库备份
适合小公司MySQL备份
#!/bin/bash ############################# # time:20191210 # fage trainning ############################# BACK_DIR=/application/mysqlbakdb DB_LST=/tmp/db.lst DATE=`date +%Y-%m-%d` mysql -u root -p'S1yxYRrE33TZPn1o' -e 'show databases' > $DB_LST [ ! -d $BACK_DIR ] && mkdir -p $BACK_DIR for i in $(grep -vE "Database|information_schema|test" $DB_LST) do mysqldump --user='root' --password='S1yxYRrE33TZPn1o' --default-character-set=utf8 --add-locks --lock-all-tables $i > $BACK_DIR/$i-$DATE.sql [ "$PWD" != "$BACK_DIR" ] && cd $BACK_DIR gzip -f $BACK_DIR/$i-$DATE.sql done find $BACK_DIR -mtime +7 | xargs -i rm -rf {}
mysqldump工具备份-1
#!/bin/bash #2020-02-23 #auto_backup_mysql.sh #by Flyaway ############################## SQL_DB="$*" SQL_USR="backup" SQL_PWD="123456" SQL_CMD="/usr/bin/mysqldump" SQL_DIR="/data/backup/`date +%F`" if [ $# -eq 0 ];then echo -e "\033[32m---------------------\033[0m" echo -e "\033[32m Usage:{/bin/bash $0 jfedu001|jfedu002|jfedu003|jfedu004|all|help} \033[0m" exit fi if [ ! -d $SQL_DIR ];then mkdir -p $SQL_DIR echo -e "\033[32m The dir $SQL_DIR create succeded.\033[0m" fi #备份all 所有库: if [ $1 == "all" ];then $SQL_CMD -u$SQL_USR -p$SQL_PWD --all-databases > $SQL_DIR/${SQL_DB}.sql if [ $? -eq 0 ];then echo -e "\033[32m---------------------\033[0m" echo -e "\033[32m The mysql $SQL_DB database backup succeded. \033[0m" echo -e "\033[32m $SQL_DIR\033[0m" ls -l $SQL_DIR exit else echo -e "\033[32m---------------------\033[0m" echo -e "\033[31m The mysql $SQL_DB database backup failed,please check. \033[0m" rm -rf $SQL_DIR/${SQL_DB}.sql echo -e "\033[32m $SQL_DIR \033[0m" ls -l $SQL_DIR exit fi fi #同时备份多个库: for i in $SQL_DB do echo $i sleep 2 $SQL_CMD -u$SQL_USR -p$SQL_PWD $i > $SQL_DIR/${i}.sql if [ $? -eq 0 ];then echo -e "\033[32m---------------------\033[0m" echo -e "\033[32m The mysql $i database backup succeded. \033[0m" echo -e "\033[32m $SQL_DIR\033[0m" ls -l $SQL_DIR else echo -e "\033[32m---------------------\033[0m" echo -e "\033[31m The mysql $i database backup failed,please check. \033[0m" rm -rf $SQL_DIR/${i}.sql echo -e "\033[32m $SQL_DIR \033[0m" ls -l $SQL_DIR fi done
mysqldump工具备份-2
#!/bin/bash #2020-02-29 #auto_backup_mysql.sh #by Flyaway ############################## SQL_DB="$*" SQL_USR="backup" SQL_PWD="123456" SQL_CMD="/usr/bin/mysqldump" SQL_DIR="/data/backup/`date +%F`" #Define dunction check_backu_status function check_backup_status(){ if [ $? -eq 0 ];then echo -e "\033[32m---------------------\033[0m" echo -e "\033[32m The mysql $SQL_DB database backup succeded. \033[0m" #备份成功发送邮件 #echo "The database backup succeded"|mail -s "database backup" f15372368849@163.com #备份成功发送微信信息 /usr/src/weixin.sh FengYuanFei test hello,world. echo -e "\033[32m $SQL_DIR\033[0m" ls -l $SQL_DIR exit else echo -e "\033[32m---------------------\033[0m" echo -e "\033[31m The mysql $SQL_DB database backup failed,please check. \033[0m" #备份失败将自动删除该文件 rm -rf $SQL_DIR/${SQL_DB}.sql #备份失败发送邮件 echo -e "The database backup failed"|mail -s "database backup" f15372368849@163.com echo -e "\033[32m $SQL_DIR \033[0m" ls -l $SQL_DIR exit fi } if [ $# -eq 0 ];then echo -e "\033[32m---------------------\033[0m" echo -e "\033[32m Usage:{/bin/bash $0 jfedu001|jfedu002|jfedu003|jfedu004|all|help} \033[0m" exit fi #判断目录是否存在,不存在则创建 if [ ! -d $SQL_DIR ];then mkdir -p $SQL_DIR echo -e "\033[32m The dir $SQL_DIR create succeded.\033[0m" fi #备份all 所有库: if [ $1 == "all" ];then $SQL_CMD -u$SQL_USR -p$SQL_PWD --all-databases > $SQL_DIR/${SQL_DB}.sql check_backup_status fi #同时备份多个库: for i in $SQL_DB do echo $i sleep 2 $SQL_CMD -u$SQL_USR -p$SQL_PWD $i > $SQL_DIR/${i}.sql check_backup_status done