python remote database connect to mysql

Question

I have a Mac computer and want to connect to a mysql db of an Amazon Linux server.

In terminal I did:

sudo ssh -v -i "key_file.pem" -L 22:localhost:22 [email protected]

After that I could connect to the db on Amazon from Mac in MySQL Workbench with following settings:

However, when I try something like: "mysql -h localhost —-protocol=TCP -u root -p" or when I try in SQLalchemy:

engine = create_engine("mysql+mysqldb://root:password_mysql_amazon@localhost/db?host=localhost?port=3306")

I get an error: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

So I switched localhost for 127.0.0.1:22 but then I couldn't find where to add my key_file.pem?

So how do I arrange connection in SQLalchemy to this server that needs to be accessed with a key-file via ssh? Or am going about this all wrong? Thanks!

CorneeldH · Accepted Answer · 2018-10-17 16:52:12Z

1

Solved it with the help of this tutorial (not easy to find): https://medium.com/@amirziai/query-your-database-over-an-ssh-tunnel-with-pandas-603ce49b35a1.

Since I used SQLalchemy I didn't do instructions for pandas + MySQLdb / mysqlclient, but:

from sshtunnel import SSHTunnelForwarder
server = SSHTunnelForwarder(
        (host, 22),
        ssh_username=ssh_username,
        ssh_private_key=ssh_private_key,
        remote_bind_address=(localhost, 3306))

server.start()
engine = create_engine(
        'mysql+mysqldb://root:[email protected]:%s/db' % server.local_bind_port)`

answered Oct 17, 2018 at 16:52

CorneeldH

5932 gold badges8 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

python remote database connect to mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related