Mysqldump launched by cron and password security

user_595223 0
Viewed 28571

I wrote a script to backup my MySQL databases using:

mysqldump --opt --all-databases -u user -pmypassword > myDump.sql

A cron launches it every night and scp the result to another server. mypassword appears in clear in my script, everyone can see it with the appropriate rights. I have been told about /proc issues too (where the cmd run can be seen).

MySQL documentation says:

Specifying a password on the command line should be considered insecure. See Section 7.6, "Keeping Your Password Secure".

I have not found this magic 7.6 sections anywhere.

What is the good practice to deal with automatic mysqldump and password security?

mysqldump security mysql
6 Answers

to add to Sahil's answer above, use --defaults-extra-file

--defaults-extra-file is used to tell a program to read a single specific option file in addition to the standard option files.

whereas --defaults-file is read instead of the default my.cnf file.

user_10484800

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
user_50752140
Related