Page 1 of 1
[SOLVED]Another Database backup question
Posted: Mon Nov 07, 2005 5:24 am
by Skittlewidth
Let me begin by quoting an old post:
feyd wrote:most hosts have backup systems, which you can often schedule to keep a copy in your document tree..
As far as I can see, FastHosts

(for some reason one of the post popular hosts in the UK, even though in our experience they're completely useless) don't provide any way via their account management panel to backup databases hosted on their servers. Instead they recommend using DBTools' DBManager for all your database needs. Neither do they give any kind of command line access to the database server (again, as far as I can see). So this brings me to my latest challenge:
I've just been given the task of finding a way to automatically back up all the databases we have with Fasthosts on a weekly basis. (I currently back them up one by one manually.) They all seem to be on the same db server, so that's one bit of good news I suppose.
I need to back them up to a machine in our office or one of the fasthost web servers since backing them on the db server we have no access to would be pointless.
So far all the scripts I have seen that would do what I need rely on cron jobs (logically). The only other way I think I can do it is to purchase DBManager Enterprise edition which I think allows me to access the backup tool via a windows command line and then I guess I just have to schedule a batch file to run using windows.
If I have shell access to one of the Fasthost web servers could I run a script from there that could back up the required databases on the database servers? If so how? I'm new to this kind of thing.
Any advice is helpful.... thanks
Posted: Mon Nov 07, 2005 5:39 am
by Chris Corbyn
Give your local machine's IP address access to mysql on the server (or just give all IP addresses access).
Then (fromm your own machine, with the same major version of mysql installed)....
Code: Select all
command-line # mysqldump some_database -h yourhost.com -u your_username -p > backup_of_database-2005-11-07.sql
It should create you a nice file with the export in it. You can import it again by:
Code: Select all
command-line # mysql -h yourhost.com -u your_username -p
password:
mysql > use database_name;
database name selected.
mysql > \. /path/to/local/dump/backup_of_database-2005-11-07.sql
.... (Queries whizzing around.....)
mysql >
Posted: Mon Nov 07, 2005 5:54 am
by Skittlewidth
Brilliant! And so simple, thanks.
I think I know where to go from here. If not, I'll be back

Posted: Mon Nov 07, 2005 6:40 am
by Skittlewidth
Ah... problem.
Is there a way I can automatically supply the password in the batchfile so that it doesn't open up the prompt and ask me to enter it manually?
Also is there anyway of automatically appending the date to the filename so it doesn't just overwrite the previous file each time?
Posted: Mon Nov 07, 2005 7:09 am
by Chris Corbyn
Skittlewidth wrote:Ah... problem.
Is there a way I can automatically supply the password in the batchfile so that it doesn't open up the prompt and ask me to enter it manually?
Also is there anyway of automatically appending the date to the filename so it doesn't just overwrite the previous file each time?
Is your local machine linux or windows? I can do the date thing with linux.... not sure how in windows.
Anyway, to supply the password in batch file (ohh.... windows then

):
Code: Select all
mysqldump some_database -h yourhost.com -u your_username --password=your_password > backup_of_database-2005-11-07.sql
Posted: Mon Nov 07, 2005 7:14 am
by n00b Saibot
d11wtq wrote:I can do the date thing with linux.... not sure how in windows.
se here
viewtopic.php?t=39044
Posted: Mon Nov 07, 2005 7:54 am
by Skittlewidth
Thanks guys
Still trying to get the script in n00b Saibot's recommended post to work but I'm sure I'll spot my mistake in a sec!
Posted: Mon Nov 07, 2005 8:30 am
by Skittlewidth
Nope, another problem:
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
I've been trying to debug this in the command window and I immediately get "%%i was unexpected at this time" after the first "for" line. I'm guessing this is setting up a loop where i is the counter but I don't know enough to know why this is causing an error. When I run the entire script in a batch file (with my database settings, and only one database at the moment) I don't get a new folder, I get a file called "dbname~6" with now extension and just the first 5 or so lines of the mysql dump.
EDIT: Solved the problem, instead of trying to manuipulate the date into the "thedate" variable and extracting the date, month, year from there I targetted %date directly, so my working script is as follows:
Code: Select all
cd c:\dbases\
@ECHO ON
set dir = ""
set mm=%date:~0,2%
set dd=%date:~3,2%
set yyyy=%date:~6,4%
set dir=%dd%%mm%%yyyy%
mkdir c:\dbases\%dir%
@ECHO ON
mysqldump dbname --host localhost --user=username --password=password > c:\dbases\%dir%\dbname.sql
pause