How to connect to a MySQL database from another system (remotely)

Today we would discuss, how to connect to a MySQL database from another system? After going through this article, one would have the answers of below questions:

• Unable to connect to a MySQL database from remote system in the same network?
• Although able to connect to a MySQL database using localhost or 127.0.0.1 but unable to connect it using the IP address of the same machine in which MySQL database is present?

Table Of Contents

1. Prerequisites
2. The MySQL Access Privilege System
3. MySQL Error
4. Resolution
5. Conclusion

Prerequisites

To complete this tutorial, one will need:

• A machine running MySQL that is accessible from the remote system.
• A MySQL client on the remote system like HeidiSQL, MySQL Workbench or MySQL installed in that system etc.

Note: In this article, we are concentrating on authentication part rather than authorization.

The MySQL Access Privilege System

One needs to understand The MySQL Access Privilege System in order to resolve MySQL connection issues. It's primary function is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE.

According to this access privilege system, important pointers regarding authentication & authorization:

• Authentication: As a user, when one connect to a MySQL server, the identity is determined by the host from which one connect and the user name one specify.
• Authorization: When one issue requests after connecting, the system grants privileges according to the identity and what one want to do.

MySQL Error

One will get a similar MySQL errors, when the user don't have permission to connect to a MySQL server from the remote host.

D:\softwares\mysql-5.6.37-winx64\bin>mysql -hW.X.Y.Z -p3306 -u anshul -p
Warning: Using a password on the command line interface can be insecure.
Enter password: ********
ERROR 1045 (28000): Unknown error 1045
Note:
W.X.Y.Z - IP address of the machine running MySQL server
anshul - user by which trying to connect to MySQL server

Let's validate our understanding that user['anshul'] doesn't have permission to connect to a MySQL server from remote host. You will see that user['anshul'] can connect to a MySQL database only from 'localhost', just hit the below command:

mysql> select user, host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+         
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| anshul        | localhost |
| anshul1       |   %       |
| anshul2       | W.X.Y.Z   |    
+---------------+-----------+
5 rows in set (0.00 sec)
mysql>

Listen carefully! table is speaking all the hidden story 😊:

• Using user ['root', 'anshul'] & host [localhost], connection to a MySQL database is possible
• Using user ['root','anshul'] & host [IP address of machine having MySQL server or remote system], connection to a MySQL database is not possible
• Using user['anshul1'] & host[from any host ], connection to a MySQL database is possible
• Using user['anshul2'] & host[W.X.Y.Z ], connection to a MySQL database is possible

Resolution

To resolve the MySQL connection error from happening in the remote system or in local system(trying to connect using host IP address), one need to either create new user having required access or provide proper access to the existing user specific to the host (remote or local) from which one tries to connect to a MySQL server.

Create a new user with required permissions

## This user can connect to a MySQL Server from any remote systems, see '%'
## This user has privileges on all databases and all tables, see '*.*', [databases.tables]
mysql> CREATE USER 'your_user_name_here'@'%' IDENTIFIED BY 'your_password_here';
mysql> GRANT ALL ON *.* to 'user_name'@'localhost';
mysql> FLUSH PRIVILEGES;

## This user can connect to a MySQL Server from remote system having IP address 10.20.30.40
## This user has privileges on all databases and all tables, see '*.*' ,[databases.tables]
mysql> CREATE USER 'your_user_name_here'@'10.20.30.40' IDENTIFIED BY 'your_password_here';
mysql> GRANT ALL ON *.* to 'user_name'@'localhost';
mysql> FLUSH PRIVILEGES;

Change privileges for an existing user

## This updates the access for provided username from 'locahost' to '%'
mysql> UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='your_username_here';
mysql>FLUSH PRIVILEGES;

Note: Depending upon different scenarios, the solution may differ because the given user may 
have different permission on different tables, accordingly, one have to run the update command.
One may find it easy to revoke all the permission and then grant them back. 

mysql>UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='your_username_here';
mysql>UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='your_username_here';
mysql>UPDATE mysql.tables_priv SET Host='%' WHERE Host='localhost' AND User='your_username_here';
mysql>FLUSH PRIVILEGES;

Disclaimer:

Now we have connected to a MySQL databases from remote system, but what operations one can perform on the existing databases in the MySQL server is all together a different ball game. It depends upon the privileges granted to the user.

Conclusion

MySQL connection error can be easily resolved by setting up the permission for the user specific to the required host(remote or local). For reference, providing some useful MySQL commands:

## Check the user and host association to identify the MySQL connection issue 
mysql> select user, host from mysql.user;
## see the privileges given to an user 
mysql>show grants for 'your_username_here'@'your_host_name_here';

Thank you for reading this article! Hoping that now you can easily diagnosis the MySQL connection error. Don't forget to share your comments. Happy Learning !!