Oracle XE Database 설치를 알아본다.

1. Oracle XE 설치
2. Uninstall

1. 오라클 XE 설치

XE 는 Express Edition 의 약어입니다.

오라클 18c 버전은 설치 파일이 용량이 2G가 넘기 때문에 다운로드부터 설치까지 수분~수십분 이상이 소요

설치에 문제가 생기는 원인들

• 컴퓨터 이름이 한글로 되어 있을 떄
• 오라클을 한번 설치한 적이 있을 경우
  • oracle home 에서
    • ./deinstall/deinstall
    • regedit를 이용하여 기록을 삭제
    • 방법 명령창에서 sc delete OracleServiceXE 를 수행)

XE 라이센스

• https://www.oracle.com/downloads/licenses/oracle-free-license.html

Download

최신 버전은 다운로드 페이지에서 직접 다운로드 할 수 있다.

• 2022/3월 현재 21c 버전을 다운로드 할 수 있다.

https://www.oracle.com/kr/database/technologies/xe-downloads.html

이전 버전은 로그인후 다운로드 할 수 있다. 회원가입후 로그인한다.

회원가입

무료 Oracle 계정은 이메일 인증을 통해서 계정 생성이 가능하다.

XE 시스템 제약

• 운영체제: Windows 10, Linux (우분투, 레드햇, CentOS 등)
• 최대 12GB 사용자 데이터
• 최대 2GB 데이터베이스 RAM
• 최대 CPU 스레드 2개
• 최대 3개의 PDB (Up to 3 Pluggable Databases)

Oracle XE 21c 설치

다운로드한 설치 파일을 실행한다.

라이센스

설치 위치

SYS, SYSTEM, PDBADMIN 계정 비밀번호입력: 관리자 계정 비밀번호를 정하여 주는 것으로 오라클 관리자 계정 아이디는 ‘SYSTEM’ 이다.

• 혼란을 방지하기 위해 Manager1 으로 입력하자.

설치 시작

윈도우 보안 경고에서 Java 허용

구성요소 구성

설치 완료

오라클 XE 설치후 시작메뉴

설치후 확인

서비스 상태 확인

1. Oracle XE 설치 완료되면, 윈도우 cmd 창에서 sc query 명령어를 통해 설치를 확인할 수 있다.

2. 윈도우 메뉴 중 ‘앱 및 기능’ 목록 확인

데이터베이스 연결 확인

tnslistener 사용

• Oracle XE 설치후 DB 연결을 위해 꼭 필요한 서비스인 ‘TNSListener’가 자동설치 되는데, cmd 창에서 연결 상태 확인이 가능하다.

1. tnsping

• cmd 창에서 tnsping 명령어 이용.
• ‘tnsping 127.0.0.1’을 실행하면 아래와 같이 TNSListener, 연결 상태를 확인할 수 있다.

2. lsnrctl

접속 에러

1. 접속에러 : TNS-12541 TNS 리스너 에러
   • https://shanepark.tistory.com/223

윈도우 컴퓨터 이름 에러일 가능성이 크다.
1. 내 컴퓨터 -> 속성 에서 컴퓨터 이름 확인
2. listener.ora 에서 HOST 이름 변경

2. 오라클 서비스 재시작
   • 윈도우 서비스 Oracle TNS Listener 실행 확인
   • 윈도우의 서비스에서 OracleServiceXE 서비스 재시작

작업관리자 혹은 서비스 매니져에서 사용 가능.

• 작업관리자 : Ctrl+Shift+ESC
• 서비스 매니져 : Win + R 에서 services.msc

listener.ora, tnsnames.ora

tnsnames.ora 와 listener.ora 파일의 HOST 를 localhost 로 변경한다.

tnsnames.ora

listener.ora

2 Uninstall

• https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinw/deinstalling-oracle-database-xe.html

참고

• IT CookBook, 오라클로 배우는 데이터베이스 개론과 실습(2판)-설치
  • https://download.hanbit.co.kr/exam/4502/
• Oracle Database가 갑자기 안될 때 TNS-12541, TNS-12560, TNS-00511 에러 해결
  • https://shanepark.tistory.com/223

클라이언트에서 원격 서버 MySQL DB server 접속시 SSH turnneling 을 이용할 수 있다.

1. ssh tunneling 이해
2. SSHTunnelForwarder 클래스
3. HeidiSQL ssh tunnel 사용

ssh tnnneling 이해

비 암호화된 통신을 지원하는 원격 프로그램이 암호화된 통신을 지원하는 ssh 를 사용해서 원격지에 접속이 가능하게 한다. 직접 TLS/SSL 구현을 하지 않더라도 SSL 로 암호화된 통신 채널을 통해 안전하게 프로그램을 사용할 수 있다.

터널링은 아래 같이 SSH 서버를 통해서 원격지에서 SSH 통신을 지원한다.

링크 참고2 의 그림

MySQL client 에서 Database server 포트 3306으로 직접 접속하지 않고 ssh 를 이용해 원격 서버에 연결하고 원격 서버 내부에서 mysql server 에 접속해 사용하게 해준다.

SSHTunnelForwarder 이용 tunneling 사용

sshtunnel 모듈이 필요하다.

1
2
3

> pip install pymysql
> pip install sshtunnel
> pip install paramiko

시나리오

다음 2개의 시나리오는 참조 링크 3 의 공식문서에 있는 내용이다.

사례1) 사용자가 8080 웹 서비스에 연결할 필요가 있을 때 22번 포트로만 연결가능할 때

1
2
3
4
5
6
7
8
9
10
11

----------------------------------------------------------------------

                            |
-------------+              |               +----------+
    LOCAL    |              |               |  REMOTE  | :22 SSH
    CLIENT   | <== SSH ========> |  SERVER  | :8080 web service
-------------+              |               +----------+
                            |
                         FIREWALL (only port 22 is open)

----------------------------------------------------------------------

사례2) SSH server 가 허용하는 경우.

1
2
3
4
5
6
7
8
9
10
11

----------------------------------------------------------------------

                            |
-------------+              |    +----------+               +---------
    LOCAL    |              |    |  REMOTE  |               | PRIVATE
    CLIENT   | <== SSH ========> |  SERVER  | <== local ==> | SERVER
-------------+              |    +----------+               +---------
                            |
                         FIREWALL (only port 443 is open)

----------------------------------------------------------------------

DB접속 정보

sshtunnel 패키지를 사용한다. DB 접속 정보를 외부 json 파일에서 얻어온다고 가정한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

import json
from pathlib import Path

import sqlalchemy as sqla
from sqlalchemy import text
import pymysql

import pandas as pd
import numpy as np

ROOT_PATH = Path.home()

with open( ROOT_PATH / Path('.api_keys/secret_keys.json')) as f:
    secrets = json.loads(f.read())

DB_USER, DB_PW = secrets['lecture']['userid'], secrets['lecture']['password']
SERVER24_USER, SERVER24_PW = secrets['DBSERVER']['userid'], secrets['DBSERVER']['password']

SSHTunnelForwarder 사용

1
2
3
4

SSHTunnelForwarder((HOST_IP, SSH_PORT),
                    ssh_username='USER_ID',
                    ssh_pkey='SSH_KEY',
                    remote_bind_address=('127.0.0.1', 3306))

• ssh_address_or_host:

  • tuple 형태나 string 형태로 설정할 수 있으며, 위와같이 키워드를 생략할 수 있습니다.
  • 튜플형식: (호스트주소, 포트) 함께 정의
  • 문자열 형식: 호스트 주소 설정 (ssh_port 키워드도 별도로 설정해줘야 합니다.)
    • ~/.ssh/config 에 설정한 Host

• ssh_port: ssh 서비스 포트

• ssh_username: ssh 연결에 사용될 인증된 사용자

• ssh_password: ssh 연결에 사용될 사용자의 접속 비밀번호
  보안을 위해 비밀번호를 설정하는 것보다는 private key을 사용하는 것을 권장합니다.

• ssh_pkey: ssh 연결에 사용될 private key 파일 혹은 paramiko.pkey.PKey

• remote_bind_address: (호스트주소, 포트) 튜플 형식. ssh 연결을 통해 접속한 원격 서버에서 접속할 private server 접속 정보.

with 구문과 사용

SSHTunnelForwarder 클래스에 __enter__, __exit__ magic method가 정의되어 with 구문과 함께 사용한다.

1
2
3
4
5
6
7
8
9
10
11
12
13

with sshtunnel.SSHTunnelForwarder(
        (_host, _ssh_port),
        ssh_username=_username,
        ssh_password=_password,
        remote_bind_address=(_remote_bind_address, _remote_mysql_port),
        local_bind_address=(_local_bind_address, _local_mysql_port)
) as tunnel:
    connection = mysql.connector.connect(
        user=_db_user,
        password=_db_password,
        host=_local_bind_address,
        database=_db_name,
        port=_local_mysql_port)

Windows 경우 보안 경고를 확인!

윈도우즈에서 파이썬 코드에서 SSHTunnelForwarder 를 처음 사용시 아래 같이 경고를 만난다.

사례1

사례1 로 접속하는 방법이 전형적인 터널링 구현이다. SSHTunnelForwarder을 with 구문과 함께 쓸 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

import pymysql
from sshtunnel import SSHTunnelForwarder

with SSHTunnelForwarder(('DBSERVER', 2020),
                        ssh_username=SERVER24_USER,
                        ssh_password=SERVER24_PW,
                        remote_bind_address=('127.0.0.1', 3306), ) as tunnel:

    # connect MySQL like local
    with pymysql.connect(
        host='127.0.0.1', #(local_host)
        user=DB_USER,
        passwd=DB_PW,
        db='lecture',
        charset='utf8',
        port=tunnel.local_bind_port, # ssh로 접속한 클라이언트 포트
        cursorclass=pymysql.cursors.DictCursor) as conn:
            with conn.cursor() as cur:
                sql = "show tables;"
                cur.execute(sql)
                print(sql)
                results = cur.fetchall()
                print(results)
                for result in results:
                    print(result)

터널링을 통해서 SQL Query가 잘 진행된다.

사례2) ssh_pkey 사용 (실패)

기록을 위해 남긴다. 잘 안되는 코드이다.

사례2 같이 직접 서버에 접근이 안되는 경우 ssh key pair 를 사용할 수 있다.

사용자 아이디, 패스워드 형식에서 패스워드를 직접 코드 등에 입력하기 보다 private key 쌍을 사용하면 좀 더 보안에 안전하다.

ssh-keygen 으로 생성한 공개키를 DB 서버의 ssh/authorized_keys`` 파일에 id_rsa.pub 내용을 ssh-copy-id또는scp` 명령으로 서버에 복사한다.

1

> ssh-copy-id -i ~/.ssh/id_rsa.pub username@jump_server_host -p ssh_port

scp 를 사용해도 좋다

1
2
3

> scp -P 2020 .\.ssh\id_rsa qkboo@DBSERVER:~/auth_key
#복사한 공개키를 `.ssh/authorized_keys` 에 추가한다.
> cat auth_key >> .ssh/authorized_keys

SSHTunnelForwarder 에서 ssh_pkey 에 비밀키를 지정해 준다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

with SSHTunnelForwarder(('DBSERVER', 2020),
                        ssh_username='qkboo',
                        ssh_pkey='~/.ssh/id_rsa',
                        remote_bind_address=('127.0.0.1', 3306)) as tunnel:

    print( tunnel.ssh_host_key )
    print( tunnel.local_bind_address )
    print( tunnel._remote_binds )
    
    # connect MySQL like local
    with pymysql.connect(
        host='127.0.0.1', #(local_host)
        user='user1',
        passwd='012345',
        db='lecture',
        charset='utf8',
        port=3306,
        # port=tunnel.local_bind_port,
        cursorclass=pymysql.cursors.DictCursor) as conn:
            with conn.cursor() as cur:
                sql = "show tables;"
                cur.execute(sql)
                print(sql)
                results = cur.fetchall()
                print(results)
                for result in results:
                    print(result)

SQLAlchemy engine 생성하기

SQLAlchemy 의 engine 을 생성할 수 있다. with 구문과 함께 사용할 수 있지만 engine 을 여러 프로시저에서 지속해서 사용한다면 아래 같이 start(), stop() 사이에 pymysql, sqlalchemy 코드를 배치해도 좋다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# SSH Tunnel 사용1
from sshtunnel import SSHTunnelForwarder

server = SSHTunnelForwarder(('DBSERVER', 2020),
                        ssh_username=SERVER24_USER,
                        ssh_password=SERVER24_PW,
                        remote_bind_address=('127.0.0.1', 3306), )
server.start() # Tunnel 시작

local_port = str(server.local_bind_port)
engine = sqla.create_engine(f'mysql+pymysql://{DB_USER}:{DB_PW}@127.0.0.1:{local_port}/bookstore')
query = """SELECT * FROM book;"""
df = pd.read_sql(sqla.text(query), con=engine)
engine.dispose()

server.stop() # Tunnel 종료

with 구문과 사용

1
2
3
4
5
6
7
8
9
10
11
12

# SSH Tunnel 사용2 - with 구문과 사용
with SSHTunnelForwarder(('192.168.0.24', 2020),
                        ssh_username=SERVER24_USER,
                        ssh_password=SERVER24_PW,
                        remote_bind_address=('127.0.0.1', 3306), ) as tunnel:
    
    local_port = str(tunnel.local_bind_port)
    engine = sqla.create_engine(f'mysql+pymysql://{DB_USER}:{DB_PW}@127.0.0.1:{local_port}/bookstore')
    
    query = """SELECT * FROM book;"""
    df_sector = pd.read_sql(sqla.text(query), con=engine)
    engine.dispose()

클라이언트에서 ssh tunnel 사용

클라이언트에서 MySQL / MariaDB 접속시 보안을 위해서 SSH over 방식을 통해 3306 포트가 아닌 ssh over 방법을 사용할 수 있다.

1. HeidiSQL ssh tunnel 사용하기
2. MysQL Workbench 에서 ssh tunnel 사용하기
3. Server 에서 bind 제외하기

1. HeidiSQL ssh tunnel 사용하기

유형을 SSH Tunnel 로 선택하고 MySQL / MariaDB 데이터베이스의 DB 사용자 계정과 비밀번호을 입력한다.

SSH tunnel 을 만들어줄 ssh client 를 선택하고 개인 키 파일을 선택한다. SSH 로 로그인하는 DB server의 사용자 계정 ID를 입력한다.

선택한 개인 키가 로그인 패스워드를 대체한다.

SSH 아이디-패스워드 방법은 잘 안된다.

2. MysQL Workbench 에서 ssh tunnel 사용하기

MySQL workbench 에서 ssh tunnel 로 접속하려면 DB 서버측에 authorized_keys 에 클라이언트 공개키가 등록되어야 한다.

DB 서버의 `ssh/authorized_keys`` 파일에 id_rsa.pub 내용을 추가한다.

1

> scp -P 2020 .\.ssh\id_rsa qkboo@192.168.0.24:~/auth_key

복사한 공개키를 .ssh/authorized_keys 에 추가한다.

1

cat auth_key >> .ssh/authorized_keys

root 계정은 잘 안된다.

3. Server 에서 bind 제외하기

서버에서 my.cnf 에 bind를 0.0.0.0 으로 해두었으면 기본 값인 127.0.0.1로 해두고 SSH tunnel 방법으로 통해 접근하므로 좀 더 안전할 수 있다.

• DB server 로컬에서는 직접 mysql client 로 접근가능하고
• 외부 / 원격지에서는 ssh tunnel 을 통해서 작업이 가능하다.

1

bind-address  = 127.0.0.1

참고

1. 참고1 : MySQL SSH Tunnel 소개, blog
2. 참고2 - SSH Tunneling : SSH Tunnel 이란
3. 참고3 : sshtunnel docs

MySQL / MariaDB 에 TLS 를 활성화 하고 클라이언트를 사용시 몇 가지 사례를 살펴본다.

글 타래:

1. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(1)
2. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(2)
3. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(3)

글 진행 순서:

1. Grant 와 SSL 사용

1. Grant 와 SSL 사용

GRANT 로 어떤 사용자에게 SSL 을 필수로 지정한다.

1

mysql> grant all privileges on freedb.* to 'USERID'@'%' require ssl;

결과적으로 USERID 가 freedb 에서 허용된 권한은 다음 같다

1
2
3

mysql> show grants for 'USERID'@'%';
| GRANT USAGE ON *.* TO `USERID`@`%` IDENTIFIED BY PASSWORD '*0F5BEAE3607A82096C813BBE86B0BD7F91BD7339' REQUIRE SSL |
| GRANT ALL PRIVILEGES ON `freedb`.* TO `USERID`@`%` |

이 계정에 주어진 SSL 필수 권한 상태에서 MySQL Workbench로 SSl 을 강제로 끄고 연결해 보자.

SSL을 연결하지 않고 접속하면 접근을 막는 것을 확인할 수 있다.

2. TLS 세션 확인

mysqlclient, MySQLworkbench 혹은 Python/C/Java 등에서 DBI 인터페이스로 SSL 을 연결하고 SQL 을 실행한다. 이때 접속한 세션이 TLS 상태로 연결됐는지를 확인할 수 있다.

접속한 클라이언트에서 아래 SQL 로 Ssl_cipher 를 검색해보면 현재 세션이 TLS 상태인지를 확인이 가능하다. 빈 결과가 나오면 평문 TCP/IP 으로 접속되 것이다.

1
2
3
4
5
6
7

> show session status like 'Ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.006 sec)

—

1. 참고1 : Verifying that a Connection is Using TLS
2. 참고2 : Configuring MySQL to Use Encrypted Connections

MariaDB 클라언트-서버 TLS/SSL 클라이언트 사용

글 타래:

1. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(1)
2. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(2)
3. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(3)

서버측에서 MariaDB/MySQL 의 TLS를 활성화한 다음 실제 다양한 클라이언트에서 접속을 시도해 보자. 서버에서 생성한 CA 파일 ca.pem, 클라이언트 인증서 client-ssl.pem, 클라이언트 키 client-key.pem 을 다운로드해서 사용한다.

글 진행 순서

1. 클라이언트 인증서 다운로드
2. DB API: python 에서 SSL 접속
3. HeidiSQL 에서 SSL 접속
4. MySQL Workbench 에서 SSL 접속

1. 클라이언트 인증서 다운로드

앞서 MariaDB 클라언트-서버 TLS/SSL 암호화 연결(1) 만든 ca.pem, client-cert.pem, client-key.pem 을 외부접속할 클라이언트 PC로 다운받는다.

1
2
3
4
5
6

> mkdir .ssl/mysql/
> cd .ssl/mysql
# 다운로드
> scp USERID@HOST_IP:/etc/ssl/mysql/ca.pem .
> scp USERID@HOST_IP:/etc/ssl/mysql/client-cert.pem .
> scp USERID@HOST_IP:/etc/ssl/mysql/client-key.pem .

SSH 에 다른 포트 번호를 사용하면 scp -P PORT 로 사용한다.

다운로드한 파일은 3종류로

1
2
3
4
5
6

> ls .ssl/mysql
Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a---        2023-07-16  오후 4:38           1984 ca.pem
-a---        2023-07-16  오후 4:38           1497 client-cert.pem
-a---        2023-07-16  오후 4:38           1679 client-key.pem

서버와 비슷하게 mysql client 설정에 인증키들을 설정해주면 그냥 접속해도 ssl로 접속된다. 아래 참고2 에서 mysql client 의 클라이언트 측 my.cnf/my.ini 파일에 ssl-ca, ssl-cert, ssl-key 를 설정하면 된다.

2. DB API: python 에서 SSL 접속

pymysql, sqlalchemy 등 mysql client API 에 ssl_ca, ssl_key, ssl_cert 인자에 클라이언트용 인증 파일을 연결한다.

pymysql

pymysql.connect 에 ssl_ca, ssl_key, ssl_cert 인자 클라이언트용 인증 파일의 경로가 SSL_CA, SSL_CERT, SSL_KEY 에 있다고 가정한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

conn = pymysql.connect(host='192.168.0.10', 
                       user=DB_USER, 
                       password=DB_PW,
                       db='bookstore', 
                       ssl_ca=SSL_CA,
                       ssl_key=SSL_KEY,
                       ssl_cert=SSL_CERT,
                       charset='utf8')
 
# Connection 으로부터 Cursor 생성
curs = conn.cursor()
sql = "select * from book"
curs.execute(sql)
rows = curs.fetchall()
print(rows)     # 전체 rows
conn.close()

SQLAlchemy : engine

sqlalchemy의 create_engin에 connect_args 인자에 클라이언트 인증 파일의 경로가 SSL_CA, SSL_CERT, SSL_KEY 에 있다고 가정한다.

1
2
3
4
5
6
7
8
9

ssl_args = {'ssl_ca': SSL_CA,
           'ssl_cert': SSL_CERT,
           'ssl_key': SSL_KEY}
engine = sqla.create_engine(
    f'mysql+pymysql://{DB_USER}:{DB_PW}@192.168.0.24/bookstore',
    connect_args=ssl_args)

query = """SELECT * FROM book;"""
df = pd.read_sql(sqla.text(query), con=engine)

C 라이브러리 사용 SSL 접속

참고2 를 보면 C API를 사용한 `libmariadb.dll`` 라이브러리에서 SSL 사용하는 C++ 콘솔 예제 가 있다.

3. HeidiSQL 에서 SSL 접속

HeidiSQL 에서 SSL 을 이용해 접속할 수 있다. 아래 그림 같이 DB 정보를 입력한 후에 SSL 탭에서 SSL을 체크만 하면 된다.

접속이 되면 프로그램 아래 상태바의 정보를 클릭해 보면 SSL 연결을 확인할 수 있다.

앞서 다운로드한 서버측이 제고한 클라이언트 인증 키를 지정하고 연결하면 아래 같은 cipher mis match 에러가 뜬다.

이것은 아마도 서버측의 openssl.cnf 에 정의되어 있는 CipherString 버전이 달라서 그런것 같다. 여기 Connect error “SEC_E_ALGORITHM_MISMATCH”.. 의 글타래에 설명되어 있다.

• 테스트한 서버는 ** Armbian 23.02.2 Bullseye** 로 CipherString 이 DEFAULT@SECLEVEL=2 로 나온다.

TLS versions

1
2
3
4
5
6
7

> show global variables like 'tls_version';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| tls_version   | TLSv1.1,TLSv1.2,TLSv1.3 |
+---------------+-------------------------+
1 row in set (0.007 sec)

CipherString 테스트

MySQL 설정파일 참조3에 따르면 server-side encrypted-connection control 을 위해서 아래 변수를 사용할 수 있다고 한다.

1

ssl_cipher: The list of permissible ciphers for connection encryption.

my.cnf 의 SSL/TLS 영역 다음을 추가해 보자.

1

ssl_cipher=DEFAULT:@SECLEVEL=1

재시작한후 아래 그림 같이 서버에서 만든 클라이언트 인증서를 사용해보니 잘 된다. root 계정 접속도 잘 된다.

4. MySQL Workbench 에서 SSL 접속

MySQL Workbench 에서도 SSL 연결로 데이터베이스에 접속할 수 있다. DB 접속 계정 정보를 입력한다.

SSL 탭에서 SSL 관련 옵션을 선택하는데 여기서는 if available 로 지정했다. 서버측이 SSL활성화가 되어 있으면 자동으로 SSL 통신을 진행한다.

Test 로 확인해 보면 SSL 접속이 잘 되고 있는 것을 알 수 있다.

5. 사용자 SSL 권한 부여

새 사용자를 생성할 때 아래 같이 접속시 REQUIRE 인자로 SSL 로만 접속하도록 할 수 있다.

사용자의 추가/권한 부여에 대해서 MySQL CLI/Admin: 권한부여 글을 참고.

1
2
3
4
5
6
7
8
9
10

# SSL/TLS(가장 기본적인 암호화 접속)
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY '********' REQUIRE SSL;

# X509
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY '********' REQUIRE X509;

# CIPHER 'cipher'
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY '********' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

mysql> flush privileges;

기존 사용자는 사용자 권한에서 SSL을 권한을 추가한다.

1
2

mysql> GRANT ALL PRIVILEGES ON DB이름.* TO 'USERID'@'%' REQUIRE SSL;
mysql> flush privileges;

주어진 grant에 SSL/X509 등이 주어졌는지 확인한다.

1

mysql> SHOW GRANTS FOR 'USERID'@'HOST';

내부 네트워크 외부 네트워크로 분리해 사용한다면 외부에서는 무조건 SSL 을 사용하도록 할 수 있을 것 같다.

—

1. 참고1 : OpenSSL을 이용한 사설 인증서 생성
2. 참고2 : MariaDB 외부접속시 ssl 사용법, 그리고 ssl 로 replication(동기화) 하기
3. 참고3 : Configuring MySQL to Use Encrypted Connections

MySQL / MaraiDB 서버에 TLS 를 활성화 해서 암호화 통신을 할 수 있다. MySQL (MariaDB도 동일함)에서는 서버-클라이언트 사이에 전송되는 데이터를 TLS (이전 SSL) 프로토콜을 이용하여 암호화하여 DB 정보가 노출되지 않게 방지할 수 있다. SSL을 통해서 Replication Master - Slave 도 가능하다.

글 타래:

1. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(1)
2. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(2)
3. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(3)

글 진행 순서:

1. 사설 CA 인증서 생성
2. 클라이언트 인증서 생성
3. 사설 CA 인증서 설정

시스템 사양

1. Armbian 23.8.1 Bullseye / mariadb-server-10.5

1. 사설 CA 인증서 생성

openssl을 사용해서 ssl_cert, ssl_key, ssl_ca 에 사용하는 서버용 사설 인증서를 생성한다.

openssl을 이용한 사설 인증서 생성에 대해서는 HTTPS 를 위한 Private SSL 기사도 참조할 수 있다.


이 글에서는 아래 참고1, 참고2 를 적용했다.

인증서는 사설 CA(Certificate Authority)용 인증서, 서버용, 클라이언트용 총 3가지를 만든다.

인증서 저장 위치 생성

1
2

(SERVER) $ sudo mkdir /etc/ssl/mysql
(SERVER) $ cd /etc/ssl/mysql

생성한 TLS 인증서는 MySQL/MariaDB 의 my.cnf 설정파일의 변수를 3가지 설정한다.

• ssl_cert 시스템 변수: 서버 인증서(X509) 경로 지정
• ssl_key 시스템 변수: 서버 개인키 경로 지정
• ssl_ca 혹은 ssl_capath 시스템 변수: Self-signed CA certificate CA 키 경로

openssl 사설 CA 인증서 생성

저장 위치 /etc/ssl/mysql 에서 관리용 CA 인증서 생성한다.

먼저 openssl에서 rsa 알고리즘으로 4096 크기 비밀키를 생성한다.

1
2
3

(SERVER) $ sudo openssl genrsa -out ca-key.pem 4096
Generating RSA private key, 4096 bit long modulus (2 primes)
...

비밀키 파일 ca-key.pem 파일을 사용해서 CA인증서 ca.pem 파일로 생성한다. 비밀키로 CA를 위한 CSR(certificate signing request) 과정을 거쳐 ca.pem 을 생성해9서 필요한 인증서 정보를 묻는다.

1
2
3
4
5
6
7
8
9

(SERVER) $ sudo openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca.pem
...
Country Name (2 letter code) [AU]: KR
State or Province Name (full name) [Some-State]:Seoul
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:YOUR company
Organizational Unit Name (eg, section) []: Administration
Common Name (e.g. server FQDN or YOUR name) []::mysql-admin.DOMAIN.name
Email Address []:

현재까지

1
2
3

(SERVER) $ ls -l
-rw------- 1 root root 3243 Jul 16 15:04 ca-key.pem
-rw-r--r-- 1 root root 1972 Jul 16 15:11 ca.pem

서버 인증서 생성

서버용 인증서 생성한다. 단, openssl 명령 과정에서 Common Name 을 3가지 모두 다르게 해야 검증오류를 피할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

(SERVER) $ sudo openssl req -newkey rsa:4096 -days 365000 -nodes -keyout server-key.pem -out server-req.pem

Country Name (2 letter code) [AU]:KR
State or Province Name (full name) [Some-State]:Seoul
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:YOUR company
Organizational Unit Name (eg, section) []:Administration
Common Name (e.g. server FQDN or YOUR name) []:server.DOMAIN.name
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
...

현재 디렉토리

1
2
3
4
5

(SERVER) $ ls -l
-rw------- 1 root root 3243 Jul 16 15:04 ca-key.pem
-rw-r--r-- 1 root root 1972 Jul 16 15:11 ca.pem
-rw------- 1 root root 3272 Jul 16 15:15 server-key.pem
-rw-r--r-- 1 root root 1704 Jul 16 15:16 server-req.pem

RSA 알고리즘으로

1
2

(SERVER) $ sudo openssl rsa -in server-key.pem -out server-key.pem
writing RSA key

CA 비밀키 ca-key.pem 를 사용해 X509 인증서를 사이닝한다.

1
2
3
4

(SERVER) $ sudo openssl x509 -req -in server-req.pem -days 365000 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=C = KR, ST = Seoul, O = Thinkbee company, OU = Administration, CN = admin.thinkbee.kr
Getting CA Private Key

1
2

(SERVER) $ sudo openssl verify -CAfile ca.pem server-cert.pem
server-cert.pem: OK

현재 디렉토리

1
2
3
4
5
6

(SERVER) $ ls -l
-rw------- 1 root root 3243 Jul 16 15:04 ca-key.pem
-rw-r--r-- 1 root root 1972 Jul 16 15:11 ca.pem
-rw-r--r-- 1 root root 1862 Jul 16 15:20 server-cert.pem
-rw------- 1 root root 3243 Jul 16 15:19 server-key.pem
-rw-r--r-- 1 root root 1704 Jul 16 15:16 server-req.pem

2. 클라이언트 인증서 생성

클라이언트 인증서는 다음과 같은 용도로 사용한다. 단, openssl 명령 과정에서 Common Name 을 3가지 모두 다르게 해야 검증오류를 피할 수 있다.

• DB 서버와 별도로 존재하는 웹 서버에서 DB 서버로 SSL 통신을 할 때 웹 서버에 적용
• 접속하고자 하는 DB 서버와 별도의 리눅스 환경에서 mysql 클라이언트 프로그램으로 DB 서버에 접속할 때 클라이언트에 적용
• Replication 에서 Master와 Slave 간의 SSL 통신을 하고자 할 때 Slave 서버에 적용

1
2
3
4
5
6
7
8
9

(SERVER) $ sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
Country Name (2 letter code) [AU]:KR
State or Province Name (full name) [Some-State]:Seoul
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []::mysql-client.thinkbee.kr

...

1
2

(SERVER) $ sudo openssl rsa -in client-key.pem -out client-key.pem
writing RSA key

1
2
3
4

(SERVER) $ sudo openssl x509 -req -in client-req.pem -days 365000 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=C = KR, ST = Seoul, O = Internet Widgits Pty Ltd, CN = client-thinkbee.kr
Getting CA Private Key

1
2

(SERVER) $ sudo openssl verify -CAfile ca.pem client-cert.pem 
client-cert.pem: OK

현재 디렉토리를 접근 권한을 조정한다.

1
2
3
4
5
6
7
8
9
10

(SERVER) $ sudo chown mysql.mysql *.*
(SERVER) $ ls -l
-rw-r--r-- 1 mysql mysql 3243  7월 16 15:43 ca-key.pem
-rw-r--r-- 1 mysql mysql 1984  7월 16 15:46 ca.pem
-rw-r--r-- 1 mysql mysql 1497  7월 16 15:51 client-cert.pem
-rw-r--r-- 1 mysql mysql 1679  7월 16 15:51 client-key.pem
-rw-r--r-- 1 mysql mysql  989  7월 16 15:50 client-req.pem
-rw-r--r-- 1 mysql mysql 1838  7월 16 15:48 server-cert.pem
-rw------- 1 mysql mysql 3243  7월 16 15:48 server-key.pem
-rw-r--r-- 1 mysql mysql 1671  7월 16 15:47 server-req.pem

파일 모드를 644 으로 변경한다.

1

(SERVER) $ sudo chmod 644 *.*

인증서의 내용 확인방법

1
2
3

(SERVER) $ openssl x509 -text -in ca.pem
(SERVER) $ openssl x509 -text -in server-cert.pem
(SERVER) $ openssl x509 -text -in client-cert.pem

3. 사설 CA 인증서 설정

my.cnf 에 TLS 를 위한 구성을 해야 한다. 주로 ssl_cert, ssl_key, ssl_ca 에 대한 인증서 파일을 지정해 준다.

• ssl_cert 시스템 변수: X509 인증서 경로 지정
• ssl_key 시스템 변수: 서버 개인키 경로 지정
• ssl_ca 혹은 ssl_capath 시스템 변수: Certificate Authority (CA) 경로

TLS 활성화

참고3 의 mysql 설정파일 my.cnf 에 따르면 아래 변수가 설정되야 한다.

1
2
3
4
5

[mariadb]
...
ssl_cert = /etc/ssl/mysql/server-cert.pem
ssl_key = /etc/ssl/mysql/server-key.pem
ssl_ca = /etc/ssl/mysql/ca.pem

서버를 재시작한다.

TLS 가 활성화 되었는지 환경변수로 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

MariaDB [(none)]> show variables like '%ssl%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| have_openssl        | YES                            |
| have_ssl            | YES                            |
| ssl_ca              | /etc/ssl/mysql/ca.pem          |
| ssl_capath          |                                |
| ssl_cert            | /etc/ssl/mysql/server-cert.pem |
| ssl_cipher          |                                |
| ssl_crl             |                                |
| ssl_crlpath         |                                |
| ssl_key             | /etc/ssl/mysql/server-key.pem  |
| version_ssl_library | OpenSSL 1.1.1n  15 Mar 2022    |
+---------------------+--------------------------------+
10 rows in set (0.003 sec)

이렇게 구성한 TLS 인증서는 만료기간이 있다. 아래 같이 만료 기간을 확인 할 수 있다.

1
2
3
4
5
6
7

>  SHOW STATUS LIKE 'Ssl_server_not%';
+-----------------------+--------------------------+
| Variable_name         | Value                    |
+-----------------------+--------------------------+
| Ssl_server_not_after  | Nov 16 06:48:41 3022 GMT |
| Ssl_server_not_before | Jul 16 06:48:41 2023 GMT |
+-----------------------+--------------------------+

mysql client TLS 사용 접속

mysql 클라이언트로 TLS 인증서를 사용해서 접속해 보자.

1

(SERVER) $ mysql -u root -p -h localhost --ssl=TRUE --ssl-ca=/etc/ssl/mysql/ca.pem --ssl-cert=/etc/ssl/mysql/client-cert.pem --ssl-key=/etc/ssl/mysql/client-key.pem

접속한 클라인트에서 status 를 살펴보면 TLS 로 접속한 내역을 SSL: Cipher in use is TLS_AES_256_GCM_SHA384 항목으로 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.11.2-MariaDB, for debian-linux-gnu (aarch64) using  EditLine wrapper

Connection id:          315
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.11.2-MariaDB-1:10.11.2+maria~deb11 mariadb.org binary distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
UNIX socket:            /run/mysqld/mysqld.sock
Uptime:                 6 min 54 sec

Threads: 69  Questions: 2386  Slow queries: 0  Opens: 37  Open tables: 30  Queries per second avg: 5.763
--------------

참고

1. OpenSSL을 이용한 사설 인증서 생성
2. MariaDB 외부접속시 ssl 사용법, 그리고 ssl 로 replication(동기화) 하기
3. Configuring MySQL to Use Encrypted Connections

jupyter notebook에서 실행하면 차일드 프로세스로 실행한 print() 로 출력하는 결과를 확인할 수 업다. print 는 std out 에 출력하도록 되어 있다. 자식 프로세스에서 print 를 호출하면 spawn 을 사용해서 호출되어서 출력되지 않는다.

차일드 프로세스 print 문제

아래 코드는 아주 간단하게 메인 프로세스에서 새 프로세스를 생성하고 실행한다.

1
2
3
4
5
6
7
8
9

from multiprocessing import Process

def func(msg):
    print(msg)

if __name__ == "__main__":
    proc = Process(target=func, args=('Hello multiproess',))
    proc.start()
    proc.join()

이 코드는 소스로 쉘에서 실행하거나 Web 기반의 jupyterlab 에서 실행하면 아무 문제이 print 문이 잘 작동하고 출력 결과가 나온다.

1
2

>>> ! python mutiprocess1.py
Hello multiproess

print 의 Multiprocessing 에서 문제는 링크 참고1 을 읽어보기를 권한다. 이 글에서는 테스트한 내용을 정리만 했다.

원인

아래 링크 참고1 기사에서 설명을 자세히 하고 있다. 보통 파이썬에서 차일드 프로세스를 시작할 때는 'fork', 'spawn', 'forkserver' 방법이 있다고 한다. 그런데 spawn 으로 차일드 프로세스를 실행하면 std io 출력 버퍼가 자동으로 비워지지 않는다(print는 기본으로 flush가 되지 않는다). 그러다 보니 차일드 프로세스가 종료 하면서 자동으로 gabage collection에 의해 버퍼에 남아 있는 메시지가 사라지는 것이다.

해결 방법

1. 메시지 버퍼가 사라지기 전에 flush 를 한다.
2. fork 기반의 프로세스를 생성한다.

1. flush 사용

1. 메시지 버퍼가 사라지기 전에 flush 를 한다.

이를 해결하기 위해 모든 print 의 출력은 즉시 flush 되도록 flush=True 옵션을 사용할 수 있다고 한다.

1
2

def func(msg):
    print(msg, flush=Yes)

이 방법은 VS code 현재 jupyter 확장에서는 효과가 없다.

flush 란?

std io 의 버퍼 처리 흐름은 글 파일 I/O 버퍼링 을 살펴보고,

python에서 print 같은 표준 출력의 flush에 대해서 이글 의 그림을 참고한다.

2. fork 기반의 프로세스를 생성한다.

지원되는 프로세스 시작 방법은 아래 같이 확인이 가능하다.

1
2
3
4

import multiprocessing as mp

mp.get_start_method()       # 현재 start 메서드
mp.get_all_start_methods()  # 모든 start 메서드

아래 같이 start method 를 fork 로 강제 사용할 수 있다.

1
2
3

import multiprocessing as mp

mp.set_start_method('fork')       # 현재 start 메서드

브라우저 기반의 jupyter 실행환경은 'fork', 'spawn', 'forkserver' 를 모두 가능하다. 하지만 현재 VS code 의 jupyter 확장은 에서는 spawn 만 지원하고 있다.

fork, spawn

링크 침고2 에 있는 글에서 Fork, Spawn 에 대한 설명을 읽어보자.

Forking

• 포크한 부모 프로세스의 이미지를 그래도 사용한다.

Spwaning

• 포크한 부모 프로세스와 다르게 새 이미지로 갱신한다.

sleep 으로 지연하면?

VS code 환경에서 아래 같이 sleep 으로 지연을 주어 봤지만 해결이 안된다.

1
2
3
4
5
6
7
8

def func(msg):
    print(msg, flush=True)
    time.sleep(1)

if __name__ == "__main__":
    proc = mp.Process(target=func, args=('Hello multiproess',))
    proc.start()
    proc.join()

아래 같이 join 전에 sleep 으로 지연

1
2
3
4
5
6
7
8
9

def func(msg):
    print(msg, flush=True)
    time.sleep(1)

if __name__ == "__main__":
    proc = mp.Process(target=func, args=('Hello multiproess',))
    proc.start()
    time.sleep(1)
    proc.join()

결론

현재 테스트한 VS Code 의 jupyter 확장 버전은 아래 같다.

• Windows VS code, Jupyter Extension
  • v2023.6.1101941928
  • v2023.7.1001901100

만약 VS code 에서 Multiprocess 디버깅시 다른 logger 라이브러리 등을 이용해야 할 것 같다.

참고

참고1 : How to print() from a Child Process in Python

참고2 : fork, vfork 그리고 posix_spawn 이야기

참고3 : foring, spawning 이미지

Anaconda / Miniconda 를 설치하고 윈도우즈에서 실행시 바로가기로 사용하는 방법을 정리했다. 모든 Commandline, Powershell 에서 conda 명령을 사용하려면 환경변수 PATH 에 지정을 해야 한다. 이 글에서는 바로가기를 사용하면 PATH 환경변수를 활용하지 않고도 쉽게 사용할 수 있다.

Anaconda/Miniconda 설치 프로그램으로 설치를 하면 바로가기를 만들어 준다. 여기서 힌트를 얻으면 된다.

바로가기 속성을 사용해 파워쉘로 conda 환경을 초기화 하기 위해서는 conda-hook.ps1 실행이 필요하다. 설치한 Anaconda 시작프로그램의 속성을 통해 알 수 있다. 보통 설치된 아래 위치에 있다.

• C:\Users\USERID\miniconda3\shell\condabin\conda-hook.ps1

PowerShell

윈도우 기본 파워쉘과 Powershell 7 으로 바로가기 설정을 보면, conda-hook.ps1 을 포함해 바로가기 아이콘 속성에 파워쉘을 통해 실행하도록 설정하면 된다.

PowerShell 7

powershell 7 으로 Miniconda3 의 base 가상환경 기반 쉘을 실행한다.

1

"C:\Program Files\PowerShell\7\pwsh.exe" -ExecutionPolicy ByPass -NoExit -Command "& 'C:\Users\USERID\miniconda3\shell\condabin\conda-hook.ps1' ; conda activate 'C:\Users\USERID\miniconda3' "

기본 PowerShell 버전

1

%windir%\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy ByPass -NoExit -Command "& 'C:\Users\USERID\miniconda3\shell\condabin\conda-hook.ps1' ; conda activate 'C:\Users\USERID\miniconda3' "

Command Line

윈도우 Command 로 anaconda / miniconda 를 실행하려면 아래 같이 activate.bat 배치를 실행하면 된다.

1

%windir%\System32\cmd.exe "/K" C:\Users\daddy\miniconda3\Scripts\activate.bat C:\Users\daddy\miniconda3

Windows Terminal 프로파일 설정

윈도우 터미널에 새 프로파일 추가시 랜덤한 GUID 값이 필요하다. 터미널에서 New-Guid 명령으로 발행해 사용한다.

1
2
3
4
5

PS> New-Guid

Guid
----
9f2a1a27-fe9b-4421-ba19-6cea57188b17

PowerShell 7 버전

guid 는 New-Guid 로 발행해서 등록하면 된다.

1
2
3
4
5
6
7
8
9
10
11
12

{
    "commandline": "C:\\Program Files\\PowerShell\\7\\pwsh.exe -ExecutionPolicy ByPass -NoExit -Command \"& 'C:\\Users\\USERID\\miniconda3\\shell\\condabin\\conda-hook.ps1' ; conda activate 'C:\\Users\\daddy\\miniconda3' \"",
    "guid": "{fe2a1a27-9efb-4421-ba19-6cea57188b17}",
    "name": "Miniconda3 64bit(ps7)",
    "startingDirectory": "%USERPROFILE%"
},
{
    "commandline": "C:\\Program Files\\PowerShell\\7\\pwsh.exe -ExecutionPolicy ByPass -NoExit -Command \"& 'C:\\Users\\USERID\\anaconda3\\shell\\condabin\\conda-hook.ps1' ; conda activate 'C:\\Users\\daddy\\anaconda3' \"",
    "guid": "{3f2k7199-d35c-44e5-b82d-7cd5442175fc}",
    "name": "Anaconda 64bit(ps7)",
    "startingDirectory": "%USERPROFILE%"
},

윈도우 기본 PowerShell 버전

1
2
3
4
5
6
7
8
9
10
11
12
13

{
    "commandline": "%windir%\\System32\\WindowsPowerShell\\v1.0\\powershell.exe -ExecutionPolicy ByPass -NoExit -Command \"& 'C:\\Users\\USERID\\miniconda3\\shell\\condabin\\conda-hook.ps1' ; conda activate 'C:\\Users\\daddy\\miniconda3' \"",
    "guid": "{573e44d9-1ff5-4f3f-b083-1b2fd9a6a575}",
    "name": "Miniconda3 64bit",
    "startingDirectory": "%USERPROFILE%"
},
{
    "commandline": "%windir%\\System32\\WindowsPowerShell\\v1.0\\powershell.exe -ExecutionPolicy ByPass -NoExit -Command \"& 'C:\\Users\\USERID\\anaconda3\\shell\\condabin\\conda-hook.ps1' ; conda activate 'C:\\Users\\daddy\\anaconda3' \"",
    "guid": "{f441f482-8cf6-47ed-9a9c-fc40df46c9ac}",
    "name": "Anaconda 64bit",
    "startingDirectory": "%USERPROFILE%"
},

Pandas 에서 2차원 data를 Stack, unstack, melt 를 이용해 복합 인덱스를 사용할 수 있고 Pivot 을 이용해 특정 데이터 중심의 2차원 데이터로 생성할 수 있다.

1. Pivot
2. Stack & Unstack
3. Melt

1
2

import pandas as pd
import numpy as np

2차원 테이블

행과 열로 구성된 데이터 집합

1
2
3
4
5
6
7

df = pd.DataFrame(
    {'foo' : ['One','One','One','Two','Two','Two'],
        'bar': ['A','B','C','A','B','C'],
        'baz': [1,2,3,4,5,6] ,
        'zoo': ['x','y','z','q','w','t']}
)
df

Pivot

피봇/피봇 테이블은 2차원 데이터 열에서 공통된 부분을 중심으로 새 테이블 집합을 형성하게 해준다. 피봇은 index, columns, values 라는 이름을 가진 세 가지 parameter를 취한다. 이러한 각 파라미터의 값으로 원래 표에 열 이름을 지정해야 한다.

foo, bar, baz, zoo 컬럼 중에서 foo 에 대해서 정리를 하고 bar 를 컬럼으로 지정하면 아래와 같다.

1
2

df_pivot = df.pivot_table(index='foo', columns='bar', values='baz')
df_pivot

ex) nba 데이터를 포지션의 연령별 연봉 테이블로 전환

1
2

dfnba = pd.read_csv('../data/nba.csv')
dfnba

1

pd.options.display.float_format = "{:,.2f}".format

컬럼의 데이터에 공통된 모습이 많이 보인다. 이 중에서 포지션을 기준으로 나이에 따른 연봉을 본다고 가정하면 포지션을 인덱스로하고 나이를 컬럼으로 지정하면 아래와 같다.

1
2

# 포지션의 연령별 연봉 테이블
dfnba.pivot_table(index='Position', columns='Age', values='Salary')

Stack & Unstack

2차원 테이블은 행 과 열이 순차적 값으로 교차하게 되어 있다. 스택은 컬럼의 값을 아래-위로 배치를 시킨다고 상상이 된다. 그래서 스택과 언스택은 이렇게 생각된다.

• stack : 2차원 컬럼의 내용을 수직방향으로 쌓는 구조, 즉 새로운 인덱스가 더해진다.
• unstack : 인덱스 구성요소를 한 단계 컬럼으로 만들며 수평방향으로 쌓게 한다.

stack

pandas reshaping

1
2
3
4
5
6
7

df_single_level = pd.DataFrame(
    [['Mostly cloudy', 10], ['Sunny', 12]],
    index=['London', 'Oxford'],
    columns=['Weather', 'Wind']
)
df_single_level

index와 weather, wind 라는 컬럼을 stack을 호출하면 weather-wind 관련 인덱스를 생성하고 데이터를 나열한다.

1

df_single_level.stack()

London  Weather    Mostly cloudy
        Wind                  10
Oxford  Weather            Sunny
        Wind                  12
dtype: object

다른 데이터를 살펴보자.

1
2
3
4
5
6
7
8
9
10
11
12

# MultiIndex
tuples = list(
    zip(
        *[
            ["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"],
            ["one", "two", "one", "two", "one", "two", "one", "two"],
        ]
    )
)
index = pd.MultiIndex.from_tuples(tuples, names=["first", "second"])
df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=["A", "B"])
df

위 데이터는 stack()을 하면 A, B 컬럼이 MultiIndex 로 추가되며 A, B 컬럼 데이터 포인트가 배치된다.

1

df.stack()

first  second   
bar    one     A   -1.301694
               B   -0.013259
       two     A   -0.197846
               B    0.879890
baz    one     A    0.718211
               B   -0.739434
       two     A   -0.140217
               B    0.071260
foo    one     A   -1.142268
               B   -2.606413
       two     A    1.119145
               B    0.109402
qux    one     A   -0.504167
               B   -1.703280
       two     A    1.064976
               B    1.011060
dtype: float64

1
2

df2 = df[:4]
df2

1
2

stacked = df2.stack()
stacked

first  second   
bar    one     A   -1.301694
               B   -0.013259
       two     A   -0.197846
               B    0.879890
baz    one     A    0.718211
               B   -0.739434
       two     A   -0.140217
               B    0.071260
dtype: float64

Unstack

pandas reshaping

1

stacked

first  second   
bar    one     A   -1.301694
               B   -0.013259
       two     A   -0.197846
               B    0.879890
baz    one     A    0.718211
               B   -0.739434
       two     A   -0.140217
               B    0.071260
dtype: float64

1

stacked.unstack()

레벨을 지정

1

stacked.unstack(1)

1

stacked.unstack(2)

1

Melt

melt() 는 ID 변수를 기준으로 원래 데이터셋에 있던 여러개의 칼럼 이름을 ‘variable’ 칼럼에 위에서 아래로 길게 쌓아놓고, ‘value’ 칼럼에 ID와 variable에 해당하는 값을 넣어주는 식으로 데이터를 재구조화합니다.

1
2
3
4
5
6
7
8
9

cheese = pd.DataFrame(
    {
        "first": ["John", "Mary"],
        "last": ["Doe", "Bo"],
        "height": [5.5, 6.0],
        "weight": [130, 150],
    }
)
cheese

1
2

# colums중 height, weight 를 row 로 융합
cheese.melt(id_vars=['first','last']) # first, last 인덱스로 나머지 컬럼은 value 로 치환

1

cheese.melt(id_vars=['first','last'], value_name='quantity') 

1

참고

1. pandas.DataFrame.stack

VS Code에서 Jupyterlab 3.x를 사용하는데 classic jupyter notebook 에서 가능하던 content assistance 기능이 작동을 하지 않았다.

상황

Jupyterlab, classic jupyter notebook 에서 가능하던 content assistance 기능으로 속성/모듈/함수/도움말 등의 지원이 가능했다.

• Jupyterlab에서 노트북 셀의 tab & shift tab

그런데 Visual studio code 에서 .ipynb 노트북 파일을 열어 사용하면 이런 기능이 안되고 있어서 궁금했다.

구글 검색으로 스택오버플로우에 올라온 기사를 보니 settings.json 을 보면 python language server 설정을 해야 한다고 한다. 현재는 Default 상태로 선택되어 있어서 그렇다고 한다. 그래서 Pylance 를 지정했다.

languageserver 를 지정하고 재시작한 후 살펴보니 잘 된다.

Language Server extension 이란?

language-server-extension-guide 에서 설명한 바로는

여러 프로그래밍 언어에 대해서 강력한 편집 경험을 제공하고자 한다.

아래 그림에서 languageserver 의 효과를 설명하고 있다. 왼쪽 같이 LSP 가 없으면 각 편집기에서 직접 언어 엔진을 가동해야 한다. 그런데 오른쪽 깥이 LSP를 사용하면 하나의 서버를 통해서 여러 편집기에서 언어의 특성을 활용할 수 있다는 설명 이다.

numpy 와 pandas 에서 수를 출력할 때 형식, 크기 및 범위를 설정할 수 있다. 간단히 보면 아래 테이블 같이 형식을 바꿔준다.

아래 요약한 옵션 방법을 사용해서 Numpy 와 Pandas에 있는 숫자를 출력할 때 표현방법, 표기법, 환률, 정밀도 등을 변경해 사용할 수 있다

numpy 출력 형식 변경

numpy 숫자 출력 형식 변경

numpy.set_printoptions 을 사용할 수 있다.

1
2

numpy.set_printoptions(precision=None, threshold=None, edgeitems=None, linewidth=None, suppress=None,
                       nanstr=None, infstr=None, formatter=None, sign=None, floatmode=None, *, legacy=None)

현재 출력형식 확인

np.get_printoptions() 으로 현재 상태를 출력할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13

> np.get_printoptions()
{'edgeitems': 3,
 'threshold': 1000,
 'floatmode': 'maxprec',
 'precision': 8,
 'suppress': False,
 'linewidth': 75,
 'nanstr': 'nan',
 'infstr': 'inf',
 'sign': '-',
 'formatter': None,
 'legacy': False
}

Numpy 에서 실수 Float 의 출력 형식을 바꾸는 몇가지 사례를 보자

- formatter 이용

1
2

import numpy as np
np.set_printoptions(formatter={'float_kind': lambda x: "{0:0.3f}".format(x)})

- precision 이용

1
2
3

> np.set_printoptions(precision=4)
> np.array([1.123456789])
[1.1235]

- threshold 이용

개수가 많은 아이템을 출력할 때 요약해 출력할 수 있다.

1
2
3

> np.set_printoptions(threshold=5)
> np.arange(10)
array([0, 1, 2, ..., 7, 8, 9])

numpy.printoptions 사용

numpy.printoptions 를 with 구문과 함께 사용해 제한된 출력 조정을 할 수 있다.

출력시 printoptions 를 with 구문과 사용할 수 있다. set_printoptions 의 인자를 동일하게 적용할 수 있다

• precision, threshold, edgeitems, linewidth, suppress, nanstr, infstr, formatter, sign, floatmode

1

numpy.printoptions(*args, **kwargs)[source]

소수점 출력 변경

1
2
3
4
5
6

> np.array([2.0]) / 3
array([0.66666667])


> with np.printoptions(precision=3):
>    print( np.array([2.0]) / 3 )

pandas 숫자 출력 형식 변경

pandas에서 몇 가지 옵션을 바꾸는 방법을 정리해 보자. pandas의 옵션은 pd.options 를 사용한다.

pd.options.display

출력의 형태, 표기를 변경하는 것은 pd.options.display 아래에 있다. 여기서 사용할 수 있는 옵션은 describe_option() 으로 확인할 수 있다.

1
2
3
4
5
6
7

> pd.describe_option()
compute.use_bottleneck : bool
    Use the bottleneck library to accelerate if it is installed,
    the default is True
    Valid values: False,True
    [default: True] [currently: True]
  ...

- row, column 출력 개수 조정

• pd.options.display.max_rows : 표를 출력할 때 최대 행 수입니다.
• pd.options.display.min_rows : 표를 출력할 때 최소 행 수입니다.

1
2
3
4
5

import pandas as pd
> pd.options.display.max_rows
60
> pd.options.display.min_rows
10

min_row, max_row에 직접 대입하면 해당 옵션의 현재 값이 변경된다.

1
2
3

> pd.options.display.min_rows=100
> pd.options.display.min_rows
100

max_rows에 값을 입력하면 테이블의 최대 행수를 바꿀 수 있다.

^{이미지 참조 1}

1
2
3

> pd.options.display.max_rows = 100
> pd.options.display.max_rows
100

- pd.get_option(), pd.set_option() 함수

pd.get_option() 함수를 이용해서 옵션인자에 대한 정보를 확인할 수 있다.

1
2

> pd.get_option('min_rows')
100

get_option은 옵션 이름의 일부만 일치해도 된다.

1
2

> pd.get_option('min_r')
100

max_rows 수를 설정한다. set_option도 일부만 일치해도 된다.

1
2
3
4
5
6

> pd.set_option('max_rows', 20)
> pd.options.display.max_rows
20
> pd.set_option('max_r', 50)
> pd.options.display.max_rows
50

- 컬럼의 폭 조정

display.max_colwidth 는 보통 50~70자 정도 정해져 있다. 컬럼에 표시되는 텍스트가 50자가 넘으면 ... 줄임 표시가 나타난다.

1
2

> pd.options.display.max_colwidth
50

- chop_threshold

chop_threshold 는 값의 크기 한계를 지정해서 이 값보다 작은 수는 모두 0으로 표시한다.

1
2
3
4
5
6

> pd.options.display.chop_threshold = 0.99
> pd.DataFrame({'x': [10, 1, 0.1]})
> print(x)
0	10.0
1	1.0
2	0.0

숫자 포매팅

다양한 사례는:

• https://pandas.pydata.org/docs/user_guide/options.html#number-formatting

- float_format

float_format 는 실수 값을 출력시 소수점의 출력의 정밀도를 조정할 수 있다. 아래 람다 함수 lambda x: f'{x:.1f} 는 실수 x를 받아 소수점 첫째 자리까지 출력해 준다.

1
2
3
4

> pd.options.display.float_format = lambda x: f'{x:.1f}'
> pd.DataFrame({'x': [3.141592]})
x
0	3.1

또한 set_option 을 사용할 수 있다.

1

> pd.set_option('display.float_format', '{:.2f}'.foramt )

금액 단위에 사용하는 천단위 구분을 위해서 {:,.2f} 형식을 사용하면 화폐 단위를 추가하고 천단위 구분자를 추가해 주고 소수점 2자리수 정밀로를 지정한다.

1
2
3
4
5

> pd.set_option('display.float_format', '${:,.2f}'.format )
> pd.DataFrame({'x': [10000000.0, 34589234.4]})
  x
0 $10,000,000.00
1 $34,589,234.40

- precision

실수의 소수점은 precision 로 과학적 표기법으로 변환할 자릿수를 지정한다. 아래와 같이 하면 소수점 셋째 자리 밑으로는 과학적 표기법으로 표시합니다.

1
2
3
4

> pd.options.display.precision = 3
> pd.DataFrame({'x': [0.5], 'y': [0.0005]})
x	y
0	0.5	5.000e-04

과학적 표기법으로 3.000e-04는 3.000 이다. 자릿수가 아주 작거나 큰 수를 표기할 때 유용합니다.

- 설정 초기화 reset_option()

설정을 초기화할 때 사용한다.

1
2
3
4

# chop_threshold 옵션 초기화
pd.reset_option('display.chop_threshold')
# float_format 옵션 초기화
pd.reset_option('display.float_format')

- 옵션 설명 describe_option()

pd.describe_option(OPTIONS) 를 사용하면 해당 옵션에 대한 설명을 출력해 준다.

1
2
3
4
5

> pd.describe_option("max_rows")
display.max_rows : int
    If max_rows is exceeded, switch to truncate view. Depending on
    `large_repr`, objects are either centrally truncated or printed as
    a summary view. 'None' value means unlimited.

참고

1: Try These Pandas Display Configurations

2: Pandas options