2

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?

random
  • 14,638
  • 9
  • 54
  • 58
Topocalma
  • 113
  • 2
  • 2
  • 6

4 Answers4

4

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 Start → Run → 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.

slhck
  • 223,558
  • 70
  • 607
  • 592
  • thank you, I was suspecting sth. like it. Which tool do I need, or where do I enter a 'Windows command prompt'? – Topocalma Aug 08 '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 08 '11 at 14:22
  • 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 08 '11 at 14:28
  • @akseli Do you know if Windows comes with an easy way to pipe through GZip? – slhck Aug 08 '11 at 14:28
  • I'll add the info to my answer. It's quite lengthy. – akseli Aug 08 '11 at 14:30
  • The C;\windows\system32\cmd.exe gives me the following error: 'mysqldump is not recognized as an internal or external command, operable program or batch file. The full line: C:\Users\Matthias>mysqldump -u root -p mysql > dump.sql ('the db is called mysql unfortuanetly :-) – Topocalma Aug 08 '11 at 14:36
  • You might need to enter the following *first*, before the `mysqldump` command: `cd C:\path\to\mysql\bin`, or wherever you installed MySQL to). @Mat – slhck Aug 08 '11 at 14:37
  • cd path seems to work, I am at the bin. sqldump command as per above triggers 'access is denied' response. I tried entering the password '-ppassword but without success. I assume I should be prompted the root password? The only user that is defined it 'root', the server is 'localhost'. Thank you for any ideas. – Topocalma Aug 08 '11 at 15:09
  • If your user is root, then only specify `-u root -p`. Witout a password. It will prompt for your password after that. Since you opened a new question anyway, you might want to close and accept this one, otherwise there's no point opening a new one. @Mat – slhck Aug 08 '11 at 16:10
2

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

Zds
  • 2,449
  • 18
  • 15
  • 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 – Topocalma Aug 10 '11 at 13:15
1

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!

akseli
  • 4,123
  • 19
  • 25
  • This answer is incorrect. `mysqldump` outputs to stdout. It can be (and usually is) redirected to a file, but there is no requirement to do so. – Allen Jun 02 '14 at 05:31
1

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.

soandos
  • 24,206
  • 28
  • 102
  • 134
  • 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. – Topocalma 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 – Topocalma Aug 10 '11 at 13:28