20

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?

Zoredache
  • 130,897
  • 41
  • 276
  • 420
Trip
  • 401
  • 3
  • 6
  • 9
  • Please accept answers on some of your previous questions, as it appears that several of those answers have been correct solutions - click the outlined checkmark to the left of the answer. – Shane Madden Feb 10 '12 at 20:02

4 Answers4

24

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.

Community
  • 1
Zoredache
  • 130,897
  • 41
  • 276
  • 420
  • This is great it worked .. Thanku – Trip Feb 14 '12 at 16:36
  • If the answer solved your problem then you should click the check mark to accept it. – Zoredache Feb 14 '12 at 16:41
  • 1
    This will store the password as plain text, a practice that I would discourage from a security point of view. – Giovanni Jun 29 '16 at 15:40
  • @Giovanni sure, though the mysql_config_editor you mention in your answer, while useful isn't available until you are running 5.6.6. There are still distributions today that have older versions. – Zoredache Jun 29 '16 at 17:04
  • But how is this done on Windows? – Wolfpack'08 Aug 12 '19 at 00:59
  • Not really sure, how it works on windows. It sure seems likely that it would use the same filename/format. But I have never tried. – Zoredache Aug 12 '19 at 18:57
  • Please note that config editor only obfuscates and doesn't really encrypt. MariaDB is not including the feature because it gives a false sense of security. – Izzy Apr 15 '23 at 13:07
10

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

Giovanni
  • 209
  • 2
  • 3
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.

Thomas
  • 4,225
  • 5
  • 23
  • 28
Mark Butler
  • 31
  • 1
0

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

tjb
  • 135
  • 2
  • 6