【开放mysql远程连】 报错:1130-host ... is not allowed to connect to this MySql server 不使用localhost

开放mysql远程连接

报错:1130-host … is not allowed to connect to this MySql server

解决方法：

1. 改表法。

可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 “mysql” 数据库里的 “user” 表里的 “host” 项，从”localhost”改称”%”

mysql -u root -pvmware

mysql>use mysql;

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

mysql>select host, user from user;

2. 授权法。

例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。

GRANT ALL PRIVILEGES ON . TO ‘myuser’@’%’ IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;

FLUSH PRIVILEGES;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器，并使用mypassword作为密码

GRANT ALL PRIVILEGES ON . TO ‘myuser’@’192.168.1.3’ IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;

FLUSH PRIVILEGES;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器的dk数据库，并使用mypassword作为密码

GRANT ALL PRIVILEGES ON dk.* TO ‘myuser’@’192.168.1.3’ IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;

FLUSH PRIVILEGES;

原作者心得

我用的第一个方法,刚开始发现不行,在网上查了一下,少执行一个语句 mysql>FLUSH RIVILEGES 使修改生效.就可以了

另外一种方法,不过我没有亲自试过的,在csdn.net上找的,可以看一下.

在安装mysql的机器上运行：

d:\mysql\bin\>mysql   -h   localhost   -u   root //这样应该可以进入MySQL服务器

mysql>GRANT   ALL   PRIVILEGES   ON   *.*   TO   'root'@'%'   WITH   GRANT   OPTION //赋予任何主机访问数据的权限

mysql>FLUSH   PRIVILEGES //修改生效

mysql>EXIT //退出MySQL服务器

这样就可以在其它任何的主机上以root身份登录啦！

现在是我呃。。

第一个改表法我试了并没有成功。用了第二个方法成功了，记住，千万不要把linux的root账户密码当成MySQL的root账户密码输进去，当然你要两个设置的一样当我白说。

[root@VM_156_134_centos html]# mysql -u root -p  //进入mysql
Enter password:                                 //输入密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;                               //这里~~
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host = '%' where user = 'root';
ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
mysql> update user set host = '%' where user = 'root';
ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'  //第一个方法失败的经历
mysql> GRANT   ALL   PRIVILEGES   ON   *.*   TO   'root'@'%'   WITH   GRANT   OPTION
    -> ;                                  //第二个方法
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;                    //不要忘记这步，更新权限
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye
[root@VM_156_134_centos html]# service mysqld restart     //重启MySQL服务
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]