how to connect to mysql via ssh by Python

Question

I have check these answers in StackOverflow but they don't work for me: A1, A2 and A3.

I write my code follows as these answers and got'(1045, "Access denied for user 'root'@'localhost' (using password: YES)")'. Why is that?

my code here:

first try:

from sshtunnel import SSHTunnelForwarder

with SSHTunnelForwarder(
      ('****.ucd.ie', 22),
      ssh_password="***",
      ssh_username="s***",
      remote_bind_address=('127.0.0.1', 3306)) as server:

     con = pymysql.connect(user='root',passwd='***',db='dublinbus',host='127.0.0.1',port=3306)

second try:

from sshtunnel import SSHTunnelForwarder
import pymysql
import pandas as pd

tunnel = SSHTunnelForwarder(('****.ucd.ie', 22), ssh_password='***', ssh_username='s***t',
     remote_bind_address=('127.0.0.1', 3306)) 
tunnel.start()
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='****', port=3306)
data = pd.read_sql_query("SHOW DATABASES;", conn)

third try:

from sshtunnel import SSHTunnelForwarder
import pymysql

with SSHTunnelForwarder(
    ('****.ucd.ie',22),  
    ssh_password='****',
    ssh_username='s****t',
    remote_bind_address=('127.0.0.1', 3306)) as server:  
 
    db_connect = pymysql.connect(host='127.0.0.1',  
                                 port=3306,
                                 user='root',
                                 passwd='****',
                                 db='dublinbus')
 
    cur = db_connect.cursor()
    cur.execute('SELECT stop_num FROM dublinbus.stops limit 10;')
    data=cur.fetchone()
    print(data[0])

All of them give me: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

So how could I connect to my remote Mysql via SSH?

Moreover, I use the same 'ssh hostname' and 'mysql hostname' 'mysql server port' in my local mysql workbench. I donot know whether it will have some impact or not.

#I use *** to replace some private information.

tanglai · Accepted Answer · 2020-06-23 13:04:16Z

2

this could work:

from sshtunnel import SSHTunnelForwarder
import pymysql
import pandas as pd

server = SSHTunnelForwarder(
ssh_address=('****', 22),
ssh_username="****",
ssh_password="****",
remote_bind_address=("127.0.0.1", 3306))

server.start()

con = pymysql.connect(user='root',passwd='***',db='****',host='127.0.0.1',port=server.local_bind_port)

answered Jun 23, 2020 at 13:04

tanglai

1612 silver badges11 bronze badges

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

1 Comment

Rob.Kachmar Over a year ago

This worked for me as well. Although, to help clarify, I had to put my remote MySQL server name in the remote_bind_address argument, instead of 127.0.0.1, and I had to leave 127.0.0.1 in the pymsql.connect function. At first, I changed both the 127.0.0.1 values to the remote MySQL server name, which kept giving timeout errors. This article ultimately helped me figure out the issue: pauldesalvo.com/…

Collectives™ on Stack Overflow

how to connect to mysql via ssh by Python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related