Connect Django Project to a distant Mysql Database

Question

I'm working on a Django Project on my localhost and I would like to use a distant MySQL Database.

My localhost IP is : 172.30.10.XX

My MySQL distant server is : 172.30.10.XX

In my Django settings.py file, I wrote :

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'DatasystemsEC',
        'USER': 'root',
        'PASSWORD': '*****',
        'HOST': '172.30.10.XX',
        'PORT': '3306',
        'OPTIONS': {
            'init_command': 'SET innodb_strict_mode=1',
        },
    }
}

My Database name is : DatasystemsEC

But, when I run : python manage.py migrate, I get this error :

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 367, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 359, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 294, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 342, in execute
    self.check()
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 374, in check
    include_deployment_checks=include_deployment_checks,
  File "/usr/local/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 61, in _run_checks
    issues = run_checks(tags=[Tags.database])
  File "/usr/local/lib/python2.7/site-packages/django/core/checks/registry.py", line 81, in run_checks
    new_errors = check(app_configs=app_configs)
  File "/usr/local/lib/python2.7/site-packages/django/core/checks/database.py", line 10, in check_database_backends
    issues.extend(conn.validation.check(**kwargs))
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/mysql/validation.py", line 9, in check
    issues.extend(self._check_sql_mode(**kwargs))
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/mysql/validation.py", line 13, in _check_sql_mode
    with self.connection.cursor() as cursor:
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 231, in cursor
    cursor = self.make_debug_cursor(self._cursor())
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 204, in _cursor
    self.ensure_connection()
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 199, in ensure_connection
    self.connect()
  File "/usr/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 199, in ensure_connection
    self.connect()
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 171, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 263, in get_new_connection
    conn = Database.connect(**conn_params)
  File "/Library/Python/2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/Library/Python/2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
django.db.utils.OperationalError: (2003, "Can't connect to MySQL server on '172.30.10.XX' (61)")

On MySQL with phpmyadmin, I have :

enter image description here

I assume I need to configure a new user in order to register my MacOSX localhost ?

So I created a new user named osx with all granted privileges. But it still doesn't work.

Thank you if you could help me

For security reasons, I suggest you remove your real IP addresses and DB name from the question. Try testing the connection using the following method http://stackoverflow.com/a/11790458/988082 — ozking, Feb 01 '17 at 11:25

score 2 · Answer 1 · answered Feb 01 '17 at 11:47

2

I found a solution :

In my my.cnf file I added this lines :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
bind-address=172.30.10.XX

and I run sudo service mysql restart

It seems to work (no error)

answered Feb 01 '17 at 11:47

Essex

6,042
11
67
139

1

you are well aware of the security implications of leaving a mysql port open to the world right? – e4c5 Feb 01 '17 at 14:35
What I have to do in order to allow my computer, but just him ? – Essex Feb 01 '17 at 17:59
You need to setup a firewall, create a VPC or setup ssh tunnels – e4c5 Feb 01 '17 at 18:02
I have a very good firewall in my compagny. PaloAlto. So it's not possible to access to our network – Essex Feb 01 '17 at 18:09
And what happens when a port is just left open? That's no longer protected by firewalls :)))) – e4c5 Feb 01 '17 at 23:39

score 0 · Answer 2 · answered Feb 01 '17 at 11:27

0

All of your users are created solely to use from localhost (when you ssh to server).

You should have users that could access from outside but be careful to limit access as much as possible for other intruders

Hostname of your user should be changed to for instance your IP address

answered Feb 01 '17 at 11:27

iklinac

14,944
4
28
30

Also the MySQL instance config must be set to allow connections from external networks (e.g. `listen = 0.0.0.0`) but this should be done securely and the database should have strong passwords for all users. – ojrask Feb 01 '17 at 11:30
@iklinac Yes it's what I've done, but stille the same error. I learnt I have to modify `bind-address` but my `my.cnf` file doesn't have this line – Essex Feb 01 '17 at 11:41

Connect Django Project to a distant Mysql Database

2 Answers2