摘要：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)]>
如果是高版本的MySQL8.0，认证方式有所变更，加密规则为caching_sha2_password，而在此之前的加密规则为mysql_native_password，报错如下
[root@ubuntu ~]# mysql -h192.168.67.72 -uroot -p123456
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
可以将加密规则改成mysql_native_password，在本地登录NySQL8.0，修改为mysql_native_password认证如下即可
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
