3

I have 2 servers with digitalocean - web (nginx) and database (mysql) servers - and can't get a connection from the web to the mysql server.

From what I've read in other SO posts on the topic, the error I'm getting is 'good' in that it shows mysql is trying to authenticate the connection, and the issue is very likely related to the mysql user, in particular the host or grant option related.

Here's mysql.user:

mysql> SELECT User,Host FROM mysql.user;

+-------+--------------+------------+
| User  | Host         | Grant_priv |
+-------+--------------+------------+
| root  | localhost    | Y          |
| root  | dev-db01     | Y          |
| root  | 127.0.0.1    | Y          |
| root  | ::1          | Y          |
|       | localhost    | N          |
|       | dev-db01     | N          |
| admin | %            | N          |
| admin | localhost    | N          |
| admin | xx.xxx.x.xxx | N          |
+-------+--------------+------------+

Some tutorials state that the remote user only needs the remote host entered, others the wildcard '%', and others '%' and localhost. I'd rather not use a wildcard host but just the internal IP of the web server xx.xxx.x.xxx. As you can see above I've tried all three, and before this, I've tried each separately.

I've also granted admin user root-like privileges with:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'xx.xxx.x.xxx' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

I'm not sure exactly what the Grant_priv column is supposed to mean, in particular the No against the admin user - does this mean 'WITH GRANT OPTION' didn't work? Even so, I wouldn't expect this to prevent the user from authenticating.

I haven't set up a firewall yet, so can telnet from the webserver to the mysql server, and connect before being rejected:

telnet xx.xxx.x.xx 3306

Trying xx.xxx.x.xx...
Connected to xx.xxx.x.xx.
Escape character is '^]'.
EHost 'xx.xxx.x.xx' is not allowed to connect to this MySQL serverConnection closed by foreign host.

My goal is to create a database via the remote connection from the web server to the mysql server using doctrine as part of a symfony app. Here's the relevant part of app/config/parameters.yml if helpful:

sudo vi app/config/parameters.yml
parameters:
database_driver:   pdo_mysql
database_host:     xx.xxx.x.xx
database_port:     3306
database_name:     appdatabasename
database_user:     admin
database_password: ~

As you can see, I've left the password empty (~ is interpreted as empty), but have also tried with a password (while updating admin user in mysql with same password).

Set up details:

  • VPS: DigitalOcean
  • MySQL Server Ver: 5.7
  • OS: FreeBSD 10.1

I'm really confused. Any ideas?

Thanks!

0

2 Answers 2

1

Have you tried updating the db?

You can use the following commands changing xx.xxx.x.xxx into the IP of the nginx server, and the placeholders for the real values:

mysql> update db set Host='xx.xxx.x.xxx' where Db='placeholder_db';
mysql> update user set Host='xx.xxx.x.xxx' where user='placeholder_user';
6
  • Hey thanks - I saw a post on the webs about this - but the issue I thought I'd have is that the db isn't created yet and the doctrine file I'm using will create it. Will your solution still work? Feb 3, 2016 at 2:10
  • I thought it was an existing database, have you setup the password for the remote user? You can do something like: mysql> GRANT ALL ON placeholder_db.* TO placeholder_user@'xx.xxx.x.xxx IDENTIFIED BY 'placeholder_password';
    – Douglas P.
    Feb 3, 2016 at 12:58
  • Thanks, I get this when trying that: mysql> GRANT ALL PRIVILEGES ON db.* TO 'admin'@'xx.xxx.x.xxx' WITH GRANT OPTION; ERROR 1133 (42000): Can't find any matching row in the user table Feb 3, 2016 at 23:54
  • I've found a similar question on Stack Overflow, please take a look at it, I guess your solution is there.
    – Douglas P.
    Feb 4, 2016 at 0:38
  • Thanks, I've tried the solution on that page but with no luck. The first two statements are fine - the db is created, and granting usage works too. However, the last statement grant all privileges on db.* to [email protected]; fails with ERROR 1133 (42000): Can't find any matching row in the user table. Feb 4, 2016 at 2:03
0

Entering the error into Google gives me this as 2nd hit:

https://confluence.atlassian.com/jirakb/configuring-database-connection-results-in-error-host-xxxxxxx-is-not-allowed-to-connect-to-this-mysql-server-358908249.html

Which says:

This error occurs due to the default configuration your MySQL database is currently using. This configuration allows connections only from the 'root' user when coming from 'localhost' and not other IP address ranges.

It also gives instructions how to solve this. This seems to indicate that the user you try to use is IP limited. There is a lot of instructions there, but due to copyright I am not really thinking about copy/pasting the whole article here.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .