您好,登錄后才能下訂單哦!
這篇文章主要介紹“MySQL自動備份的腳本及異地定時FTP”,在日常操作中,相信很多人在MySQL自動備份的腳本及異地定時FTP問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對大家解答”MySQL自動備份的腳本及異地定時FTP”的疑惑有所幫助!接下來,請跟著小編一起來學習吧!
分享個MySQL自動備份腳本、定時執(zhí)行設置及Windows自動FTP。
前提環(huán)境:MySQL數(shù)據(jù)庫服務器開啟vsftpd,并配置合適帳號以便能被內網(wǎng)存儲服務器FTP
第一步:編寫MySQL自動執(zhí)行腳本
#!/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 <<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;
第二步:定時執(zhí)行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%
上面的弄完后,你可以開始測試了。
到此,關于“MySQL自動備份的腳本及異地定時FTP”的學習就結束了,希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習,快去試試吧!若想繼續(xù)學習更多相關知識,請繼續(xù)關注億速云網(wǎng)站,小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
免責聲明:本站發(fā)布的內容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經查實,將立刻刪除涉嫌侵權內容。