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?
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.
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.
You can use the
mysql_config_editor utility to store authentication credentials in an encrypted login path file named
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
mysql_config_editor set --login-path=db2 --host=db2.host.org --user=dbuser --password
By default, the
mysql client reads the
[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 client reads
[mysql] from other option files, 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".
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.
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