MySql服务器IP是192.168.0.104，测试连接的笔记本IP为192.168.0.100.

以下命令说明两台机器的网络连接是通畅的：

[root@bogon bin]# ping 192.168.0.100
PING 192.168.0.100 (192.168.0.100) 56(84) bytes of data.
64 bytes from 192.168.0.100: icmp_seq=1 ttl=128 time=9.26 ms
64 bytes from 192.168.0.100: icmp_seq=2 ttl=128 time=3.85 ms
64 bytes from 192.168.0.100: icmp_seq=3 ttl=128 time=6.85 ms
64 bytes from 192.168.0.100: icmp_seq=4 ttl=128 time=4.25 ms
64 bytes from 192.168.0.100: icmp_seq=5 ttl=128 time=10.4 ms
64 bytes from 192.168.0.100: icmp_seq=6 ttl=128 time=8.26 ms
64 bytes from 192.168.0.100: icmp_seq=7 ttl=128 time=3.24 ms
64 bytes from 192.168.0.100: icmp_seq=8 ttl=128 time=4.90 ms
64 bytes from 192.168.0.100: icmp_seq=9 ttl=128 time=3.40 ms
64 bytes from 192.168.0.100: icmp_seq=10 ttl=128 time=4.03 ms
64 bytes from 192.168.0.100: icmp_seq=11 ttl=128 time=9.22 ms
64 bytes from 192.168.0.100: icmp_seq=12 ttl=128 time=4.73 ms
64 bytes from 192.168.0.100: icmp_seq=13 ttl=128 time=6.34 ms
64 bytes from 192.168.0.100: icmp_seq=14 ttl=128 time=4.09 ms

--- 192.168.0.100 ping statistics ---
14 packets transmitted, 14 received, 0% packet loss, time 13000ms
rtt min/avg/max/mdev = 3.241/5.919/10.401/2.370 ms

MySQL的配置文件/etc/my.cnf的内容如下：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

也曾参考网文的做法进行授权，如下

[root@bogon bin]# ./mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all privileges on *.* to root@'%' identified by "root";
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@bogon bin]# service mysqld restart
Stopping MySQL:                                            [  OK  ]
Starting MySQL:                                            [  OK  ]
[root@bogon bin]# ./mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant select,update,insert,delete on *.* to root@192.168.0.100 identified by "root";
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

授权重启后没有问题。
[root@bogon bin]# service mysqld restart
Stopping MySQL:                                            [  OK  ]
Starting MySQL:                                            [  OK  ]

再看看数据表里面：

[root@bogon bin]# ./mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use ,ysql
ERROR 1049 (42000): Unknown database ',ysql'
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user,password from user;
+---------------+------+------------------+
| host          | user | password         |
+---------------+------+------------------+
| localhost     | root | 565491d704013245 | 
| bogon         | root |                  | 
| 127.0.0.1     | root |                  | 
| %             | root | 67457e226a1a15bd | 
| 192.168.0.100 | root | 67457e226a1a15bd | 
+---------------+------+------------------+
5 rows in set (0.01 sec)

mysql>

这已经把网文提到的步骤都完成了。

/etc/sysconfig/iptables里3306端口已经打开。

重启mysqld服务后，看看端口情况。

[root@bogon bin]# netstat -tlnpu
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 0.0.0.0:40679               0.0.0.0:*                   LISTEN      1795/rpc.statd      
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      4107/mysqld         
tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      1776/rpcbind        
tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      2149/cupsd          
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      2021/sendmail: acce 
tcp        0      0 :::22                       :::*                        LISTEN      1997/sshd           
udp        0      0 0.0.0.0:32768               0.0.0.0:*                               1795/rpc.statd      
udp        0      0 0.0.0.0:32769               0.0.0.0:*                               2130/avahi-daemon:  
udp        0      0 0.0.0.0:679                 0.0.0.0:*                               1776/rpcbind        
udp        0      0 0.0.0.0:703                 0.0.0.0:*                               1795/rpc.statd      
udp        0      0 0.0.0.0:68                  0.0.0.0:*                               1655/dhclient       
udp        0      0 0.0.0.0:5353                0.0.0.0:*                               2130/avahi-daemon:  
udp        0      0 0.0.0.0:111                 0.0.0.0:*                               1776/rpcbind        
udp        0      0 0.0.0.0:631                 0.0.0.0:*                               2149/cupsd 

3306哪一行说明对外网是有端口监听着的。

 

可是，通过DbVisualizer连接失败，报错如下：

Product:  DbVisualizer Pro 9.0.2
Build:  #1912 (2012/12/18 10:21)
Java VM:  Java HotSpot(TM) Client VM
Java Version:  1.6.0_23
Java Vendor:  Sun Microsystems Inc.
OS Name:  Windows 7
OS Arch:  x86
OS Version:  6.1

An error occurred while establishing the connection:

Long Message:
Access denied for user 'root'@'192.168.0.100' (using password: YES)

Details:
   Type: java.sql.SQLException
   Error Code: 1045
   SQL State: 28000

MySql-Front的错误消息如下：

Access denied for user 'root'@'192.168.0.100'(using password:YES)

这已经比前几次有进步了，再查。

偶然间看到了以下网页：

http://bbs.csdn.net/topics/320044602   第43楼，里头提到：

===================================================================================
错误信息 ：ERROR 1045 (28000): Access denied for user 'usera'@'localhost' (using password:YES)
原因 : 用户账号并未创建
检查 ：以管理员ROOT登录后，show grants for 'usera'@'localhost'; 或者 select user from mysql.user; 确认用户账号是否存在。
处理 ：创建用户账号。
===================================================================================

然后又看到了这个网页：

http://www.linuxidc.com/Linux/2008-08/15067.htm ， 里头提到要重设root密码。

两篇网文言下之意似乎都是说root及密码是不对的。

不管了，拣到篮里就是菜，试试看再说。登录104执行以下命令

[root@bogon ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test               | 
+--------------------+
3 rows in set (0.03 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user from user
    -> ;
+------+
| user |
+------+
| root | 
| root | 
| root | 
| root | 
| root | 
+------+
5 rows in set (0.00 sec)

mysql> update user set password=password('12345678') where user='root';
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@bogon ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye
[root@bogon ~]#

然后，没有重启mysqld服务，直接用DBVisualizer，MySQL-Front和SQLToolbox测试，均告成攻。

以下是截图：

如果不是网文提醒，谁知道需要将root密码从原来的123456换到12345678呢，不换还连不上。换了就好了，这就是开门的钥匙。看来这把钥匙早已存在了，只是不了解它而已，了解了，问题也就解决了。

事后综合来看，正确启动，iptables开放3306端口，授权是必要的，重设密码也是必要的，四者在一起为充要条件，缺一不可。数据库客户端软件原来报的错也是对的，只是让人疑惑不去怀疑root和原有密码有问题，因为毕竟是不久前才设置好的.

于是为文，希望能对遇到同样问题的人有所帮助。

《全文完》

本文转自张昺华-sky博客园博客，原文链接：http://www.cnblogs.com/xiandedanteng/p/3301356.html，如需转载请自行联系原作者