How to connect to a remote database

Access a remote MySQL database

post / By

At times, there is a need to access a database that resides on a different server. But that is not possible directly as it is disabled by default for security reasons. However, by certain steps, you can connect to a remote database.

You might need to use a remote MySQL server for the following reasons:

  • To use a server that has set up especially for hosting databases. A server could be used to host databases only in many cases. For example, if the database is quite large.
  • To centralize management of database for a number of servers.
  • Isolate the database from servers accessible via a public IP address.
  • To offload database workloads from the webserver to improve the performance of busy sites and the database.

I have personally done this on my portfolio website. I have a portfolio website and a blog website and they are on different servers. In the portfolio, I am showing three of the recent blog posts by accessing the blog database remotely.

In this article, I will show you how you can do this!

Let us say that we have two different servers named server X and server Y hosting two different websites. The domain name of server X website be siteone.com and that of server Y be sitetwo.com.

The database name on server Y be ‘my database, database user name be ‘test’ and password be ‘remoteaccess1234’.

Suppose you need to connect to the database of server Y through server X. Then the procedure would be something like this –

  • Login to your CPanel account of the server Y (On which the database is residing).
  • Under the section named ‘Databases’, click on the option ‘Remote MySQL’.

remote mysql

  • You would be redirected to a page where you would need to add an access host. In the input field shown, enter the IP address of server X and click on Add Host.

Add Host IP

The process for the remote database server is over. Now it’s time to work in the PHP script to fetch the database. In the script, you would need to write the IP address or domain name in the place of a hostname.

<?php
        $dbhostname = "IP or domain of server Y";
        $dbname = "database name of server Y";
        $dbusername = "username of database of server Y";
        $dbpassword = "password of database on server Y";
        $conn = mysqli_connect($dbhostname, $dbusername, 
        $dbpassword, $dbname);
?>
<?php
        $dbhostname = "sitetwo.com";
        $dbname = "mydatabase";
        $dbusername = "test";
        $dbpassword = "remoteaccess1234";

        $conn = mysqli_connect($dbhostname, $dbusername, 
        $dbpassword, $dbname);
        $result = array();
        $SQL = "select * from table_name";
        $res = mysqli_query($conn, $SQL);

        while($row = mysqli_fetch_array($res))
        {
                $result[] = $row;
        } 
?>

The array named $result would contain all of the data of the selected database table.

That’s it! You have now accessed the data from the table in the remote database.

You may also like