从外部主机/ip 访问 mysql 数据库？（即：mysql工作台）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5915534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Accessing a mysql database from external host/ip? (ie: mysql workbench)
提问by Jon
I have a mysql server running on x.x.x.x, and can access it internally no problem (of course). However when attempting to connect externally, ie using mysql workbench, or even from an external server, I get the error message "Host 'bla.bla.bla' is not allowed to connect to this MySQL server".
我有一个运行在 xxxx 上的 mysql 服务器，并且可以在内部访问它没有问题（当然）。但是，当尝试从外部连接时，即使用 mysql 工作台，甚至从外部服务器连接时，我收到错误消息“不允许主机 'bla.bla.bla' 连接到此 MySQL 服务器”。
I have done:
我已经做好了：
GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY "somepass";- And I have opened port 3306 in iptables.
GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY "somepass";- 我在iptables中打开了3306端口。
Is there another fundamental security issue I am missing?
我还缺少另一个基本的安全问题吗？
采纳答案by nobody
You need to do
你需要做
GRANT ALL PRIVILEGES ON *.* TO mysql@'bla.bla.bla' ...
The part after the @is the host from which the connection is coming, so you have allowed only connections coming from localhost. You need to allow access from each remote host necessary (or all hosts - ... mysql@'%' ...- if applicable).
后面的部分@是连接来自的主机，因此您只允许来自 localhost 的连接。您需要允许从每个必要的远程主机（或所有主机 - ... mysql@'%' ...- 如果适用）进行访问。
回答by jesse reiss
Are you connecting as user mysql? You might try running the GRANT query as : GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY "somepass";
您是否以 mysql 用户身份连接？您可以尝试将 GRANT 查询运行为：GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY "somepass";
回答by Charles Ray
Comment out the line:
注释掉这一行：
bind-address = localhost
#bind-address = localhost < this is what it should look like.
in your MySQL my.conf file. It is normally located in /etc/mysql/my.conf.
在您的 MySQL my.conf 文件中。它通常位于 /etc/mysql/my.conf 中。
回答by eykanal
Have you verified that mysql workbench is trying the connect using the appropriate username? Have you flushed the privilegesafter running the grant command?
您是否确认 mysql 工作台正在尝试使用适当的用户名进行连接？运行授权命令后是否刷新了权限？
回答by gmhk
When I Got my server,even I had the same problem accessing the mysql from MySQL client application, Then I granted the Mysql permission, with following query.
当我得到我的服务器时，即使我从 MySQL 客户端应用程序访问 mysql 也遇到了同样的问题，然后我授予了 Mysql 权限，并使用以下查询。
it worked Great
它工作得很好
**GRANT ALL PRIVILEGES ON db_base.* TO db_user @'%' IDENTIFIED BY 'db_passwd';**
db_base is the database Name
db_user is database User
db_passwd is the database password
Once you execute this flush it, by the following command
FLUSH PRIVILEGES;
执行此刷新后，通过以下命令
FLUSH PRIVILEGES;
Suppose if you are looking to give privileges to access certain tables in the Database you can use the following command
假设您想授予访问数据库中某些表的权限，您可以使用以下命令
GRANT ALL PRIVILEGES ON db_base.HELLOWORLD TO db_user @'%' IDENTIFIED BY 'db_passwd';
Where HELLOWORLDis the table Name
HELLOWORLD表名在哪里
回答by Ahmad
To solve this you needed to perform the following commands:
要解决此问题，您需要执行以下命令：
mysql -u root -p
[enter in your password]
CREATE USER 'mysqluser'@'%' IDENTIFIED BY 'aC0MPL3XPa33W0RD';
GRANT ALL PRIVILEGES ON *.* TO 'mysqluser'@'%' WITH GRANT OPTION;
回答by oli
I dont know the ins and outs of security behind this bind-addressthing, just learning by installing a debian server on a virtual-box machine.
This guest has a virtual network card set up as a bridge, so the rest of the house can see it. Its IP is 192.168.1.4.
From another computer (192.168.1.3), connection failed with bind-address = 127.0.0.1.
Set up bind-address = 192.168.1.4works fine.
(its own address, litterally)
It must be the interpretation of 127.0.0.1 inside a virtual config, not sure...
我不知道这bind-address件事背后安全的来龙去脉，只是通过在虚拟机上安装 debian 服务器来学习。这个客人有一个虚拟网卡作为网桥，所以房子的其他部分可以看到它。它的 IP 是 192.168.1.4。在另一台计算机 (192.168.1.3) 上，连接失败并显示bind-address = 127.0.0.1. 设置bind-address = 192.168.1.4工作正常。（它自己的地址，字面上）它必须是虚拟配置中 127.0.0.1 的解释，不确定......
回答by Yoosaf Abdulla
I had the exactly similar situation.my MYSQL is installed on a centOS. The path to Nirvana is as below.
我有完全相似的情况。我的 MYSQL 安装在 CentOS 上。涅槃之道如下。
- bind-address: DID NOT WORK
- grant permission: DID NOT WORK
- Iptables when turned off: DID work.
- 绑定地址：不起作用
- 授予权限：不起作用
- 关闭时的 iptables：DID 工作。
SOLUTION:I went fishing into the iptables and made following changes:
解决方案：我去钓鱼到 iptables 并进行了以下更改：
- Access the iptables using the command : vim /etc/sysconfig/iptables
If you find the below statements COMMENT them out by adding a '#' at the beginning of the line.
-A INPUT -s 123.123.123.123/32 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A OUTPUT -p tcp -m tcp --dport 3306 -j ACCEPT
Restart the iptables using the command: service iptables restart
- 使用以下命令访问 iptables：vim /etc/sysconfig/iptables
如果您发现以下语句，请在该行的开头添加“#”来将它们注释掉。
-A 输入 -s 123.123.123.123/32 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A 输出 -p tcp -m tcp --dport 3306 -j 接受
使用命令重新启动 iptables：service iptables restart
Yep, that worked for me. Hope it is useful to someone.
是的，这对我有用。希望它对某人有用。
