To connect a remote MySQL database from LAN (e.g. 10.1.0.0/16), you need an account for remote accesses like "[email protected].%" 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 "[email protected].%".
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
[email protected]'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:
This will be better since you are reusing existed SSH channel to perform the connection, no more risk can weight on you.