Remotely connect to MySQL with Python mysql.connector

Question

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:

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.
Connect remotely via phpmyadmin using http://imaginarywebsite.ddns.net/phpmyadmin, so I am sure the problem does not come from my DNS server.

And here are my 3 questions :

Any Idea where the problem can come from?
Should using SSH connection be a solution to my problem?
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?

Thanks.

Did you verify on which ports/interfaces is the MySQL server listening on? Using command "netstat -ln |grep 3309" on the server you can see if MySQL is accepting only localhost interface or other interfaces as well. — Alesito
– Alesito, Commented Oct 31, 2016 at 13:33

MERose · Accepted Answer · 2021-05-02 09:31:22Z

10

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

import mysql.connector

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

edited May 2, 2021 at 9:31

MERose

4,4817 gold badges58 silver badges86 bronze badges

answered Oct 31, 2016 at 11:13

Alexey Smirnov

2,62316 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-04-13 12:36:24Z

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.

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.

edited Apr 13, 2017 at 12:36

CommunityBot

11 silver badge

answered Oct 28, 2016 at 18:28

gbe

6813 silver badges11 bronze badges

1 Comment

ylnor Over a year ago

Thanks. Actually I didn't specify it, but I am running both codes from a different machine(192.168.0.10) than the mysql server (192.168.0.24). And my.cnf (not mysql.cnf, does it matter?) file already contained the line bind-address = 0.0.0.0

Deepanshu Mehta · Accepted Answer · 2023-04-12 19:33:53Z

1

Super Simple Example:

import mysql.connector

# Create a connection object
conn = mysql.connector.connect(
    user='user',
    password='password',
    host='localhost',
    database='mydb',
)

# Create a cursor object
cursor = conn.cursor()

# Execute a SELECT query
query = "SELECT * FROM mytable WHERE id = %s"
params = (1,)
cursor.execute(query, params)

# Check if any rows were returned
if cursor.rowcount == 0:
    print("No rows returned from the query")
else:
    # Fetch the first row of the result set
    row = cursor.fetchone()

    # Process the row as needed
    print(row)

# Close the cursor and connection
cursor.close()
conn.close()

answered Apr 12, 2023 at 19:33

Deepanshu Mehta

1,85817 silver badges15 bronze badges

Comments

ylnor · Accepted Answer · 2016-10-31 13:30:43Z

0

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

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

answered Oct 31, 2016 at 13:30

ylnor

4,9692 gold badges26 silver badges43 bronze badges

1 Comment

Mohnish Satidasani Over a year ago

Also do not put port number with IP address / host name like this host/ip:port Keep them separate in the object like this python mysql.connector.connect( host='imaginarywebsite.ddns.net', port=3309 ) Thanks I faced this issue while connecting to the database using mysql-connector in python

Eamonn Kenny · Accepted Answer · 2023-06-19 16:02:50Z

0

Standard port is 3306 not 3309

answered Jun 19, 2023 at 16:02

Eamonn Kenny

2,11221 silver badges21 bronze badges

Collectives™ on Stack Overflow

Remotely connect to MySQL with Python mysql.connector

5 Answers 5

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related