MySQL的远程连接

在默认情况下，MySQL只允许本地连接并不允许远程连接。为什么会这样呢？下面这段话会解决这个问题。

Internally, the server stores privilege information in the grant tables of the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables into memory when it starts and bases access-control decisions on the in-memory copies of the grant tables.

The MySQL privilege system ensures that all users may perform only the operations permitted to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

接下来我们看一下mysql database中user table的内容。

mysql -uroot -pyourpasswd

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 from user ;

+-----------+------------------+

| host | user |

+-----------+------------------+

| 127.0.0.1 | root |

| localhost | debian-sys-maint |

| localhost | root |

| ubuntu | root |

+-----------+------------------+

5 rows in set (0.00 sec)

到此为止我们应该能明白如何进行远程连接了。没错，把我们的host（也就是上文中提到的the host from which you connect中的host）想办法添加到user表中。

方法当然有很多。

其中一个就是直接update user table。注意哦我说的是update（SQL中的update），你也可以insert。下面我们看一下user的结构，也许你会放弃insert的想法。

+-----------------------+-----------------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------------------+-----------------------------------+------+-----+---------+-------+

| Host | char(60) | NO | PRI | | |

| User | char(16) | NO | PRI | | |

| Password | char(41) | NO | | | |

| Select_priv | enum('N','Y') | NO | | N | |

| Insert_priv | enum('N','Y') | NO | | N | |

| Update_priv | enum('N','Y') | NO | | N | |

| Delete_priv | enum('N','Y') | NO | | N | |

| Create_priv | enum('N','Y') | NO | | N | |

| Drop_priv | enum('N','Y') | NO | | N | |

| Reload_priv | enum('N','Y') | NO | | N | |

| Shutdown_priv | enum('N','Y') | NO | | N | |

| Process_priv | enum('N','Y') | NO | | N | |

| File_priv | enum('N','Y') | NO | | N | |

| Grant_priv | enum('N','Y') | NO | | N | |

| References_priv | enum('N','Y') | NO | | N | |

| Index_priv | enum('N','Y') | NO | | N | |

| Alter_priv | enum('N','Y') | NO | | N | |

| Show_db_priv | enum('N','Y') | NO | | N | |

| Super_priv | enum('N','Y') | NO | | N | |

| Create_tmp_table_priv | enum('N','Y') | NO | | N | |

| Lock_tables_priv | enum('N','Y') | NO | | N | |

| Execute_priv | enum('N','Y') | NO | | N | |

| Repl_slave_priv | enum('N','Y') | NO | | N | |

| Repl_client_priv | enum('N','Y') | NO | | N | |

| Create_view_priv | enum('N','Y') | NO | | N | |

| Show_view_priv | enum('N','Y') | NO | | N | |

| Create_routine_priv | enum('N','Y') | NO | | N | |

| Alter_routine_priv | enum('N','Y') | NO | | N | |

| Create_user_priv | enum('N','Y') | NO | | N | |

| Event_priv | enum('N','Y') | NO | | N | |

| Trigger_priv | enum('N','Y') | NO | | N | |

| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |

| ssl_cipher | blob | NO | | NULL | |

| x509_issuer | blob | NO | | NULL | |

| x509_subject | blob | NO | | NULL | |

| max_questions | int(11) unsigned | NO | | 0 | |

| max_updates | int(11) unsigned | NO | | 0 | |

| max_connections | int(11) unsigned | NO | | 0 | |

| max_user_connections | int(11) unsigned | NO | | 0 | |

+-----------------------+-----------------------------------+------+-----+---------+-------+

很多很长，对吧，那好吧我们寻求另外一个更直观的解决办法。

GRANT ALL PRIVILEGES ON *.* TO admin@"%" IDENTIFIED BY 'administrator' WITH GRANT OPTION;

FLUSH PRIVILEGES；

授与admin用户从任何其它主机发起的访问（通配符％）

接下来就是测试我们的配置了。

在另外一个装有MySQL client的机器上登录MySQL Server。如果没有错误就恭喜你了。不幸的是我的出错了。

 

思索很久终于找到解决办法，在此要备注一下，以免以后时间长了，忘了还要从头来。

刚开始我以为是授权的配置错了。可是思来想去也道不出个所以然。所以我决定在本地执行一下mysql -hip -uuser -ppasswd.结果同样报错。用netstat -nal | grep tcp查得关于mysql的listen: 127.0.0.1:3306 。如果把它改为本机ip效果如何呢？反正远程连接现在是不能用所以就抱着尝试一下的心理，把/etc/mysql/my.inf中的bind-address的值改为192.168.186.134然后sudo /etc/init.d/mysql restart。皇天不负有心人，嘿嘿，居然可以用了。可以用了就先不管它了，以后出了问题再去解决。

For details, see MySQL manual