【Linux】之远程连接 MySQL 服务器-蒲公英云

【Linux】之远程连接 MySQL 服务器

默认情况下，mysql 只允许本地登录，查询MySQL数据库是否允许远程ip访问,命令如下：

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+

如果查询结果为127.0.0.1或者localhost或者当前服务器域名，则表示不允许远程连接

（下面以 CentOS 7 为例，演示如何连接远程服务器的 MySQL，比如阿里云服务器）

一、修改/etc/mysql/my.conf**

vim /etc/my.cnf

找到bind-address = 127.0.0.1这一行
改为bind-address = 0.0.0.0即可

二、为需要远程登录的用户赋予权限

1、新建用户远程连接mysql数据库

grant all on *.* to admin@'%' identified by '123456' with grant option; 
flush privileges;

允许任何 ip 地址(%表示允许任何ip地址)的电脑用 admin 用户和密码(123456)来访问这个mysql server。

注意：admin 账户不一定要存在，这里只是授权给 admin 这个用户而已。

2、支持root用户允许远程连接mysql数据库

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;

三、再次查看系统用户

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+