Connecting to MySQL over SSH using Python

Question

I need some help adding tables from a desktop PC to an existing MySQL database (db name DB2639162) on a webserver using a python script. I have written the following script (create_db.py):

import MySQLdb
from sshtunnel import SSHTunnelForwarder
ssh_pwd=''
ssh_usr=''
mysql_pwd=''
mysql_usr=''
with SSHTunnelForwarder(('ssh.strato.de', 22),
                        ssh_password=ssh_pwd, ssh_username=ssh_usr,
                        remote_bind_address=('rdbms', 3306)) as server:
    print 'Server connected via SSH!'

    db1 = MySQLdb.connect(host='127.0.0.1',
                      port=server.local_bind_port,
                      user=mysql_usr,
                      passwd=mysql_pwd,
                      db='DB2639162')
    cursor = db1.cursor()
    sql = 'CREATE TABLE mydata'
    cursor.execute(sql)
    db1.close()

But unfortunately the script does not work and I get the output below. Obviously, the SSH connection can be established successfully, but the access to the database fails.

Server connected via SSH!
2016-09-18 11:02:19,291| ERROR   | Secsh channel 0 open FAILED: open failed: Administratively prohibited
2016-09-18 11:02:19,295| ERROR   | Could not establish connection from ('127.0.0.1', 44017) to remote side of the tunnel
Traceback (most recent call last):
  File "create_db.py", line 18, in <module>
    db='DB2639162')
  File "build/bdist.linux-x86_64/egg/MySQLdb/__init__.py", line 81, in Connect
  File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 193, in __init__
_mysql_exceptions.OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 0")

Additionally, the usernames, passwords are fine as the terminal commands work well and grant me access to the MySQL database.

ssh [email protected]
mysql -h rdbms -u mysql_usr -p DB2639162

Thank you in advance

Did you manage to solve this?

duality_
– duality_

2017-06-13 08:33:12 +00:00
Commented Jun 13, 2017 at 8:33 — duality_
– duality_, Commented Jun 13, 2017 at 8:33

nobody · Accepted Answer · 2016-09-18 14:28:39Z

1

The answer is right there in the error message:

2016-09-18 11:02:19,291| ERROR | Secsh channel 0 open FAILED: open failed: Administratively prohibited

Running the MySQL command-line client in a shell session on a server and setting up port forwarding/tunneling are completely different things. The fact that you can do one does not imply that you can do the other.

This server obviously forbids port forwarding/tunneling ("Administratively prohibited"). You'll need a different approach.

answered Sep 18, 2016 at 14:28

nobody

20.3k17 gold badges59 silver badges80 bronze badges

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

1 Comment

Abhishake Gupta Over a year ago

You mentioned to use a different approach, Can you suggest something for this ?

Collectives™ on Stack Overflow

Connecting to MySQL over SSH using Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related