To connect a remote MySQL database from LAN (e.g. 10.1.0.0/16), you need an account for remote accesses like "firstname.lastname@example.org.%" 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@example.com.%".
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 your server and to connect the local database like this:
- Install phpMyAdmin in the server for remote clients, and you can connect the database via web.
- Use a GUI tool that can connect the database over SSH.
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
Last login: Mon Apr 7 19:26:09 2014 from 10.1.51.182
[root@test ~]# mysql -u'user01' -p'password' test
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
# Apache 2.2
Deny from All
Allow from 127.0.0.1
Allow from ::1
Allow from 10.1.0.0/16
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.
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:
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.