3 Ways to Connect Remote MySQL

To connect a remote MySQL database from LAN (e.g. 10.1.0.0/16), you need an account for remote accesses like "user01@10.1.%" instead of "user01@localhost". If the two accounts coexist, there will be  a drawback, the definers of triggers will be hard to keep consistent as time goes on, some triggers might be defined by "user01@localhost" and the others might be defined by "user01@10.1.%".

On the other hand, if there're only local accounts allowed to use due to the requirements of security, you must connect the database just like a local one remotely.

Here I introduce 3 ways to connect a remote database with a local account (i.e. "xxx@localhost"):

SSH Console

SSH your server and to connect the local database like this:

In this way, you connect and operate the database in a text-based console with your bare hands, and I know it's less productive than GUI tools.

login as: root
root@10.1.25.98's password:
Last login: Mon Apr  7 19:26:09 2014 from 10.1.51.182
[root@test ~]# mysql -u'user01' -p'password' test
...
mysql>

phpMyAdmin

We should install phpMyAdmin in the server for remote clients, and you can connect the database via web.

After installation, you have to specify the CIDR that are allowed to access the database via phpMyAdmin.

[root@test ~]# vi /etc/httpd/conf.d/phpMyAdmin.conf
...
<Directory /usr/share/phpMyAdmin/>
...
   <IfModule !mod_authz_core.c>
     # Apache 2.2
     Order Deny,Allow
     Deny from All
     Allow from 127.0.0.1
     Allow from ::1
     Allow from 10.1.0.0/16
   </IfModule>
</Directory>
...

Let's see the login page:

Kindly remind you that installing phpMyAdmin provides another possible channel for public to access the database, it might become a security risk.

GUI Tools

Use a GUI tool that can connect the database over SSH.

In general, GUI tools are more productive and more manageable than consoles in real world. For example, using SQL developer to connect to MySQL database. Here I use Toad for MySQL Freeware to demonstrate the connection over SSH:

Create a new connection:

Choose SSH for Connection type:

Provide your account information for the database connection:

Provide your account information for the SSH connection and then click "Connect" or "Save":

This will be better since you are reusing existed SSH channel to perform the connection, no more risk can weight on you.