0

When trying to connect to MySQL running on a local network I'm getting the error message:

Host 'XXX' is not allowed to connect to this MySQL server.

Disclaimer (MySQL Ver 8)

The answers provided bellow do not solve te problem in MySQL v8

Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

host 'localhost' is not allowed to connect to this MySQL server (#1130)

The solution provided in these old answers are returning the following error in MySQL Ver 8.0.13.

GRANT ALL PRIVILEGES ON mysql.* TO root@localhost IDENTIFIED BY 'pass123' WITH GRANT OPTION;

Or

Grant All Privileges ON *.* to 'root'@'%' Identified By 'pass123';

Output message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'pass123' WITH GRANT OPTION' at line 1

Azevedo
  • 2,059
  • 6
  • 34
  • 52

1 Answers1

1

See https://dev.mysql.com/doc/refman/8.0/en/grant.html

The GRANT syntax has changed in V8, you no longer need or are allowed the IDENTIFIED .. part.

CREATE USER 'fred'@'localhost' IDENTIFIED BY 'password';
GRANT All ON db.* to 'fred'@'%';

Or for anywhere on your local lan segment maybe something like this

GRANT All ON db.* to 'fred'@'10.0.0';
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
  • 1
    Indeed MySQL below version 8 allowed to automatic creating a user within the GRANT statement with `IDENTIFIED BY ...` – Raymond Nijland Jan 12 '19 at 16:15
  • `GRANT All ON db.* to 'fred'@'%'; ` should be `GRANT All ON db.* to 'fred'@'localhost'; ` is this example by the way.. – Raymond Nijland Jan 12 '19 at 16:16
  • Yea @RaymondNijland Was not sure if the OP wanted access from anywhere or just locally. As they say a server running on their network I assumed it was not going to be `localhost` – RiggsFolly Jan 12 '19 at 16:18
  • `localhost` will do for a LAN environment. Now it gives me: `ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'`. The root user is already there. I just need to grant the permissions. – Azevedo Jan 12 '19 at 16:58
  • `root` is automatically granted permissions to do anything. NOTE: You should not use `root` for normal access by for example a we site! Create an account specific to the app and grant it ONLY the rights and privilages it requires for normal useage – RiggsFolly Jan 13 '19 at 13:10