Mysqldump launched by cron and password security
Quoting the MySQL docs(http://dev.mysql.com/doc/refman/5.1/en/password-security-user.html):
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, wherefile_name
is the full path name to the file.
The accepted answer stores the password in a plain text file, which could be read by anyone with administrative (root) access. If your database is in a shared hosting environment, this is undesirable.
A better option would be to use mysql_config_editor
to create an encrypted login path named mysqldump
. According to the MySQL documentation:
mysql_config_editor encrypts the .mylogin.cnf file so it cannot be read as cleartext, and its contents when decrypted by client programs are used only in memory. In this way, passwords can be stored in a file in non-cleartext format and used later without ever needing to be exposed on the command line or in an environment variable.
The following command will create your mysqldump
login path:
mysql_config_editor set --login-path=mysqldump --host=your_hostname --user=your_username --password
You will be prompted to enter your password, and the login path you created will be stored in encrypted format. mysqldump
will automatically use this login path whenever you call it in the future, unless you specify a different login path with the --login-path
command line option.
Here is how you would invoke mysqldump
after creating an encrypted login path:
mysqldump database_name > output_file