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

本地数据库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