Protect the password when using Mysql in an unattended bash-script

Question

I am writing a bash script (for a cron job) that uses mysql:

mysql -uusername -ppassword -e 'something;'

I am looking for a good way to keep the password handy for use in the script, but in a manner that will also keep this information secure from other users on that system. Users who could use ps -ef and users who might read text files...

So how can I safeguard passwords that will be used in an automated script on Linux?

Answer 1

This is an updated answer for users of MySQL 5.6.6+

As documented in 4.6.6 mysql_config_editor — MySQL Configuration Utility, there is now a more secure way to store mySQL passwords, that does not store clear text passwords in a local configuration file.

The mysql_config_editor utility (available as of MySQL 5.6.6) enables you to store authentication credentials in an encrypted login path file named .mylogin.cnf. The file location is the %APPDATA%\MySQL directory on Windows and the current user's home directory on non-Windows systems. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server.

This file can be created by running the following command:

mysql_config_editor set --login-path=client  --host=localhost --user=root --password

You can print the existing settings with the following command:

mysql_config_editor print --login-path=client

This will output the current settings:

[client]
user = root
password = *****
host = localhost

Notice the password is encrypted by default.

Answer 2

Put all the settings in an option file. You can use your default ~/.my.cnf file, or you can specify an alternate file using --defaults-file==filename. See the documentation 4.2.3.4. Command-Line Options that Affect Option-File Handling

The option file contains default settings for mysql commands. You can put the following in it, for example.

[mysql]
user=username
password=password
database=yourdb

Make the option file readable only by you, so other users can't see your password.