一、问题描述
- 我们需要登录另一台服务器上的,我们需要通过IP来登录mysql,而非使用localhost
- 业务服务器和数据库服务器不在同一台机上的情况
二、解决思路
mysql -u root -p
mysql> select Host,User,plugin from user;
+---------------+------------------+-----------------------+
| Host | User | plugin |
+---------------+------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+---------------+------------------+-----------------------+
CREATE USER 'admin'@'192.168.0.221' IDENTIFIED WITH mysql_native_password BY '123456';
Host:192.168.0.221
User:admin
password: 123456
身份认证插件:mysql_native_password 也可以是 caching_sha2_password (仅mysql8.0.4以后版本可用)
mysql> select Host,User,plugin from user;
+---------------+------------------+-----------------------+
| Host | User | plugin |
+---------------+------------------+-----------------------+
| 192.168.0.221 | admin | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+---------------+------------------+-----------------------+
- 给admin用户 增加访问数据库的权限(以下操作还会修改admin 的登录密码,所以建议和创建admin的密码保持一致)
grant all privileges on dbdata.*@'%' to admin identified by '123456';