Mysql的远程访问（Server端权限设置和Client端编程）

mysql远程访问的设置

登陆以后运行以下命令，给予远程访问客户端权限.. 

grant all on *.* to 'remote'@'172.16.21.39' identified by 'password';

remote表示用户名.

'172.16.21.39' 远程ip地址

password表示远程登陆密码.

默认状况下，出于安全考虑，mysql数据库屏蔽了远程访问功能。 然而在许多状况下，你需要在家或者从web程序去访问远端数据库服务器，这就相当麻烦了。

第一步: 激活网络设置
你需要编辑mysql配置文件my.cnf.

通常状况，my.cnf放置于在以下目录：
/etc/mysql/my.cnf (Debian linux)
/etc/my.cnf （Red Hat Linux/Fedora Linux)
/var/db/mysql/my.cnf (FreeBSD)

# vi /etc/my.cnf
然后用vi编辑my.cnf，修改内容从以下行：

[mysqld]

你所需要:
1. 确保skip-networking被删除或者屏蔽，否则不支持TCP/IP 访问
2. 增加行bind-address = 65.55.55.2，替代65.55.55.2 为你的服务器地址
修改后，配置为：
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 65.55.55.2
# skip-networking
....
..

保存并关闭配置文件
重启mysql服务器:# /etc/init.d/mysql restart

第二步， 给远程ip授访问权限
1. 以root身份登陆mysql # mysql -u root -p
2. 授权给新数据库
如果你有一个新的数据库名称为foo。用户bar需要在客户端地址为202.54.10.20上访问。

在服务器端执行 （授权法）：
mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';

如何给以存在的数据库授权？

如果用户 webadmin 经常在客户端地址202.54.10.20 访问 webd数据库，那么在服务器端执行的命令应该为（改表法）：

mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';

第三步： 执行exit命令退出mysql环境，

第四步 测试
From remote system type command:
$ mysql -u webadmin –h 65.55.55.2 –p

注解；
-u webadmin: webadmin为mysql的用户名
-h IP or 域名: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
-p : 密码提示
你也可以使用telnet去连接远程的3306端口 $ telnet 65.55.55.2 3306

笔者注：

这篇文章解决了我在suse环境中的mysql远程访问问题。但是实际操作中，my.cnf似乎并不是特别重要。在我的操作中，my.cnf创建和修改没有执行（忽略第一步）。最重要的步骤应该是第四步
GRANT ALL ON b2c.* TO root@'5.179.201.86' IDENTIFIED BY '';
其中b2c为所要访问的远程数据库名，5.179.201.86为mysql客户端主机的ip地址，也就是你的机器ip地址。
有个小细节需要注意。如果你的服务器装了防火墙的话，需要打开3306的访问，否则也会连接不上。

 

    mysql_real_connect()试图建立到运行host的一个MySQL数据库引擎的一个连接。其函数原形为：

   MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned int client_flag)   

   我要编一段程序用来远程连接一个mysql数据库。我在数据库的本机上做试验，发现mysql_real_connect()的第二个参数设为"localhost"时可以正常连接，当改写为该机器的IP地址"192.168.100.2"时就连接失败。我在程序中写的原句为：

mysql_real_connect(mysql,"192.168.100.2","root","","mydns",0,NULL,0)。

 

   最初，我以为将"localhost"改为"192.168.100.2"之后要将第六个参数端口号写上，但还是不行。由于我是在Linux上做的，所以想也许需要将第七个参数unix_socket写上，结果还是不行。于是到google找解决方法，发现要远程连接mysql需要为mysql的指定帐号设置远程连接的权限。在我的程序中，就要为root设置这一权限。事实证明，我之前连接失败正是由于这个原因导致的。但问题是，网上被广泛转贴的方法是错误的，我照做后还不行，最后问了同学才解决的。下面我具体说一下，望大家不会再被误导。

 

   在mysql数据库中有一个mysql库，它里面包含一个user表，里面存有所有帐号及它们的权限及特征。对应我的user表中找到User项为root的，发现有两项，其Host项分别为localhost和localhost.localdomain。我们只需要把'localhost'改为'%'就可以让所有远程机器以root帐号登陆mysql服务器。也可以将'localhost'改为某一个ip地址，这样就可以在该地址的机器上登陆mysql服务器。这些都没有问题，只是网上被广为传颂的一个更改Host项的方法是错误的。他的方法是用update语句将指定User项的Host项改过来，如果想添加一个新的用于远程访问的帐号，就用insert语句添加。事实是这根本不行，我按照作了，表是变了，但还是连不上。后来经过询问才知道，要想改，结合我的情况，必须用下面的这条语句：

     grant all privileges on mydns.* to ；

来改变root帐号的权限。如果添加新帐号也是如此。想必这个表比较特殊，用普通的SQL语句不能操作。如果帐号有密码，后面再加上identified by '***'(*为你帐号的密码)就可以了。

 

 

关于mysql_real_connect()的说明

 

 

在前一篇文章中，讲述了如何进行mysql源程序代码的编译链接，但是没有讲述运况，在按照上一篇文章代码下进行编译运行后，发现无法链接数据库文件，显然是在mysql_real_connect（）函数中出现了问题。在mysql的英文手册中找到关于mysql_real_connect()的如下描述：
     
//函数原型描述 MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user,
const char *passwd, const char *db, unsigned int port, const char *unix_socket,
unsigned long client_flag)

Description

mysql_real_connect() attempts to establish a connection to a MySQL database engine
running on host. mysql_real_connect() must complete successfully before you can
execute any other API functions that require a valid MYSQL connection handle structure.

The parameters are specified as follows:

    *

      The first parameter should be the address of an existing MYSQL structure. Before
 calling mysql_real_connect() you must call mysql_init() to initialize the MYSQL
structure. You can change a lot of connect options with the mysql_options() call.
See Section 17.2.3.47, “mysql_options()”.
    *

      The value of host may be either a hostname or an IP address. If host is NULL or the
string "localhost", a connection to the local host is assumed. If the OS supports sockets
(Unix) or named pipes (Windows), they are used instead of TCP/IP to connect to the server.
    *

      The user parameter contains the user's MySQL login ID. If user is NULL or the empty
string "", the current user is assumed. Under Unix, this is the current login name. Under
 Windows ODBC, the current username must be specified explicitly. See Section 18.1.9.2,
 Â“Configuring a MyODBC DSN on Windows”.
    *

      The passwd parameter contains the password for user. If passwd is NULL, only entries
 in the user table for the user that have a blank (empty) password field are checked for a
match. This allows the database administrator to set up the MySQL privilege system in
such a way that users get different privileges depending on whether they have specified
a password.

      Note: Do not attempt to encrypt the password before calling mysql_real_connect();
 password encryption is handled automatically by the client API.
    *

      db is the database name. If db is not NULL, the connection sets the default database
to this value.
    *

      If port is not 0, the value is used as the port number for the TCP/IP connection. Note
that the host parameter determines the type of the connection.
    *

      If unix_socket is not NULL, the string specifies the socket or named pipe that should
 be used. Note that the host parameter determines the type of the connection.
    *

      The value of client_flag is usually 0, but can be set to a combination of the following
flags to enable certain features:

上面描述了五个参数的主要取值，MYSQL *为mysql_init函数返回的指针，host为null或localhost时链接的是本地的计算机，当mysql默认安装在unix（或类unix）系统中，root账户是没有密码的，因此用户名使用root，密码为null，当db为空的时候，函数链接到默认数据库，在进行mysql安装时会存在默认的test数据库，因此此处可以使用test数据库名称，port端口为0，使用unix连接方式，unix_socket为null时，表明不使用socket或管道机制，最后一个参数经常设置为0.

      Flag Name    Flag Description
      CLIENT_COMPRESS    Use compression protocol.
      CLIENT_FOUND_ROWS    Return the number of found (matched) rows, not the number of
 changed rows.
      CLIENT_IGNORE_SPACE    Allow spaces after function names. Makes all functions names
reserved words.
      CLIENT_INTERACTIVE    Allow interactive_timeout seconds (instead of wait_timeout
seconds) of inactivity before closing the connection. The client's session wait_timeout
variable is set to the value of the session interactive_timeout variable.
      CLIENT_LOCAL_FILES    Enable LOAD DATA LOCAL handling.
      CLIENT_MULTI_STATEMENTS    Tell the server that the client may send multiple
statements in a single string (separated by ‘;’). If this flag is not set,
multiple-statement execution is disabled. Added in MySQL 4.1.
      CLIENT_MULTI_RESULTS    Tell the server that the client can handle multiple result
sets from multiple-statement executions or stored procedures. This is automatically
set if CLIENT_MULTI_STATEMENTS is set. Added in MySQL 4.1.
      CLIENT_NO_SCHEMA    Don't allow the db_name.tbl_name.col_name syntax. This is for
ODBC. It causes the parser to generate an error if you use that syntax, which is useful
 for trapping bugs in some ODBC programs.
      CLIENT_ODBC    The client is an ODBC client. This changes mysqld to be more
 ODBC-friendly.
      CLIENT_SSL    Use SSL (encrypted protocol). This option should not be set by
application programs; it is set internally in the client library. Instead, use
mysql_ssl_set() before calling mysql_real_connect().

For some parameters, it is possible to have the value taken from an option file rather
than from an explicit value in the mysql_real_connect() call. To do this, call
mysql_options() with the MYSQL_READ_DEFAULT_FILE or MYSQL_READ_DEFAULT_GROUP option
before calling mysql_real_connect(). Then, in the mysql_real_connect() call, specify
the “no-value” value for each parameter to be read from an option file:

    *

      For host, specify a value of NULL or the empty string ("").
    *

      For user, specify a value of NULL or the empty string.
    *

      For passwd, specify a value of NULL. (For the password, a value of the empty string in
 the mysql_real_connect() call cannot be overridden in an option file, because the empty
string indicates explicitly that the MySQL account must have an empty password.)
    *

      For db, specify a value of NULL or the empty string.
    *

      For port, specify a value of 0.
    *

      For unix_socket, specify a value of NULL.

If no value is found in an option file for a parameter, its default value is used as
indicated in the descriptions given earlier in this section.

Return Values

A MYSQL* connection handle if the connection was successful, NULL if the connection
was unsuccessful. For a successful connection, the return value is the same as the value
of the first parameter.
// 返回值：当连接成功时，返回MYSQL连接句柄，失败，返回NULL。当成功时，返回值与第一个参数值是// 相同的。

Errors

    *

      CR_CONN_HOST_ERROR

      Failed to connect to the MySQL server.
    *

      CR_CONNECTION_ERROR

      Failed to connect to the local MySQL server.
    *

      CR_IPSOCK_ERROR

      Failed to create an IP socket.
    *

      CR_OUT_OF_MEMORY

      Out of memory.
    *

      CR_SOCKET_CREATE_ERROR

      Failed to create a Unix socket.
    *

      CR_UNKNOWN_HOST

      Failed to find the IP address for the hostname.
    *

      CR_VERSION_ERROR

      A protocol mismatch resulted from attempting to connect to a server with a client
library that uses a different protocol version. This can happen if you use a very old
client library to connect to a new server that wasn't started with the --old-protocol
option.
    *

      CR_NAMEDPIPEOPEN_ERROR

      Failed to create a named pipe on Windows.
    *

      CR_NAMEDPIPEWAIT_ERROR

      Failed to wait for a named pipe on Windows.
    *

      CR_NAMEDPIPESETSTATE_ERROR

      Failed to get a pipe handler on Windows.
    *

      CR_SERVER_LOST

      If connect_timeout > 0 and it took longer than connect_timeout seconds to connect to
the server or if the server died while executing the init-command.

    因此mysql_real_connect()函数调用为：
       mysql_real_connect(mysql,"localhost","root",NULL,"test",0,NULL,0);
判断是否出错，出错调用mysql_error()函数显示出错信息，或使用mysql_errno()函数获取出错代号。