mysql自动异地备份脚本_MySQL自动备份脚本及异地定时FTP

分享个MySQL自动备份脚本、定时执行设置及Windows自动FTP。

前提环境：MySQL数据库服务器开启vsftpd，并配置合适帐号以便能被内网存储服务器FTP

第一步：编写MySQL自动执行脚本

#!/bin/bash

# mysql_db_backup.sh: backup mysql databases.

#

# Last updated: Wed Nov  9 07:01:01 CST 2011

# ----------------------------------------------------------------------

# This is a free shell script under GNU GPL version 2.0 or above

# Copyright (C) 2011 Andy Yao

# Blog:http://t.qq.com/andy_microblog

# ----------------------------------------------------------------------

# your mysql login information

# db_user is mysql username

# db_passwd is mysql password

# db_host is mysql host

# -----------------------------

db_user="root"

db_passwd="123456"

db_host="192.168.1.11"

# the directory for story your backup file.

backup_dir="/mnt/sdb1/mysql_db_backup"

# date format for backup file (dd-mm-yyyy)

time="$(date +"%Y-%m-%d_%H-%M-%S")"

file_time="$(date +"%Y-%m-%d_%H-%M-%S")"

mysql_backup_path="$backup_dir/$file_time"

mkdir $backup_dir/$file_time

log_path="$backup_dir/$file_time.log.txt"

#------------this log is for monitor ssh status

ssh_log_path="$backup_dir/log.txt"

echo "---------------------" >> $ssh_log_path

date >> $ssh_log_path

echo "-------------------------------------------------------------------------------" >> $log_path

echo "--------------" >> $log_path

echo "--------" >> $log_path

echo "backup mysql db start" >> $log_path

date >> $log_path

echo "---------------------" >> $log_path

#!/bin/bash

cat /dev/null > $backup_dir/mysqlback.txt

connmsg=`mysql -h$db_host -u$db_user -p$db_passwd $db <

show databases;

exit

EOF`

echo "$connmsg" > $backup_dir/mysqlback.txt

while read line

do

if [ "$line" != "Database" ]; then

#mysqldump -u$user -p$ps "$line" >/share/"$line".sql

echo "--------" >> $log_path

date >> $log_path

echo "$line" >> $log_path

mysqldump -h$db_host -u$db_user -p$db_passwd "$line" --lock-tables=false | gzip -9 > "$mysql_backup_path/$line.$time.sql.gz"

date >> $log_path

echo "--------" >> $log_path

fi

done < $backup_dir/mysqlback.txt

echo "---------------------" >> $log_path

echo "backup mysql db stop" >> $log_path

date >> $log_path

echo "--------" >> $log_path

echo "--------------" >> $log_path

echo "-------------------------------------------------------------------------------" >> $log_path

#------------this log is for monitor ssh status

date >> $ssh_log_path

echo "---------------------" >> $ssh_log_path

ls -l $mysql_backup_path >> $log_path

echo "--------------" >> $log_path

cd $backup_dir

du -s >> $log_path

du -sm >> $log_path

du -sh >> $log_path

echo "--------------" >> $log_path

du -h |sort -rk2 >> $log_path

exit 0;

第二步:定时执行mysql备份脚本，设置crontab，这个应该不用解释吧？

[root@localhost /]# cat /etc/crontab

SHELL=/bin/bash

PATH=/sbin:/bin:/usr/sbin:/usr/bin

MAILTO=root

HOME=/

# run-parts

01 * * * * root run-parts /etc/cron.hourly

02 4 * * * root run-parts /etc/cron.daily

22 4 * * 0 root run-parts /etc/cron.weekly

42 4 1 * * root run-parts /etc/cron.monthly

01 7 * * * root /mysql_db_backup/mysql_db_backup_auto_db_filelist.sh

* */1 * * * root ntpdate 203.129.68.14 && hwclock -w

第三步：Windows端自动定时FTP，将下代码保存为bat，并设置计划任务

@echo off & color 1f & title 自动FTPMYSQL备份文件

mode con: cols=60 lines=10

echo ==========================================================

echo --

echo --

echo --            ----### 自动FTPMYSQL备份文件 ###----

echo --

echo --

echo --处理中，请不要手动关闭程序窗口，

echo --

echo --完成后，程序会自动关闭...

set xtime=%time::=%

set xdate=%date%

set copy_path=%xdate:~0,4%-%xdate:~5,2%-%xdate:~8,2%_07-01-01

rem 指定LOG存放路径

set log_path=c:\bat\log\ftp_mysql_copy.log.txt

echo -------------------------------------- >>%log_path%

echo -------------------- >>%log_path%

date /t >>%log_path% & time /t >>%log_path%

echo --开始------------------ >>%log_path%

cd E:\MYSQL_BACKUP_12

e:

md %copy_path%

cd %copy_path%

echo open 192.168.1.11 >ftp.src

echo username>>ftp.src

echo password>>ftp.src

echo cd /software/mysql_db_backup/%copy_path%/>>ftp.src

echo pwd>>ftp.src

echo ls>>ftp.src

echo prompt>>ftp.src

echo bin>>ftp.src

echo mget *>>ftp.src

echo bye>>ftp.src

ftp -s:ftp.src

del ftp.src

echo --结束------------------ >>%log_path%

date /t >>%log_path% & time /t >>%log_path%

echo -------------------- >>%log_path%

echo -------------------------------------- >>%log_path%

上面的弄完后，你可以开始测试了。