Remotely connect to MySQL with Python mysql.connector

13,123

Solution 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.

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

Solution 2

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
)

Solution 3

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.

Share:
13,123

Related videos on Youtube

ylnor
Author by

ylnor

Updated on September 19, 2022

Comments

  • ylnor
    ylnor about 1 year

    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 :

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

  • ylnor
    ylnor about 7 years
    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