MySQL Database Remote Access from a Network Segment

By Posted on

Configuring MySQL Database Remote Access from a certain Network Segment

RDBMS : MySQL

Version :

I am using the following version which can be found by executing the following command : 

mysql --version

[root@localhost ~]# mysql --version
mysql  Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using  EditLine wrapper
[root@localhost ~]#

Server Location : We assume that the location of the server is remote or we cannot even access it physically.

Operating System : CentOS 7.1.1503

To be able to know our own operating system, it can be done by executing the following command in Redhat Linux Distribution Family’s operating system : 

[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.1.1503 (Core)
[root@localhost ~]#

Below are steps which can be followed in order to give the authorization of remote access to MySQL database :

  1. First of all, access the server remotely by executing the following command :
username@hostname:~$ ssh username_at_remotehost@remotehost
username_at_remote_host@remotehost's password:
Last login: xxx xxx xx xx:xx:xx xxxx from xx.xx.xx.xx
[username_at_remote_host@remote_hostname ~]$
The above command is a command which is used to perform database server’s remote access via ‘ssh’ command.

 

    1. After succeeding on login to database server which the authorization is going to be created, we have to access the MySQL console by executing the following command :

 

  

[username_at_remote_host@remote_hostname ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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>
  1. Succeed on entering the MySQL console , execute the following command to create user which is going to be used for accessing the database service in this server remotely :
mysql> create user 'username'@'ip_address_of_client' identified by 'password';
Query OK, 0 rows affected (0,02 sec)
* ip_address_of_client is the address of our workstation which is used to remotely access the database server. It can be an IP Adddress such as : 192.168.0.2 or a segment of IP Address such as : 192.168.0.%.
  1. After creating user which can be used to access the database, we have to grant some privileges to specific user which has been created in the above step as follows :
mysql> grant all on database_name.* to 'username'@' ip_address_of_client ';
Query OK, 0 rows affected (0,00 sec)

* database_name.* is pointing to the database which is going to be accessed by the already created user from specific IP address in the previous steps. The * sign means we are giving all of the tables exists in the database names “database_name” to be accessed.

  1. Finishing on executing the above steps, do the following command to reload MySQL privilege tables to be able to implement the specific command which is granting privilege in the previous steps.
mysql> flush privileges;
Query OK, 0 rows affected (0,00 sec)

In conclusion, we can access the database service exists in the remote database server using the username and also password which has already created from command line interface or even any kinds of SQL Editor as long as we access it from the client which has the IP Address that has been defined in the above steps.

Leave a Reply