@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_backup\files"
 
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 Files\MySQL\MySQL Server 5.5\bin\mysql.exe"
SET MYSQLDUMP="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe"
SET WINRAR="C:\Program Files\WinRAR\Rar.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;