Page 1 of 2

help backinp up DB

Posted: Thu Sep 21, 2006 5:30 am
by anime-me
I need help backing up my database. I know how to do it on linux servers but I'm currently on a windows server and can't remmber the exact commands i need to dump my database.

i think the commands go something like this:

Code: Select all

c:\mysql\bin\mysqldump  -uusername -ppassword -ddatabase name > backup.sql
but when ever i enter that it doesn't work.
my DB is too large to backup up via phpmyadmin and even if i do it table by table its still too large cuz phpbb_post_text is something around 60MB.

thanx in advance.

Posted: Thu Sep 21, 2006 10:44 am
by anime-me
anyone?

Posted: Thu Sep 21, 2006 11:01 am
by GreyDogz
Create a batch file on the windows server :(

Code: Select all

@ECHO OFF
for /f "tokens=1-4 delims=/ " %%a in ('date/t') do (
set dw=%%a
set mm=%%b
set dd=%%c
set yy=%%d
)

SET bkupdir=C:\path\to\where\you\want\backups
SET mysqldir=D:\path\to\mysql
SET dbname=this_is_the_name_of_my_database
SET dbuser=this_is_my_user_name
SET dbpass=this_is_my_password

@ECHO Beginning backup of %dbname%...

%mysqldir%\bin\mysqldump -B %dbname% -u %dbuser% -p%dbpass% > %bkupdir%\dbBkup_%dbname%_%yy%%mm%%dd%.sql
@ECHO Done! New File: dbBkup_%dbname%_%yy%%mm%%dd%.sql
pause
You could use the task manager to run it for you but delete the last 2 lines if you do or pipe it to a log file.

Posted: Thu Sep 21, 2006 8:06 pm
by anime-me
Thanks GreyDogz :)

im sorry for being such a noob at this but could u please tell me how to create a batch file on a windows server? This is my first time on a windows server and definatley my last :(

Posted: Fri Sep 22, 2006 2:27 am
by GreyDogz
Is this your server or a hosting provider?

To create a batch file just copy the above text or any text into notepad, wordpad or my favorite notepad++ and save it as a filename.bat

Upload it to your windows server and give the correct permissions for you to run it.

To manually execute it just type the filename at the command prompt. If all works. You can add it to the task manager.

BTW: I hate windows servers too.

Posted: Sun Sep 24, 2006 5:47 pm
by anime-me
it's a host but i have root access.

i tried ur code and it didn't exactly work. it said:
'C:\mysql' is not reognized as an internal or external command

and then i find a backup file but its 0KB.
the path to mysql dir is C:\mysql...
BTW: I hate windows servers too.

i don't think their's anyone out there that likes em :P

Posted: Mon Sep 25, 2006 10:09 am
by GreyDogz
Lets manually find the BU script Do you have this file:
C:\mysql\bin\mysqldump

Secondly, make sure you don't have a space in the command:
%mysqldir%\bin\mysqldump
I'm asking this because I would have thought the error you'd get would state C:\mysql\bin\mysqldump is not recognized as a command.

Posted: Mon Sep 25, 2006 5:29 pm
by anime-me
Lets manually find the BU script Do you have this file:
C:\mysql\bin\mysqldump


yes, i do have that file. and i made sure there were no spaces in the batch file but still got the same prob.

btw, i really apreciate the help greydogz :)

Posted: Mon Sep 25, 2006 11:11 pm
by GreyDogz
What version of mysql are you running? I just want to verify we are using the correct command and options.

Are you able to run the B/U manually at the cmd prompt?

Posted: Tue Sep 26, 2006 5:28 pm
by anime-me
GreyDogz wrote: What version of mysql are you running? I just want to verify we are using the correct command and options.

Are you able to run the B/U manually at the cmd prompt?


im really not sure what version im using. i think its 3.X.X or something.
and yes i can run the B\U manually at the cmd prompt.

a friend of mine once did it for me using a 2 lines of code. it went something like:

Code: Select all

C:\mysql\bin\mysqldump -uusername -ppassword -ddatabasename > backup.sql
it won't work atm. maybe because its wrong?

Posted: Wed Sep 27, 2006 10:06 am
by GreyDogz
Try adding a space between -u username and -d databasename put no space between -ppassword

Posted: Wed Sep 27, 2006 5:43 pm
by anime-me
well i tried it ur way and it didn't work. said that i was not authorised. and then i tried it like this:

Code: Select all

-uusername -ppassword -d databasename
it worked but when i checked the file size it was 35KB. im sure that my database is much larger.

*will never get another windows server*

Posted: Wed Sep 27, 2006 11:22 pm
by GreyDogz
Try it without the "-d" but with just the DBname. I think the "-d" is telling it to just dump the structure and no data.

Posted: Thu Sep 28, 2006 7:46 am
by amanti27
how do you find out what version you are using? does it make a difference?

Posted: Thu Sep 28, 2006 7:08 pm
by anime-me
thanx a million greydogz. i finally backed up my database :)
i really apreciate ur help.