root用户允许远程登录不限制IP
root目前只有本地localhost可以登录
mysql> select user,host,authentication_string from user where user='root';
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)
配置一下
mysql> grant all privileges on *.* to 'root'@'%' identified by "设置远程登录的密码" with grant option; ##命令进行授权
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>flush privileges; ##重载授权表
Query OK, 0 rows affected (0.00 sec)
查询一下root发现host哪里已经有了%,就可以访问了
mysql> select user,host,authentication_string from user where user='root';
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql>exit ##退出数据库
root用户在特定的IP进行远程登录(具有所有库任何操作权限)
*.* ##代表所有资源所有权限
'root'@'%' ##root代表账户名,%代表所有的访问地址。
identified by '密码' ##设置密码
with grant option ##允许级联授权。
在本机上登录mysql的root账号
root ]# mysql -uroot -p "你的root密码"
mysql> grant all privileges on *.* to 'root'@'166.85.36.2' identified by "设置远程登录的密码" with grant option; ##命令进行授权
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>flush privileges; ##重载授权表
Query OK, 0 rows affected (0.00 sec)
mysql>exit ##退出数据库
root用户在特定的IP进行远程登录(具有所有库指定操作权限)
在本机上登录mysql的root账号
root ]# mysql -uroot -p "你的root密码"
mysql> grant select,insert,update,delete on *.* to 'root'@'166.85.36.2' identified by "设置远程登录的密码"; ##命令进行授权
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>flush privileges; ##重载授权表
Query OK, 0 rows affected (0.00 sec)
mysql>