Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to create a dump of a locally installed MySQL database on my laptop.

I am aware of the command:

%>mysqldump -u root -p --opt [database name] 

but I am not able to execute it. I might be missing the obvious here.

  1. I open command line manager, which prompts the root password.
  2. Once I enter the password, I get straight into mysql>
  3. I am not able to exit the mysql> to get back into the shell.

Above command entered:

mysql> mysqldump -u -root -p --opt [database]

gives me the following:

-> 

I have also tried the following:

C:\> cd c:\program files\mysql\...\bin 
C:\program files\mysql\...\bin> mysqldump -u root -p [database name] > dump.sql 

Which only then reports back "Access denied".

I am not prompted the password. User is 'root'. I have full admin rights under Windows. I have tried this on two laptops with different DBs and passwords with the same result. I installed the MySQL database using the wizard.

I am lost. It seems I can only log straight into the database, but am not able to get into the shell (shell> ). Am I wrong just using the command line manager? Do I need workbench or something else?

share|improve this question
    
Looks like the username/password is wrong. Can you double check you can connect using those details via a gui like mysql workbench? –  Tom Squires Aug 8 '11 at 15:50
    
please don't repost. Just go back and edit your old question. –  techie007 Aug 10 '11 at 13:17
add comment

4 Answers

up vote 3 down vote accepted

Ths mysqldump executable is different from the mysql executable. You don't enter mysqldump within mysql, but just in a normal Windows command prompt, which you'll get by going to StartRun → Type "cmd", Enter.

mysqldump -u <username> -p <database> > dump.sql

Here, <database> is replaced with your database name. It will prompt you for your password and dump the contents to dump.sql. You can specify an absolute path for the dump file as well, for example C:\dump.sql

See this Howto for a few more details.

share|improve this answer
    
thank you, I was suspecting sth. like it. Which tool do I need, or where do I enter a 'Windows command prompt'? –  Mat Aug 8 '11 at 14:21
    
Just go to Start > Run, enter cmd. Then you can enter the mysqldump line. You might want to replace dump.sql with C:\Temp\dump.sql to specify where the file really goes (see akseli's answer below). –  slhck Aug 8 '11 at 14:22
    
perfect! thanks a million –  Mat Aug 8 '11 at 14:24
2  
By the way, once your DB's start growing in size, you should start looking into different compression methods to keep your dumps within reasonable sizes. –  akseli Aug 8 '11 at 14:28
    
@akseli Do you know if Windows comes with an easy way to pipe through GZip? –  slhck Aug 8 '11 at 14:28
show 5 more comments

Try dumping to some directory that's not under "Program Files". Only binaries and static data as supposed to go there, not user files.

share|improve this answer
    
Yes - this solves the problem. thank you. –  Mat Aug 10 '11 at 13:13
    
For clarification to future readers with the same problem what worked: In the windows cmd.exe C:\users[user name]> cd c:\program files\mysql...\bin C:\program files\mysql...\bin> mysqldump -u root -p [database name] > c:[folder name]\dump.sql –  Mat Aug 10 '11 at 13:15
add comment

It's been a while since I've had to use mysqldump, but I'm pretty sure that you have to define a destination file for the dump.

That is, try the following syntax:

mysqldump -u root -p --databases [database name] > C:\Temp\DBDump.sql

You don't need the --opt option, simply use either --databases [dbname] or --all-databases to dump.

Secondly, you want to make sure that you're dumping to a file, not simply on screen (unless this is what you're after).

Ok, so to compress your image you can follow the following guide posted to the MySQL 5.5 Reference Guide:

Posted by Mike Ng on May 16 2005 5:40pm [Delete] [Edit]

Following Lon B helpful post:

You can pipe it to gzip to compress in windows. I didn't think it would work on windows, but apparently it does.

@ECHO Beginning backup of %dbname%...

%mysqldir%\bin\mysqldump -B %dbname% -u %dbuser% | gzip> %bkupdir%\dbBkup_%dbname%_%yy%%mm%%dd%.sql.gz

Of course,you need gng gzip in your path or directory

You can read it at the bottom of the MySQL 5.5 Reference Guide on MYSQLDUMP

Sorry, i changed the post; I copied the wrong one ... Sorry!

share|improve this answer
add comment

It could also be that you are not windows command manager as an admin, and therefore, you do not have write access to program files.

share|improve this answer
    
I have administrator rights under Windows, and I never even get the password prompt. User is 'root'. The problem must be sth. else. I have re-installed everything on a second machine with the exact same result. –  Mat Aug 10 '11 at 12:46
    
Found solution: Specify directory for dump that is not under 'program files' In the windows cmd.exe: C:\users[user name]> cd c:\program files\mysql...\bin C:\program files\mysql...\bin> mysqldump -u root -p [database name] > c:[folder name]\dump.sql –  Mat Aug 10 '11 at 13:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.