mysql 2003 111_MySQL：解决ERROR 2003 (HY000): Can't connect to MySQL server on 'XXX' (111)

摘要：MySQL

想从远程集群上访问本地的MySQL数据库，输入host，用户和密码报错

ERROR 2003 (HY000): Can't connect to MySQL server on 'XXX.XXX.XX.XX' (111)

修改mysqld.cnf

修改mysqld.cnf允许其他机器ip访问。mysqld.cnf目录在/etc/mysql/mysql.conf.d下，如果设置了bind_address=127.0.0.1，只允许本地socket连接，将这一行注释掉

#bind-address = 127.0.0.1

重启mysqld

service mysql restart

重新连接MySQL继续报错，显示gp用户以host为cloudera01不允许链接

[root@cloudera01 pgeng]# mysql -h192.168.67.72 -ugp -p123456

ERROR 1130 (HY000): Host 'cloudera01' is not allowed to connect to this MySQL server

更改MySQL用户连接权限

更改MySQL用户连接权限使得用户在其他机器有访问权限。打开MySQL mysql库下的user表，查看user和host，gp用户默认host是localhost

mysql -uroot

mysql> select host,user from user;

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

| host | user |

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

| localhost | debian-sys-maint |

| localhost | gp |

| localhost | mysql.session |

| localhost | mysql.sys |

| localhost | root |

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

修改gp用户的host为%

mysql> update user set host='%' where user='gp';

Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

赋予全部权限给gp@%，使得gp用户可以在其他机器输入密码连接MySQL

mysql> grant all privileges on *.* to 'gp'@'%';

Query OK, 0 rows affected (0.00 sec)

重启MySQL

service mysql restart

再次在远程集群链接MySQL

[root@cloudera01 ubuntu]# mysql -hXXX.XXX.XX.XX -ugp -pXXXXXX

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

密码不变和本地机器连接MySQL一致，不输入密码没有权限