MS-DOS batch scripting.. :)
Moderator: General Moderators
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
MS-DOS batch scripting.. :)
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
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
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
[feyd@office]>echo %date%
Tue 10/04/2005
[feyd@office]>echo %time%
20:25:32.82You 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
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
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
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
that only works if the MySQL box accepts connections from remote locations.. most will not.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.
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
oh, ok. ive only ever had 2 mysql databases hosted remotely and they both allowed external connections, im just telling from experience.feyd wrote:that only works if the MySQL box accepts connections from remote locations.. most will not.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.
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
It does accept from remote locations.. from my php/web serverfeyd wrote:that only works if the MySQL box accepts connections from remote locations.. most will not.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.
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
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
although I haven't tested it,may work..
Code: Select all
mysqldump your options > "%date% %time%.sql"-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
It doesn't for me...feyd wrote:although I haven't tested it,may work..Code: Select all
mysqldump your options > "%date% %time%.sql"
batch file..
Code: Select all
mysqldump --opt --databases dbname --user=root --password=********* -c > e:\mysqlbackups\%DATE% %time%.sqlCode: 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 databasepart 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
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
[SOLVED] MS-Dos Batch Scripting..
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
That creates a filename:
Comments are welcome and encouraged..
Will
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%.sqlCode: Select all
e:\mysqlbackups\dbname_200510050204PM.sqlWill
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
[UN-Solved] MS-DOS Scripting : Getting a Timestamp
sorta..pilau wrote:Well is it working?
for some reason the date and time keep coming up the same..
It worked great the first time
DOH..
ws
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
[Re-Solved] :) MS-Dos Scripting
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.
Comments and Suggestions welcome.. again.. 
Will
the string was
Code: Select all
%yyyy%%mm%%dd%%hh%%mm%%ampm%Code: Select all
%yyyy%%mm%%dd%%hh%%min%%ampm%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
exitWill