ERROR 1130 (HY000): Host 'Lenovo-PC' is not allowed to connect to this MySQL server
MySQL授权允许连接之ERROR 1130 (HY000)
C:\Users\Lenovo>mysql -h 10.255.9.79 -u root -p034039
Warning: Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'Lenovo-PC' is not allowed to connect to this MySQL server
C:\Users\Lenovo>mysql -h localhost -u root -p034039
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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>
大家从上面这段信息中可以看出当使用h参数为本机的ip地址时,mysql不允许该客户端连接,这是为什么呢?
看下面这段sql查询:
mysql> use mysql
Database changed
mysql> select user,host from user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
root用户只允许在127.0.0.1和localhost上进行连接,而不允许使用本机在局域网中的ip进行连接。。。
这样就明白为什么不允许连接的原因了。
下面就要给root用户相应的登陆权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.255.9.79' IDENTIFIED BY '034039' WITH GRANT OPTION;
Query OK, 0 rows affected (0.23 sec)
mysql> select user,host from user ;
+------+-------------+
| user | host |
+------+-------------+
| root | 10.255.9.79 |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
+------+-------------+
5 rows in set (0.00 sec)
mysql>
通过这条授权语句,
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.255.9.79' IDENTIFIED BY '034039' WITH GRANT OPTION;
允许地址10.255.9.79上用root用户,密码034039来连接mysql的所有数据库,付给select,insert,update,delete权限。
最后刷新一下权限:
mysql> flush privileges;
Query OK, 0 rows affected (0.68 sec)
OK,这样就完成了授权。
C:\Users\Lenovo>mysql -h 10.255.9.79 -u root -p034039
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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>
=======END=======