1

A mysql server with default settings is listening on a port on a machine: 

$ nmap -p0-65535 192.168.1.97

Starting Nmap 7.60 ( https://nmap.org ) at 2019-03-20 14:34 EDT
Nmap scan report for ocean.fios-router.home (192.168.1.97)
Host is up (0.00036s latency).
Not shown: 65533 closed ports
PORT      STATE SERVICE
22/tcp    open  ssh
3306/tcp  open  mysql
33060/tcp open  mysqlx

Nmap done: 1 IP address (1 host up) scanned in 4.50 seconds

I can't connect to the mysql server from another machine:

$ mysql  --user=root --host=ocean --password test
Enter password: 
ERROR 1130 (HY000): Host 'olive.fios-router.home' is not allowed to connect to this MySQL server

How shall I understand the two contradictory results?

Did mysql server receive the connection request, but then refuses it?

If it doesn't accept nonlocal connection request, why does it listen on a tcp port?

Thanks.

Improve this question
3
  • 4
    Possible duplicate of Why is mysql server accepting nonlocal request, while postgresql server is not? – user34720 Mar 20 '19 at 22:04
  • @nwildner, that one looks like it's about what address mysql is listening to, this seems to be about what it does when it gets a connection. That's not really the same. – ilkkachu Mar 21 '19 at 18:47
  • Maybe not the exact same question(2 or 3 words different), but it seems from the title, the time between those question and being the same user, that he just asked this question before even trying to use what was said on that queston. Take your time to look at both and my comments at the answer of that other question... – user34720 Mar 21 '19 at 21:00
2

That's because what MySQL does consider as a users. In MySQL You grant privileges to user+host combination written as user@host.

Following each of following examples are is different

GRANT ALL PRIVILEGES ON db.* TO 'user1'@'%';
GRANT ALL PRIVILEGES ON db.* TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON db.* TO 'user1'@'192.168.1.97';

Each user+host combination can have a different password (… if append IDENTIFIED BY 'your-password';

You can find more examples and better description in MySQL GRANT documentation

Improve this answer
2

Well, it seems that you have duplicated your question instead of editing the original one.

If you have edited bind-address as said at the other question, your service should not listen to other ip address than the one configured on that parameter.

Second, you are not allowed to connect to this server because you need to also provide access at the application level of MySQL.

At the local shell of that remote server, access your mysql prompt and execute the following

`GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';`

You seem to be missing 2 basic things at MySQL: network configuration and application level authorization/permission. I suggest you to study that technology further.

Google it further by ERROR 1130 (HY000): Host is not allowed. You will be surprised that the FIRST RESULT of that search will also lead you to StackExchange. Here: ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL server [duplicate]

Improve this answer
4
  • Thanks. If it doesn't accept nonlocal connection request in terms of application level authorization/permission, why does it listen on a tcp port? – Tim Mar 20 '19 at 22:09
  • Cause to make it listen to other addresses than 0.0.0.0(all addresses at all interfaces) you need to edit your my.cnf file and configure it no NOT LISTEN to all existing addresses you have. I have already answered that at the other question, and even explained WHY this behavior exists. Take your time to read it carefully – user34720 Mar 20 '19 at 22:10
  • Maybe I wasn't clear. If mysql server doesn't grant application level authorization/permission to nonlocal authentication request, why does it bother to listen at a TCP port? – Tim Mar 20 '19 at 22:15
  • Cause, again, you need to configure your my.cnf to bind ONLY no localhost ip address(127.0.0.1). Otherwise, it will bind to ALL existing IP addresses of your computer.... – user34720 Mar 20 '19 at 22:20

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.