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 |
| 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’;
update user set host = ’%’ where user = ’root’ and host = ‘centos’;
改后表结构:
+--------------+------+-------------------------------------------+
| host | user | password |
+--------------+------+-------------------------------------------+
| localhost | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
| % | 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 PRIVILEGESmysql> use mysql;Database changedmysql> grant all privileges on *.* to root@'%' identified by "root";Query OK, 0 rows affected (0.00 sec) www.2cto.commysql> 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 |