Remote Mysql Access Help?

3 posts / 0 new
Last post
#1 Thu, 05/31/2018 - 01:05
pomtom44

Remote Mysql Access Help?

Hi all I assume this is the place to put this topic?

Note: i have removed names for privacy at this stage

Im trying to configure a database for remote access, and while I can easily do it though the command line, I was looking for a way to do it though webmin. I have the database created, and the user created, and can access it fine on the local PC either though PHP or PHPmyadmin.

Side note, not sure if important. The user was created though virtualmin domain users so their username is user@domain.com which works fine when using localhost scripts. And I have given access to the database within this settings page (Other user permissions)

However I seem to have issues with remote access.

I have gone into the Webmin -> Servers -> MySQL Server page and this is where I am changing things as thats where I assume things need changing. I have given the user any host permission (though edit users) and I have given the user access to the database from any host by going into database permissions, finding the user and changing the hosts to any.

however my remote script still returns "Access denied for user 'user@domain.com'@'%' to database 'Database name' How do I allow remote access to a database for a user?

Following question once this is working, I want to lock remote access to only a single host (with a known static IP) I assumed I went into the user and database permissions again and changed the hosts to have localhost and the IP, I can set the database hosts fine by entering "localhost IPaddress" and it saves and shows both hosts but when I change it for the user I get Failed to save user : Missing or invalid allow host(s)

I have no way of testing if this works as above I cant connect remotely at all. But once thats working, I want to get the remote access locked down

I hope this all makes sense and that someone can help me, as google doesn't seem to be returning much help (That or I just dont know how to word it on google) Thanks in advance

Thu, 05/31/2018 - 01:12
pomtom44

Nevermind Found I had a small typo on my database name, which is why it wasnt connecting. So thats that problem solved.

However Im still unsure how to restrict access to the database to a single IP I know I can enter that IP and it will stop access from everyone else. But that also blocks Localhost How do I allow both?

Thu, 05/31/2018 - 13:18
scotwnw

You'd need to restrict access to mysql via firewall on the msql remote machine.

Only accept connections to the mysql port if they are from the correct IP.

And as long as local host accept line is above the deny lines in the firewall, local host wont be denied. 'Localhost accept' is generally the 1st line in the firewall anyway.

Topic locked