自动备份mysql数据库(兼容windows,linux)

自动将mysql服务器的各数据库分别备份压缩并上传到制定FTP服务器，先编辑脚本更改数据库连接配置,留空FTP信息则只备份不上传。

@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

Windows版 mysql_backup.bat编辑后，命令行测试，测试成功后加入到计划任务中.(windows版只备份，不压缩备份文件)

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

Linux版 mysql_backup.sh编辑后，命令行测试，测试成功后加入到crontab.