11

I want to run

mysqldump -u aUser -p P4SSw0rd --all-databases > backup.sql

in a cron job. How can I do so securely?

I know I could put the command right there, but anyone with access to the machine would see it straight away via crontab. Is there a better way to do it?

  • 1
    Your example is incorrect, it should be -pP4SSw0rd with no space. – Simon Woodside Jul 7 '16 at 2:58
14

As stated in man mysqldump: see 6.1.2.1. End-User Guidelines for Password Security in the MySQL reference manual.

An option file is the safest bet, not least according to the above reference. Giving it in plaintext in crontab is not good, not least since the process command line by default is visible through ps for other users. The same actually applies for environment variables as explained in the reference.

Relevant portion of the MySQL reference manual:

Store your password in an option file. For example, on Unix, you can list your password in the [client] section of the .my.cnf file in your home directory:

[client]
password=your_pass

To keep the password safe, the file should not be accessible to anyone but yourself. To ensure this, set the file access mode to 400 or 600. For example:

shell> chmod 600 .my.cnf

To name from the command line a specific option file containing the password, use the --defaults-file=file_name option, where file_name is the full path name to the file. For example:

shell> mysql --defaults-file=/home/francis/mysql-opts

Section 4.2.3.3, “Using Option Files”, discusses option files in more detail.

Also see https://stackoverflow.com/q/10725209.

  • It seems that ps command obfuscate the password with one x: ps: mysqldump -uroot -px xx mydb. I'm not saying that it's a good protection though (if you type jobs, then the password is revealed in plain text). – ling Sep 21 '15 at 5:41
4

Run the cronjob as a specific user and use some simple Bash logic to extract the password from a plaintext file stored somewhere on the system with permissions that only allow the user (or perhaps group) to access it.

PASS=`cat /path/to/pwdfile`

 mysqldump -u aUser -p $PASS--all-databases > backup.sql

So if the cronjob runs as user 'example', the ownership of the file should be "example:example" and permissioned 0400.

You can also achieve a similar function using a user-level .my.cnf.

  • 1
    read PASS < /path/to/pwdfile is a idiomatically cleaner to do the same thing (arguably I guess; superuser.com/q/323060/49184 applies). – Daniel Andersson Oct 21 '12 at 10:52
  • *"is AN idiomatically cleaner WAY"... Lost in editing. – Daniel Andersson Oct 21 '12 at 15:45
  • Someone with even the most basic understanding of Bash should be able to see what's going on with that cat. :) – Garrett Oct 21 '12 at 16:38
  • 1
    True, I would even say it is the most common way to do it, but it still a bit of an eyesore :-) . If one makes UUOC a habit it will bite when the file is bigger than ARGMAX, it takes an extra process instead of using a shell built-in, it might tempt constructs such as for i in `cat file`; do ... with its own array of problems, etc. But of course, as with most things: if one knows what is happening one is free to do as one chooses. I'm not on a crusade here, regardless of how it might appear :-D . – Daniel Andersson Oct 21 '12 at 17:35
0

Anyone with access to the machine has the same level of access to /var/spool/cron/crontabs/ as to /var/lib/mysql you allow them to have. So, set the proper permissions on the directories and done. Anyone with root-access has direct access to the database files directly. Anyone you do not trust to have access to the machine should not have access at all to the machine.

Usually folks only see their own cronjobs via crontab -l.

0

For backup purposes, consider having a read-only user in mysql, like so

CREATE USER bUser IDENTIFIED BY 'p4ss';

GRANT SELECT ON *.* TO bUser@localhost;
GRANT LOCK TABLES ON *.* TO bUser@localhost;

mysqldump requires only SELECT and LOCK TABLES privileges to do its job.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.