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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4912
  • Last Modified:

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
0
andrew67
Asked:
andrew67
  • 8
  • 6
1 Solution
 
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
 
Aleksandar BradarićSoftware DeveloperCommented:
Try:
---
mkdir C:\mysqlbackups\%date%
---
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
Aleksandar Bradarić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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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