Store the MySQL client password, or setup password less authentication

I am using the mysql command line client and I do not want to need to provide the password every time I start the client. What are my options?

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".