Detailed, Step-by-Step Guide on Managing Remote MySQL Database with Local phpMyAdmin via SSH Tunneling!

Amazon phpMyAdmin may have security issues. Webmasters may not want to allow remote access to a production database through phpMyAdmin (installed on the production server) for security reasons.

Let's face it. If you set up phpMyAdmin on your production server so everyone can access http://<your production server IP>/phpmyadmin you are in trouble. Even if you add user name and password you are still exposed to hacking attempts from unscrupulous people.

What if you really want to manage your production database through phpMyAdmin because you like phpMyAdmin's friendly user interface? Rest assured there is a secure way to do it.

Read this detailed tutorial to find out step-by-step how to manage a remote MySQL database with local phpMyAdmin and SSH tunneling. This comprehensive guide will walk you through exactly how to use local phpMyAdmin to manage a remote MySQL database with SSH security.

Background

I assume the following:

You have phpMyAdmin installed locally.
You are trying to manage a remote MySQL database by using your local phpMyAdmin installation.
You are able to use SSH to log into the remote server where the MySQL database is installed.

We are going to set up a SSH tunnel between your local phpMyAdmin and remote server so that when you are using your local phpMyAdmin to manage the remote database, your local machine is using SSH to talk to the remote database under the hood. This is all transparent to you, except you may experience a slower speed.

Note: You will not need to change anything at your remote server. No configuration change. No weird hacks. Nothing.

Warning: If you manage a remote database with SSH tunneling things will be slower than if you manage a local database.

For added security you can create a new user with a strong user name and password and only the needed privileges. If you don't feel it's necessary you can skip these steps.

Optional: Create a new user on the remote MySQL server

Here is an example command to create a new user michaelonly with password set to aVeryStrongPassword.

CREATE USER 'michaelonly'@'localhost' IDENTIFIED BY 'aVeryStrongPassword';

As mentioned earlier this step can be skipped.

Optional: Grant the user only the privileges it needs

Here is an example command to grant michaelonly all privileges on databases db1, db2, db3.

GRANT ALL PRIVILEGES ON db1.* TO 'michaelonly'@'aVeryStrongPassword';
GRANT ALL PRIVILEGES ON db2.* TO 'michaelonly'@'aVeryStrongPassword';
GRANT ALL PRIVILEGES ON db3.* TO 'michaelonly'@'aVeryStrongPassword';

As mentioned earlier this step can be skipped.

Next we will look at mandatory steps to set up SSH tunneling between your local machine and the remote machine.

Set up SSH tunneling

If you are on Linux or Mac you can run the following command in a Terminal on your local machine (using your Public DNS instead of xx.xx.xx.xx):

$ ssh -Ng -L 3307:127.0.0.1:3306 root@xx.xx.xx.xx

If you are using Windows, you can use Putty:

Follow the steps below.

1. Before opening the connection. Go to Connection -> SSH -> Tunnels, enter the values below:

Source port: 3307
Destination: localhost:3306

2. Click Add

3. Click Open

Once you see the Putty window open with no failures, the SSH channel has been established.

Note that whenever you want to access the remote database with SSH tunneling you must perform this step.

Let local phpMyAdmin know about this new user

If you've skipped the steps of creating a new user, simply use the existing user credentials for the remote MySQL database.

We need to add code to the phpMyAdmin configuration file so that phpMyAdmin will know about this remote user. If you use WAMP like I do your config.inc.php should be located somewhere at c:/wamp2.4/apps/phpmyadmin4.0.4/

Add the following code to the very end of the configuration file:

// Tutorial @ https://one-minute-info.blogspot.com/2014/03/local-phpmyadmin-remote-mysql-db-ssh.html
$i++;
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3307';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'http';
$cfg['Servers'][$i]['AllowNoPassword'] = FALSE;
$cfg['Servers'][$i]['user'] = 'michaelonly';
$cfg['Servers'][$i]['password'] = 'aVeryStrongPassword';

Restart local phpMyAdmin to reflect this change.

It is possible you'll have both local user (e.g. root) on your local MySQL database and this 'remote' user michaelonly on the remote MySQL database. They'll be presented in a drop down menu for you to select.

Use PhpMyAdmin to connect to the remote MySQL database

Go to your local phpMyAdmin user interface (e.g. http://localhost/phpmyadmin) and you should see a drop down menu with 127.0.0.1:3307. Select it to log in to your remote MySQL database.

If you see a prompt asking you to enter the user name and password go ahead and do that.

You should be able to see the databases that this user has access to. Happy managing your remote databases securely with SSH tunnel on your local phpMyAdmin interface!

If things are very slow for you you can always fall back on managing the local database. Once you are done, copy the local database to the remote database server.

Questions? Let me know!