Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a MySql database that I want to backup to a file daily. (Which I will later download for off-server backup).

I have set up the following command in my crontab (ran daily):

mysqldump -–opt -Q -h [myhost] -u myusername -–password=***** ebooklibrary > /fullpath.../_db_backups/openelibrary.sql

The database name is ebooklibrary.

I am constantly getting this error:

mysqldump: Got error: 1045: Access denied for user 'myusername'@'97.74.144.139' (using password: NO) when trying to connect

How can I fix this, or do it properly, so that mysqldump will run daily?

Thanks.


Using the shell, the following command worked:

 mysql -h myhost -u myusername -p"*****" ebooklibrary
share|improve this question
    
Giving passwords in the command line is insecure, it is more secure to add it to my.cnf [client] secion. See dev.mysql.com/doc/refman/5.5/en/password-security-user.html –  Stone Feb 19 at 12:41

3 Answers 3

up vote 3 down vote accepted

I believe the matter is with how you specify the password. Try

mysqldump –opt -Q -h [myhost] -u myusername –p"*****" ebooklibrary > /fullpath.../_db_backups/openelibrary.sql
share|improve this answer
    
tnx, I will check it this solves it –  Am. Feb 4 '10 at 5:23
    
hi, still getting the same problem. is there something i am missing? –  Am. Feb 5 '10 at 6:45
    
What command line can you use to connect to the database with using the mysql client? –  Brian De Smet Feb 7 '10 at 4:54
    
I'v updated the question with the command –  Am. Feb 7 '10 at 12:24
    
this got on me on tracks, tried the dump manually, and all it took was some options change. –  Am. Feb 8 '10 at 16:09

When specifying a password on the command line, you need to use

--password="xxx"

rather than

-password="xxx"

Note the double dash on the first command.

EDIT: Do you know if the DB server is the same server as the web server? If so, have you tried omitting the host? With that thought, have you also tried --host=127.0.0.1 (Just tried that on my machine - it seems very anal about the correct host name)

share|improve this answer
    
copy&paste error, it was double-dashed in the cron. –  Am. Feb 7 '10 at 22:59
    
tnx for the help, fixed. was the options after all. –  Am. Feb 8 '10 at 16:09

There are 2 probable reasons for this error.

1) Your password has special characters like !%# etc., if this is the case you can escape the character by placing a back slash '\' before it.

2) This user truely does not have access permission from the IP address mentioned in the error. The solution is to either provide rights to that user from the specified IP or do not attempt to use that host for mysql access.

You can also use the following command if you have access to the server CLI (if you specify no host, mysql will attempt to connect to localhost):

mysqldump -uUSER -pPASS ebooklibrary > /fullpath.../_db_backups/openelibrary.sql

or you can use the following syntax if you are executing the command remotely.

mysqldump -h IP -uUSER -pPASS ebooklibrary > /fullpath.../_db_backups/openelibrary.sql

You should ascertain that you have the necessary rights to perform the dump as specified on the mysqldump page.

share|improve this answer
    
At least explain what you think the OP's error was and why you think your version should work (and is better than the already accepted answer). –  Sven Feb 22 '13 at 11:37

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.