1

I am trying to write mysql backup script for cron. Using Ubuntu server.

Current code is as below:

#!/bin/sh
mysqldump -u root --all-databases > /root/all_databases_backup-`date +%A`.sql

Do I need to add 'sudo' before mysqldump, like this:

#!/bin/sh
sudo mysqldump -u root --all-databases > /root/all_databases_backup-`date +%A`.sql

The reason I ask is because when I run mysql/mysqldump in shell, it always says Permission denied. I need to sudo to go thru.

So does the command in script also require sudo ?

Thanks.

tombull89
  • 2,964
  • 8
  • 41
  • 52
gilzero
  • 439
  • 4
  • 9
  • 20

3 Answers3

4

The mysqldump command does not require sudo, but saving the dump file under /root folder requires root privileges. So, you need to run your script using sudo.

You can also note that running the following command:

sudo mysqldump -u root --all-databases > /root/all_databases_backup-`date +%A`.sql

will give your permission denied error. This is because the output redirection is still not running as root, but only the dump part.

Khaled
  • 36,533
  • 8
  • 72
  • 99
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 comments.

EightBitTony
  • 9,311
  • 1
  • 34
  • 46
  • (1) I login as a normal user (not root), (2) I create the script as $ sudo nano /etc/cron.daily/mysqlbackup . (3) and chmod a+x to it. (4) And I also add root pass to /root/my.con. => in this case, do I still need sudo in that script? – gilzero May 08 '12 at 13:01
  • The script is running as root. So no, you don't need sudo. sudo allows you to run commands as if you were another user, usually root (but not always). If you are root, you don't need sudo. /etc/cron.daily/scripts run as root. – EightBitTony May 08 '12 at 13:09
1

In your case you need sudo to write to the /root/ folder. If you can dump the database to any other folder with correct permissions (or change permissions on /root - not recommended), you can use it without sudo.

mulaz
  • 10,682
  • 1
  • 31
  • 37