Thursday, April 21, 2011

How to create mysqldump batch file?


I tried to create a batch file that can backup all my databases. My systems details:

OS: Server -> Windows Server 2003, Testing/local machine -> Windows Vista
Databases: MySql 5.XX

Batch file:

@echo off
START C:\wamp\bin\mysql\mysql5.1.33\bin\mysqldump.exe --opt -h localhost -uroot -psecret testdb | gzip > dump.sql");

In my code, i try to dump the "testdb" database into dumb.sql file. Can I set the name into such like : "dbname_date_time.sql"??

I answered my own question. Whoever that has the privilleges, please close/delete this question. Thanks in advance :)

From stackoverflow
  • since you have mysql in your system, you can use the date/time functions that comes with mysql

    execute it using the mysql client and use a for loop to get the result. Then pluck the return result into your dump file variable name

  • I will assume you have no problem with mysqldump command.

    So, to manipulate date/time in bat file, you can use combinations of followings

    • echo %date% // which gives me "木 2010/01/14" in my pc

    • echo %time% // which gives me 4:02:15.28

    • for /f "tokens=1-5 delims=/" %%d in ("%date%") do echo %%e %%f // gives me "01 14"

    • for /f "tokens=1-5 delims=:" %%d in ("%time%") do echo %%d %%e // gives me "4 02" H/M

    • Build file name and rename your dump.sql to new filename


Post a Comment