Conecting to MySQL in a remote server from python

Question

I'm using Python 3.5, pymysql 0.7.6 on MacOS X 10.12.

I'm trying to use python to access a MySQL database in a remote server. I have no problems to access from the command line using:

ssh [email protected]
[email protected]'s password: my_server_password

and then in the server:

mysql my_database -p
Enter password: my_database_password

And it works and I can do all sort of things with my database. Now, I try to do the same within python, following the documentation or the numerous examples I've found in other posts here:

import pymysql

cnx = pymysql.connect(host='XXX.XXX.XXX.XXX', port='3306', user='root', password='my_server_password', db='my_database')

And it does not work, getting as error:

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'XXX.XXX.XXX.XXX' ([Errno 61] Connection refused)")

The credentials are the correct credentials and I've checked that the port is the correct port, as it is suggested in other posts. I suspect it might be related with the database having also a password, not just the server, but I haven't found any way of including both passwords. Indeed, I'm not sure which password should be included in the connect command, if the server password or the database password. It does not work with neither of them.

So, do you have any suggestion about what might be the issue here or if I'm missing an important bit?

gen_Eric · Accepted Answer · 2017-03-10 20:09:27Z

1

When you run the mysql command, you are doing this in an SSH shell. That is you are connecting to the server running on the remote machine via a localhost connection. That remote server doesn't seem to be set up to allow remote connections to it, only connections from the machine itself.

You'll need to have your python script connect to the MySQL server the same way you are, via SSH. You can open an SSH tunnel to port 3306 on the remote server.

The module I like to use for this purpose is: https://pypi.python.org/pypi/sshtunnel

from sshtunnel import SSHTunnelForwarder
import pymysql

server = SSHTunnelForwarder(
    'XXX.XXX.XXX.XXX',
    ssh_username='root',
    ssh_password='my_server_password',
    remote_bind_address=('127.0.0.1', 3306)
)
server.start()

cnx = pymysql.connect(
    host='127.0.0.1',
    port=server.local_bind_port,
    user='root',
    password='my_database_password',
    db='my_database'
)

# Make sure to call server.stop() when you want to disconnect
# after calling cnx.close()

edited Mar 10, 2017 at 20:09

answered Mar 10, 2017 at 20:02

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

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

1 Comment

dalonsoa Over a year ago

Thank you very much! This solution worked like a charm. Very clear explanation, too.

Livvy Mackintosh · Accepted Answer · 2017-03-11 00:49:39Z

Based on the information given, I would suggest you try the following:

Ensure the MySQL bind address is "0.0.0.0". You can do this by editing /etc/mysql/my.cnf and checking the bind-address option is set to "0.0.0.0"
Make sure the port is open from your remote machine. Install "nmap" then use nmap -sS -O -p3306 <public-server-ip>. If see something like 3306/mysql open all good. If not, you need to ensure your firewall isn't blocking that port.

As Rocket Hazmat suggests you probably will want to use an SSH tunnel to reduce the risks of exposing MySQL port directly as data is probably sent in the clear. If you just need to test your code locally before deploying to the server you can use the following command before you run your Python code: ssh -f user@serverip -L 3306:127.0.0.1:3306 -N. In this case you don't need to complete step one and two.

Aziz Zoaib · Accepted Answer · 2019-04-16 12:39:27Z

0

working code

#!/usr/local/bin/python3
# -*- coding: utf-8 -*-

import pymysql
import sshtunnel
from sshtunnel import SSHTunnelForwarder

server = SSHTunnelForwarder(
    ('xx.xx.xx.xx', 22),
    ssh_username='deploy',
    ssh_pkey='~/.ssh/id_rsa',
    remote_bind_address=('127.0.0.1', 3306)
)
server.start()

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

with con:

    cur = con.cursor()
    cur.execute("SELECT VERSION()")

    version = cur.fetchone()

    print("Database version: {}".format(version[0]))

answered Apr 16, 2019 at 12:39

Aziz Zoaib

81112 silver badges23 bronze badges

Collectives™ on Stack Overflow

Conecting to MySQL in a remote server from python

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related