使用Python MySQL .connector远程连接到MySQL

The following code (ran from a different machine than the mysql server, within the same LAN), to locally connect to MySQL database using Python3 and mysql.connector works:

下面的代码(从不同于mysql服务器的机器中运行，在相同的LAN中)使用Python3和mysql本地连接到mysql数据库。连接器工作原理:

import mysql.connector
cnx = mysql.connector.connect(host='192.168.0.24', database='import_test',user='user_builder', password='password***', port=3309)

However, the following code, to remotely connect to the same database, does NOT work:

但是，要远程连接到同一数据库，以下代码不能工作:

import mysql.connector
cnx = mysql.connector.connect(host='http://imaginarywebsite.ddns.net', database='import_test',user='user_builder', password='password***', port=3309)

Instead, I receive the following error:

相反，我收到以下错误:

File "C:\Users\****\AppData\Roaming\Python\Python34\site-packages\mysql\connector\network.py", line 464, in open_connection
     errno=2003, values=(self.get_address(), _strioerror(err)))
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'http://imaginarywebsite.ddns.net:3309' (11004 getaddrinfo failed)

Here is an extract of my my.cnf file:

以下是我的文件摘录:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1024M
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=7000
innodb_thread_concurrency=0

port            = 3309
bind-address    = 0.0.0.0

So, here is what currently works:

所以，这就是目前的工作:

Connect locally to the databse using 192.168.0.24:3309 address, so I am sure the problem does not come from 'privileges granting' or any login/password/port error.
使用192.168.0.24:3309地址在本地连接到数据库，因此我确信问题不是来自“权限授予”或任何登录/密码/端口错误。
Connect remotely via phpmyadmin using http://imaginarywebsite.ddns.net/phpmyadmin, so I am sure the problem does not come from my DNS server.
通过phpmyadmin使用http://imaginarywebsite.ddns.net/phpmyadmin进行远程连接，因此我确信问题不是来自我的DNS服务器。

And here are my 3 questions :

以下是我的三个问题:

Any Idea where the problem can come from?
你知道问题从何而来吗?
Should using SSH connection be a solution to my problem?
使用SSH连接是否可以解决我的问题?
If SSH is the solution, is it possible to use SSH parameters through mysql.connector since it does not seem to be presented in the official documentation?
如果SSH是解决方案，是否可以通过mysql使用SSH参数。连接器，因为它似乎没有出现在官方文档中?

Thanks.

谢谢。

3 个解决方案

#1

If you're running PHPMyAdmin on the same server that your mysql daemon is running on, here's what's happening: you have your webserver configured to accept connections from all interfaces, but mysql configured to only accept local connections. Since PHPMyAdmin is colocated wit mysql, it will only be making local connections which is why it works but your code doesn't.

如果在mysql守护进程所在的服务器上运行PHPMyAdmin，则会发生以下情况:您的web服务器配置为接受来自所有接口的连接，但mysql配置为只接受本地连接。因为PHPMyAdmin和mysql是共配置的，所以它只能进行本地连接，这就是为什么它可以工作的原因，但是您的代码不能。

So, double check that your mysql daemon is configured to listen interfaces. You should have a line something like bind-address=0.0.0.0 in your mysql.cnf file. See this answer for more details.

因此，请仔细检查您的mysql守护进程是否配置为监听接口。在您的sqmyl .cnf文件中应该有一个类似bind-address= 0.0.0.0.0的行。更多细节见此答案。

#2

Try to edit you non working example to this (no http in the host)

尝试将不工作的示例编辑为这个(主机中没有http)

import mysql.connector
cnx = mysql.connector.connect(host='imaginarywebsite.ddns.net', database='import_test',user='user_builder', password='password***', port=3309)

#3

-1

Actually the answer was to use the global IP address of my server instead of the domain name, probably an issue of name management.

实际上，答案是使用服务器的全局IP地址而不是域名，这可能是名称管理的问题。

Replacing host='http://imaginarywebsite.ddns.net' by 'host=85.23.56.****" made it work.

更换主机= ' http://imaginarywebsite.ddns.net ','主机= 85.23.56。* * * *”工作。

#1