Connecting Django to a DB Behind a Proxy

Question

I'd like to connect a Django to my production MySQL database (read only) so that I can use it to test an analytics application using real data.

Is there a way to specify that django should connect to the db through an ssh proxy in the same way that, say, Sequel Pro lets you specify to connect through a particular SSH proxy with username and password?

If so, what would the DATABASES entry look like?

Answer 1

Just use SSH tunneling. To ease your job, you can install autossh which automatically checks if the ssh connection is alive and re-establishes it if it goes down.

Then you can open a tunnel using the following command:

autossh -f -N -L LocalPort:MySQLAddress:MySQLPort your_login@your_server

Where:

• your_server - the server to which you connect via ssh
• your_login - your login on your_server
• LocalPort - any unused port on the machine where Django is running. This port will be used for the tunnel.
• MySQLAddress - the address of your MySQL server as it is reachable from your_server (that is, if MySQL is running on your_server it will be 127.0.0.1)
• MySQLPort - the port MySQL is listening on

Then in Django settings you specify MySQL IP as 127.0.0.1 and port as the LocalPort you set above.

An example:

autossh -f -N -L 10000:127.0.0.1:3306 [email protected]

When you run this command, you will have an SSH tunnel listening on the local port 10000. So if you connect to localhost:10000, your connection will be tunneled to localhost:3306 on the 192.168.1.3 server.

Answer 2

You could also do the SSH tunnel directly in Python using sshtunnel, and a variant of the following:

import mysql.connector
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.yourserver.com'),
    ssh_username='your SSH server username', ssh_password='secret1',
    remote_bind_address=('your database hostname', 3306)
) as tunnel:
    connection = mysql.connector.connect(
        user='your database username', password='secret2',
        host='127.0.0.1', port=tunnel.local_bind_port,
        database='your database name, e.g. yourusername$mydatabase',
    )
    # Do stuff
    connection.close()

Source: PythonAnywhere