How to connect to a MySQL database, via SSH-tunnel, using Python 3.5?

Question

I am using windows 7 with Python 3.4 (with Pycharm) and try to acces a remote mySQL-database through SSH with a private key.

I use SSHTunnel (ghithub_link) to setup the SSH-Tunnel as follow :

    from sshtunnel import SSHTunnelForwarder

server = SSHTunnelForwarder(
    ("blablabla.ddns.net", 3307),
    ssh_host_key=None,
    ssh_username="name",
    ssh_password=None,
    ssh_private_key="E:\id_rsa",
    ssh_private_key_password="pssrd",
    remote_bind_address=('192.168.0.24', 3306))

The I connect to mySQL database using Oracle's MySQL-connector-Python (source) as follow :

    cnx=mysql.connector.connect(user='user_worker', password='passwrd',host="blablabla.ddns.net", port=3307, database='base_101')
    cnx.close()

I receive the following error :

       Traceback (most recent call last):

      File "C:/Users/PycharmProjects/untitled _Get.py", line 37, in <module> cnx=mysql.connector.connect(user='user_worker', password=’pass',host="blablabla.ddns.net", port=22, database=’base_101')

      File "C:\Users \Python34\site-packages\mysql\connector\__init__.py", line 179, in connectreturn MySQLConnection(*args, **kwargs)

      File "C:\Users \Python34\site-packages\mysql\connector\connection.py", line 95, in __init__self.connect(**kwargs)

      File "C:\Users \Python34\site-packages\mysql\connector\abstracts.py", line 719, in connectself._open_connection()

      File "C:\Users\YOANN\AppData\Roaming\Python\Python34\site-packages\mysq\connector\connection.py", line 206, in _open_connectionnself._socket.open_connection()

      File "C:\Users\YOANN\AppData\Roaming\Python\Python34\site-packages\mysq\connector\network.py", line 475, in open_connection errno=2003, values=(self.get_address(), _strioerror(err)))


   mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (10061 Aucune connexion n’a pu être établie car l’ordinateur cible l’a expressément refusée)

I am assuming the problem comes from the ports, but I can't figure it out. In an other hand I can connect to the base via the SSh and SSh-key using the pycharm database tool, so no problem on the server's side nor the key/ssh connection).

Here is how it is succesfully working using Oracle-Worbench. This is what I am trying to do in Python : workbench-example-image.img . — ylnor
– ylnor, Commented Jan 14, 2016 at 16:41

Peter Pudaite · Accepted Answer · 2020-03-30 09:26:24Z

3

You've got your host addresses and ports mixed up. This is what it needs to look like based on your example:

server = SSHTunnelForwarder(
    ("blablabla.ddns.net", 22),
    ssh_host_key=None,
    ssh_username="name",
    ssh_password=None,
    ssh_private_key="E:\id_rsa",
    ssh_private_key_password="pssrd",
    remote_bind_address=("127.0.0.1", 3306))

server.start()

cnx=mysql.connector.connect(user='user_worker',
    password='passwrd',
    host="127.0.0.1", 
    port=server.local_bind_port, 
    database='base_101')

# Do some DB stuff...

cnx.close()
server.stop()

This configuration assumes that you are tunneling directly to the MySQL server. If you are not (like when proxying through a DMZ or NAT) then the remote_bind_address will need to be the MySQL server address instead of the loopback.

edited Mar 30, 2020 at 9:26

answered Jul 19, 2017 at 23:22

Peter Pudaite

4451 gold badge9 silver badges20 bronze badges

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

Comments

Carlos Henrique Reis · Accepted Answer · 2020-04-07 04:30:12Z

-2

Using the pymysql lib in python code solves the problem.

I hope it helps

answered Apr 7, 2020 at 4:30

Carlos Henrique Reis

1099 bronze badges

Collectives™ on Stack Overflow

How to connect to a MySQL database, via SSH-tunnel, using Python 3.5?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related