automating mysqldump on windows

Hi i have the following mysqldump running in a .bat file on a windows server.

mysqldump --user user --password=password db >C:\mysqlbackups\output.sql

however i want to run this daily and keep all the dumps so what i want to do is instead of naming the output output.sql i want to name it "todaydate" + output.sql

but i have no idea how to script this on windows

can any one help

thanks in advance
LVL 3
andrew67Asked:
Who is Participating?
 
Aleksandar BradarićConnect With a Mentor Software DeveloperCommented:
Strange... Works perfectly for me :( Here's a test case:
---
E:\test>dir C:\Mysqlbackups
 Volume in drive C is One
 Volume Serial Number is 7C35-049A

 Directory of C:\Mysqlbackups

11/10/2007  13:43    <DIR>          .
11/10/2007  13:43    <DIR>          ..
               0 File(s)              0 bytes
               2 Dir(s)   4,320,915,456 bytes free

E:\test>test2.bat

E:\test>echo on

E:\test>FOR /F "TOKENS=1* DELIMS=/ " %A IN ('DATE/T') DO SET CDATE=%B

E:\test>SET CDATE=10/2007

E:\test>FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %A IN ('DATE/T') DO SET dd=%A

E:\test>SET dd=11

E:\test>FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %A IN ('echo 10/2007 ') DO SET mm=%A

E:\test>SET mm=10

E:\test>FOR /F "TOKENS=1,2 DELIMS=/ " %A IN ('echo 10/2007 ') DO SET yyyy=%B

E:\test>SET yyyy=2007

E:\test>SET date=20071011

E:\test>mkdir C:\mysqlbackups\20071011

E:\test>dir C:\Mysqlbackups
 Volume in drive C is One
 Volume Serial Number is 7C35-049A

 Directory of C:\Mysqlbackups

11/10/2007  13:44    <DIR>          .
11/10/2007  13:44    <DIR>          ..
11/10/2007  13:44    <DIR>          20071011
               0 File(s)              0 bytes
               3 Dir(s)   4,320,915,456 bytes free
---

Could you go to command line, call the batch file and post the results here? Quite the same as I've done by posting my test. Thanks.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
What you need is to enter the date variable into that output filename. See here for an example: http://www.tech-recipes.com/computer_programming_tips956.html

Let me know if you have any more problems...
0
 
andrew67Author Commented:
hi thanks for that.  i know this mite sound really stupid but im using it like this to start with

echo on
@REM Seamonkey's quick date batch (MMDDYYYY format)
@REM Setups %date variable
@REM First parses month, day, and year into mm , dd, yyyy formats and then combines to be MMDDYYYY

FOR /F "TOKENS=1* DELIMS= " %%A IN ('DATE/T') DO SET CDATE=%%B
FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %%A IN ('DATE/T') DO SET mm=%%B
FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %%A IN ('echo %CDATE%') DO SET dd=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('echo %CDATE%') DO SET yyyy=%%B
SET date=%mm%%dd%%yyyy%
mkdir C:\mysqlbackups\date

and its greate it creates a directory but names it data not "currentdate" can you tell me where im going wrong

thanks
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Aleksandar BradarićSoftware DeveloperCommented:
Try:
---
mkdir C:\mysqlbackups\%date%
---
0
 
andrew67Author Commented:
tried that it creates a dir but calls it "10on"

then in the mysqldump script that runs straight after i have the path to put the file as

 C:\mysqlbackups\%date%\dump.sql

but it doesnt create it

thanks
0
 
Aleksandar BradarićSoftware DeveloperCommented:
You might have a different date format on your computer. Could you tell me what is it (e.g. 2007/10/11 or 11.10.2007 etc.)?
0
 
andrew67Author Commented:
i know this might sound really stupid but how would i find out?
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Go to command prompt and type:
---
date /t
---
0
 
andrew67Author Commented:
comes back "11/10/2007"
0
 
Aleksandar BradarićSoftware DeveloperCommented:
OK. Here's your script then:
---
echo on
FOR /F "TOKENS=1* DELIMS=/ " %%A IN ('DATE/T') DO SET CDATE=%%B
FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %%A IN ('DATE/T') DO SET dd=%%A
FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %%A IN ('echo %CDATE%') DO SET mm=%%A
FOR /F "TOKENS=1,2 DELIMS=/ " %%A IN ('echo %CDATE%') DO SET yyyy=%%B
SET date=%yyyy%%mm%%dd%
mkdir C:\mysqlbackups\%date%
---
0
 
andrew67Author Commented:
sorry about this but its now creating the dir "on.10" and still i presume fining an error because the script ends then, and doesnt run the mysqldump command


thanks

andrew
0
 
andrew67Author Commented:
C:\>mysqlbackup.bat

C:\>echo on

C:\>FOR /F "TOKENS=1* DELIMS= " %A IN ('DATE/T') DO SET CDATE=%B

C:\>SET CDATE=

C:\>FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %A IN ('DATE/T') DO SET mm=%B

C:\>SET mm=10

C:\>FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %A IN ('echo ') DO SET dd=%B

C:\>SET dd=

C:\>FOR /F "TOKENS=2,3 DELIMS=/ " %A IN ('echo ') DO SET yyyy=%B

C:\>SET yyyy=on.

C:\>SET date=on.10

C:\>mkdir C:\mysqlbackups\on.10

C:\>mysqldump --user paineurope --password=rocky packerforbes  1>C:\mysqlbackups
\on.10
Access is denied.

C:\>
0
 
andrew67Author Commented:
ok ive sorted out the name creation

it needed the / after DELIMS mine didnt have it  "DELIMS=/"

but im still getting access is denied

andrew
0
 
andrew67Author Commented:
sorted thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.