MySQL刚安装完只认得的主机是:localhost和127.0.0.1,如果你要让你机上的IP可以正常登录有两种办法: 一是对你的IP 172.16.1.10 进行授权 grant privileges on dbname.* to "user"@"172.16.1.10 " identified by "password"; 如果只是你自己用的话就可以"偷懒"这样写 grant all on *.* to "user"@"172.16.1.10 " identified by "password"; grant具体的用法你就参考一下mysql的用户手册吧,如果这样不行的话,就只能通过以下方法进行授权了 mysql>use mysql; database changed mysql>insert into user values(val1,val2,....);进行了,到底有多少字段你用mysql query browser进去后看一下就知道了,很多的,呵呵.二是就将root用户设为可以远程登录,也即是可以从任何机器上登录 mysql>use mysql; database changed mysql>update user set host="%" where user="root";
非常感谢兄弟们的指点，不过这些方法我之前都试过。 但问题好像不在于权限，而似乎在于根本找不到172.16.1.10。 请看我的test帐号： mysql> select host,db,user from db; +-------------+---------+--------------+ | host | db | user | +-------------+---------+--------------+ | % | test | | | % | test\_% | |[root@mail bin]# mysql -utest -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 5.0.15-max-logType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec)mysql> 说明可以随便登陆,我用mysql -uanyname -p都能够进入，anyname，可以是任何名字！ [root@mail bin]# mysql -h 172.16.1.10 -utest -p Enter password: ERROR 2003 (HY000): Can't connect to MySQL server on '172.16.1.10' (111) [root@mail bin]# 照样出错！ 我用telnet测试如下： [root@mail bin]# telnet localhost 3306 Trying 127.0.0.1... Connected to localhost.localdomain (127.0.0.1). Escape character is '^]'. < 5.0.15-max-log^H-IeC_4,?#QR%a:;Y2,%qConnection closed by foreign host. [root@mail bin]# [root@mail bin]# telnet 172.16.1.10 3306 Trying 172.16.1.55... telnet: connect to address 172.16.1.10: Connection refused telnet: Unable to connect to remote host: Connection refused [root@mail bin]# 似乎这不是权限的问题啊。为什么telnet172这个ip和telnet 127.0.0.1就会导致不同结果呢？很奇怪！谢谢！
手册上的说明: If you get the error message Can't connect to MySQL server on some_host, you can try the following things to find out what the problem is: Check whether the server is running on that host by executing telnet some_host 3306 and pressing the Enter key a couple of times. (3306 is the default MySQL port number. Change the value if your server is listening to a different port.) If there is a MySQL server running and listening to the port, you should get a response that includes the server's version number. If you get an error such as telnet: Unable to connect to remote host: Connection refused, then there is no server running on the given port. If the server is running on the local host, try using mysqladmin -h localhost variables to connect using the Unix socket file. Verify the TCP/IP port number that the server is configured to listen to (it is the value of the port variable.) Make sure that your mysqld server was not started with the --skip-networking option. If it was, you cannot connect to it using TCP/IP. Check to make sure that there is no firewall blocking access to MySQL. Applications such as ZoneAlarm and the Windows XP personal firewall may need to be configured to allow external access to a MySQL server.你按照的说明试一下,看看能不能查出到底是什么回事.
我认为我的问题不是权限问题，-h参数应该是目标mysqlserver，权限问题是那些你从哪台机器脸上来的，所以上面有些朋友让我做授权，那些之前我也做过，但并没有关系。我认为现在似乎是通过ip直接连目标主机，这个地方我装的mysql可能不认，因为我的网卡实际ip是172.16.1.10,tcp端口是没问题的，看127.0.0.1的测试，就可以看到tcp是在侦听的！同一台机器不会是防火墙的问题，何况我并没有安装防火墙，我是直接在linux机器上进行操作的！ 问题是，错误提示如：telnet: Unable to connect to remote host: Connection refused，为什么会认为访问172.16.1.10成了访问远程主机了？分明是自己啊，mysql就这么愚昧？[root@mail data]# mysqladmin -h '127.0.0.1' version -uroot -p Enter password: mysqladmin Ver 8.41 Distrib 5.0.15, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL licenseServer version 5.0.15-max-log Protocol version 10 Connection 127.0.0.1 via TCP/IP TCP port 3306 Uptime: 3 min 24 secThreads: 1 Questions: 4 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 6 Queries per second avg: 0.020 [root@mail data]# mysqladmin -h '172.16.1.55' version -uroot -p Enter password: mysqladmin: connect to server at '172.16.1.10' failed error: 'Lost connection to MySQL server during query' [root@mail data]# telnet 127.0.0.1 3306 Trying 127.0.0.1... Connected to localhost.localdomain (127.0.0.1). Escape character is '^]'. < 5.0.15-max-logVmDH!e]%,?;;Uy&J$p./@6 Connection closed by foreign host. [root@mail data]# telnet 172.16.1.10 3306 Trying 172.16.1.10... telnet: connect to address 172.16.1.10: Connection refused telnet: Unable to connect to remote host: Connection refused [root@mail data]#
增加一个用户test密码为abc，让他可以在任何主机上登录，并对test数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL，然后键入以下命令： mysql>grant select,insert,update,delete on test.* to test@"%" Identified by "abc"; 然后执行下面的命令,让权限生效 mysql> FLUSH PRIVILEGES;
一是对你的IP 172.16.1.10 进行授权
grant privileges on dbname.* to "user"@"172.16.1.10 " identified by "password";
如果只是你自己用的话就可以"偷懒"这样写
grant all on *.* to "user"@"172.16.1.10 " identified by "password";
grant具体的用法你就参考一下mysql的用户手册吧,如果这样不行的话,就只能通过以下方法进行授权了
mysql>use mysql;
database changed
mysql>insert into user values(val1,val2,....);进行了,到底有多少字段你用mysql query browser进去后看一下就知道了,很多的,呵呵.二是就将root用户设为可以远程登录,也即是可以从任何机器上登录
mysql>use mysql;
database changed
mysql>update user set host="%" where user="root";
但问题好像不在于权限，而似乎在于根本找不到172.16.1.10。
请看我的test帐号：
mysql> select host,db,user from db;
+-------------+---------+--------------+
| host | db | user |
+-------------+---------+--------------+
| % | test | |
| % | test\_% | |[root@mail bin]# mysql -utest -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19 to server version: 5.0.15-max-logType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)mysql>
说明可以随便登陆,我用mysql -uanyname -p都能够进入，anyname，可以是任何名字！
[root@mail bin]# mysql -h 172.16.1.10 -utest -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '172.16.1.10' (111)
[root@mail bin]# 照样出错！
我用telnet测试如下：
[root@mail bin]# telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
<
5.0.15-max-log^H-IeC_4,?#QR%a:;Y2,%qConnection closed by foreign host.
[root@mail bin]#
[root@mail bin]# telnet 172.16.1.10 3306
Trying 172.16.1.55...
telnet: connect to address 172.16.1.10: Connection refused
telnet: Unable to connect to remote host: Connection refused
[root@mail bin]# 似乎这不是权限的问题啊。为什么telnet172这个ip和telnet 127.0.0.1就会导致不同结果呢？很奇怪！谢谢！
好像是因为防火墙的原因。可能你需要添加例外，允许访问3306端口。
If you get the error message Can't connect to MySQL server on some_host, you can try the following things to find out what the problem is: Check whether the server is running on that host by executing telnet some_host 3306 and pressing the Enter key a couple of times. (3306 is the default MySQL port number. Change the value if your server is listening to a different port.) If there is a MySQL server running and listening to the port, you should get a response that includes the server's version number. If you get an error such as telnet: Unable to connect to remote host: Connection refused, then there is no server running on the given port. If the server is running on the local host, try using mysqladmin -h localhost variables to connect using the Unix socket file. Verify the TCP/IP port number that the server is configured to listen to (it is the value of the port variable.) Make sure that your mysqld server was not started with the --skip-networking option. If it was, you cannot connect to it using TCP/IP. Check to make sure that there is no firewall blocking access to MySQL. Applications such as ZoneAlarm and the Windows XP personal firewall may need to be configured to allow external access to a MySQL server.你按照的说明试一下,看看能不能查出到底是什么回事.
问题是，错误提示如：telnet: Unable to connect to remote host: Connection refused，为什么会认为访问172.16.1.10成了访问远程主机了？分明是自己啊，mysql就这么愚昧？[root@mail data]# mysqladmin -h '127.0.0.1' version -uroot -p
Enter password:
mysqladmin Ver 8.41 Distrib 5.0.15, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL licenseServer version 5.0.15-max-log
Protocol version 10
Connection 127.0.0.1 via TCP/IP
TCP port 3306
Uptime: 3 min 24 secThreads: 1 Questions: 4 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 6 Queries per second avg: 0.020
[root@mail data]# mysqladmin -h '172.16.1.55' version -uroot -p
Enter password:
mysqladmin: connect to server at '172.16.1.10' failed
error: 'Lost connection to MySQL server during query'
[root@mail data]# telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
<
5.0.15-max-logVmDH!e]%,?;;Uy&J$p./@6
Connection closed by foreign host.
[root@mail data]# telnet 172.16.1.10 3306
Trying 172.16.1.10...
telnet: connect to address 172.16.1.10: Connection refused
telnet: Unable to connect to remote host: Connection refused
[root@mail data]#
mysql>grant select,insert,update,delete on test.* to test@"%" Identified by "abc";
然后执行下面的命令,让权限生效
mysql> FLUSH PRIVILEGES;
修改一下配置文件打开tcp/ip模块
------------------------------------------------------------------------------------
LZ，看清楚，这个不是 MySQL 的提示，是 telnet 的提示，是它报告说连接不到目标机器的 3306 端口，这个结果有很多因素会造成，比如根本就没有打开 3306 端口，比如有防火墙，等等……所以还不能据此判断 MySQL 是聪明还是愚蠢。netstat -an 看看 mysql 的监听地址，会不会它被设置成只监听本地地址？比如启动的时候用了参数 --bind-address=127.0.0.1。按你的要求应该要监听所有地址。再来，就是还要建立一个使用该IP作为主机的用户，当然象你那样用 ％ 来作为主机也行，只是不安全。
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
似乎只有防火墙阻隔这个理由了。
另外你确定一下本机 IP 的确是172.16.1.10。MySQL 是允许这样访问的(在本机通过本机的IP地址访问)，我就是这么在用。