11

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?

Kapa
  • 121

8 Answers8

19

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

$ sudo su
# mysqldump database> database.sql
Bryan Zavala
  • 191
  • 1
  • 2
5

Try to save dump into user home directory

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

Dima Kurtash
  • 51
  • 1
  • 2
3

This did work for me: 

mysqldump -u {db_user} -p --databases {db_name} > /home/$USER/my.dump.sql
Pablo Bianchi
  • 17,371
Pish Peter
  • 31
  • 1
3

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

Raja G
  • 105,327
  • 107
  • 262
  • 331
1

Add write permissions of the folder you want to dump the sql file in:

sudo chmod 755 folder_url

and run:

mysqldump -u user -p db_name > db_dump.sql

William
  • 111
0

Dump to your home directory was the better answer here for me. If, for example, you can't sudo and you get permission denied, it's actually denying you permission to write the dump to your current directory. Specifying your home directory will solve it.

mysqldump -u me -p --databases my_db > $HOME/mysqldump.sql
dylzee
  • 101
0

Make sure you command destination path mentioned.

For eg: sudo mysql -u YourUsername -p DatabaseName > ~/Path/Where/you/Want/The/Dump/FiletoBe/Kept/dumpfile.sql

user591987
  • 1
0

mysqldump requires username of the current user else it will take root as default. When you do aisi su, you automatically provide root access. Try this with your username in place of user. mysqldump -u user -p Enter password when prompt.

collinx
  • 1