How to connect django developer server to MySQL database?

Question

Before I begin please respect that I'm a newbie, ask me what more information you need to solve this problem rather than close the question or something. ;) OK. I am trying to connect my Django developer server to a MySQL database that is being hosted on a remote server. I've installed MySQL and MySQL-Python and have these settings in 'settings.py':

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'database_name',                      # Or path to database file if using sqlite3.
        'USER': 'database_user',                      # Not used with sqlite3.
        'PASSWORD': 'database_password',                  # Not used with sqlite3.
        'HOST': 'db9.subsys.no',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
    }
}

When i try to start the developer server using python manage.py runserver I get this:

Traceback (most recent call last):
  File "/Library/Python/2.7/site-packages/django/core/management/commands/runserver.py", line 91, in inner_run
    self.validate(display_num_errors=True)
  File "/Library/Python/2.7/site-packages/django/core/management/base.py", line 266, in validate
    num_errors = get_validation_errors(s, app)
  File "/Library/Python/2.7/site-packages/django/core/management/validation.py", line 103, in get_validation_errors
    connection.validation.validate_field(e, opts, f)
  File "/Library/Python/2.7/site-packages/django/db/backends/mysql/validation.py", line 14, in validate_field
    db_version = self.connection.get_server_version()
  File "/Library/Python/2.7/site-packages/django/db/backends/mysql/base.py", line 411, in get_server_version
    self.cursor()
  File "/Library/Python/2.7/site-packages/django/db/backends/__init__.py", line 306, in cursor
    cursor = self.make_debug_cursor(self._cursor())
  File "/Library/Python/2.7/site-packages/django/db/backends/mysql/base.py", line 387, in _cursor
    self.connection = Database.connect(**kwargs)
  File "build/bdist.macosx-10.7-intel/egg/MySQLdb/__init__.py", line 81, in Connect
  File "build/bdist.macosx-10.7-intel/egg/MySQLdb/connections.py", line 187, in __init__
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on 'db9.subsys.no' (60)")

When I changed 'HOST': 'db9.subsys.no' to 'HOST': '' I got this instead:

Traceback (most recent call last):
  File "/Library/Python/2.7/site-packages/django/core/management/commands/runserver.py", line 91, in inner_run
    self.validate(display_num_errors=True)
  File "/Library/Python/2.7/site-packages/django/core/management/base.py", line 266, in validate
    num_errors = get_validation_errors(s, app)
  File "/Library/Python/2.7/site-packages/django/core/management/validation.py", line 103, in get_validation_errors
    connection.validation.validate_field(e, opts, f)
  File "/Library/Python/2.7/site-packages/django/db/backends/mysql/validation.py", line 14, in validate_field
    db_version = self.connection.get_server_version()
  File "/Library/Python/2.7/site-packages/django/db/backends/mysql/base.py", line 411, in get_server_version
    self.cursor()
  File "/Library/Python/2.7/site-packages/django/db/backends/__init__.py", line 306, in cursor
    cursor = self.make_debug_cursor(self._cursor())
  File "/Library/Python/2.7/site-packages/django/db/backends/mysql/base.py", line 387, in _cursor
    self.connection = Database.connect(**kwargs)
  File "build/bdist.macosx-10.7-intel/egg/MySQLdb/__init__.py", line 81, in Connect
  File "build/bdist.macosx-10.7-intel/egg/MySQLdb/connections.py", line 187, in __init__
_mysql_exceptions.OperationalError: (1045, "Access denied for user 'database_user'@'localhost' (using password: YES)")

Is it not possible to connect to MySQL using the developer server or do I need to do some kind of configuration on with the database? Reply if you have the answer or need more information.

Big Thanks in Advance!

Answer 1

Try using an IP-Adress instead of 'db9.subsys.no' after the 'HOST': Tag.

Answer 2

Just a quick sanity check - I assume you replaced the placeholder values (e.g. *database_name*) with the actual values?

Also, have you tried connecting to the database over the command line first (on your local development machine - not through some sort of SSH connection to a VPS etc.) to check that it's reachable from wherever you're developing from, e.g. mysql -u - p? If not, it probably means your web hosting company (I'm making an assumption that this isn't your database - please correct me if this is wrong and/or see @Burhan Khalid's answer) doesn't allow external connections to that database server and you won't be able to connect remotely from any software - this has nothing to do with Django. In this case either contact the hosting company to find out what port number to use (or if external database connections are even allowed under their firewall rules) or use a local database for development and then populate the production database from a dump-file of your local database. Here is a tutorial on this process - http://php.about.com/od/learnmysql/ss/mysql_backup.htm.

In my opinion this is a better way of doing things, as it is probably easier and more practical to develop locally and then upload. It can also prevent you from making a costly mistake if that winds up being the live database and you run one of the manage.py commands that can wipe out a table(s)!

Answer 3

Three things you should make sure of if you want to connect to a remote MySQL server:

1. The server is using networking and not sockets (there is no skip-networking line in my.cnf), and the server is listening on the public IP (with bind-address).

2. Your firewall allows remote access to the MySQL port, which is 3306 by default.

3. The user is allowed remote access:

   GRANT ALL ON someDatabase.* to someUser@some.remote.ip IDENTIFIED BY 'foopass';

Answer 4

You can use the database with the development server.

When you keep the host and port blank, django uses the default port to connect to the database on the local machine. In your case, you are getting an error because the username or password that you are using while connecting to the database on localhost is incorrect.

While connecting to a remote server, please make sure that you are allowed to access it remotely. MySQL doesnt allow remote connections by default.

Also, make sure that the port you are trying to connect on is not blocked by a firewall.

Read this to figure out what you need to do to allow this behaviour: http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

I hope this helps you figure out the solution.