Store the MySQL client password, or setup password less authentication

Question

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?

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

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

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

Answer 4

There is another way, orthogonal to the methods mentioned above, but it can be a security risk if someone else is watching your monitor, OR if you are saving your history.

Nevertheless it is an option, which will prevent you from being prompted, and one which I use in throw-away docker images and such....

mysql -u YOUR_USER --password=YOUR_PASSWORD_HERE your_database -e "your query" etc.

You will not be prompted, you can set a temporary alias in the shell if you wish.

Use with caution.

mysql Ver 14.14 Distrib 5.5.61-38.13, for debian-linux-gnu (x86_64) using readline 5.1