2

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

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';
|improve this answer|||||
  • 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? – Server Admin Feb 3 '16 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 '16 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 – Server Admin Feb 3 '16 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 '16 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 admin@xx.xxx.x.xxx; fails with ERROR 1133 (42000): Can't find any matching row in the user table. – Server Admin Feb 4 '16 at 2:03

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.