解决MySQL拒绝远程连接的问题

刚装好的MySQL是不允许远程访问的，如果远程访问，会报类似：“Host'XXX' is not allowed to connect to this MySQL server”这种错误，解决这个问题有几种方法：

1.改表法；

2.授权法；

 

我这里演示授权法：

1.用root用户连接MySQL

[root@localhost ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 17

Server version: 5.6.31 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, Oracle and/or itsaffiliates. All rights reserved.

 

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarks of theirrespective

owners.

 

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

 

2.创建新的数据库

mysql> create database mydb;

Query OK, 1 row affected (0.01 sec)

 

2.1.查看数据库

mysql> show databases;

+-----------------------------------+

| Database                              |

+-----------------------------------+

| information_schema         |

| mydb                                    |

| mysql                                    |

| performance_schema       |

+-----------------------------------+

4 rows in set (0.01 sec)

 

3.切换到mysql数据库

mysql> use mysql;

Database changed

 

4.执行授权语句

mysql> grant all PRIVILEGES onmydb.* to root@'192.168.1.107' identified by 'rootroot';

或者：

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

grant all privileges on *.* to 'root'@'%' identifiedby 'rootroot' with grant option;

 

Query OK, 0 rows affected (0.00 sec)

 

说明：

格式：GRANTSELECT,INSERT,UPDATE,DELETE ON [db_name].* TO [username]@[ipadd] identified by '[password]';

如果授予所有权限，则不必写SELECT.INSERT等等，只要写PRIVILEGES即可

[username]:远程登入的使用者代码

[db_name]:表示欲开放给使用者的数据库称

[password]:远程登入的使用者密码

[ipadd]:IP地址或者IP反查后的DNS Name，此例的内容需填入'60-248-32-13.HINET-IP.hinet.ne\t'，包函上引号(')

 

5.刷新权限，立即生效

mysql> FLUSH   PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

 

这个时候，远程客户端就可以连接MySQL了