How to Enable Remote Access To MySQL Database Server

Here i’m going to explain, step’s to connect the mysql database remotely.

1) If you want to access mysql database remotely, you should have a Static IP so that you can access it while adding your IP address to mysql user.
2) All Servers block port 3306 inbound request, you need to allow the port number to particular IP on your server.

1) What is localhost ?

Localhost is a reserved address that a computer or device can use when referencing itself.

2) What is Remote Access ?

Remote access is the ability to get access to a computer or a network from a remote distance.

Now, i’m going to access the mysql database from MySQL Workbench from my PC and see what will happen.

Configuration is are below:
Connection Name : 2daygeek-demo-db (Name of this connection)
Connection Method : Standard (TCP/IP)
Parameters:
Host Name : 83.170.96.101 (Server IP Address)
Port : 3306 (mysql access port)
User Name : demou (Database Name which you want to connect)
Password : ******* (store your database password)

When i access the database, i got below error message but i have given correct required details.

3) How to show’s created database ?

Use the below command to list out the database which is created on mysql server.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demodb             |
| demodb1            |
| 2daygeek           |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.01 sec)

4) How to shows created database users ?

Use the below command to list out the database users which is created on mysql server.

mysql> SELECT User FROM mysql.user;
+-----------+
| User      |
+-----------+
| 2daygeek  |
| root      |
| root      |
|           |
| root      |
|           |
| demou     |
| demouser1 |
| 2daygeek  |
| root      |
+-----------+
10 rows in set (0.00 sec)

5) How to shows user,host privilege ?

Use the below command to check the list of host assigned to users on mysql server.

mysql> select user,host from mysql.user;
+-----------+------------------------+
| user      | host                   |
+-----------+------------------------+
| 2daygeek  | %                      |
| root      | 127.0.0.1              |
| root      | ::1                    |
|           | Server10001.uk2net.com |
| root      | Server10001.uk2net.com |
|           | localhost              |
| demou     | localhost              |
| demouser1 | localhost              |
| 2daygeek  | localhost              |
| root      | localhost              |
+-----------+------------------------+
10 rows in set (0.00 sec)

The output is clearly shows, demou user will be accessible locally(inside the server) and not for outside (remotely). I’m going to give a remote access to my static IP. See the below output.

6) Assigning privilege to user ?

Use the below command to enable remote access which you want.

mysql> GRANT ALL PRIVILEGES ON demodb.* TO 'demou'@'219.91.219.14';
Query OK, 0 rows affected (0.00 sec)

Whatever the permission which you assign to database you should flush the privileges.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Now, i’m going to check whether the IP address is added or not to particular user.

mysql> select user,host from mysql.user;
+-----------+------------------------+
| user      | host                   |
+-----------+------------------------+
| 2daygeek  | %                      |
| root      | 127.0.0.1              |
| demou     | 219.91.219.14          |
| root      | ::1                    |
|           | Server10001.uk2net.com |
| root      | Server10001.uk2net.com |
|           | localhost              |
| demou     | localhost              |
| demouser1 | localhost              |
| 2daygeek  | localhost              |
| root      | localhost              |
+-----------+------------------------+
11 rows in set (0.00 sec)

I’m going to access the database once again via MySQL Workbench. Now its got successes. See the output below.























We are preparing all articles in-depth to understand by all level/stage Linux administrators. If the article is useful for you, then please spend less than a minute to share your valuable comments in our commenting section.

Please stay tune with us…Good Luck 🙂