Shi Zhuolin's Blog - linux
type: channel
link: https://www.shizhuolin.com/tag/linux/
items: 自动备份mysql数据库(兼容windows,linux)

自动备份mysql数据库(兼容windows,linux)
type: item
date: 2014-02-24T08:35:00+00:00
description: 自动将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 编辑后，命令行测试，测…