Store the MySQL client password, or setup password less authentication
Solution 1:
Create a file named .my.cnf
in your home directory that looks like this. Make sure the filesystem permissions are set such that only the owning user can read it (0600).
[client]
host = localhost
user = username.
password = thepassword
socket = /var/run/mysqld/mysqld.sock
#database = mysql
Since you also tagged your question mysqldump you should look at this question.
Using mysqldump in cron job without root password
Update (2016-06-29) If you are running mysql 5.6.6 or greater, you should look at the mysql_config_editor tool that allows you to store credentials in an encrypted file. Thanks to Giovanni for mentioning this to me.
Solution 2:
You can use the mysql_config_editor
utility to store authentication credentials in an encrypted login path file named .mylogin.cnf
.
To create a new set of credentials run:
mysql_config_editor set --host=db.host.org --user=dbuser --password
and enter your password when prompted.
This will store your authentication credentials in the default client
login path.
You can store multiple authentication credentials by specifying a different --login-path
option:
mysql_config_editor set --login-path=db2 --host=db2.host.org --user=dbuser --password
By default, the mysql
client reads the [client]
and [mysql]
groups from other option files, so it reads them from the login path file as well. With a --login-path
option, client programs additionally read the named login path from the login path file. The option groups read from other option files remain the same. Consider this command:
mysql --login-path=db2
The mysql
client reads [client]
and [mysql]
from other option files, and [client]
, [mysql]
, and [mypath]
from the login path file.
To print out all the information stored in the configuration file run:
mysql_config_editor print --all=true
More information about the utility can be found at "mysql_config_editor — MySQL Configuration Utility".
Solution 3:
We must not pretend that the .mylogin.cnf is secure at all since I can use my_print_defaults -s [use your login-path]
to make that password appear in plain text. This is why MariaDB does not support this 'security by obscurity' approach.