Mysql自动定时备份及上传到FTP中

来源:菩提树下 博客  发布日期:2012-08-17 13:20:57

分享个自己写的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 
#www.it165.net 
 
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 <<EOF 
  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 
[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% 


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

Tag标签: Mysql   定时备份  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规