How to securely login to MySQL without providing password each time

Introduction

Using a long and secure MySQL password is necessary, however having to type that long password every time you run mysql -u root -p could be a bit frustrating especially if you want to run some MySQL commands in a script without being prompted for a password.

One way to login to MySQL is to append your password right after the -p flag:

mysql -u root -pYOUR_PASSWORD

However, using a password on the command line interface can be insecure.
This will also be recorded in your bash history.

Here is a better and more secure way on how to do that!

Prerequisites

In order to be able to follow along, you will need a Linux server with MySQL installed.

I will be using an Ubuntu Droplet deployed on DigitalOcean. If you wish to follow along you can sign up for DigitalOcean via my referral link below and you will get $100 free DigitalOcean credit:

https://m.do.co/c/2a9bba940f39

Configuration of .my.cnf

By configuring the ~/.my.cnf file in your user's home directory, MySQL would allow you to login without prompting you for a password.

In order to make that change, what you need to do is first create a .my.cnf file in your user's home directory:

touch ~/.my.cnf

After that set secure permissions, so that other regular users could not read the file:

chmod 600 ~/.my.cnf

Then using your favorite text editor open the file:

nano ~/.my.cnf

And add the following configuration

[client]
user=YOUR_MYSQL_USERNAME
password=YOUR_MYSQL_PASSWORD

Make sure to update your MySQL credentials accordingly, then save the file and exit.

After that, if you run just mysql you will be authenticated directly with the credentials that you've specified in the ~/.my.cnf file without being prompted for a password.

Testing with mysqladmin

As a quick test you could check all of your open SQL connections by running the following command:

mysqladmin proc

The mysqladmin tool would also use the client details from the ~/.my.cnf file and it would list your current MySQL process list.

Another cool thing that you could try doing is combining this with the watch command and kind of monitor your MySQL connections in almost real-time:

watch -n1 mysqladmin proc

To stop the watch command just hit CTRL+C

Conclusion

Adding your MySQL client details in your ~/.my.cnf could be a good way to speed up your workflow and access your MySQL server without typing your password each time.

This is also super handy in case you want to run some MySQL commands via a bash script.

Hope that this helps!

Comments

[Darshit Patel]

Any way to do this with multiple MySQL servers one might need to connect to?

[Bobby Iliev]

Hi there 👋,

Very good question.

Yes, this is doable, you can specify a host and then have a suffix for each host in your config file.

Example:

• DB Host 1:

[clientdb1]
user=your_username
password=your_pass
host=db1.example.com

Note the client + db1 suffix, then to specify which SQL server you would like to connect to you need to use the --defaults-group-suffix= flag, for the above example it will look like this:

mysql --defaults-group-suffix=db1

To make things easier, you could also add a Bash alias for that command so that you don't have to type the whole thing each time.

Hope that this helps!

[Darshit Patel]

Thanks for the reply! I will try this out