(macOS)[mysql] django local mysqlDB 연동

INTRO

OS, python, django, mysql 환경

[macbook : local]

macOS : Catalina 10.15.1

python 3.7.4

django 2.2.6

mysql  Ver 8.0.18 for osx10.15 on x86_64 (Homebrew)

 

## Version 확인 방법


## python 3.7.4
# virtualenv 환경에서 python or python3
(jbe)$ python -V

## django 2.2.6
# python console에서 확인
>> import django
>> django.VERSION

## mysql  Ver 8.0.18 for osx10.15 on x86_64 (Homebrew)
# macOS mysql terminal 확인
$ mysql -V

1. [macOS/mysql] root pass 변경 및 DB/USER 생성 및 권한 부여 

1) mysql install

$ brew install mysql

$ pip install mysqlclient

Collecting mysqlclient
  Using cached https://files.pythonhosted.org/packages/d0/97/7326248ac8d5049968bf4ec708a5d3d4806e412a42e74160d7f266a3e03a/mysqlclient-1.4.6.tar.gz
Building wheels for collected packages: mysqlclient
  Building wheel for mysqlclient (setup.py) ... error
  ERROR: Command errored out with exit status 1:
   command: /Users/jinoz/.virtualenvs/jbe/bin/python3.7 -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/private/var/folders/ym/dmbvt01n3ksccz5gx1jyg0s40000gn/T/pip-install-8lv6fen3/mysqlclient/setup.py'"'"'; __file__='"'"'/private/var/folders/ym/dmbvt01n3ksccz5gx1jyg0s40000gn/T/pip-install-8lv6fen3/mysqlclient/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' bdist_wheel -d /private/var/folders/ym/dmbvt01n3ksccz5gx1jyg0s40000gn/T/pip-wheel-_4ax9170 --python-tag cp37
       cwd: /private/var/folders/ym/dmbvt01n3ksccz5gx1jyg0s40000gn/T/pip-install-8lv6fen3/mysqlclient/
  Complete output (30 lines):
  running bdist_wheel
  running build
  running build_py
  creating build
  creating build/lib.macosx-10.14-x86_64-3.7
  creating build/lib.macosx-10.14-x86_64-3.7/MySQLdb

....



## Error 발생 : 참고 https://github.com/PyMySQL/mysqlclient-python/issues/131
## You can set ssl library path explicitly.
## LDFLAGS=-L/usr/local/opt/openssl/lib pip install mysqlclient

$ LDFLAGS=-L/usr/local/opt/openssl/lib pip install mysqlclient

$ mysql.server start

2) root pass 변경

$ sudo mysql -u root

mysql> use mysql;
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| global_grants             |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| password_history          |
| plugin                    |
| procs_priv                |
| proxies_priv              |
| role_edges                |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

mysql> select host, user, plugin, authentication_string, password_last_changed from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | plugin                | authentication_string                                                  | password_last_changed |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2019-11-27 19:55:33   |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2019-11-27 19:55:33   |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2019-11-27 19:55:33   |
| localhost | root             | caching_sha2_password |                                                                        | 2019-11-27 19:55:32   |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+

mysql> alter user 'root'@'localhost' identified with caching_sha2_password by 'your_root_pass';
mysql> flush privileges;

mysql> select host, user, plugin, authentication_string, password_last_changed from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | plugin                | authentication_string                                                  | password_last_changed |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2019-11-27 19:55:33   |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2019-11-27 19:55:33   |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2019-11-27 19:55:33   |
| localhost | root             | caching_sha2_password | NEW_PASS_string                                                        | 2019-12-02 11:44:55   |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+

3) DB/USER 생성 및 권한 부여

$ mysql -u root -p
Enter password:


## Test_DB 생성
mysql> create database TestDB;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| TestDB             |
+--------------------+

## Test_User 생성
mysql> create user 'testuser'@'localhost' identified by 'your_pass';
mysql> grant all privileges on TestDB.* to testuser@'localhost';
mysql> flush privileges;
mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| testuser         | localhost |
+------------------+-----------+

 

2. [macOS/mysql] django project jinozpiblog 생성 및 localhost mysqlDB setting

$ workon jbe
(jbe)$ cd ~/.virtualenvs/jbe
(jbe)$ python -m pip install --upgrade pip
(jbe)$ django-admin.py startproject jinozpiblog

(jbe)$ cd jinozpiblog
(jbe)$ tree
.
├── jinozpiblog
│   ├── __init__.py
│   ├── settings.py
│   ├── urls.py
│   └── wsgi.py
└── manage.py

--jinozpiblog/setting.py
....
ALLOWED_HOSTS = ['127.0.0.1']
....

DATABASES = {
    'default': {
        #'ENGINE': 'django.db.backends.sqlite3',
        #'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'TestDB',
        'USER': 'testuser',
        'PASSWORD': 'yourPASS',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}
....
LANGUAGE_CODE = 'ko' #default 'us-en'
TIME_ZONE = 'Asia/Seoul' #default ' UTC'
....

- django project migrate

 (jbe)$ python manage.py showmigrationsadmin
 [ ] 0001_initial
 [ ] 0002_logentry_remove_auto_add
 [ ] 0003_logentry_add_action_flag_choices
auth
 [ ] 0001_initial
 [ ] 0002_alter_permission_name_max_length
 [ ] 0003_alter_user_email_max_length
 [ ] 0004_alter_user_username_opts
 [ ] 0005_alter_user_last_login_null
 [ ] 0006_require_contenttypes_0002
 [ ] 0007_alter_validators_add_error_messages
 [ ] 0008_alter_user_username_max_length
 [ ] 0009_alter_user_last_name_max_length
 [ ] 0010_alter_group_name_max_length
 [ ] 0011_update_proxy_permissions
contenttypes
 [ ] 0001_initial
 [ ] 0002_remove_content_type_name
sessions
 [ ] 0001_initial



(jbe)$ python manage.py runserver
Performing system checks...

System check identified no issues (0 silenced).

You have 17 unapplied migration(s). Your project may not work properly until you apply the migrations for app(s): admin, auth, contenttypes, sessions.
Run 'python manage.py migrate' to apply them.

December 02, 2019 - 13:37:05
Django version 2.2.6, using settings 'jinozpiblog.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CONTROL-C.

- browser : 127.0.0.1:8000

- django project migrate 및 runserver

(jbe)$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying sessions.0001_initial... OK
  
(jbe)$ python manage.py showmigrations
admin
 [X] 0001_initial
 [X] 0002_logentry_remove_auto_add
 [X] 0003_logentry_add_action_flag_choices
auth
 [X] 0001_initial
 [X] 0002_alter_permission_name_max_length
 [X] 0003_alter_user_email_max_length
 [X] 0004_alter_user_username_opts
 [X] 0005_alter_user_last_login_null
 [X] 0006_require_contenttypes_0002
 [X] 0007_alter_validators_add_error_messages
 [X] 0008_alter_user_username_max_length
 [X] 0009_alter_user_last_name_max_length
 [X] 0010_alter_group_name_max_length
 [X] 0011_update_proxy_permissions
contenttypes
 [X] 0001_initial
 [X] 0002_remove_content_type_name
sessions
 [X] 0001_initial

(jbe)$ python manage.py runserver
Performing system checks...

System check identified no issues (0 silenced).
December 02, 2019 - 13:50:29
Django version 2.2.6, using settings 'jinozpiblog.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CONTROL-C.

- django 연동 확인 

 

- tree 확인 : mysqlDB 연동하였기 때문에 db.sqlite3는 생성되지 않는다. (sqlite3 사용 참고 : https://jinozblog.tistory.com/6)

.
├── jinozpiblog
│   ├── __init__.py
│   ├── __pycache__
│   │   ├── __init__.cpython-37.pyc
│   │   ├── settings.cpython-37.pyc
│   │   ├── urls.cpython-37.pyc
│   │   └── wsgi.cpython-37.pyc
│   ├── settings.py
│   ├── urls.py
│   └── wsgi.py
└── manage.py