MySQL connection timeouts

Question

Apparently, a former DB administrator configured our main DB server (located in Germany) so that it only would be accessable from certain IPs. 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 so. 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 grants or denies access to the database?

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.

Answer 1

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.

Answer 2

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.