These forums are locked and archived, but all topics have been migrated to the new forum. You can search for this topic on the new forum: Search for How to configure MySQL for remote access? on the new forum.
Web Hosting and Cloud Computing Control Panels
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.
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.
did you double check the correct login and password :)
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."
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)
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 :)
Ronald thanks for trying! I appreciate the help :)
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. <#>_<#>
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. [img size=581]http://www.virtualmin.com/components/com_fireboard/uploaded/images/Defin...
This should help:
http://www.velvetpixel.com/image/mysql_module.gif
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.
Yeah, I just wanted to chip in that I am having trouble with this my hoster, too.
I have been "spoiled" by cPanel, where I just select Remote MySQL, and put in my IP from DHCP server. When the DHCP lease expires, I just update the setting. Requiring that anyone who accessed a database remotely have a static IP sounds kind of draconian to me. A zillion cPanel providers feel that it's not an unreasonable security risk to allow their users to enter arbitrary IP ranges for access, apparently, including %.%.%.%, but my host has not enabled the Remote Access tab- either he disagrees or just hasn't been asked to enable it.
This could be a deal breaker for me, if I don't get this function. I guess I just have to ask my host about the feature?
Yeah, ratchet up the DWIM, for sure :) - just make it work.
There is another acronym...DWIAUT- Do What I Am Used To that applies, too, as you try to drag people over from other panels.
Thanks,
K
Can't edit that last post- pops up "you've found a bug in the software".
Anyway, it looks like at http://www.virtualmin.com/forums/webmin/re%3ahow-to-configure-mysql-for-...
that Jamie intended to address this a few months ago, but the status of that enhancement is not clear.
Thanks!
K
Thanks for the help ronald :)
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 :)
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. :(
How can I have both working at the same time?
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 :)
<div class='quote'>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.</div>
ah great, i would never have figured out the 4 step plan.
Yeah the having to add a new user is what never would have occurred to me. Working now to so I am happy :)
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?
<div class='quote'>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?</div>
Where are they seeing it? In Webmin or in the MySQL line client on the remote machine?
--
Check out the forum guidelines!
Hi Joe,
I am able to see it when accessing the DB remotely using Navicat.
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?
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.
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.
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 :-)
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
As I understand it, commenting our the bind-address is the same as setting it as 0.0.0.0
As regards iptables, you are 100% right. Though this takes a little explanation for any changes need to be saved else will be lost on reboot.
I am making a little tutorial on this and will add the link when I have it right. I imagine that cPanel do all this automatically. Thus any feature request that checks the bind-address / or sets the bind-address at the same time as allowing a remote connection should IMO add a specific rule to the iptable and block all others.
Sure -- if you haven't already, pop a feature request into the Bugs and Issues tracker (the link is below). Having Virtualmin be a little more "DWIM" may not be a bad idea, but we'll see what Jamie thinks :-)
-Eric
This issue has surfaced again, as I am trying to insert HTML pages in the database of a CMS system.
Bump!?
I'll also check that I posted this as an FR...thought I did...
The "four step plan" from velvetpixel above wasn't necessary for me - simply adding the remote IP I want to allow access using Virtualmin/Edit Databases/Remote Hosts did all that (Virtualmin 3.97.gpl).
For me, the missing step was that you have to go to Webmin/Servers/MySQL Database Server/MySQL Server Configuration/MySQL server listening address and change it to "any".
Also, don't forget to enable connections through the firewalls at both ends.
(This is not a very secure configuration for production use - at the very least, you should probably change the default port.)
Phil McKerracher www.beeches.it