MySQL远程访问权限，同时允许远程连接和本地连接

1、登陆mysql 数据库    

    mysql -u root -p

mysql> use mysql;

Database changed

mysql> select host,user,password from user;

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

| host         | user | password                                  |

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

| localhost    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
| centos       | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |

| 127.0.0.1    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |

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

2 rows in set (0.00 sec)

 

   可以看到在user表中已创建的root用户。host字段表示可以登录的主机，其值可以用IP，也可用主机名，现在是有三个本地HOST可以登录，现在可以将host为centos改为远程主机都可以访问，即
 update user set host = ’%’ where user = ’root’ and host = ‘centos’；

改后表结构：

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

| host         | user | password                                  |

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

| localhost    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
| %            | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |

| 127.0.0.1    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |

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

此时本地主机localhost和127.0.0.1可用于本地登录，%表示允许所有远端登录

然后执行

mysql> flush privileges;     立即生效

mysql> quit;

现在便可在本地或者远端登录了。

   
   

    
    2、实现远程连接(授权法)
   
   
   
   

    
       将host字段的值改为%就表示在任何客户端机器上能以root用户登录到mysql服务器，建议在开发时设为%。   
   
   
   
   

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

    
       将权限改为ALL PRIVILEGES
   
   
   
   

    
    mysql> use mysql;
   
   
   
   

    
    Database changed
   
   
   
   

    
    mysql> grant all privileges  on *.* to root@'%' identified by "root";
   
   
   
   

    
    Query OK, 0 rows affected (0.00 sec)  www.2cto.com  
   
   
   
   

    
    mysql> select host,user,password from user;
   
   
   
   

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

    
    | host         | user | password                                  |
   
   
   
   

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

    
    | localhost    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
   
   
   
   

    
    | 127.0.0.1    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
   
   
   
   

    
    | %            | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
   
   
   
   

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

    
    3 rows in set (0.00 sec)
   
   
   
   

    
    这样机器就可以以用户名root密码root远程访问该机器上的MySql.
   
   

| localhost    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |