dump Mysql database to sql file that contains creation date/time

digital1stein
digital1stein used Ask the Experts™
on
Hi,

I'm trying to dump a mysql database to a sql file that contains the creation date and time.
For example: test_20110807_0700.sql

I can already dump a database to sql, but the export file always has the same name:
mysqldump –user=username –password=1234 –databases your_database  > example.sql

Any idea of how to approach this?
Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Are you running it on Linux/Unix or on Windows?

On a *nix type system you can use the date command to generate a date/time and use it in your filename:

mysqldump –user=username –password=1234 –databases your_database  > example_`date +%Y%m%d`.sql

On Windows I'm not sure how to do it.

Author

Commented:
Sorry, forgot to tell: I'm using Windows.

Commented:
I checked it on a Windows virtual machine here and it turns out it can be done in a similar way on Windows using the %date% shell variable. It contains the current date:

C:\Users\Ruben>echo %date%
Sun 08/07/2011

You can parse it like this to turn it into a yyyymmdd format using substrings:
C:\Users\Ruben>echo %date:~10,4%%date:~4,2%%date:~7,2%
20110807

So using that in your mysqldump command you could do something like this:

mysqldump –user=username –password=1234 –databases your_database  > example_%date:~10,4%%date:~4,2%%date:~7,2%.sql
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
I put the date in the name like:

mysqldump –user=username –password=1234 –databases your_database  > 20110806example.sql

Author

Commented:
@Rubene,

Works perfectly! Thanks.
Now the only thing I'm missing is the time. Aside of the date I would also need to put the time in the filename.
Do you know which parameters I should use then?
Commented:
Hi again,

You can do the same thing with the %time% variable. For instance:

C:\Users\Ruben>echo %time%
22:54:54.88

So using the same parsing on that to get the parts you're interested in, you could get the hhmm part like this for instance:

C:\Users\Ruben>echo %time:~0,2%%time:~3,2%
2258

Using that in the command gets you something like this:
mysqldump –user=username –password=1234 –databases your_database  > example_%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%.sql

Author

Commented:
Found it myself meantime:

mysqldump -u 'root' -p your_database > D:\example_%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%h%time:~3,2%.sql

gives something like:

example_20110807_22h58.sql

Thanks for putting me on the way, Rubene!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial