A simple, scheduled MySQL database backup method with zipped files, archive pruning and FTP/offsite upload
After a fruitless search for free MySQL backup tools, the solution turned out to be surprising – using good old-fashioned batch scripting to surprising affect! This leverages the MySQLDump tool which produces .sql files as backups.
This simple script will backup selected databases and do the following:
- keep rolling versions (backups older than your specified number of days will be deleted.
- zip them to save on storage
- upload them to an FTP site
- keep an ongoing log of operations
Process
- Save the batch file below somewhere on your server and ammend the “USER VARIABLES HERE” section
- Create a shortcut pointing to this. Go into properties and set to “Run as Administrator.” You need this to leverage the FTP functions.
- Run the two commands below through an elevated command prompt to allow the ftp function to work:
- Set up a task-schedule to run the backup by pointing to the shortcut (not the batch file)
Script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 |
@echo off setlocal EnableDelayedExpansion :: MySQL Backup Script by stigzler 2019 :: Use windows task scheduler to make it automatic! :: ============================================================================================== :: USER VARIABLES HERE :: Be pedantic - every space + character counts! :: Variable specific notes: :: 'databaseNames' - include all db names for backup separated by a space. :: 'extendedInserts' - changing this to false produces more readable SQL files at the cost to filesize and time. :: 'hostName' - e.g. "localhost" or "mysite.org" :: 'retaindays' - how long to keep backup files for :: 'guarenteedNumberBackups' - ensurtes this number of backups are kept in folder (failsafe incase backup gen fails) :: 'FtpKeepFiles' - specifies how many backup files to keep on the ftp server (oldest deleted) set MySQLDumpDir="C:\Program Files\MySQL\MySQL Server 5.5\bin" set username=root set password=YourPassword set hostName=localhost set databaseNames=database1 database2 ultimateDatabase set outputFilePrefix=ServerMySQLBackup set outputDirectory=D:\MySQLBackups\All_Dbs_Auto_BUs set extendedInserts=True set retaindays=64 set /a guarenteedNumberBackups=32 :: Optional :: Zip Backup Files set use7zip=true set ZipExe=C:\Program Files\7-Zip\7z.exe :: Optional :: FTP file to remote site set useFTP=true set FtpUsername=FTPUserName set FtpPassword=97Dkoi22r set FtpDirectory=backups set FtpServerAddress=yourftpsite.uwebweb.com set /a FtpKeepFiles=4 :: ============================================================================================== :: OPERATIVE VARIABLES :: Date-Time: for /F "usebackq tokens=1,2 delims==" %%i in (`wmic os get LocalDateTime /VALUE 2^>NUL`) do if '.%%i.'=='.LocalDateTime.' set ldt=%%j set ldt=%ldt:~0,4%-%ldt:~4,2%-%ldt:~6,2%_%ldt:~8,2%-%ldt:~10,2%-%ldt:~12,6% set finalFilename=%outputDirectory%\%outputFilePrefix%_%ldt%.sql set log=%outputDirectory%\log.log :: METHOD cd %MySQLDumpDir% echo Starting MySql Backup script echo. >> %log% echo ====================================================================== >> %log% echo *** STARTING MYSQL Backup ******************************************** >> %log% echo ====================================================================== >> %log% echo Run On: %ldt% >> %log% echo This Script: %0 >> %log% echo Backing up databases: %databaseNames% >> %log% echo Backups presently retained for: %retaindays% days >> %log% echo Backups Folder: %outputDirectory% >> %log% echo ---------------------------------------------------------------------- >> %log% echo Running MySQLDump.. >> %log% mysqldump.exe --extended-insert=%extendedInserts% -u%username% -p%password% -h%hostName% --databases %databaseNames% > "%finalFilename%" 2>> %log% if %errorlevel%==0 echo Successfull. >> %log% :: De-comment the below for an alternaitve to backup ALL databases in this domain: :: mysqldump.exe --extended-insert=%extendedInserts% -u%username% -p%password% -h%hostName% --all-databases > %finalFilename% echo ---------------------------------------------------------------------- >> %log% :: Zip it if %use7zip%==true ( echo Zipping up .sql file >> %log% "%ZipExe%" a -tzip "%outputDirectory%\%outputFilePrefix%_%ldt%.zip" "%outputDirectory%\%outputFilePrefix%_%ldt%.sql" >> %log% echo Deleting original .sql file >> %log% del "%finalFilename%" 2>> %log% if %errorlevel%==0 echo Successfull. >> %log% ) echo ---------------------------------------------------------------------- >> %log% :: Now do any archive pruning beyond keep date echo Completing any archive pruning >> %log% for /f %%A in ('dir "%outputDirectory%" /a-d-s-h /b ^| find /v /c ""') do set cnt=%%A echo File count = %cnt% >> %log% if %cnt% LEQ %guarenteedNumberBackups% ( echo File count is lower than minimum of %guarenteedNumberBackups%. Skipping pruning. >> %log% goto :skipPrune ) echo Any files older than %retaindays% days: >> %log% Forfiles -p "%outputDirectory%" -s -m *.* -d -%retaindays% -c "cmd /c echo @file is old" >> %log% echo Deleting any old files... >> %log% Forfiles -p "%outputDirectory%" -s -m *.* -d -%retaindays% -c "cmd /c del @file" >> %log% if %errorlevel%==0 echo Successfull. >> %log% :skipPrune echo ---------------------------------------------------------------------- >> %log% :: Now do FTP if selected if NOT %useFTP%==true GOTO skip_FTP echo Completing FTP operations.. >> %log% echo Ensuring number of FTP files is %FtpKeepFiles% >> %log% :: First get file list echo user %FtpUsername%>ftpup.dat echo %FtpPassword%>>ftpup.dat echo dir %FtpDirectory%>>ftpup.dat echo quit>>ftpup.dat set ftpcmd=ftp -n -s:ftpup.dat %FtpServerAddress% ::Now construct new ftpdel.dat to delete files + do so echo user %FtpUsername%>ftpdel.dat echo %FtpPassword%>>ftpdel.dat if NOT "%FtpDirectory%"=="" ( echo cd %FtpDirectory%>>ftpdel.dat ) :: First get total number of files: set /a TotalFiles=0 for /f "tokens=2 delims=:" %%a in ('!ftpcmd!') do ( set /a TotalFiles+=1 ) set /a LinesToDelete= %TotalFiles% - %FtpKeepFiles% :: Now write files to be deleted set /a fileCount=0 for /f "tokens=2 delims=:" %%a in ('!ftpcmd!') do ( set line=%%a set line=!line:~3! set /a fileCount+=1 if !fileCount! leq %LinesToDelete% ( echo delete "!line!">>ftpdel.dat ) ) echo quit>>ftpdel.dat ftp -n -s:ftpdel.dat %FtpServerAddress% >> %log% :: Now upload new backup file echo user %FtpUsername%>ftpup.dat echo %FtpPassword%>>ftpup.dat if NOT "%FtpDirectory%"=="" ( echo cd %FtpDirectory%>>ftpup.dat ) echo binary>>ftpup.dat if %use7zip%==true ( echo put "%outputDirectory%\%outputFilePrefix%_%ldt%.zip">>ftpup.dat ) else ( echo put "%outputDirectory%\%outputFilePrefix%_%ldt%.sql">>ftpup.dat ) echo quit>>ftpup.dat ftp -n -s:ftpup.dat %FtpServerAddress% >> %log% del ftpup.dat del ftpdel.dat :skip_FTP echo ---------------------------------------------------------------------- >> %log% echo Backup Finished. >> %log% echo. >> %log% echo. >> %log% echo. >> %log% echo MySql backup script completed. |
Leave a Reply