配置MySQL开启远程连接的方法

1.需求

两台服务器“centos1（阿里云）”和“centos2（腾讯云）”，其中数据库在“centos1”上，当前需要在“centos2”上远程链接“centos1”的数据库。

2.授权“centos2”

登录“centos1”服务器，用root账户登录mysql查看当前已被授权的用户

mysql> select Host,User,Password from mysql.user;
+-----------------+------+-------------------------------------------+
| Host            | User | Password                                  |
+-----------------+------+-------------------------------------------+
| localhost       | root | *94D9CC39FA9435D611421A4E8BA68C659AD46961 |
| iz2ze9hcysthc1c | root | *94D9CC39FA9435D611421A4E8BA68C659AD46961 |
| 127.0.0.1       | root | *94D9CC39FA9435D611421A4E8BA68C659AD46961 |
| ::1             | root | *94D9CC39FA9435D611421A4E8BA68C659AD46961 |
+-----------------+------+-------------------------------------------+
4 rows in set (0.01 sec)

授权新的用户

mysql> grant all privileges on *.* to 'root'@'ip' identified by 'password' with grant option;
Query OK, 0 rows affected (0.00 sec)

all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。

on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写*.*表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.user

to：将权限授予哪个用户。格式：”用户名”@”登录IP或主机名”。%表示没有限制，在任何主机都可以登录。比如：”zhyd”@”192.168.1.%”，表示zhyd这个用户只能在192.168.1IP段登录。为了安全起见，请使用指定ip或者主机进行授权

identified by：指定用户的登录密码

with grant option：表示允许用户将自己的权限授权给其它用户

刷新权限

flush privileges;

查看用户权限

mysql> show grants for 'root'@'ip';
+---------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@ip                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip' IDENTIFIED BY PASSWORD '*94D9CC39FA9435D611421A4E8BA68C659AD46961' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.链接远程数据库

在“centos2”服务器上尝试链接“centos1”的数据库

[root@xxx blog]# mysql -h'centos1_ip' -uroot -p Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'centos1_ip' (110)

链接失败！

验证“centos1”服务器3306接口是否对外开放

telnet 'centos1_ip' 3306
Trying 'centos1_ip'...
telnet: connect to address 'centos1_ip': Connection timed out

链接失败！

在“centos1”中添加防火墙规则，开放3306端口

vi  /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
systemctl restart iptables

再次使用“telnet”尝试，结果依然是链接超时！

按说服务器端的配置已经完成了：mysql服务开启、分配用户、开放端口都已经没问题，但是此时仍旧连不上。因为涉及到两个不同的服务器提供商，且每个服务器提供商都对应一套安全组规则，所以尝试修改“centos1（阿里云）”服务器的安全组。增加一条入站规则，入站端口设置为3306/3306，授权对象为“centos2（腾讯云）”服务器的ip地址，如下图所示

再次使用“telnet”尝试，提示连接成功！

在“centos2”上链接“centos1”的数据库

[root@xx www]# mysql -h'centos1_ip' -p3306 -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 597
Server version: 5.xx MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

OK，大功告成！

4.注意问题

1.授权用户务必遵循最小权限化原则

2.远程无法连接mysql时，可从四方面入手排查：①mysql服务是否开启？②防火墙是否配置？③安全组是否配置？（非必须）④是否进行授权