| 【mysq 5.7.31】远程访问权限（允许远程连接） | 您所在的位置：网站首页 › 远程连接域名怎么设置密码 › 【mysq 5.7.31】远程访问权限（允许远程连接） |
|
前言
mysql 5.7.31centos 7.0默认情况下，mysql帐号不允许从远程登陆，只能在localhost登录。在开发环境中，允许任意ip可以连接数据库。生成环境中，应使用白名单方式，指定允许的ip。
登陆mysql数据库
mysql -u root -p
查看user表
mysql> use mysql;
Database changed
mysql> select host,user,authentication_string from user;
+-----------------------+------+-------------------------------------------+
| host | user | authentication_stringfrom |
+-----------------------+------+-------------------------------------------+
| localhost | root | *458972544F6E8F1AE69FBC6AF34CCF049417D39D |
| localhost.localdomain | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| localhost.localdomain | | |
+-----------------------+------+-------------------------------------------+
6 rows in set (0.04 sec)
5.7版本以前看select host,user,password from user;
从上面的结果可以看出，仅允许从localhost使用root账户登录数据库。 实现远程连接(授权法)允许root用户从任意ip登录（任意Ip用%表示），权限为ALL PRIVILEGES，密码为123456（密码可以改成自己喜欢的） mysql> use mysql; Database changed mysql> grant all privileges on *.* to root@'%' identified by "123456"; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select host,user,authentication_string from user; +-----------------------+------+-------------------------------------------+ | host | user | authentication_stringfrom | +-----------------------+------+-------------------------------------------+ | localhost | root | *458972544F6E8F1AE69FBC6AF34CCF049417D39D | | localhost.localdomain | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | localhost.localdomain | | | | % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------------------+------+-------------------------------------------+ 7 rows in set (0.00 sec) 5.7版本以前看select host,user,password from user;如需限定ip，则将“root@‘%’”中的%改为所需的ip即可，比如： grant all privileges on *.* to root@'192.168.1.22' identified by "123456"; 实现远程连接（改表法）将host字段的值改为%，表示在任何ip上均能以root用户登录到mysql服务器。 use mysql; update user set host = '%' where user = 'root'; 错误处理 mysqladmin flush-hosts |
| CopyRight 2018-2019 实验室设备网 版权所有 |