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:
And here are my 3 questions :
Thanks.
Python needs a MySQL driver to access the MySQL database. In this tutorial we will use the driver "MySQL Connector". We recommend that you use PIP to install "MySQL Connector".
Set up Firewall to Allow Remote MySQL ConnectionYour MySQL database should now allow remote connections from devices using your IP address. This value can be set in your MySQL configuration file or from all devices if this value is 0.0. 0.0. Your device firewall or network firewall will still block connections.
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.
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
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With