mySQL "access denied" error on simple mysqldump

murphaph

Dear all,
I'd appreciate any help with this. Trying to get a handle on mySQL here. Reading "Learning PHP & mySQL" and have hit a brick wall. The book is ok but sometimes a little vague for my liking. Here's the section giving me trouble:

8.3.2. The mysqldump Command

It's better to use the MySQL command-line tool for making complete database backups. The same tools you'll use to back up and restore can also be used to change platforms or move your database from one server to another; mysqldump creates a text file containing the SQL statements required to rebuild the database objects and insert the data. The mysqldump command is accessible from the command line and takes parameters for backing up a single table, a single database, or everything. The command's syntax is:

mysqldump -u user -p objects_to_backup



The default mode for mysqldump is to export to backup and then to standard output, which is usually the screen.
8.3.2.1. Backing up

We're going to show you the commands to back up a database called test from the shell prompt.

mysqldump -u root -p test > my_backup.sql


This tells mysqldump to log into the database as the root user with a password of barney, and to back up the test database. The output of the command is saved to a file called my_backup.sql with the help of the redirect character also known as the greater-than symbol (>).

Now, I have a database named test and when I try the above command I just get a 2 word error "access denied". It's mySQL 5.1 running on Windows 7. Here is the exact command which I enter in a DOS shell:

mysqldump -u root -p test > my_backup.sql

I have googled and found other ways to run it from the cmd line including filling in the password after the p flag but nothing seems to work. I don't really understand where mysqldump dumps to either? Do I not need to first create a folder/file called my_backup.sql, and if so, where should it be located? I also don't understand where this user "Barey" is coming from in the above example..it looks like the user is root to me? Appreciate any help/pointers folks.

mneylon

Can you login to MySQL from the command line?
If you can't then you won't be able to run the mysqldump from the command line either

.sql is just a filename - not a directory / folder

Webmonkey

Have you put a password on the database?

Try leaving out the p switch.

It dumps to the file you redirect output to, windows creates this file for you automatically in the current folder you in.

murphaph

Blacknight wrote: »

Can you login to MySQL from the command line?

Yes. With username root and my password, have no problems logging in to mySQL client.

murphaph

Webmonkey wrote: »

Have you put a password on the database?

Yes, root user has a password.

Webmonkey wrote: »

Try leaving out the p switch.

Even though there's a password in place?

Webmonkey wrote: »

It dumps to the file you redirect output to, windows creates this file for you automatically in the current folder you in.

Thanks

Is there a config for mysqldump? Could it be a problem in there?

Webmonkey

murphaph wrote: »

Yes. With username root and my password, have no problems logging in to mySQL client.

A long shot, but by any chance would it be to do with write permissions to where you are attempting to dump the file to? - try creating a file there. You may not have the user permissions.

murphaph

Webmonkey wrote: »

A long shot, but by any chance would it be to do with write permissions to where you are attempting to dump the file to? - try creating a file there. You may not have the user permissions.

I think it is a windows error myself, rather than an SQL one, but I can create new directories in the directory I'm trying to dump into.

murphaph

I found the solution. This guy actually did! Thanks lads.

Webmonkey

Yep just as thought ha.

L8rdude

Don't know if this will help, but I use it. Have you tried putting the password in qoutes?

e.g.
mysqldump -u root -p"password" test > d:\test_backup.sql

techguy

L8rdude wrote: »

Don't know if this will help, but I use it. Have you tried putting the password in qoutes?

e.g.
mysqldump -u root -p"password" test > d:\test_backup.sql

What he said.. maybe leave out the quotes though and try having no space between -p and the actual password.

OP, from the command you are using MySQL is assuming test to be the password because its directly after the -p switch.

Webmonkey

I think he fixed it

techguy

Ha ok.. Read all posts but seemed to have missed that one!

murphaph

techguy wrote: »

OP, from the command you are using MySQL is assuming test to be the password because its directly after the -p switch.

Funnily enough it doesn't. It prompts for the password when the command is entered like that (mySQL 5.1) and works fine.

Webmonkey

murphaph wrote: »

Funnily enough it doesn't. It prompts for the password when the command is entered like that (mySQL 5.1) and works fine.

When there are spaces it could be different as both parts of the password may be interpreted as a different command argument.