Howto: mysqldump cron on godaddy

Question

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

Giving passwords in the command line is insecure, it is more secure to add it to my.cnf [client] secion. See https://dev.mysql.com/doc/refman/5.5/en/password-security-user.html — Stone, Feb 19 '14 at 12:41

score 3 · Accepted Answer · answered Feb 04 '10 at 05:02

3

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

answered Feb 04 '10 at 05:02

Brian De Smet

1,139
7
10

tnx, I will check it this solves it – Am. Feb 04 '10 at 05:23
hi, still getting the same problem. is there something i am missing? – Am. Feb 05 '10 at 06:45
What command line can you use to connect to the database with using the mysql client? – Brian De Smet Feb 07 '10 at 04:54
I'v updated the question with the command – Am. Feb 07 '10 at 12:24
this got on me on tracks, tried the dump manually, and all it took was some options change. – Am. Feb 08 '10 at 16:09

Ben Pilbrow · Answer 2 · 2010-02-07T23:54:07.553

1

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)

edited Feb 07 '10 at 23:54

answered Feb 07 '10 at 13:11

Ben Pilbrow

11,995
5
35
57

copy&paste error, it was double-dashed in the cron. – Am. Feb 07 '10 at 22:59
tnx for the help, fixed. was the options after all. – Am. Feb 08 '10 at 16:09

score -1 · Answer 3 · edited Feb 19 '14 at 11:42

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.

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

Howto: mysqldump cron on godaddy

3 Answers3