PhpMyAdmin 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!
