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

coverl

　　分享个自己写的mysql自动备份脚本、定时执行设置及windows自动FTP，请大家指教。
　　前提环境：mysql数据库服务器开启vsftpd，并配置合适帐号以便能被内网存储服务器FTP
　　第一步：编写mysql自动执行脚本
　　

• #!/bin/sh
  
• # 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 /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 &quot;---------------------&quot; >> $log_path
  
• echo &quot;backup mysql db stop&quot; >> $log_path
  
• date >> $log_path
  
• 
  
• echo &quot;--------&quot; >> $log_path
  
• echo &quot;--------------&quot; >> $log_path
  
• echo &quot;-------------------------------------------------------------------------------&quot; >> $log_path
  
• 
  
• #------------this log is for monitor ssh status
  
• date >> $ssh_log_path
  
• echo &quot;---------------------&quot; >> $ssh_log_path
  
• 
  
• ls -l $mysql_backup_path >> $log_path
  
• 
  
• echo &quot;--------------&quot; >> $log_path
  
• 
  
• cd $backup_dir
  
• du -s >> $log_path
  
• du -sm >> $log_path
  
• du -sh >> $log_path
  
• 
  
• echo &quot;--------------&quot; >> $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
  
• [root@localhost /]# 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%
  

　　

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