asked on

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.

Squeebee

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.

SOLUTION

heskyttberg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jconde

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 :)

Squeebee

If he gets cron and cygwin online I would be happy to post it.

ASKER CERTIFIED SOLUTION

nevahj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

txtphile

ASKER

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

txtphile

ASKER

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.