原创

配置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 -pEnter 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服务是否开启?②防火墙是否配置?③安全组是否配置?(非必须)④是否进行授权

正文到此结束