Search
Close this search box.
Search

How to Connect to a Remote Database using PhpMyadmin

Listen to this article

phpmyadmin-logoPhpMyAdmin is indeed a very useful tool to perform database related operations. Of course, you can perform all those operations using a command line tool, but the GUI tools makes life pretty easy :-p

But since phpMyAdmin is a web application primarily, there are chances hackers will try to attack your database using phpMyAdmin. And if you have weak password, then my dear friend, ‘God save you!’ 😀

Due to these security aspects, sometimes users do not wish to install phpMyAdmin on their server. And instead prefer working with  a command line tool.

But what if you needed to access their database, and preferred using phpMyAdmin instead?!

Well, today is your lucky day! I’ll explain how you can connect to their remote database using phpMyAdmin installed on your localhost.

Let’s get started.

#0 Install phpMyAdmin

😀

Yes. If you have phpMyAdmin installed on your computer, skip this step. But if you don’t, you can just fire the below command (on Ubuntu) to install it:

sudo apt-get install phpMyAdmin

#1 Create a User

Now, we’ll be able to connect to a remote server only if it has a user matching our host. Therefore, the first thing you should do is, create a database user on the server’s MySQL with the host as your own computer’s public IP address. Then, assign the desired database to that user.

Sounds complicated?!

Just follow the below steps to get this done:

  • Login to remote server using SSH
  • Connect to mysql using mysql command mysql -u -p
  • And then fire these queries
CREATE USER ‘’@'' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON . * TO '’@'';
FLUSH PRIVILEGES;
quit;
  • Log out of SSH

Once you have that database user created on the server, open a terminal on your computer and check whether you are able to connect to that database. To do that, fire the below command:

mysql -u -p -h -P

In most of the cases, the port_number_on_which_mysql_service_runs will be 3306 but if you, or your client runs MySQL service on some other port, then supply that port there. If you are not sure about on which port your MySQL service is running on, then login to server over SSH and once you are in, fire this command:

sudo netstat -tulpn | grep 'mysql'

You should be able to see the port number in the output (specifically after ‘:’ in 4th column).

After firing the above command close the SSH. If ‘mysql’ command fired above lets you in, only then proceed to configuring phpMyAdmin. Resolve any problems before proceeding.

Now, lets configure our PHPMyadmin installed on localhost.

#2 Configure PhpMyAdmin

In your terminal, fire the command

sudo nano /etc/phpmyadmin/config.inc.php

And add the below content at the bottom of that file (without the comments of course :-p )

$i++;
$cfg['Servers'][$i]['host'] = ''; //If you don't have the IP address, you can directly enter the hostname of remote server
$cfg['Servers'][$i]['port'] = ''; //The port number on which MySQL service is running
$cfg['Servers'][$i]['user'] = ''; // Username with which you want to connet
$cfg['Servers'][$i]['password'] = ''; //Password associated with the username specified above
$cfg['Servers'][$i]['extension'] = 'mysqli'; // Extension to be used to connect to the database. It can be either mysql or mysqli. PHP program will use the respective extension to connect to the database
$cfg['Servers'][$i]['auth_type'] = 'config'; // This is the authentication mode. Since we are doing this on localhost, it is safe to set is as config. You can read more about authentication types available here: https://wiki.phpmyadmin.net/pma/auth_types

After adding the above content, save the file.

And now try accessing phpMyAdmin by going to http://localhost/phpmyadmin. You should find phpMyAdmin listing your newly added server in the dropdown. Select that server, enter the username and password for it and you are in! 😀

[space]

Two steps and done! Do leave your comments and questions in the comment section below!

Happy weekend!

Sumit Pore

Sumit Pore

Leave a Reply

Your email address will not be published. Required fields are marked *

Get The Latest Updates

Subscribe to our Newsletter

A key to unlock the world of open-source. We promise not to spam your inbox.

Suggested Reads

WordPress Development

Custom WordPress Solutions, for You

LearnDash Development

Custom LearnDash Solutions, for You

WooCommerce Development

Custom WooCommerce Solutions, for You

WordPress Plugins

Scale your WordPress Business

WooCommerce Plugins

Scale your WooCommerce Business

LearnDash Plugins

Scale your LearnDash Business

Label

Title

Subtext

Label

Title

Subtext

Join our 55,000+ Subscribers

    The Wisdm Digest delivers all the latest news, and resources from the world of open-source businesses to your inbox.

    Suggested Reads