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