自动将mysql服务器的各数据库分别备份压缩并上传到指定FTP服务器，先编辑脚本更改数据库连接配置,留空FTP信息则只备份不上传。windws用bat脚本,linux用bash脚本,请分别将此脚本加载到计划任务中定时执行.@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 :EOFWindows版 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.