【mysq 5.7.31】远程访问权限（允许远程连接）

前言

• mysql 5.7.31
• centos 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";
FLUSH PRIVILEGES;

实现远程连接（改表法）

将host字段的值改为%，表示在任何ip上均能以root用户登录到mysql服务器。

use mysql;
update user set host = '%' where user = 'root';

错误处理

mysqladmin flush-hosts