0

Apparently, a former DB administrator configured our main DB server (located in germany) so that only from certain IPs would be accessable. Now we just added a new host (which needs to access that data) and for the life of me I cannot get it to do it. php programs trying to connect just clock-out for about 30 seconds and then they time out with the following error:

[19-Jan-2016 13:35:51 America/New_York] PHP Warning: mysql_connect(): Connection timed out in /home/criterion/public_html/main_sms.php on line 10

I checked the IP tables and added the IP of the new client, to no avail.

Can anybody tell me WHERE is the "allowed IPs" list or something of that sort that grans or denies access to the dabase?

I did a " select * from user_privileges;" and it listed several records. The funny thing is that among the IPs listed there were not the ones that DO work. So that tells me that is not the place. I must be looking in the wrong place.

0

Inside MySQL, see the GRANTs. They are hopefully of the form of

GRANT ALL ON dbname.* TO user@'11.22.33.44' IDENTIFIED BY '...'.

Your probably may be solved as simply as copying that and changing the IP address. Well, change the IDENTIFIED BY part to IDENTIFIED BY PASSWORD('*1234ASDF...') -- the 41-char encrypted password you see in mysql.user.

Note: If you are on 5.7, or have an "authentication plugin" the instructions may be quite different.

Or...

It may be that the OS has firewall protections for port 3306 (or whatever MySQL is using), and you need to poke another hole in the firewall.

  • This is what I found on my "grants" setup: GRANT ALL PRIVILEGES ON . TO 'criterion'@'%' IDENTIFIED BY PASSWORD '*ADB60BA2CBB1B1EA40A2360020CFA6101D71883F' WITH GRANT OPTION <--- Shouldn't this allow everyone to connect? – Luis Jan 21 '16 at 19:00
  • ON . is wrong syntax. Perhaps you meant ON *.*? – Rick James Jan 22 '16 at 1:03
0

I'm sure this has been long since solved, but on 5.6 (running on Windows if that matters) at least, who can connect to a MySQL server is contained in SELECT * FROM mysql.user;, specifically the user and host columns. You can use wildcards (e.g., 192.168.1.%) to specify hosts within a range. I have not had to do multiple specific IPs so am unsure if that is possible.

If there are restrictions to specific schemas that is found with SELECT * FROM mysql.db;

All of this can be found in MySQL Workbench (I'm currently using v8.0.15) on the Administration tab on the left sidebar > Users and Privileges. Select a user and the Login tab and adjust the Limit to Hosts Matching: field or select the Schema Privileges tab to limit schema access as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.