ERROR 1130 (HY000) or ERROR 2003 (HY000) : connect mysql from windows to ubuntu on VMware

Question

I tried many ways but failed.I want to connect the MySQL on Linux in VMware from Windows.

• VMware:11.1.0 build-2496824
• Linux:Ubuntu 14.10
• Windows:win 8.1 x64

• MySQL Version

  • windws: 5.6.24
  • Linux: 5.5.43

• IP

  • Windows:192.168.1.102
  • Linux: 192.168.1.100

PS: Ping requests receive packets from each other.

When I input the cmd:

mysql -h 192.168.1.100 -u root_test -p

i am sure i input the right pwd,but the mysql server returns "ERROR 1130 (HY000): Host '192.168.1.100' is not allowed to connect to this MySQL Server" or "ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.100' (10061)".

Proto   Recv-Q   Send-Q   Local Address      Foreign Address       State       PID/Program name
tcp          0        0   0.0.0.0:3306       0.0.0.0:*             LISTEN        -    



my.cnf:bind-address = 0.0.0.0

service iptables status
iptables: unrecognized service

select user,host from user;
+------------------+---------------+
| user             | host          |
+------------------+---------------+
| root             | 127.0.0.1     |
| root_test        | 192.168.1.102 |
| debian-sys-maint | localhost     |
| root             | localhost     |
+------------------+---------------+

show grants for 'root_test'@'192.168.1.102';

+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root_test'@'192.168.1.102' IDENTIFIED BY PASSWORD 'MD5-PWD'                                   |

+-------------------------------------------------------------------------------------------------------------------------------+

---

Update

I finally figured it out by changing the IPv4 properties Obtain an IP address automatically to use the following IP address on windows.

PS:VMware's internet setting is Bridged.

thank @s3v3n and @Drew Pierce very much,you are kind and friendly. thank you.

Answer 1

1) change my.conf (whatever your mysql conf file is called). And set bind-address to 0.0.0.0 as it is prob 127.0.0.1

2) stop/restart mysql daemon

Connections now are not limited to those from localhost. The default is localhost for obvious security reason until dev guy tweaks it

3) grants with flush

4) firewall

Good luck!

---

Edit:

shows info necessary in some environments to set up user / get in

mainly it shows how sqlyog can clue you into your hostname as seen or known by the mysql daemon (regardless of what you think your IP address is).

so I am sitting here thinking my external ip address on my workstation is 188.188.188.188 and I plow forward with that in my servers create user and grants and it doesn't work ultimately to get in to mysql.

I am also looking for tight security.

create an instance on aws ec-2 or your vm environment.

mine is created with public dns = ec2-23-21-7-136.compute-1.amazonaws.com

only port 22 open (ssh)

mysql is baked-in and running, blind to outside world.

run sqlyog to get in. it bombs. good. visible here

---

ssh/putty into or sit at server (mine is in Singapore, not sitting at) at linux prompt i get into mysql

so now i am at a mysql prompt (not OS, i know u know this someone else might not :>)

mysql> select user,host from mysql.user;
+------------------+------------------------+
| user             | host                   |
+------------------+------------------------+
| root             | 127.0.0.1              |
| foo_user         | w.x.y.z                |
+------------------+------------------------+
2 rows in set (0.00 sec)

---

go into vm and open port 3306 for inbound 0.0.0.0/0 (anywhere)

---

try sqlyog connect again for root.

bombs. good. the bomb gave us good info.

visible here

i know who mysql thinks i should be, glad root can't get in

'root'@'xxxx.yyyy.comcastbusiness.net'

---

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| foo                |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+
6 rows in set (0.00 sec)

---

so with that i create user

CREATE USER 'fred7'@'xxxx.yyyy.comcastbusiness.net' IDENTIFIED BY 'bonehead7';

and

GRANT ALL PRIVILEGES ON test.* TO 'fred7'@'xxxx.yyyy.comcastbusiness.net';

---

for those of you crazy about wildcards, make your security less tight

---

mysql> select user,host from mysql.user;
+------------------+-------------------------------+
| user             | host                          |
+------------------+-------------------------------+
| root             | 127.0.0.1                     |
| foo_user         | w.x.y.z                       |
| fred7            | xxxx.yyyy.comcastbusiness.net |
+------------------+-------------------------------+

---

try sqlyog connect again, this time for user=fred7 password=bonehead7 good.

i am in.

visible here

---

and there was much rejoicing (at least for me)