mysql远程登陆以及授权

43234

设置远程登入
首先给客户机授权

1	[iyunv@gz2 ~]# mysql -uroot -p654321

1 2 3

mysql> grant all on *.* to 'root'@'192.168.1.181' identified by '654321'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges;               //刷新权限

1 2 3

mysql> use mysql Database changed mysql> select *from user where host='192.168.1.181'\G;

1	[iyunv@gz2 ~]# mysql -uroot -h192.168.1.181 -p3306 -p654321   远程登录测试

参考

mysql> grant all on *.* to 'root'@'%' identified by '654321';   把所有的IP授权登录
mysql> grant all on discuz.* to 'root'@'192.168.1.180 identified by '654321'; 指定授权一个表
mysql> grant all on *.* to 'root'@'192.168.1.% identified by '654321';  授权所有192.168.1.网段登录。









我们可以查看下当前用户

1 2 3 4 5 6 7

mysql> select user(); +---------------------+ | user()              | +---------------------+ | troot@192.168.1.181 | +---------------------+ 1 row in set (0.00 sec)

1 2 3

[iyunv@localhost ~]# mysql -uroot -h192.168.1.181 -p3306 -p Enter password: ERROR 1130 (HY000): Host '192.168.1.191' is not allowed to connect to this MySQL server

通过另一台机器连接MYSQL数据库报错：

1	ERROR 1130 (HY000): Host '192.168.1.191' is not allowed to connect to this MySQL server

说明所连接的用户帐号没有远程连接的权限，只能在本机(localhost)登录。
需更改 mysql 数据库里的 user表里的 host项把localhost改称%
首先按下面的步骤登录Mysql服务器
登录mysql需要切换到dos下的mysql的bin目录，进行如下操作：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

mysql> use mysql; Database changed mysql> update user set host = '%'  where user ='root'; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select host, user from user; +-----------+-------+ | host      | user  | +-----------+-------+ | %         | root | | 127.0.0.1 | root  | | gz2       |       | | gz2       | root  | | localhost |       | | localhost | root  | +-----------+-------+ 6 rows in set (0.00 sec) mysql> quit Bye

1 2 3 4 5 6 7 8 9 10 11 12 13

[iyunv@localhost ~]# mysql -uroot -h192.168.1.181 -p3306 -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.1.73 MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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.

OK。远程连接成功！