Mysql automatic backup script and remote timed FTP

Share a self-written mysql automatic backup script, timed execution settings, and windows automatic FTP. Please advise.

Prerequisites: vsftpd is enabled on the mysql database server, and an appropriate account is configured for FTP

Step 1: Compile the mysql Automatic Execution script

 

 
 

  
  
#!/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 <<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; 
 
 

 

Step 2: Execute the mysql backup script regularly and set crontab. Do you have to explain this?

 

 
 

  
  
[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 
 
 

 

Step 3: automatically schedule FTP on windows, save the following code as bat, and set the scheduled task

 

 
 

  
  
@ Echo off & color 1f & title automatic FTPMYSQL backup file
  
  
Mode con: cols = 60 lines = 10
  
  
Echo ===================================================== ==============================
  
  
Echo --
  
  
Echo --
  
  
Echo -- ---- ### automatic FTPMYSQL backup file ###----
  
  
Echo --
  
  
Echo --
  
  
Echo -- processing in progress. Do not close the program window manually,
  
  
Echo --
  
  
After echo -- is completed, the program will automatically close...
  
  
 
  
  
Set xtime = % time: = %
  
  
Set xdate = % date %
  
  
Set copy_path = % xdate :~ 0, 4%-% xdate :~ 5, 2%-% xdate :~ 8, 2% _ 07-01-01
  
  
 
  
  
Rem specifies the LOG storage path
  
  
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 -- start ------------------> % 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 -- end ------------------> % log_path %
  
  
Date/t> % log_path % & time/t> % log_path %
  
  
Echo --------------------> % log_path %
  
  
Echo --------------------------------------> % log_path %
 
 

 

After completing the above steps, you can start testing.

This article is from the "Bodhi Tree" blog, please be sure to keep this source http://andyptz.blog.51cto.com/5736197/965420