摘要: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一致,不输入密码没有权限