Running thesql dump in a cron job without exposing passwords

cronSecurity

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?

Best Answer

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.

Related Question