Page 1 of 1
MS-DOS batch scripting.. :)
Posted: Tue Oct 04, 2005 4:11 pm
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
Posted: Tue Oct 04, 2005 7:25 pm
by feyd
Code: Select all
[feyd@office]>echo %date%
Tue 10/04/2005
[feyd@office]>echo %time%
20:25:32.82
Posted: Tue Oct 04, 2005 7:57 pm
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
Posted: Wed Oct 05, 2005 9:38 am
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
Posted: Wed Oct 05, 2005 10:00 am
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.
Posted: Wed Oct 05, 2005 10:02 am
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.
Posted: Wed Oct 05, 2005 10:06 am
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.
Posted: Wed Oct 05, 2005 10:11 am
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
Posted: Wed Oct 05, 2005 10:33 am
by feyd
although I haven't tested it,
Code: Select all
mysqldump your options > "%date% %time%.sql"
may work..
Posted: Wed Oct 05, 2005 10:49 am
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
Posted: Wed Oct 05, 2005 12:28 pm
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.
[SOLVED] MS-Dos Batch Scripting..
Posted: Wed Oct 05, 2005 2:05 pm
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
Posted: Wed Oct 05, 2005 2:48 pm
by pilau
Well is it working?
[UN-Solved] MS-DOS Scripting : Getting a Timestamp
Posted: Wed Oct 05, 2005 3:04 pm
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
[Re-Solved] :) MS-Dos Scripting
Posted: Wed Oct 05, 2005 3:18 pm
by waskelton4
ok.. i had the month variable in the minutes var spot so it was goofing up..
the string was
now is
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