MS-DOS batch scripting.. :)

XML, Perl, Python, and other languages can be discussed here, even if it isn't PHP (We might forgive you).

Moderator: General Moderators

Post Reply
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

MS-DOS batch scripting.. :)

Post by waskelton4 »

I'm working on getting a batch file that will run and create backups for all of my mysql databases.

I've gotten the file to run.. and backup the databases on a scheduled basis..

my question is....

Is there an easy way in my batchfile to name the .sql backup file so its name contains the date and time so it isn't overwritten everynight?

Thanks

ws
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

[feyd@office]>echo %date%
Tue 10/04/2005

[feyd@office]>echo %time%
20:25:32.82
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

You could write a php script to do your backup, and then put the following in your batch file:

Code: Select all

@ECHO OFF
C:/php/php-cgi.exe -f C:/scripts/sync/sync.php
pause
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post by waskelton4 »

Thanks for the replies!

feyd, I just tried to drop "%date%" into my outfile portion of the mysqldump command and the filename is being truncated after the day of the week due to the space that "%date%" returns. I'd really prefer to have just a timestamp also because the rest of the date has forward slashes in it.

jshpro2, my mysql box doens't have php installed on it and I'd like to keep it that way if possible. I really would like to use mysqldump to run the backup just so I can keep it simple.

Any Ideas on how to invoke mysqldump from a remote server? CURL maybe?

Thanks for the help!

Will
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

you dont need php installed on your mysql box to be able to back up the databases. where you would put localhost in the php backup script, put the ip and port of your mysql server and run it on any box with php installed.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

jayshields wrote:you dont need php installed on your mysql box to be able to back up the databases. where you would put localhost in the php backup script, put the ip and port of your mysql server and run it on any box with php installed.
that only works if the MySQL box accepts connections from remote locations.. most will not.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

feyd wrote:
jayshields wrote:you dont need php installed on your mysql box to be able to back up the databases. where you would put localhost in the php backup script, put the ip and port of your mysql server and run it on any box with php installed.
that only works if the MySQL box accepts connections from remote locations.. most will not.
oh, ok. ive only ever had 2 mysql databases hosted remotely and they both allowed external connections, im just telling from experience.
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post by waskelton4 »

feyd wrote:
jayshields wrote:you dont need php installed on your mysql box to be able to back up the databases. where you would put localhost in the php backup script, put the ip and port of your mysql server and run it on any box with php installed.
that only works if the MySQL box accepts connections from remote locations.. most will not.
It does accept from remote locations.. from my php/web server

I'd like to have the entire backup process contained entirely on the mysql server though. I've tried to get MySQL Administrator to run a scheduled backup but it isn't working for me.

I decided to create a .bat file that will run via the task scheduler everynight that can run multiple mysqldump statements. It works great only the backup files are being overwritten everynight. I'd like to keep a week or so of backup files on hand so I need to have a way to make the filenames and/or paths unique. I could install php on the mysql server and write a php script to exec() the mysqldump statement but I'd rather not install anything else on this DB server.

mysqldump can't be run through a remote connection can it?? or a query?? it is an .exe after all

or .. if anyone has any other simple bulletproof suggestions for a backup solution.. i'm all ears.. :)

Thanks again..
Will
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

although I haven't tested it,

Code: Select all

mysqldump your options > "%date% %time%.sql"
may work..
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post by waskelton4 »

feyd wrote:although I haven't tested it,

Code: Select all

mysqldump your options > "%date% %time%.sql"
may work..
It doesn't for me...

batch file..

Code: Select all

mysqldump --opt --databases dbname --user=root --password=********* -c > e:\mysqlbackups\%DATE% %time%.sql
here is the text from my cmd window...

Code: Select all

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

E:\>cd mysqlbackups

E:\mysqlBackups>test

E:\mysqlBackups>mysqldump --opt --databases dbname --user=root --password=***** -c  10/05/2005 10:43:12.19.sql 1>e:\mysqlbackups\Wed
mysqldump: Got error: 1102: Incorrect database name '10/05/2005' when selecting the database
kinda odd..
part of the date and the time are moved up behind the -c and the rest of the date goes where it is supposed to..

ideas?
ws
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

I'm bad at DOS batch, but try setting %date% and %time% in a variable, then doing
mysqldump ... e:\mysqlbackups\$variable
or however.
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

[SOLVED] MS-Dos Batch Scripting..

Post by waskelton4 »

ok.. so here is my solution.

I did some seraching on the web and probably bastardized the code pretty good.. but it works.. I spent a little time trying to get it to convert the PM in to 12 more hours added to the hours variable but then decided that 12 hr time was just fine..

here is the code.. don't ask me why it does what it does.. I was guessing at the syntax half the time :)

Code: Select all

:--------backupdbs.bat----------
@ECHO OFF

for /f "tokens=2" %%i in ('date /t') do set thedate=%%i

set mm=%thedate:~0,2%
set dd=%thedate:~3,2%
set yyyy=%thedate:~6,4%

for /f "tokens=1" %%j in ('time /t') do set thetime=%%j
for /f "tokens=2" %%k in ('time /t') do set ampm=%%k

set hh=%thetime:~0,2%
set min=%thetime:~3,2%
set ap=%thetime:~6,2%

if "%ap%" == "PM" set hh=(%hh% + 12)

mysqldump --opt --databases dbname --user=root --password=* -c > e:\mysqlbackups\dbname_%yyyy%%mm%%dd%%hh%%mm%%ampm%.sql
That creates a filename:

Code: Select all

e:\mysqlbackups\dbname_200510050204PM.sql
Comments are welcome and encouraged..

Will
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

Well is it working?
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

[UN-Solved] MS-DOS Scripting : Getting a Timestamp

Post by waskelton4 »

pilau wrote:Well is it working?
sorta..

for some reason the date and time keep coming up the same..

It worked great the first time :) now it keeps writing over the same files with the old timestamp..

DOH..

ws
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

[Re-Solved] :) MS-Dos Scripting

Post by waskelton4 »

ok.. i had the month variable in the minutes var spot so it was goofing up..

the string was

Code: Select all

%yyyy%%mm%%dd%%hh%%mm%%ampm%
now is

Code: Select all

%yyyy%%mm%%dd%%hh%%min%%ampm%
careless mistake..

it works now..

and i chaged it up a little to create a directory for each time the backup script is run.

Code: Select all

:--------backupdbs.bat----------
@ECHO OFF
set dir = ""
for /f "tokens=2" %%i in ('date /t') do set thedate=%%i
set mm=%thedate:~0,2%
set dd=%thedate:~3,2%
set yyyy=%thedate:~6,4%
for /f "tokens=1" %%j in ('time /t') do set thetime=%%j
for /f "tokens=2" %%k in ('time /t') do set ampm=%%k
set hh=%thetime:~0,2%
set min=%thetime:~3,2%
set ap=%thetime:~6,2%

set dir=%yyyy%.%mm%.%dd%.%hh%.%min%.%ampm%
mkdir e:\mysqlbackups\%dir%

@ECHO ON
mysqldump --opt --databases dbname1 --user=root --password=* -c > e:\mysqlbackups\%dir%\dbname1.sql 

mysqldump --opt --databases dbname2 --user=root --password=* -c > e:\mysqlbackups\%dir%\dbname2.sql

mysqldump --opt --databases dbname3 --user=root --password=* -c > e:\mysqlbackups\%dir%\dbname3.sql

mysqldump --opt --databases dbname4 --user=root --password=* -c > e:\mysqlbackups\%dir%\dbname4.sql

mysqldump --opt --databases dbname5 --user=root --password=* -c > e:\mysqlbackups\%dir%\dbname5.sql

mysqldump --opt --databases dbname6 --user=root --password=* -c > e:\mysqlbackups\%dir%\dbname6.sql

exit
Comments and Suggestions welcome.. again.. :)

Will
Post Reply