25

How do I do this properly. I'm trying to name the sql file that is being produced by mysqldump into the current date and time. I've already some research in this site and found a code in here: How to get current datetime on Windows command line, in a suitable format for using in a filename?

Tried to mixed it up with my current code and I came up with this one. The file is named into the current date and time but its only a 1kb file and does not produce a .sql file. It is supposed to be a 7 kb sql file.

@For /f "tokens=2-4 delims=/ " %%a in ('date /t') do @(set mydate=%%c-%%a-%%b)
@For /f "tokens=1-2 delims=/:" %%a in ('time /t') do @(set mytime=%%a%%b)    

@echo mydate= %mydate%
@echo mytime= %mytime%

mysqldump -u root -p --add-drop-table --create-options --password= onstor >c:\%mydate%_%mytime%.sql

UPDATE I don't think there's a problem with the mysqldump command since it works well when I do it this way. The code below just uses the date as its filename. 

@For /F "tokens=2,3,4 delims=/ " %%A in ('Date /t') do @( 
    Set Month=%%A
    Set Day=%%B
    Set Year=%%C   
)    

@echo DAY = %Day%
@echo Month = %Month%
@echo Year = %Year%    

mysqldump -u root --add-drop-table --create-options --password= onstor >c:\%Day%-%Month%-%Year%.sql

Please help, thanks.

Community
  • 1
  • 1
user225269
  • 10,007
  • 65
  • 169
  • 241

3 Answers3

48

On Linux, simply put $(date +%Y-%m-%d-%H.%M.%S) to show date and time in the file name, so it looks like:

mysqldump -u <user> -p <database> | bzip2 -c > <backup>$(date +%Y-%m-%d-%H.%M.%S).sql.bz2

(This command also compresses the file using bzip2)

Tracy Logan
  • 60
  • 4
chrowe
  • 712
  • 6
  • 6
4

I think the syntax of your mysqldump command is wrong;

mysqldump -u root -p --add-drop-table --create-options --password= onstor

You use both -p and --pasword=, you should only use one option. And there is a space before the password.

Just try to run the mysqldump command on the commandline to see error messages. Alternatively add 2>&1 at the end of the command in the batchfile. Then you would also see error messages in the output file.

mysqldump -u root --add-drop-table --create-options --password=onstor >c:\%mydate%_%mytime%.sql 2>&1
wimh
  • 14,481
  • 5
  • 42
  • 89
  • I don't think there's a problem with the mysqldump command, please see my edit – user225269 Dec 27 '10 at 11:42
  • @user225269: the mysqldump command is different in your second example. But anyway, can you show us what the 1kb file contains? – wimh Dec 27 '10 at 13:35
1

For those who want to get this to work in crontab, in that case it's a bit different:

30 01 * * * /opt/bitnami/mysql/bin/mysqldump -u root --password=MySecretPass database_name | gzip >  /path/backup/`date "+\%d-\%m-\%y_\%H:\%M"`.gz
Ale DC
  • 577
  • 6
  • 11