[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1970
  • Last Modified:

MSDOS Bat/Exe SQL Dump scheduler

Hi - I am looking to develop a small batch file that is scheduled (cron) to create a SQL dump of MYSQL databases at regular intervals e.g. every hour. These files are part pf the daily backup. Furthermore, a developer will then copy the SQL files via FTP at certain periods during the week for contingency. The scheduler will delete the contents of the folder weekly. What I am looking for is an application to perform this task. It can be an exe or bat file. The application must be able to run under the system account. What I was thinking was a little bat that got the latest time as a variable and then used this variable with the dump command? Any suggestions and help appreciated.
2 Solutions
Too bad this had to be under Windows, under Linux I have a script that does a regular mysqldump, compresses it, and then FTPs it to the server of your choice.

That being said, if the files are only being pulled off the server intermittently, you are better off turning on the binary log. The binary log will store every query that affects data, and you can then just FTP away the latest log files and the occasional dump o the data (flushing the logs when yuo do). This will be much more space efficient and result in faster transfers over FTP, otherwise you copy the full table every time, which can be redundant if only two rows have changed.

First of all deleteing files older than x hours is a pain in windows, there just arent and cmd line tools for this, so I

recommend the following:
Download this:

Unzip, make sure the files are in the path of the system.

Then there is an excellent tool in there called find, I usually keep i in c:\gnu, so I do like this:
c:\gnu\usr\local\wbin\find c:\oracle\database\archive -ctime +10 -exec rm -f {}

For rm command to work the c:\gnu\usr\local\wbin, needs to be in your path.
The above command will result in all files older than 10 days will be deleted from the c:\oracle\database\archive dir.

This is so useful whne it comes to transaction logs, at least in Oracle.
We use hot backups each night, and in this way I can keep as many days of archive logs as I want, currently we keep 20 days

online on disk.

As for ftp batching please consider this:

Dumping MySQL, consult the MySQL documentation:

With theese tools you shouldn't have any trouble at all creating a batch file which you could schedule with Task Manager to

run as often as you like.

to make things easier for you, you might want to install cygwin in your computer and make use of cron ! ... then if Squeebee is kind enough he could send you his script :)
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

If he gets cron and cygwin online I would be happy to post it.
You can use VBS to accomplish this. The following is a single VBS file that I named "mysql_dump.vbs". First, it looks for files older than 2 days and deletes them. Then it uses "mysqldump" to dump a specific table to a specified location. Finally, it uses an FTP script to FTP the file to another server.

You can separate this script into three different files to do just one thing -- like dump databases. On windows you can use the Task Scheduler to run this or on NT machines use the DOS AT command. Personally, I use "AutoTask" -- it can run things based on conditions (file exists).

Here is my VBS script file:
strPath = "C:\temp\mysql\"      'path to location of files to delete
intDays = 2                  'number of days to keep

Function FileCreateDate(filename)
   Dim fname
   Set fname = objFSO.GetFile(filespec)
   ShowFileInfo = fname.DateCreated
End Function

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
For Each objItem In objFolder.Files
   if objItem.DateLastModified < date() - intDays then
      objFSO.DeleteFile(strPath & objitem.Name)
   END if
Next 'objItem

Set objItem = Nothing
Set objFolder = Nothing
Set objFSO = Nothing

strDBPath = "C:\MySQL\"            'Location of MySQL
strDumpPath = "C:\Temp\MySQL\"      'Location to dump .sql file
strUser = "root"            'User for MySQL
strPass = "friday13"            'Password for MySQL
strDB = "dbMain"            'Database to dump

'NOTE: Win9x uses "command" -- NT uses "cmd"
'If running on Windows 9x ADD apostrophe to beginning of next line
strCMD = "cmd /c " & strDBPath & "bin\mysqldump -u" & strUser & " -p" & strPass & " --opt " & strDB & " > " & strDumpPath & strDB & ".sql"

'If running on Windows 9x REMOVE apostrophe from beginning of next line
'strCMD = "command /c " & strDBPath & "bin\mysqldump -u" & strUser & " -p" & strPass & " --opt " & strDB & " > " & strDumpPath & strDB & ".sql"

'Run OS command
set wshShell = createobject("wscript.shell")
rc = wshShell.run (strCMD,0,true)

'strPath = "c:\temp\"                        'location to create script file (I am using strPath from above)
strServer = ""                  'FTP server ip
strFTPUser = "iamadmin"                        'FTP user name
strFTPPass = "letmein"                        'FTP password
strPutLoc = "mysql"                        'location on ftp host to store file
strPutFile = strDumpPath & strDB & ".sql"      'file to upload

'Create FTP script
Set objFile1=Server.CreateObject("Scripting.FileSystemObject")
Set scrfil=objFile1.OpenTextFile(strPath & strPutFile & ".scr"), 8, TRUE)
scrfil.Write("open " & strServer & vbcrlf)
scrfil.Write(strFTPUser & vbcrlf)
scrfil.Write(strFTPPass & vbcrlf)
scrfil.Write("hash" & vbcrlf)
scrfil.Write("lcd " & strPutLoc & vbcrlf)
scrfil.Write("put " & strPutFile & vbcrlf)
scrfil.Write("bye" & vbcrlf)
'Run OS command FTP using the FTP script
'NOTE: Win9x uses "command" -- NT uses "cmd"
set wshShell = createobject("wscript.shell")
'rc = wshShell.run ("command /c ftp -s:" & strPath & strPutFile & ".scr",0,true)
rc = wshShell.run ("cmd /c ftp -s:" & strPath & strPutFile & ".scr",0,true)

Set f2 = objFile1.GetFile(strPath & strPutFile & ".scr")

Hope this helps,
txtphileAuthor Commented:
All interesting comments ... Charles - nice suggestion.

I ended up using this - http://www.kalab.com/freeware/cron/cron.htm to schedule as a service - very nice little app.


This to create - please note the command line zip command ... sweet.


@ echo off
for /F "tokens=1-4 delims=:., " %%a in ('time/T') do set TIME=%%a%%b%%c

for /F "tokens=1-4 delims=/- " %%A in ('date/T') do set DATE=%%B%%C%%D


cd mysql\bin

md d:\SQLDB\%DATE%\

mysqldump --user dbsys --password=psswrd dbname > d:\SQLDB\%DATE%\dbname-%DATE%-%TIME%.sql



this to clean up weekly

@ echo off

xcopy D:\SQLDB\%DATE%\*.* D:\Scratch\%DATE%\ /a /e /k /y



xcopy D:\Scratch\*.* D:\SQLDB /a /e /k /y

RD D:\scratch\ /S /Q

md d:\SQLDB\; d:\SQLZIP\; d:\scratch\


And then with cron on a remote machine I have nightly backup off site using

wget - http://unxutils.sourceforge.net/ that downloads the zip files via http (I prefer this to ftp …) - one line of code. Bing Bang bosh. Hourly zipped SQl dumps to your desktop.

If anybody and improve the method I have used please improve. Everybody gave suggestions and although I will not be using them I think Charles solution is the most comprehensive and an interesting alternative to mine - points to brain on brass.

Thanks all!!
txtphileAuthor Commented:
hhhhhhmmmm actually heskyttberg offered nice little alternative doing it another way but just as valid ... split points!! Squeebee ... what is your script ... interested to see all possibilities.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now