mysql自动备份脚本及异地定时FTP
分享个自己写的mysql自动备份脚本、定时执行设置及windows自动FTP，请大家指教。前提环境：mysql数据库服务器开启vsftpd，并配置合适帐号以便能被内网存储服务器FTP
第一步：编写mysql自动执行脚本
[*]#!/bin/sh
[*]# mysql_db_backup.sh: backup mysql databases.
[*]#
[*]# Last updated: Wed Nov9 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 /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，这个应该不用解释吧？
[*]# 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
[*]# cat /etc/crontab
第三步：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%
上面的弄完后，你可以开始测试了。
页:
[1]