MySQL客户端 登录 ERROR 1130 解析

今天在本地远程登录MySQL服务器时，报了一个错误，错误如下：

[root@mysqlserver1 ~]# mysql -h192.168.xxx.xxx -uroot -P6193
ERROR 1130 (HY000): Host '192.168.xxx.xxx' is not allowed to connect to this MySQL server

然后去数据库所在的服务器去查看用户信息，发现如下：

mysql> select host,user,password from user\G
*************************** 1. row ***************************
host: localhost
user: root
password:
*************************** 2. row ***************************
host: 127.0.0.1
user: root
password:
*************************** 3. row ***************************
host: ::1
user: root
password:
3 rows in set (0.01 sec)

也就是说除了本地之外，远程是无法登陆root用户的。所以我尝试把user表中的host为localhost的字段改为%，这样远程就可以访问MySQL服务器了（千万不要这么做！！！）。但是这样会带来一些不必要的麻烦。建议的修改方法如下：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '******' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

之前的root用户没有密码，这样既授予了权限，又更新了密码。

Tips.1:

*.* 在MySQL中代表 Global Privileges，意思就是全部的数据库。具体内容参考官方解释。

Tips.2:

localhost和127.0.01
这两者分别有自己的含义，通过localhost连接数据库，使用的是UNIX socket；而127.0.0.1使用的是TCP/IP:

[root@mysqlserver2 ~]# mysql -h 127.0.0.1 -uroot -P6193

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using  EditLine wrapper

Connection id:		30
Current database:	
Current user:		root@127.0.0.1
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.30-log Source distribution
Protocol version:	10
`Connection:		127.0.0.1 via TCP/IP`
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		6193
Uptime:			22 hours 17 min 8 sec

Threads: 1  Questions: 324  Slow queries: 0  Opens: 48  Flush tables: 1  Open tables: 41  Queries per second avg: 0.004

[root@mysqlserver2 ~]# mysql -h localhost -uroot -S /data/mysql6193/mysql.sock -P6193 

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using  EditLine wrapper

Connection id:		28
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.30-log Source distribution
Protocol version:	10
`Connection:		Localhost via UNIX socket`
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/data/mysql6193/mysql.sock
Uptime:			22 hours 16 min 41 sec

Threads: 1  Questions: 320  Slow queries: 0  Opens: 48  Flush tables: 1  Open tables: 41  Queries per second avg: 0.003