How to configure MySQL for remote access?

Webmin
1

What do I need to do in the Webmin MySQL module to allow remote connection to datebases on the server?

I like to use Navicat and am being refused connection.

I have opened up port 3306
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m tcp --sport 3306 -j ACCEPT

I tried adding host permission to the DB I wanted (and then to any) for my static IP of my home office with no luck.

The DB user for the DB I want to work with has full permissions for the DB.

Any ideas?

2

is the DB user that needs to remotely connect to your server using the servers IP and not hostname, cause hostname usually doesn’t work.

3

Yeah it’s connecting using the IP.

Also when I try to check the port with telnet I get this:

telnet xx.xx.xx.xx 3306
Trying xx.xx.xx.xx

Connected to vps.example.net.
Escape character is ‘^]’.
ZHost ‘h-xx-xx-xx-xx.something.covad.net’ is not allowed to connect to this MySQL serverConnection closed by foreign host.

4

did you double check the correct login and password :slight_smile:
As according to the documentation a user is allowed to connect from any host.

"# The Hosts field allows you to choose which client host(s) the user will be able to connect to the database from. You should normally select Any, which gives him access from anywhere - unless the user himself is prevented from connecting from some hosts, explained in the Managing MySQL? users section. "

However it also says a bit further:

“In the Hosts field, select the second radio button and enter a hostname, IP address or hostname or IP pattern (like %.example.com or 192.168.1.%) into the field next to it. Selecting the Any button isn’t particularly useful.”

5

Thanks for the help ronald :slight_smile:

I am still stuck though

Here is a little info:
(not actual names)

Database = dbtest
DB User = dbuser
User PW = userpw

The database is populated and running a site with this config.

In MySQL User Permissions
User = dbuser
Hosts = localhost
Encrypted Password = it’s there
Permissions = none

In MySQL Database Permissions
Database = dbtest
User = dbuser
Host = localhost
Permissions = All

In MySQL Host Permissions
Nothing

So with this I can normally use the dynamic site BUT i can’t access the DB remotely from home through Navicat of course because it has not been allowed yet.

So I thought that I could quickly test to see if I could connect remotely by changing this:
In MySQL Database Permissions
Host = All

Seems like that should open that DB to the world as long as they have login info.
Nope.

With that setting I still have site functionality but cannot connect remotely.

So what happens if I try this:
In MySQL Database Permissions
Host = xx.xx.xx.xx <-- my local static IP.

Nope. Now I can’t connect through navicat AND the site is down of course.

So I figure maybe this is where Host Permissions comes in so I set:
In MySQL Database Permissions
Host = From Host Permissions

and then I creat a new host permission entry:

In MySQL Host Permissions
Databases = dbtest
Hosts = Any
Permissions = All

With that the site is back up but still no remote connectivity.

So for kicks I try:
In MySQL Host Permissions
Databases = dbtest
Hosts = xx.xx.xx.xx <-- my local static IP.
Permissions = All

And with that site and remote are down


OK so I am not getting how this works yet so I go back user permissions and on a whim change it to:
In MySQL User Permissions
User = dbuser
Hosts = Any <---- This is the change
Encrypted Password = it’s there
Permissions = none

and with hosts set to any for that user I am able to connect to the db though Navicat and edit the DB!!
The problem is the site is down. GAAAA :slight_smile:

OK so I know it’s possible to have successful remote connection I just need to find a way to have that user connect through remote AND have the site working from localhost.

So I thought I could just add another Host Permission for localhost
In MySQL Host Permissions
Databases = Any
Hosts = localhost
Permissions = All

And with that the site is still down but remote is working.

OK

So I go back to user permissions and change:
In MySQL User Permissions
User = dbuser
Hosts = localhost <---- This is the change
Encrypted Password = it’s there
Permissions = none

And the site is back up BUT REMOTE IS DOWN. :frowning:

How can I have both working at the same time?

6

can you try per haps to add the localhost and the remote host

In MySQL User Permissions
User = dbuser
Hosts = localhost, IP
Encrypted Password = it’s there
Permissions = none

(adding your local static IP won’t do anything besides break things as mysql is running on localhost ie 127.0.0.1)

7

CPanel has a page called Remote Database Access Hosts where you enter the remote IP you want to grant access to your database. That’s all I want to do here.

Maybe Joe could jump in to walk me through or else I am going to have to enter a "bug" I mean help request :slight_smile:

Ronald thanks for trying! I appreciate the help :slight_smile:

In MySQL User Permissions for Hosts entry:
When it says localhost the site works and remote doesn’t.
When it says Any the site doesn’t work and the remote does.
When I try to add something to localhost like
localhost, IP
where IP is my home office IP both the site and remote do not work. <#>_<#>

8

Submitted a support request and Jamie answered right away.

Thanks Jamie!!!

Here is how he says to do it and I got it working first try :slight_smile:

You can certainly do this in Virtualmin, although access is generally configured on a per-domain basis.

To set it up for existing domains, do the following :

  1. Login as the master admin (root), and go to Webmin -> Servers -> MySQL Database, and click on User Permissions.

  2. Click on ‘Create a new user’, and add an user with the username and password for the domain, but with the hosts set to something like 192.168.0.%

  3. Go back to the main page of the MySQL module, and click on Database Permissions.

  4. Click on ‘Create new database permissions’, and add a record for the domain’s database and user, with all permissions selected, and with the hosts set to 192.168.0.%

If you want all new domains to get acesss from 192.168.0.% by default, go to System Settings -> Server Templates -> Default Settings -> MySQL Database, and fill in the ‘Allowed MySQL client hosts’ field with 192.168.0.%

I admit that this is a fairly complex process, which is why I plan to add a more user-friendly single-page form for managing MySQL remote hosts to in the next Virtualmin release.

1 Like
9

ah great, i would never have figured out the 4 step plan.

10

Yeah the having to add a new user is what never would have occurred to me. Working now to so I am happy :slight_smile:

11

When accessing a MySQL database remotely with a new mysql user that has only been given access to one specific database why is that user also able to see the INFORMATION_SCHEMA database?

12
When accessing a MySQL database remotely with a new mysql user that has only been given access to one specific database why is that user also able to see the INFORMATION_SCHEMA database?

Where are they seeing it? In Webmin or in the MySQL line client on the remote machine?

13

Hi Joe,

I am able to see it when accessing the DB remotely using Navicat.

14

I followed the procedure for adding a user, etc. I can get access with that user with Navicat. However, I also see ALL databases on the server.

I don’t see any options in the webmin MySql section to limit a user to a single database, or group of databases.

The user is one that I created specifically for accessing database, under the website that I want to restrict to.

Is there a link into the wiki with information on this, perhaps?

15

When you defined the DB for that user to access in the Database Permissions section did you click the “Selected” radio button next to the chosen database? If you just select a DB and don’t click the radio button the default of “Any” will be selected and that user will be able to access all DBs remotely.

16

There is no "database permissions" section when I create a new user (per the instructions). I am using the latest Webmin and Virtualmin on RHEL4

Also, I have not been able to find a place to ALTER the user’s database restrictions, etc.

You can see what I get when I create a user on the attached screen section.

17

This should help:
http://www.velvetpixel.com/image/mysql_module.gif

18

Hey tabletguy,

If you have updated to the new version of Virtualmin at 3.58 this is automated now.

In virtualmin just select the domain you want to work with then click on Edit Databases and you will now see a new tab labeled Remote Hosts.

In the data window you will see:

localhost

Just add the IPs of the remote hosts you want to allow access to the DBs for that domain so the list looks like this:

localhost
xx.xx.xx.xx
zz.zz.zz.zz

and click save.

19

This new remote host function is very welcome. However, I have found that I have to comment out the bind-address = 127.0.0.1 and skip-networking in my.cnf as follows

bind-address = 127.0.0.1

skip-networking

Is this correct or am I missing something?
Also I checked iptables (iptables -L) and there are no rules set in the firewall to allow access to port 3306, therefore my question is how do you allow access to port 3306.

20

Yeah, I guess the above option makes the assumption that you’ve already configured MySQL to listen for remote connections.

That might not be a terrible idea for it to at least notify you when using the Remote Host function that MySQL isn’t listening for remote connections – perhaps you could pop a feature request in the Issue Tracker for that.

In any case, if you edit the my.cnf, you may need to add this in place of the bind-address you commented out:

bind-address = 0.0.0.0

Just remember that doing so makes MySQL visible to the world. I wouldn’t be comfortable doing that on my own box :slight_smile:

What I might do is determine who needs access to MySQL, and make sure they’re on a static IP – then use iptables to limit access to just those connections.
-Eric