[MySQL] shell脚本备份mysql里面所有数据库到远程服务器

Myth丶恋晨 2022-07-16 00:15 160阅读 0赞

本地数据库mysql，有多个数据库要备份到远程服务器上，远程服务器提供vsftp服务

1 - 创建一个shell脚本，写入以下内容并保存

vi.mysql\_back.sh

写入

#!/bin/bash
    
    BIN_DIR="/usr/bin"
    BACKUP_DIR="/var/mysql_dump/data"
    DATE=`date +%Y%m%d`
    mkdir -p $BACKUP_DIR
    
    MYSQL=mysql
    HOST="10.XXX.XX.XX"
    USER="user"
    PASSWORD="password"
    
    
    
    
    # 获取本地服务器IP地址
    
    local_ip=$(ifconfig | grep "inet addr" | grep -v 127.0.0.1 | awk '{print $2}' | awk -F ':' '{print $2}')
    
    
    
    
    # 查看mysql里面所有数据库名
    
    command="show databases"
    declare DBS=`$MYSQL -h${HOST} -u${USER} -p${PASSWORD} -e "${command}" --skip-column-name`
    for DB_NAME in $DBS
    do
            if [ "$DB_NAME" != "information_schema" ] && [ "$DB_NAME" != "mysql" ] && [ "$DB_NAME" != "performance_schema" ] && [ "$DB_NAME" != "tower_debug21" ]
            then
            #echo "$DB_NAME"
    
            # mysqldump出每个数据库的备份，并压缩
            $BIN_DIR/mysqldump --opt -u$USER -p$PASSWORD $DB_NAME | gzip > $BACKUP_DIR/$DB_NAME.dump_$DATE.sql.gz
            fi
    done
    
    
    
    
    # 上传到远程ftp 
    
    ftp -n<<!
    open 10.XXX.XX.XXX    
    user ftpuser ******           
    binary
    hash
    cd backups/database/$local_ip/
    lcd $BACKUP_DIR                     
    prompt
    mput *.sql.gz
    
    close
    bye
    !
    
    rm -rf $BACKUP_DIR/*

2 - 修改属性为可执行

chmod 755 mysql\_back.sh

3 - crond定时任务每天00：00：00定时执行

0 0 \* \* \* /var/mysql\_dump/mysql\_back.sh > /dev/null 2>&1