如何通过LAN访问MySQL数据库webservice？ [英] How to access a MySQL database webservice over LAN?
问题描述
我有三台电脑通过局域网连接。
- 第一台电脑到数据库;
- 第二个到Web服务CodeIgniter RESTful;
- 第三个到客户端。
如何将Web服务CodeIgniter服务器连接到数据库，我已设置IP在 aplication / config / database.php ，
$ db ['default'] ['hostname'] ='localhost';
我改变了 localhost 由第一个pc使用：
$ db ['default'] ['hostname'] ='192.168.1.10' ;
但结果是：
发生数据库错误
无法使用提供的设置连接到数据库服务器。
发生数据库错误
无法使用提供的设置连接到数据库服务器。
如果这是MySQL，则默认情况下不启用数据库服务器的网络连接。要在MySQL中启用网络，您需要找到名为 my.cnf 的活动MySQL配置文件。
我将解释如何在Ubuntu 12.04上执行此操作，但是大多数Linux安装的说明都是类似的。
启用MySQL网络
首先，使用 / code>。您可能需要通过 sudo ：
运行命令sudo nano / etc / mysql / my.cnf
然后在配置文件中查找 bind-address 选项：
＃而不是skip-networking只听
＃localhost这是更兼容，不安全。
bind-address = 127.0.0.1
现在改变 bind -address 设置为以下内容：
bind-address = 0.0.0.0
完成后保存文件&以这样的方式重新启动MySQL：
sudo服务mysql重新启动
现在，您的MySQL数据库将能够从远程计算机获取非本地连接。
MySQL端口 3306 是打开
即使启用了网络，如果您能够使用网络工具（例如 nmap ）从命令行连接到远程计算机。您可能有 192.168.1.10 阻止MySQL端口 3306 的防火墙，因此您需要检查它是否打开或关闭像这样：
nmap 192.168.1.10 -p3306
如果端口 3306 打开，这将是响应;请注意下下的 open ：：
<
启动Nmap 6.40（http://nmap.org）在2014-06-10 10:34 EDT
Nmap扫描报告为192.168.1.10
主机已启动（0.0035秒延迟）。
PORT STATE SERVICE
3306 / tcp open mysql
但如果port 3306 关闭，你会得到这个;请注意下下的已关闭：：
启动Nmap 6.40（http://nmap.org）在2014-06-10 10:34 EDT
Nmap扫描报告为192.168.1.10
主机已启动（0.0035秒延迟）。
PORT STATE SERVICE
3306 / tcp closed mysql
MySQL用户授权
现在完成所有操作后，您的MySQL数据库服务器应该可以在网络上访问。但是，您需要确保您正在使用的数据库用户可以从LAN上的远程计算机连接。所以登录MySQL&运行此命令以查看用户可能具有的授权：
SELECT用户，主机从`mysql`.`user`;
这将显示用户列表，连接到MySQL中的那些用户的主机。事情是大多数用户只被授予访问权限 localhost 或 127.0.0.1 。有些被授予％的通配符主机。您需要查看该列表，看看您要使用的用户是否有通配符主机（％）或连接到它们的特定IP地址。您可以通过运行这样的行来检查用户的 GRANT 当然更改 [your_database_user] 和 [hostname] 以匹配您的设置：
显示'[your_database_user]'@'[hostname]'的赠款;
这应该会显示可用的 GRANT s为特定主机上的用户。如果他们有允许远程访问的 GRANT ，例如使用通配符％主机，那么你都设置了。如果他们没有任何 GRANT ，您可以运行以下MySQL命令;当然更改 [your_database] 和 [your_database_user] 以匹配您的设置：
GRANT SELECT，INSERT，UPDATE，DELETE，CREATE，DROP，INDEX，ALTER，CREATE TEMPORARY TABLES，LOCK TABLES，EXECUTE，CREATE VIEW，SHOW VIEW，CREATE ROUTINE， ALTER ROUTINE，EVENT，TRIGGER ON`[your_database]`。* TO'[your_database_user]'@'192.168.0.0/255.255.0.0';
GRANT SELECT，INSERT，UPDATE，DELETE，CREATE，DROP，INDEX，ALTER，CREATE TEMPORARY TABLES，LOCK TABLES，EXECUTE，CREATE VIEW，SHOW VIEW，CREATE ROUTINE，ALTER ROUTINE，EVENT，TRIGGER ON`[your_database] `。* TO'[your_database_user]'@'10.0.0.0/255.0.0.0';
FLUSH PRIVILEGES;
两个 GRANT 对数据库的访问权限。第一行将它们应用于 192.168.x.x 的LAN网络范围中的任何连接。第二行将它们应用于 10.x.x.x 的LAN网络范围中的任何连接。我喜欢这样做，以涵盖所有内部网络的基础。最后的 FLUSH PRIVILEGES; 行基本上告诉MySQL重新加载用户权限表，允许这些授权生效。
I have three PCs that I connect over LAN.
- The first PC to database;
- The second to web service CodeIgniter RESTful;
- The third to client.
Now how can I connect web service CodeIgniter server to database, I've set the IP on aplication/config/database.php,
$db['default']['hostname'] = 'localhost';
I've changed localhost with the IP that is used by the first pc:
$db['default']['hostname'] = '192.168.1.10';
But, the result :
A database error occurred
Unable to connect to your database server using the provided settings.
A database error occurred
Unable to connect to your database server using the provided settings.
If this is MySQL, networking of the database server is not enabled by default. To enable networking in MySQL you need to find the active MySQL configuration file named my.cnf. And edit it.
I am going to explain how to do this on Ubuntu 12.04, but the instructions are similar for most any Linux install.
Enable MySQL Networking
First, open the file using an editor like nano. You might need to run the command via sudo:
sudo nano /etc/mysql/my.cnf
Then look for the area in your configuration file with the bind-address option:
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
Now change that bind-address setting to the following:
bind-address = 0.0.0.0
With that done save the file & restart MySQL like this:
sudo service mysql restart
And now your MySQL database will be able to take non-localhost connections from remote machines.
Make Sure the MySQL Port 3306 is Open
That said, even with networking enabled, you should still check if you are able to connect to the remote machine from the command line using a networking tool such as nmap. You might have a firewall on 192.168.1.10 blocking MySQL port 3306 so you need to check if it is open or closed like this:
nmap 192.168.1.10 -p3306
And if port 3306 is open, this will be the response; note the open under STATE:
Starting Nmap 6.40 ( http://nmap.org ) at 2014-06-10 10:34 EDT
Nmap scan report for 192.168.1.10
Host is up (0.0035s latency).
PORT STATE SERVICE
3306/tcp open mysql
But if port 3306 is closed, you will get this; note the closed under STATE:
Starting Nmap 6.40 ( http://nmap.org ) at 2014-06-10 10:34 EDT
Nmap scan report for 192.168.1.10
Host is up (0.0035s latency).
PORT STATE SERVICE
3306/tcp closed mysql
Check Your MySQL User Grants
Now with that all done, your MySQL database server should be accessible on the network. But that said, you need to make sure your database user you are using is allowed to connect from a remote machine on your LAN. So login to MySQL & run this command to see what grants the user might have:
SELECT user, host FROM `mysql`.`user`;
This will show you a list of users & hosts connected to those users in MySQL. The thing is most users are only granted access privileges to localhost or 127.0.0.1. Some are granted the wildcard host of %. You need to look at that list and see if the user you want to use has a wildcard host (%) or a specific IP address connected to them. You can check a user’s GRANTs by running a line like this; of course change [your_database_user] and [hostname] to match your settings:
SHOW GRANTS FOR '[your_database_user]'@'[hostname]';
That should show you a list of available GRANTs for a user on a specific host. If they have GRANTs that would allow remote access—such as using wildcard % host—then you are all set. If they don’t have any GRANTs you can run the following MySQL commands; of course change [your_database] and [your_database_user] to match your settings:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `[your_database]`.* TO '[your_database_user]'@'192.168.0.0/255.255.0.0';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `[your_database]`.* TO '[your_database_user]'@'10.0.0.0/255.0.0.0';
FLUSH PRIVILEGES;
Both GRANT lines apply a fairly standard set of access rights to a database. The first line applies them to any connection in the LAN network range of 192.168.x.x. The second line applies them to any connection in the LAN network range of 10.x.x.x. I like to do that to cover all bases on internal networks. The last FLUSH PRIVILEGES; line basically tells MySQL to reload the user privileges tables which allow those grants to take effect.
这篇关于如何通过LAN访问MySQL数据库webservice？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
