连接到远程MySQL数据库 [英] Connect to Remote MySQL Database
问题描述
我的朋友最近允许我将Web托管用于我拥有的网站，他去了所有的ftp和数据库设置程序，我可以为它做ftp，但是当我尝试使用以下方法进入数据库时像heidisql或mysql工具包之类的东西，它给我一个错误: 不允许主机"XX-XXX-XXX-XXXX.dhcp.unas.al.charter.com"连接到该mysql服务器.我给宪章打了电话，他们说他们最终什么也不能做，所以我认为这可能是他服务器上cpanel中允许远程访问它的设置?
My friend recently allowed me to use his web hosting for a site I have, and he went and got all the ftp and database setup and I can do the ftp for it, but, when I try to go into the database using something like heidisql or mysql toolkit it gives me an error: Host'XX-XXX-XXX-XXXX.dhcp.unas.al.charter.com' is not allowed to connect to this mysql server. I called charter and they said they couldn't do anything on their end so I am assuming it may be a setting in his cpanel on his server to allow remote access to it?
或者如果我完全不在家，这将如何工作?
Or if I'm completely off, how would this work?
我想知道如何通过转到cmd行并运行本地mysql.exe，然后连接-u用户-p -h -h xxx.xxx.x.xx
I would like to know how to get it to work by going to a cmd line and running a local mysql.exe and then connecting -u user -p -h xxx.xxx.x.xx
推荐答案
No, Charter wouldn't be able to do anything about it, because that's an error coming directly from the MySQL server, because your IP address is not among the hosts configured for access.
Error: 1130 SQLSTATE: HY000 (ER_HOST_NOT_PRIVILEGED)
Message: Host '%s' is not allowed to connect to this MySQL server
正确的解决方案包括了解 MySQL访问权限系统将连接始发的用户名+主机IP地址或名称视为帐户"，而不是用户名本身.
The correct solution involves understanding that the MySQL Access Privilege System treats username + host IP address or name where the connection is originating as an "account" -- not username by itself.
'foo'@'localhost' # username foo connecting from the server machine
'foo'@'192.168.1.1' # username foo from IP address 192.168.1.1
'foo'@'192.168.1.%' # username foo from IP address 192.168.1.*
'foo'@'192.168.%' # username foo from IP address 192.168.*.*
'foo'@'%' # username foo from any IP address
在MySQL的特权系统中，这些是5个不同的用户"，可能具有5个不同的密码.如果不允许任何用户从您的IP地址或与您的IP地址匹配的任何通配符地址进行连接，那么这将是您收到的消息.在此处和此处.
In MySQL's privilege system, these are 5 different "users," potentially with 5 different passwords. If no users are allowed to connect from your IP address or any wildcard address that matches yours, this would be the message you'd get. This is explained further, here and here.
尽管可以使用SSH隧道，以便服务器会认为您是从localhost或其他受信任的主机进行连接，但是您无法使用mysql命令行客户端来覆盖服务器的配置.在评论中建议.
There is nothing you can do with the mysql command line client to override the server's configuration, though you could use an SSH tunnel so that the server would think you are connecting from localhost or another trusted host, as was suggested in a comment.
此修复方法是让您的朋友在您的地址带有GRANT语句. cPanel可能会提供一个接口来执行此操作-我不知道，因为我直接与MySQL一起工作-但如果这样做，则cPanel本质上是编写相同的GRANT语句并将其发送到MySQL服务器以执行
The fix it is for your friend to grant privileges to you at your address with a GRANT statement. cPanel might provide an interface to do this -- I have no idea, since I work directly with MySQL -- but if it does, that would be cPanel essentially writing the same GRANT statement and sending it to the MySQL server to be executed.
这篇关于连接到远程MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
