连接MySQL报错，is not allowed to connect to this MySQL server

问题描述：

        本机装的MySQL数据库，本机可以正常连接，其他机器访问报错，is not allowed to connect to this MySQL server，防火墙等其他策略均配置没问题。

 解决方案：

        出现该问题的原因是，MySQL数据库只允许自身所在的本机器连接，不允许远程连接。

1、在MySQL所在服务器上使用命令登录到MySQL数据库中

mysql -u root -p

2、选择mysql数据库，并查询权限

use mysql;
 
select host from user where user='root';

可以看到，执行查询语句后得到的数据结果中 host 的值是 localhost

我们执行update语句修改权限

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

3、刷新配置

flush privileges;

再次执行查询权限语句

select host from user where user='root';

可以看到，已经修改成功

4、最后再次用其他机器连接MySQL数据库，可以连接成功

DBMS: MySQL (版本 5.7.41)

区分大小写: 普通形式=lower, 分隔形式=lower

驱动程序: MySQL Connector/J (版本 mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)，JDBC4.2)

Ping: 29毫秒

SSL: yes

 

 


  <!-- localRepository
   | The path to the local repository maven will use to store artifacts.
   |
   | Default: ${user.home}/.m2/repository
  /path/to/local/repo
  -->
 
  <!-- interactiveMode
   | This will determine whether maven prompts you when it needs input. If set to false,
   | maven will use a sensible default value, perhaps based on some other setting, for
   | the parameter in question.
   |
   | Default: true
  true
  -->
 
  <!-- offline
   | Determines whether maven should attempt to connect to the network when executing a build.
   | This will have an effect on artifact downloads, artifact deployment, and others.
   |
   | Default: false
  false
  -->
 
  
  
    <!-- pluginGroup
     | Specifies a further group identifier to use for plugin lookup.
    com.your.plugins
    -->
  
 
  
  
    <!-- proxy
     | Specification for one proxy, to be used in connecting to the network.
     |
    
      optional
      true
      http
      proxyuser
      proxypass
      proxy.host.net
      80
      local.net|some.host.com
    
    -->
  
 
  
  
    <!-- server
     | Specifies the authentication information to use when connecting to a particular server, identified by
     | a unique name within the system (referred to by the 'id' attribute below).
     |
     | NOTE: You should either specify username/password OR privateKey/passphrase, since these pairings are
     |       used together.
     |
    
      deploymentRepo
      repouser
      repopwd
    
    -->
 
    <!-- Another sample, using keys to authenticate.
    
      siteServer
      /path/to/private/key
      optional; leave empty if not used.
    
    -->
  
 
  
  
    <!-- mirror
     | Specifies a repository mirror site to use instead of a given repository. The repository that
     | this mirror serves has an ID that matches the mirrorOf element of this mirror. IDs are used
     | for inheritance and direct lookup purposes, and must be unique across the set of mirrors.
     |
    
      mirrorId
      repositoryId
      Human Readable Name for this Mirror.
      http://my.repository.com/repo/path
    
     -->
    
      maven-default-http-blocker
      external:http:*
      Pseudo repository to mirror external repositories initially using HTTP.
      http://0.0.0.0/
      true
    
  
 
  
  
    <!-- profile
     | Specifies a set of introductions to the build process, to be activated using one or more of the
     | mechanisms described above. For inheritance purposes, and to activate profiles via 
     | or the command line, profiles have to have an ID that is unique.
     |
     | An encouraged best practice for profile identification is to use a consistent naming convention
     | for profiles, such as 'env-dev', 'env-test', 'env-production', 'user-jdcasey', 'user-brett', etc.
     | This will make it more intuitive to understand what the set of introduced profiles is attempting
     | to accomplish, particularly when you only have a list of profile id's for debug.
     |
     | This profile example uses the JDK version to trigger activation, and provides a JDK-specific repo.
    
      jdk-1.4
      
        1.4
      
      
        
          jdk14
          Repository for JDK 1.4 builds
          http://www.myhost.com/maven/jdk14
          default
          always
        
      
    
    -->
 
    <!--
     | Here is another profile, activated by the system property 'target-env' with a value of 'dev',
     | which provides a specific path to the Tomcat instance. To use this, your plugin configuration
     | might hypothetically look like:
     |
     | ...
     | 
     |   org.myco.myplugins
     |   myplugin
     |
     |   
     |     ${tomcatPath}
     |   
     | 
     | ...
     |
     | NOTE: If you just wanted to inject this configuration whenever someone set 'target-env' to
     |       anything, you could just leave off the  inside the activation-property.
     |
    
      env-dev
      
        
          target-env
          dev
        
      
      
        /path/to/tomcat/instance
      
    
    -->
  
 
  <!-- activeProfiles
   | List of profiles that are active for all builds.
   |
  
    alwaysActiveProfile
    anotherAlwaysActiveProfile
  
  -->

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

Sql代码 复制代码

   1. mysql -u root -pvmwaremysql>use mysql; 

   2. mysql>update user set host = ‘%’ where user = ‘root’; 

   3. mysql>select host, user from user; 

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

Sql代码 复制代码

  1. GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@’%’ IDENTIFIED BY ‘mypassword’ WITH

      GRANT OPTION;  

  2.FLUSH   PRIVILEGES;