今天想使用本地Navicat 连接服务器上的Mysql总是报“1130-Host ... is not allowed to connect to this MySQL server”错误。
估计是帐号不允许从远程登陆,只能在localhost。
于是到服务器上查看:
mysql> select Host,User,Password from user order by user;
+-----------------------+---------+-------------------------------------------+
| Host | User | Password |
+-----------------------+---------+-------------------------------------------+
| localhost | | |
| localhost | zachaway | ***************** |
| localhost | root | ***************** |
| 127.0.0.1 | root | ***************** |
+-----------------------+---------+-------------------------------------------+
果然如此。
此时修改登录权限:
例如,如果想让myuser使用mypassword从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
安全性不高,慎用。
如果允许用户myuser从ip为10.30.28.2的主机连接到mysql服务器,并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'zachaway'@'10.30.28.2' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
此时,再次查看
mysql> select Host,User,Password from user order by user;
+-----------------------+---------+-------------------------------------------+
| Host | User | Password |
+-----------------------+---------+-------------------------------------------+
| localhost | | |
| localhost | zachaway | ***************** |
| 10.30.28.2 |zachaway | ***************** |
| localhost | root | ***************** |
| 127.0.0.1 | root | ***************** |
+-----------------------+---------+-------------------------------------------+
说明修改成功。使用Navicat连接测试,成功!
如果允许用户myuser从ip为10.30.28.2的主机连接到mysql服务器的import数据库,并使用mypassword作为密码
GRANT ALL PRIVILEGES ONimport.* TO 'myuser'@'10.30.28.2' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
==========================可爱的分隔符=======================
同理,可以直接修改“mysql”数据库中的“user”表。
mysql>use mysql;
mysql>update user set host = '10.30.28.2' where user = 'zachaway';
mysql>FLUSH PRIVILEGES;
此时,再次查看
mysql> select Host,User,Password from user order by user;
表同样已经被更改。