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
  • 15,201
Topocalma
  • 113

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
  • 235,242
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,469
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,191
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,600
  • 29
  • 105
  • 136