Solved

MSDOS Bat/Exe SQL Dump scheduler

Posted on 2003-11-06
7
1,954 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:txtphile
7 Comments
 
LVL 17

Expert Comment

by:Squeebee
ID: 9693947
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.
0
 
LVL 8

Assisted Solution

by:heskyttberg
heskyttberg earned 200 total points
ID: 9696081
Hi!

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:
http://unxutils.sourceforge.net/

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:
www.ncftp.com

Dumping MySQL, consult the MySQL documentation:
http://www.mysql.com/doc/en/mysqldump.html

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.

Regards
/Hasse
0
 
LVL 7

Expert Comment

by:jconde
ID: 9725252
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 :)
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Expert Comment

by:Squeebee
ID: 9726487
If he gets cron and cygwin online I would be happy to post it.
0
 
LVL 2

Accepted Solution

by:
nevahj earned 300 total points
ID: 9728562
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:
'--------------------------------------------------------------------
'-------------------------------------------------------------------
'DELETE OLD FILES
'-------------------------------------------------------------------
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

'-------------------------------------------------------------------
'DUMP MYSQL FILES
'-------------------------------------------------------------------
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)

'-------------------------------------------------------------------
'FTP MYSQL FILES
'-------------------------------------------------------------------
'strPath = "c:\temp\"                        'location to create script file (I am using strPath from above)
strServer = "192.168.1.1"                  '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)
scrfil.Close
 
'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")
f2.Delete          
'--------------------------------------------------------------------

Hope this helps,
Charles
 
0
 

Author Comment

by:txtphile
ID: 9730082
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

d:

cd mysql\bin


md d:\SQLDB\%DATE%\

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

WZZIP.EXE  -a D:\SQLZIP\%DATE%.zip D:\SQLDB\%DATE%\*.sql

___________________________

this to clean up weekly

@ echo off

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

RD D:\SQLDB\ /S /Q

RD D:\SQLZIP\ /S /Q

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!!
0
 

Author Comment

by:txtphile
ID: 9730152
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.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now