How to avoid passing MySQL user password to shell script

In this post, we will learn how to avoid passing the password for the shell script manually.

You can either:

1. Put the password in the [client] or [mysql] section of a .my.cnf file in your home directory.
2. Use an authentication mechanism (e.g. PAM) that does not rely on passwords
3. Make the password as dynamic variable and pass it in the crontab as an argument (however, this is less secure way of doing it)

For the third case, here is an example:

In the script:

"mysql -u root -p$PASS mysql << [...other options...]

And in the crontab:

0 0 * * * bash /path/to/script/file.sh secretpwd [...other options...]

Note: If you use the password on command line as an argument for the script, be aware that starting from MySQL 5.6, warnings would be logged about empty passwords. So if you upgrade current MySQL 5.1 or 5.5 to 5.6 in the future with this option, you should be aware of encountering this.

From MySQL 5.6.6 onwards, you could use an encrypted login file.

Example to use the mysql_config_editor utility

1. Setup a login-path, any name can be used to set login-path and pass the user credentials you wanted to use for mysqldump.

$ path/to/5.6.13/bin/mysql_config_editor set --login-path=dump --host=localhost --user=root --password --socket=/tmp/mysql_5613.sock
Enter password:

2. Once you enter the password, it will create a encrypted entry in the file ~/.mylogin.cnf , it is not necessary you should pass the user as root, you could use any user you have for the backup purpose.

3. Confirm if the config editor has created this info by running the below command:

$ path/to/5.6.13/bin/mysql_config_editor print --all
    [dump]
    user = root
    password = *****
    host = localhost
    socket = /tmp/mysql_5611.sock

Now you are all set to execute the mysqldump without passing any password, ensure you pass the login-path name correctly, so the connection details will be fetched from the encrypted file.

$ path/to/5.6.13/bin/mysqldump --login-path=dump --all-databases > test_backup.sql

Use a hidden file for storing the password

Save the password in a hidden file and while scheduling in crontab, awk the position of the password as a script argument.

NOTE : Due to a known bug, if your password contains a #, you have to enter it using quotes “abc#123”, or it will be read as just “abc”.