sudo mysqldump : Permission denied

I am trying to run

sudo mysqldump

with user account but I get : Permission denied message

If I switch to sudo su then it works.

The question is why sudo gives permission denied? should i give some other privileges on my user account?

mysqldump on a production server does not work with sudo. Please try this:

$ sudo su
# mysqldump database> database.sql

Try to save dump into user home directory

e.g. mysqldump -u {db_user} -p --databases {db_name} > /home/{user}/my.dump.sql

mysqldump will need a password for the mysql user root. If you don't supply that password it won't work, sudo or no sudo.

mysqldump can look at your .my.cnf file to get the password, and it'll get that from the user runing the mysqldump command. So, if the Linux root user has a .my.cnf configured with a password in it, then yes, sudo will help. However, if the mysql root user has a password and it's not configured in any .my.cnf's then sudo won't help.

You could add the mysql root user and password to your own user's .my.cnf and then you don't need sudo either, but that's a security risk.

As the other answers point out as well, you might need sudo to write the output somewhere that root (Linux, not mysql) owns.

Lastly, you don't tell us which user is executing the script, if it's in root's crontab (for example), then sudo will not be necessary, although you may still need a .my.cnf to provide the password.

If your mysql root user has no password set, then the only issue is writing the output, in which case sudo is required if you don't run the script as root.

If you run the script as root (for example, as a script in /etc/cron.daily) then you should not use sudo within the script, irrespective of all the above

credit

This did work for me:

mysqldump -u {db_user} -p --databases {db_name} > /home/$USER/my.dump.sql