Simple scheduled MySQL database backups with FTP offsite hosting

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:

@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^&gt;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. &gt;&gt; %log%
echo ====================================================================== &gt;&gt; %log%
echo *** STARTING MYSQL Backup ******************************************** &gt;&gt; %log%
echo ====================================================================== &gt;&gt; %log%
echo Run On: %ldt% &gt;&gt; %log%
echo This Script: %0 &gt;&gt; %log%
echo Backing up databases: %databaseNames% &gt;&gt; %log%
echo Backups presently retained for: %retaindays% days &gt;&gt; %log%
echo Backups Folder: %outputDirectory% &gt;&gt; %log%

echo ---------------------------------------------------------------------- &gt;&gt; %log%
echo Running MySQLDump.. &gt;&gt; %log%
mysqldump.exe --extended-insert=%extendedInserts% -u%username% -p%password% -h%hostName% --databases %databaseNames% &gt; "%finalFilename%" 2&gt;&gt; %log%
if %errorlevel%==0 echo Successfull. &gt;&gt; %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 &gt; %finalFilename%

echo ---------------------------------------------------------------------- &gt;&gt; %log%
:: Zip it

if %use7zip%==true (
  echo Zipping up .sql file &gt;&gt; %log%
  "%ZipExe%" a -tzip "%outputDirectory%\%outputFilePrefix%_%ldt%.zip" "%outputDirectory%\%outputFilePrefix%_%ldt%.sql" &gt;&gt; %log%
  echo Deleting original .sql file &gt;&gt; %log%
  del "%finalFilename%" 2&gt;&gt; %log%
  if %errorlevel%==0 echo Successfull. &gt;&gt; %log%	
  )
  
echo ---------------------------------------------------------------------- &gt;&gt; %log%
:: Now do any archive pruning beyond keep date
echo Completing any archive pruning  &gt;&gt; %log%
for /f %%A in ('dir "%outputDirectory%" /a-d-s-h /b ^| find /v /c ""') do set cnt=%%A
echo File count = %cnt% &gt;&gt; %log%
if %cnt% LEQ %guarenteedNumberBackups% (
  echo File count is lower than minimum of %guarenteedNumberBackups%. Skipping pruning. &gt;&gt; %log%
  goto :skipPrune
  )
echo Any files older than %retaindays% days: &gt;&gt; %log%
Forfiles -p "%outputDirectory%" -s -m *.* -d -%retaindays% -c "cmd /c echo @file is old" &gt;&gt; %log%
echo Deleting any old files... &gt;&gt; %log%
Forfiles -p "%outputDirectory%" -s -m *.* -d -%retaindays% -c "cmd /c del @file"  &gt;&gt; %log%
if %errorlevel%==0 echo Successfull. &gt;&gt; %log%

:skipPrune
echo ---------------------------------------------------------------------- &gt;&gt; %log%
:: Now do FTP if selected
if NOT %useFTP%==true GOTO skip_FTP
echo Completing FTP operations.. &gt;&gt; %log%
echo Ensuring number of FTP files is %FtpKeepFiles% &gt;&gt; %log%
:: First get file list
echo user %FtpUsername%&gt;ftpup.dat
echo %FtpPassword%&gt;&gt;ftpup.dat
echo dir %FtpDirectory%&gt;&gt;ftpup.dat
echo quit&gt;&gt;ftpup.dat
set ftpcmd=ftp -n -s:ftpup.dat %FtpServerAddress%

::Now construct new ftpdel.dat to delete files + do so
echo user %FtpUsername%&gt;ftpdel.dat
echo %FtpPassword%&gt;&gt;ftpdel.dat
if NOT "%FtpDirectory%"=="" (
  echo cd %FtpDirectory%&gt;&gt;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!"&gt;&gt;ftpdel.dat
    )
  )

echo quit&gt;&gt;ftpdel.dat
ftp -n -s:ftpdel.dat %FtpServerAddress%  &gt;&gt; %log%

:: Now upload new backup file
echo user %FtpUsername%&gt;ftpup.dat
echo %FtpPassword%&gt;&gt;ftpup.dat
if NOT "%FtpDirectory%"=="" (
  echo cd %FtpDirectory%&gt;&gt;ftpup.dat
  )
echo binary&gt;&gt;ftpup.dat
if %use7zip%==true (
  echo put "%outputDirectory%\%outputFilePrefix%_%ldt%.zip"&gt;&gt;ftpup.dat
  ) else (
  echo put "%outputDirectory%\%outputFilePrefix%_%ldt%.sql"&gt;&gt;ftpup.dat
  )
echo quit&gt;&gt;ftpup.dat
ftp -n -s:ftpup.dat %FtpServerAddress% &gt;&gt; %log%
del ftpup.dat
del ftpdel.dat

:skip_FTP
echo ---------------------------------------------------------------------- &gt;&gt; %log%
echo Backup Finished. &gt;&gt; %log%
echo. &gt;&gt; %log%
echo. &gt;&gt; %log%
echo. &gt;&gt; %log%
echo MySql backup script completed.

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 MySql backup script completed.

Simple scheduled MySQL database backups with FTP offsite hosting

A simple, scheduled MySQL database backup method with zipped files, archive pruning and FTP/offsite upload

Process

Be the first to comment

Leave a Reply Cancel reply

Keep Focussed

Automate publishing to your personal NuGet repo

MantisBT WordPress Plugin

SFXInstaller

VisualSVN auto backup script

GIMP Plugin – Search and Replace in Layer Names

Simple scheduled MySQL database backups with FTP offsite hosting

CBMStudioSVN