Access MySQL without password

Access MySQL without password locally may be sometimes needed and quite handy for various reasons like for example if you are a very passionate database developer or just occasionally doing DBA stuff and you desperately need to quickly access a specific database or table, can be quite annoying to type in your username and password each time you are using the console. In this very short tutorial we’ll talk about how to access MySQL without using a password locally via terminal window. We have tested this method using MySQL version 5.6 and 5.7 based on CentOS 7 operating system but we are sure that this simple solution can be easily applied with other versions of MySQL as well.

Table of Contents

Security comes first
Creating mysql read-only username
Creating .my.cnf file
Testing the solution

Security comes first

We must begin our short tutorial by telling you that this method can present a serious security risk if your MySQL server is publicly exposed to the internet, meaning that it has a public IP assigned and also port 3306 exposed. We strongly recommend not to use this solution if any of the above are true, you can end up with a compromised server, data leaks or data loss. It really doesn’t matter if your username is locked via MySQL host like 10.123.%.%, this is one of the weakest security solution for MySQL for the simple reason because MySQL hasn’t been designed as server security solution, MySQL has a very think layer in terms of security and has been designed for a completely different purpose.

We recommend to use this solution when your servers are not publicly exposed and also will be wise not to use root account unless you’re accessing MySQL without password in a development environment.

Creating mysql read-only username

Assuming that you already have the credentials for root user let’s login to mysql in order to create a new read-only username for out test case, as a good practice try avoid to use the password directly on the login statement, always type / paste the password in the prompt filed.

$ mysql -u root -p

Having now access to MySQL console let’s start by creating a completely new MySQL username which will have read-only privileges for all databases like show in the example below.

mysql> CREATE USER 'my_username'@'localhost' IDENTIFIED BY 'A-Very-Complex-Password';
mysql> GRANT SELECT ON *.* TO 'my_username'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> QUIT;

Well done, we can now test the connectivity using our newly create username my_username by invoking the same MySQL command as we did for the root username before:

$ mysql -u my_username -p

If everything goes fine then we can safely move to our next step where we’ll have to create a file that will handle the authentication for all our mysql local sessions.

Creating .my.cnf file

So far we have managed to create a new username and to grant read-only permissions to it for all our databases and now is the time to pass its credentials to a file named .my.cnf which is read by default by MySQL each time the command mysql is invoked via terminal window. First we will have to change directory to our current username that we are logged with and to create the needed file using vi editor like shown in the example below.

$ cd ~
$ vi .my.cnf

Once vi editor is open please add the next lines to your file:

[client]
user=my_username
password=A-Very-Complex-Password

Save and close .my.cnf file and lets move to the next step of our tutorial.

Testing the solution

On the final step of our tutorial we will try to access MySQL without password, so lets invoke once again in our terminal window MySQL client without any arguments like -u or -p.

$ mysql

A successful output of mysql command should look similar to this one listed below:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 414658
Server version: 5.7.19-17-57-log Percona XtraDB Cluster (GPL), Release rel17, Revision 35cdc81, WSREP version 29.22, wsrep_29.22

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

That is all that we need to configure in order to access MySQL without password, is quite easy but as we have mentioned in the beginning of our tutorial Security comes first.