mysql自动备份脚本(bat批处理与shell脚本)

@ECHO OFF
REM name: mysql_backup.bat
REM description: mysql backup and upload
REM version: v1.0.0
REM author: Shi Zhuolin
REM E-mail: shizhuolin@hotmail.com shizhuolin@qq.com
REM the directory for story your backup file
SET backup_dir="D:mysql_backupfiles"
REM your mysql login information
SET db_host=
SET db_user=
SET db_passwd=
REM your ftp login information
SET ftp_host=
SET ftp_user=
SET ftp_passwd=
SET ftp_path=/
REM mysql, mysqldump, winrar and some other bin's path
SET MYSQL="C:Program FilesMySQLMySQL Server 5.5binmysql.exe"
SET MYSQLDUMP="C:Program FilesMySQLMySQL Server 5.5binmysqldump.exe"
SET WINRAR="C:Program FilesWinRARRar.exe"
SET FTPCMD=%backup_dir%ftpcmd.txt
SET FTPLOG=%backup_dir%upload.log
SET STRDATE=%date:~10,4%-%date:~4,2%-%date:~7,2%
SET STRTIME=%time:~0,2%%time:~3,2%%time:~6,2%
FOR /F "tokens=*" %%a IN ('"systeminfo | find /i "zh-cn""') DO (
SET STRDATE=%date:~0,10%
SET STRTIME=%time:~0,2%%time:~3,2%%time:~6,2%
)
SET PREFIX=%STRDATE%-%STRTIME%-
IF "%ftp_host%" NEQ "" (
ECHO open %ftp_host%>%FTPCMD%
ECHO user %ftp_user% %ftp_passwd%>>%FTPCMD%
ECHO prompt off>>%FTPCMD%
ECHO binary>>%FTPCMD%
ECHO cd %ftp_path%>>%FTPCMD%
)
FOR /F "tokens=*" %%a IN ('"%MYSQL% -h%db_host% -u%db_user% -p%db_passwd% -e"show databases""') DO (
IF "%%a" NEQ "information_schema" (
IF "%%a" NEQ "performance_schema" (
IF "%%a" NEQ "Database" (
SET database=%%a
SET filesql=%backup_dir%%PREFIX%%%a.sql
SET filerar=%backup_dir%%PREFIX%%%a.rar
CALL :backup
)
)
)
)
IF "%ftp_host%" NEQ "" (
ECHO bye>>%FTPCMD%
ftp -n<%FTPCMD%>>%FTPLOG%
)
GOTO :EOF
:backup
%MYSQLDUMP% -h%db_host% -u%db_user% -p%db_passwd% %database%>%filesql%
IF exist %WINRAR% (
%WINRAR% a -k -df -ep %filerar% %filesql%
IF "%ftp_host%" NEQ "" (
ECHO put %filerar%>>%FTPCMD%
)
) ELSE (
IF "%ftp_host%" NEQ "" (
ECHO put %filesql%>>%FTPCMD%
)
)
GOTO :EOF
 

#!/bin/sh
# name: mysql_backup.sh
# description: mysql backup and upload
# version: v1.0.0
# author: Shi Zhuolin
# E-mail: shizhuolin@hotmail.com shizhuolin@qq.com
# ----------------------------------------------------------------------
# the directory for story your backup file.
backup_dir="/home/mysql_bak"
# your mysql login information
# db_user is mysql username
# db_passwd is mysql password
# db_host is mysql host
# -----------------------------
db_host=""
db_user=""
db_passwd=""
# -----------------------------
# your ftp login information
# ftp_host is ftp server host address
# ftp_user is ftp server username
# ftp_passwd is ftp server password
# ftp_path is upload path for ftp root
ftp_host=""
ftp_user=""
ftp_passwd=""
ftp_path="/"
# -----------------------------
# mysql, mysqldump and some other bin's path
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
gzip="/bin/gzip"
FTP="/usr/bin/ftp"
FTPCMD="$backup_dir/ftpcmd.txt"
FTPLOG="$backup_dir/upload.log"
# date format for backup file (yyyy-mm-dd-HH-nn-ss)
STRDATE=$(date +"%Y-%m-%d")
STRTIME=$(date +"%H%M%S")
PREFIX="$STRDATE-$STRTIME-"
if [ "$ftp_host" != "" ]; then
echo "open $ftp_host">$FTPCMD
echo "user $ftp_user $ftp_passwd">>$FTPCMD
echo "prompt off">>$FTPCMD
echo "binary">>$FTPCMD
echo "cd $ftp_path">>$FTPCMD
fi
# get all databases
all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"
for db in $all_db; do
if [ "$db" != "information_schema" -a "$db" != "performance_schema" ]; then
echo "backup $db to $backup_dir/$PREFIX$db.gz"
$MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/$PREFIX$db.gz"
if [ "$ftp_host" != "" ]; then
echo "put $backup_dir/$PREFIX$db.gz $PREFIX$db.gz">>$FTPCMD
fi
fi
done
if [ "$ftp_host" != "" ]; then
echo "bye">>$FTPCMD
$FTP -n<$FTPCMD>>$FTPLOG
fi
exit 0;