MySQL 创建远程登陆用户并授权

用root连接上数据库进行查看user表的情况

mysql> select Host,User from mysql.user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| localhost | java_test     |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

一共又4个mysql账号,Host列可以看出来,这些账号只支持服务器本机连接,现在来创建一个远程用户;

mysql> create user test identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select Host,User from mysql.user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | test          |
| localhost | java_test     |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
5 rows in set (0.00 sec)

创建完成,在程序里面连接发现还是没有权限,刚才我们知识创建了用户,还没有对这个用户分配权限

mysql> grant all privileges on *.* to 'test'@'%' identified by '123456' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

all代表接受所有操作,比如 select,insert,delete…; . 代表所有库下面的所有表;% 代表这个用户允许从任何地方登录;为了安全期间,这个%可以替换为你允许的ip地址;

然后刷新mysql用户权限相关表

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)