Skip to content

3 Ways to Connect Remote MySQL Databases Like Locals

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"):

  1. SSH your server and to connect the local database like this:
  2. 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>
  3. Install phpMyAdmin in the server for remote clients, and you can connect the database via web.
  4. 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:

    phpmyadmin Login

    phpmyadmin Login

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

  5. Use a GUI tool that can connect the database over SSH.
  6. In general, GUI tools are more productive and more manageable than consoles in real world. Here I use Toad for MySQL Freeware to demonstrate the connection over SSH:

    Create a new connection:

    Toad for MySQL Freeware SSH Connect 01

    Toad for MySQL Freeware SSH Connect 01

    Choose SSH for Connection type:

    Toad for MySQL Freeware SSH Connect 02

    Toad for MySQL Freeware SSH Connect 02

    Provide your account information for the database connection:

    Toad for MySQL Freeware SSH Connect 03

    Toad for MySQL Freeware SSH Connect 03

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

    Toad for MySQL Freeware SSH Connect 04

    Toad for MySQL Freeware SSH Connect 04

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

Tags:

Leave a Reply

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