DDiran
DDiran

Reputation: 573

Connecting Django to remote MySQL database using Workbench

I am hosting my website on PythonAnywhere.

I have two databases on the service: myusername$production and myusername$dev.

The production db backs itself up every night and loads the dev database with the most recent batch of live data. I am trying to connect my Django development environment to the dev database through ssh, which is possible as a paying PythonAnywhere customer.

I followed their advice and downloaded MySQL Workbench, which connects to said database without any issues. I then tried to set up my dev.py settings in Django as follows:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', 
        'NAME': 'myusername$dev',
        'USER': 'myusername',
        'PASSWORD': 'mypassword',
        'HOST': '127.0.0.1',
        'PORT': '3306',
    }
}

However this results in a django.db.utils.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' (10061)") error. I also tried replacing 127.0.0.1 with localhost, as well as leaving both host and port blank, but neither of these worked.

I then tried the other two options on their website, aka accessing the db directly through python code, as well as using Putty (I'm a windows 10 user) however non of these worked.

Any advice would be greatly appreciated.

Upvotes: 2

Views: 4930

Answers (1)

Giles Thomas
Giles Thomas

Reputation: 5867

PythonAnywhere dev here. The help page that you link to has several different options for connecting to a PythonAnywhere MySQL database from outside; it sounds like you are -- or, at least, initially were -- confusing the first with the second two.

The first is to use MySQL Workbench. MySQL Workbench has options for setting up an SSH tunnel so that you can connect to a database that isn't directly accessible from the public Internet. But once you've set up that tunnel, it only helps if you're running stuff within MySQL Workbench -- it doesn't affect other code that you're running on your machine. So it won't help with Django code.

The second option is when you want to connect to the database from Python code. If you have complete control over when the database connections are made, you can put the SSHTunnelForwarder wrapper around the SQL code, and any SQL code you do inside that with statement will be able to connect to the database -- but code outside that statement won't. So that doesn't help when you're using Django either, because its MySQL code is hidden deep inside the Django library and wrapping it with the with statement would be very tricky.

The third option is for setting up an SSH tunnel manually, but unfortunately the instructions are targeted at a Unix environment -- eg. Linux or Macs. When you do that, it starts a server on your local machine -- IP address 127.0.0.1, port 3306 -- which accepts connections, and then forwards them over SSH so that they come out inside the Pythonanywhere firewall and are sent to the correct MySQL server. This means that the local server looks, to code on your machine, as if it's a MySQL server, even though it's actually just handing off the work to the PythonAnywhere machine.

So what you needed to do (and it looks like you almost got there!) was to set up PuTTY to do what the Unix instructions do with the ssh command. These instructions are almost what you want; the differences would be

  • The "SSH gateway" would be ssh.pythonanywhere.com
  • The "Source port" would be 3306.
  • The "destination address" to the name of your PythonAnywhere MySQL database host (from the "Databases" tab) followed by :3306.

Upvotes: 5

Related Questions