MySQL Port Listening with Consideration to Virtual Hosts

November 25, 2014 2.4k views
john912311
By:
john912311

I'm running a Droplet with Ubuntu 14.04.1 LTS and I have multiple domains hosted there. I just setup phpmyadmin on the server and then realized, "Wait a sec... I need one of these for each domain! Bummer."

Currently, when to make connections via php I use hostname 127.0.0.1 to connect to mysql. Here is why:
Query: sudo netstat -plutn | grep -i sql
 Output: ```tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 960/mysqld


Is it recommended to only have mysql listening on the loopback interface?  How can I make mysql listen on each of my virtual hosts (i.e. host='example.com') (i.e. host='example.net')?

Can anyone think of a way that I can restrict access to databases for example.net requested from example.com (and vice versa)?  Being a novice, it seems that these two questions are probably very closely related (control of mysql access via port listening).

Thank you :)

~John
Log In to Comment
1 Answer
EpicCDN November 26, 2014

If your MySQL is secure, there is no need to do separation of hosts, is the same MySQL anyways. Usually you can run it on localhost/loopback if you are getting access from same server websites, if you want it to be accessible from sites hosted on other servers all you need to do is grant access to all or to the hosts will need access to, MySQL is usually open to all interfaces by default, but each user has to be granted a permission to access from the outside or an specific host ie:

username host

user1.com % (all)
user2.net localhost

Each user can only access the databases it has access to, so if they are on same server as other users they still can't see other users DB's. phpMyAdmin works that way. Also you can create an alias or symbolic link to each user website home/root folder so you don't need to install/copy phpMyAdmin multiple times.

I for example, set it to have an universal hostname for all my clients/users so they are tell to always use this hostname (db.providersamplename.com), so if at any moment we choose to separate db from webserver, or even host MySQL into a different provider we can do it without the users to noticed or without code change on their apps/websites.

Reply
Have another answer? Share your knowledge.