mysql备份脚本分享

MySQL服务器创建备份用户，授权select权限就可以了。

登陆数据库：

 MariaDB [(none)]> grant select,lock tables，show view on *.* to backup@'localhost' identified by '123456';
 Query OK, 0 rows affected (0.00 sec)
 MariaDB [(none)]> flush privileges;
 Query OK, 0 rows affected (0.00 sec)

mysql备份脚本分享

 #!/bin/bash
 #2019年10月25日 11:01:58
 #by mysql_backup
 #wangxuejin www.cnbugs.com
 ###############################
 SQL_CMD="/usr/bin/mysqldump"
 SQL_USR="backup"
 SQL_PWD="123456"
 SQL_DB="$*"
 SQL_DIR="/data/backup"
 SQL_HOST="127.0.0.1"
 SQL_DATE=date +%Y%m%d
 if [ ! -d $SQL_DIR ];then
     mkdir -p $SQL_DIR
 fi
 if [ $# -eq 0 ];then
     echo -e "\033[32m-----------------------------------\033[0m"
                 echo -e "\033[32mUsage:{/bin/bash $0 a|b|c|d|all|help}\033[0m"    
         exit 0
 fi
 for SQL_DB in `echo $SQL_DB`
 do
 if [ $SQL_DB == "all" ];then
     $SQL_CMD -h $SQL_HOST -u$SQL_USR -p$SQL_PWD -P 3306  --all-databases >$SQL_DIR/${SQL_DATE}${SQL_DB}.sql     if [ $? -eq 0 ];then         echo -e "\033[32m------------------------------\033[0m"                echo -e "\033[32mThe $SQL_DB backup success.\033[0m"                cd $SQL_DIR         tar -czvf ${SQL_DATE}${SQL_DB}.tar.gz ${SQL_DATE}${SQL_DB}.sql;rm -rf ${SQL_DATE}${SQL_DB}.sql
         ls -l $SQL_DIR
         exit
     else
         echo -e "\033[32m------------------------------\033[0m"
             echo -e "\033[32mThe $SQL_DB backup failed,please check $SQL_DB backup..\033[0m"
                rm -rf ${SQL_DIR}/${SQL_DATE}_{SQL_DB}.sql
                exit 1
     fi
 fi
 $SQL_CMD -h $SQL_HOST -u$SQL_USR -p$SQL_PWD -P 3306  $SQL_DB >$SQL_DIR/${SQL_DATE}_${SQL_DB}.sql
 if [ $? -eq 0 ];then
     echo -e "\033[32m------------------------------\033[0m"
     echo -e "\033[32mThe $SQL_DB backup success.\033[0m"
 else
     echo -e "\033[32m------------------------------\033[0m"
         echo -e "\033[32mThe $SQL_DB backup failed,please check $SQL_DB backup..\033[0m"
         rm -rf ${SQL_DIR}/${SQL_DATE}_${SQL_DB}.sql
         exit 1
 fi
 cd $SQL_DIR
 tar -czvf ${SQL_DATE}${SQL_DB}.tar.gz ${SQL_DATE}${SQL_DB}.sql;rm -rf ${SQL_DATE}_${SQL_DB}.sql
 ls -l $SQL_DIR
 done

使用rsync把备份文件传到远端

rsync -avz --ignore-existing --ignore-existing $SQL_DIR rsync0172.18.1.115::backup/MySQL_PRD_172.18.2.68 --password-file=/etc/rsyncd.password

脚本执行需要外面增加参数，就是需要跟需要备份的库，通过for循环去执行备份

例如：我需要备份wxj库和Syslog库，执行命令如下：

[root@wxj mysql]# sh /backup_mysql_v1.sh wxj Syslog

Mr丶点心永久会员

打赏 收藏 海报 链接