fishfreek

Well-Known Member
Jan 2, 2004
238
0
166
A client is trying to setup a mysql dump command to run during cron. THe problem is he is getting a permission denied message for the username he is trying to use on the dump.

The username and password is assocated with the database but it will only let us run the mysqldump command with the mysql user root and roots password.

It shoudl not do this since the username thats assocated with the database has full permissions.
 

mickalo

Well-Known Member
Apr 16, 2002
782
5
318
N.W. Iowa
Originally posted by fishfreek
A client is trying to setup a mysql dump command to run during cron. THe problem is he is getting a permission denied message for the username he is trying to use on the dump.

The username and password is assocated with the database but it will only let us run the mysqldump command with the mysql user root and roots password.

It shoudl not do this since the username thats assocated with the database has full permissions.
Can you post the command line sytnax your using or how your trying to execute the mysqldump via the cronjob ?

Mickalo
 

fishfreek

Well-Known Member
Jan 2, 2004
238
0
166
mysqldump --user=username --password=password --no-create-info --tab=test/ --where='category NOT IN (9235, 7641, 7849, 10259, 10260, 11041, 6145, 6652, 10137, 10126, 10127, 10128, 10129, 10130, 10131, 10132, 10133, 10134, 10135, 10136, 10139, 10140, 10141, 10142, 10143, 10144, 10145, 10146, 10147, 10148, 10149, 10150, 10151, 10152, 10153, 10137, 10901)' --fields-enclosed-by='"' --fields-terminated-by=';' username_database tablename
Obviously the correct username and password is substituted for those in the commandline and the correct username_database name and tablename is substituted in the commandline.

It works if its --user=root --password=(rootpassword) If I make it anything else it bombs out. I cant even use the main username and password assocated with that account. So im fairly certin the syntax is correct else it woudl not work with even the root username and password.
 

mickalo

Well-Known Member
Apr 16, 2002
782
5
318
N.W. Iowa
have you tried using the --defaults-file=/home/username/.my.cnf instead of the --username and --password options?

Create a .my.cnf configuration file with the account's DB access info, then try it.

I've done this many of our accounts that want auto nightly backups, using mysqldump and works fine, as long as they have full privileges to the database that's being accessed.

Mickalo
 

fishfreek

Well-Known Member
Jan 2, 2004
238
0
166
Ok i think i found the format. Soemething like

user=username
password=password

Anyway I put that in a .my.cnf file in the home directory of the user and ran the command and once again I get

mysqldump: Got error: 1045: Access denied for user: '[email protected]' (Using password: YES) when trying to connect
Why would only the root mysql user be able to do a database dump?
 

mickalo

Well-Known Member
Apr 16, 2002
782
5
318
N.W. Iowa
If the account db privileges are set up correctly, they should be able to utilize the mysqldump.

A typical .my.cnf looks something like this:
Code:
[client]
user        = username
password = password
host        = localhost
what MySQL version are you running, and is this a standard install or custom installation ??

Mickalo
 

fishfreek

Well-Known Member
Jan 2, 2004
238
0
166
Its a standard install as best I know. Its a server matrix server that we just got setup a few days ago.

Mysql version 4.0.18-standard

Per cpanel the user im trying has full permissions. Even the main account username doesnt work.

For example lets say the account's username is abc123 and its cpanel password zxcvb and the database user thats setup in cpanel is abc123_xyz987 with password asdfg.

WHen i try to use

--user=abc123_xyz987 --password=asdfg

OR

--user=abc123 --password=zxcvb

I get the mysqldump error. Not sure if this maters but this account was moved using one of the move scripts.
 

mickalo

Well-Known Member
Apr 16, 2002
782
5
318
N.W. Iowa
Well what I would do is login as root, then verify that this account has the correct db privileges setup for the database they are trying to "dump" or create the correct privileges then do a flush-privileges to make sure the changes took affect...

Mickalo