Remote Access to MySQL Server

Submitted by sandesh on Sun, 03/08/2020 - 23:35
Get Remote Access to MySQL Server

*All the steps mentioned here are done and tested in Ubuntu 18.04 and MySQL 5.7.

There are times when you have your project deployed on a server(lets call it Server PR) and MySQL Database on another system(say Server MD).  The reason for having this segregation could be performance benefit or something else. Today I wanted to do the same in one of my Drupal project. First I tried by simply changing the host and port number in my settings.php file. For those who are not familiar with Drupal, settings.php is a file where you save your Database and Server related configurations. 

This is what I did:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'DATABASE_NAME',
      'username' => 'root',
      'password' => 'PASSWORD', 
      'host' => '192.168.1.5',   //IP Address
      'port' => '3306',  //Port Number
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

 

1. I changed the host from localhost to 192.168.1.5 (the IP address of Server MD, the system where database resides).

2. Though its optional, I changed the port to 3306. If you leave it as empty, 3306 is the value taken by default.

The changes I made here were correct but the site was not loading when I visited http://my-website.com. After a while I figured out that the system where MySQL database resides, will have to allow remote access to it. So, here comes the main part of the article: How to allow Server PR to access the database in Server MD?

 

Step 1:

In Server MD, go to the terminal and open the mysqld.cnf file with vi (or any editor of your choice). 

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Once the file opens, comment out the following line by adding a hash(#) at the starting of the line and save the file:

# bind-address          = 127.0.0.1

Basically we are telling MySQL to not bind itself to Server MD and open up for networking with other systems.

 

Step 2:

Restart the mysql server. Type the following in the terminal in Server MD:

service mysql restart

 

Step 3:

Now that we are done with opening up MySQL (Server MD) to other servers, we need to Grant Privileges to other systems.

Login to MySQL prompt in Server MD by typing the following in the terminal, 

mysql -u root -p

On the MySQL prompt, type the following:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.4' IDENTIFIED BY 'MY_PASSWORD' WITH GRANT OPTION;

This will provide remote access to Server A's root user to all the databases and their tables in Server B. If you want to restrict it to a particular database, you can type:

mysql> GRANT ALL PRIVILEGES ON DATABASE_NAME.* TO 'root'@'192.168.1.4' IDENTIFIED BY 'MY_PASSWORD' WITH GRANT OPTION;

*Here, 'root'  and '192.168.1.4' are the username and IP address of Server PR.  Note that you will have to replace 'root'@'192.168.1.4' with your 'USERNAME'@'IP_ADDRESS'

 

Step 4:

After granting the privileges to the new user, lets update the system by flushing all the privileges. On the same MySQL prompt, type the following:

mysql>FLUSH PRIVILEGES;

 

Step 5:

You should now be able to access MySQL at Server MD from the Server PR. Lets confirm by typing the following on the terminal in Server PR:

mysql -h 192.168.1.5(to be replaced with Server PR's IP Address) -u root -p

It will ask you for Sever MD's MySQL Server password. If all steps, done correctly, you should be able to see this prompt from Server PR and your project will load using database from another server.


MySQL Remote Access Ubuntu 18.04

 

Tags