MariaDB 클라언트-서버 TLS/SSL 클라이언트 사용시 HeidiSQL 12 버전에서 SSL 접속에 대해서 수정을 반영

글 타래:

1. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(1)
2. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(2)-2508업데이트
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 접속 (2508업데이트)
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 접속 (2508업데이트)

HeidiSQL 에서 SSL 을 이용해 접속할 수 있다. 아래 그림 같이 DB 정보를 입력한다.

그리고 SSL 탭에서 SSL에 서버측의 ssl 파일을 위치하고 CA 검증을 하지 않게 하면 사용이 가능하다.

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

nbconvert은 jupyter notebook 파일을 markdown 파일 혹은 html 파일로 변환해주는 프로그램입니다.

• https://nbconvert.readthedocs.io/en/latest/usage.html

설치

nbconvert 를 설치 한다.

1
2
3
4
5

# pip을 통해 nbconvert 설치
(env)$ pip install nbconvert

# conda를 통해 nbconvert 설치
(env)$ conda install nbconvert

사용

원하는 포맷을 --to 에 지정해서 변환한다, 지원되는 포맷은 다음 같다

• HTML, LaTeX, PDF,WebPDF, Reveal.js HTML slideshow,
• Markdown, Ascii, reStructuredText,executable script,notebook.

다음 같이 사용한다.

1

jupyter nbconvert --to FORMAT notebook.ipynb

nbconvert 로 markdown 변환 명령은 다음의 두가지 결과물을 생성한.

• notebook.md : markdown 파일
• notebook_files : markdown 파일에 포함되어 있는 모든 이미지를 모아놓은 폴더

1
2
3

(env)$ jupyter nbconvert notebook.ipynb
(env)$ ls
notebook.ipynb   notebook.html    notebook_files/

예

예를 들어 My_NOTEBOOK.ipynb 라는 jupyter notebook 파일을 markdown 파일로 변환한다면

1

(env)$ jupyter nbconvert --to markdown My_NOTEBOOK.ipynb

pyenv 에 doctor 명령이 생겼다.

• pyenv 설치 구성을 검증한다.
• python 버전을 빌드하기 위한 개발 도구들

doctor 명령을 사용하려면 시스템에 개발도구와 빌드도구가 설치되어야 한다.

준비

pyenv 의 시스템 빌드환경에 필요한 도구를 설치가 필요하다.

• https://github.com/pyenv/pyenv/wiki#suggested-build-environment

Ubuntu/Linux

1
2
3

sudo apt update; sudo apt install build-essential libssl-dev zlib1g-dev \
libbz2-dev libreadline-dev libsqlite3-dev curl git \
libncursesw5-dev xz-utils tk-dev libxml2-dev libxmlsec1-dev libffi-dev liblzma-dev

macOS

1

brew install openssl readline sqlite3 xz zlib tcl-tk@8 libb2

pyenv doctor 사용

1
2
3
4
5

$ pyenv doctor
Cloning /home/USER/.pyenv/plugins/pyenv-doctor/bin/.....
Installing python-pyenv-doctor...
Installed python-pyenv-doctor to /tmp/pyenv-doctor.20250327094025.5063/prefix
Congratulations! You are ready to build pythons!

기타

시스템에 빌드 도구가 설치되지 않으면 doctor 명령을 실행하면 다양한 에러를 낸다.

1
2
3
4
5
6
7

$ pyenv doctor
...
...
configure: error: GNU readline is not installed.
Problem(s) detected while checking system.

See https://github.com/pyenv/pyenv/wiki/Common-build-problems for known solutions.

Jupyter-lab 의 kernel 은 Jupyterlab 과 실행 환경과 직간접으로 연결을 해준다.

ipykernel 은 jupyter에 대해서 iPython을 제공한다.

ipython은 jupyterlab에 kernel을 통해서 python 혹은 virtualenv 의 개발환경을 제공한다.

kernel

jupyter-lab 을 실행하면 커널 launcher 화면에 kernel이 출력된다.

실행중인 커널은 사이트 바에서 확인 가능하다.

jupyter 명령으로 커널을 관리할 수 있다.

jupyter 명령

jupyter 명령에서 kernels 를 생성, 제거 관리를 할 수 있다.

현재 kernel 목록을 확인 가능하다.

1
2

$ jupyter kernelspec list
  python3    /home/user/.pyenv/versions/MyEnv/share/jupyter/kernels/python3

새 kernel 생성성

가상환경 TestEnv 가 있다고 가정한다.

TestEnv 의 python 환경을 jupyterlab 에서 사용하려면

해당 가상환경을 jupyterlab 커널에서 사용하게 하려면 커널을 생성해 주어야 한다.

그러기 위해서 ipykernel 이 필요하다

1

pip install ipykernel

ipykernel 로 새 kernel 생성:

1

python -m ipykernel install --name {MACHINE_NAME} --display-name "{DISPLAY_NAME}" --user

다음 명령에서 --user을 사용하면 사용자의 ~.local/share 공간에 커널이 추가 되고 사용자의 jupyterlab 에서 모두 사용할 수 있다.

1
2

$ python -m ipykernel install --name testenv_kernel --display-name "Test Env" --user
Installed kernelspec testenv_kernel in /home/user/.local/share/jupyter/kernels/testenv_kernel

kernelspec 으로 확인 할 수 있고,

1
2
3
4
5

~$ jupyter kernelspec list
Available kernels:
  python3           /home/qkboo/.pyenv/versions/3.12.8/envs/TestEnv/share/jupyter/kernels/python3
  testenv_kernel    /home/qkboo/.local/share/jupyter/kernels/testenv_kernel
(TestEnv) qkboo:~$

Jupyterlab 의 launcher 화면에도 새 커널을 확인할 수 있다.

kernel 제거

kernelspec uninstall 명령을 사용해서 사용자 커널을 제거할 수 있다.

1

jupyter kernelspec uninstall {KERNEL_NAME}

testenv_kernel 을 삭제해 보자

1
2
3
4
5

jupyter kernelspec uninstall testenv_kernel
Kernel specs to remove:
  testenv_kernel        /home/user/.local/share/jupyter/kernels/testenv_kernel
Remove 1 kernel specs [y/N]: y
Removed /home/user/.local/share/jupyter/kernels/testenv_kernel

— 참고 —

1. https://docs.posit.co/ide/server-pro/user/jupyter-lab/guide/jupyter-kernel-management.html

기존 MariaDB 10 에서 다른 서버로 데이터/사용자 등을 이전하기. 이전은 2가지 방법을 제시한다.

1. 데이터베이스 덤프만 이전: mysqldump 이용
2. 데이터베이스 데이터 폴더 복제: rsync 이용

준비

1. 기존서버: 현재 운영중인 mariadb 데이터베이스
2. 대상서버: 새로 설치한 mariadb 데이터베이스

양쪽 서버의 mariadb 를 모두 종료한다.

1
2
3
4
5

$ systemctl stop mariadb

OR

$ systemctl stop mysql

데이터베이스 덤프만 이전

데이터베이스 시스템의 데이터베이스를 덤프를 통해서 백업하고 리스토어한다.

- 기존 데이터베이스 덤프

모든 데이터베이스를 덤프해서 사용.

1

$ mysqldump -u [user] -p --all-databases > all_databases.sql

지정한 데이터베이스만 덤프하려면

1	mysqldump -u root -p --opt [database name] > database_name.sql

덤프 후에 덤프 파일을 대상 서버에 이전한다.

1
2

$ scp all_databases.sql user@192.168.1.10:~/
$ scp database_name.sql user@192.168.1.10:~/

- 대상 서버에서 갱신

덤프 파일을 데이터베이스에 리스토어 한다.

1
2

$ mariadb -u [user] -p --all-databases < all_databases.sql  # 모든 데이터베이스
$ mariadb -u [user] -p newdatabase < database_name.sql      # 단일 파일

덤프 파일을 리스토어 한 후에 접속해서 데이터베이스가 들어 있는지 확인.

1
2

$ mysql -u user -p
> show databases;

대상 서버에 사용자 허용 범위 설정

1
2
3
4

> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';


> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

1
2

> GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.%' 
        IDENTIFIED BY 'password' WITH GRANT OPTION;

데이터베이스, 사용자와 권한을 모두 옮기기

기존 데이터베이스 서버의 데이터베이스, 사용자, 권한과 데이터 구조 등을 모두 새 서버에 이전하려고 하면 데이터베이스 시스템이 다루고 있는 모든 파일 시스템을 대상 서버에 복제를 하면 된다. 보통 rsync 를 통해서 모든 파일을 소스에서 타겟 서버로 이전을 한다.

1. 대상서버 데이터 폴더 준비
2. 기존서버에서 rsync 로 대상서버로 데이터 폴더 전송
3. 대상서버 mariadb 종료 및 my.cnf 에 데이터 폴더 위치 확인
4. 대상서버 데이터 폴더 권한을 mysql 로 변경

1. 대상 서버 데이터 폴더와 복사

옮기는 서버가 192.168.1.10 이고 사용자 계정 user 를 사용한다고 가정하자.

기존서버에서 mariadb 를 종료한다.

1

$ susdo systemctl stop mariadb

먼저 대상서버의 data 폴더를 준비하고 사용자 계정 user 가 사용할 수 있게 한다.

1
2

$ sudo mkdir -p /data/maria_db
$ sudo chown user:user /data/maria_db

2. 기존서버에서 대상서버로 데이터 폴더 복제

기존서버에서 mariadb 를 종료한다.

1

$ susdo systemctl stop mariadb

기존서버 데이터 폴더를 대상서버로 모두 복제한다.

1

$ rsync -avz /var/lib/mysql/* user@192.168.1.10:/data/maria_db/ 

3. 대상서버 폴더 권한 및 시작

1

$ sudo chown mysql:mysql -R /data/maria_db

/etc/mysql/my.cnf 의 데이터 폴더 위치를 조정한다.

mariadb 를 재시작한다.

1

$ susdo systemctl start mariadb

에러가 없이 잘 시작되면 데이터 폴더 이전이 잘 된것으로 보인다.

4. 확인

클라이언트로 mariadb 에 root 계정으로 접속해 본다. 이 계정의 비밀번호는 이전서버의 root 계정과 비밀번호일 것이다.

1

$ mariadb -u root -p

기존 서버의 데이터베이스 계정 student 가 있다고 가정하고 클라이언트로 접속해 본다.

1

$ mariadb -u student -p

Debian계열: Ubuntu 22.04 / Armbian

MariaDB 재단은 apt를 사용하여 패키지를 관리하는 여러 리눅스 배포판을 위한 MariaDB 패키지 저장소를 제공합니다

- apt로 MariaDB 10.4 이상 설치

1
2
3
4
5
6
7

$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 11.3 is valid
# [info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
# [info] Adding trusted package signing keys...
# [info] Running apt-get update...
# [info] Done adding trusted package signing keys

그리고 apt 업데이트를 진행한다

1

sudo apt update

이제 레포지토리를 통해서 mariadb 를 업데이트할 수 있게되었다.

- MariaDB GPG 공개키

Debian 9(Stretch) 및 Ubuntu 16.04 LTS(Xenial)부터 GPG 공개 키의 ID는 0xF1656F24C74CD1D8. 전체 키 지문은 다음과 같습니다.:

1

177F 4010 FE56 CA33 3630  0305 F165 6F24 C74C D1D8

apt-key유틸리티를 사용하여 공개키를 가져올 수 있습니다. 예를 들어:

sudo apt-key adv –recv-keys –keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

- mariadb 패키지 설치.

MariaDB 10.4 이상 에서 가장 일반적인 패키지를 설치하려면 다음 명령을 실행합니다 :

1

sudo apt-get install mariadb-server galera-4 mariadb-client libmariadb3 mariadb-backup mariadb-common

• 24/3: 11.3 설치.

설치후 확인

1
2
3
4
5
6
7
8

$ systemctl status mariadb
● mariadb.service - MariaDB 11.3.2 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf
     Active: active (running) since Tue 2024-03-05 06:32:22 KST; 7min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/

MariaDB 보안 설정하기

설치후에 MariaDB 시스템 초기화와 보안 설정을 진행해야 한다.

7개 질문이 나오는데 unix_socket 은 n 이고 나머지는 기본 값으로 진행한다.

단, root 패스워드는 잊어버리지 않아야 한다.

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

$ sudo mysql_secure_installation

Enter current password for root (enter for none):

Switch to unix_socket authentication [Y/n] n

Change the root password? [Y/n] Y

Remove anonymous users? [Y/n] Y

Disallow root login remotely? [Y/n] Y


Remove test database and access to it? [Y/n] Y

Reload privilege tables now? [Y/n] Y

이제 root 계정으로 명령행으로 접속해 보자

1
2
3
4
5
6
7
8
9
10
11

~$ mariadb -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

현재 11버전까지 mysql 클라이언트를 사용하지만 향후 deprecated 될 예정으로

mariadb 클라이언트 사용을 권장한다.

Option file 설정

설정 파일은 유닉스 계열은 my.cnf (or mariadb.cnf) 이고 윈도우 계열은 my.ini 이다.

• https://mariadb.com/kb/en/configuring-mariadb-with-option-files/

Ubuntu/Debian 계열:

/etc/mysql 밑에 데이터베이스 구성 설정 파일이 있다.

1. 기본 설정 파일 my.cnf
2. Debian / Ubuntu 계열은 my.cnf -> mariadb.cnf 로 링크되어 있다.
3. mariadb.cnf 은 보통 /etc/mysql/mariadb.conf.d/ 폴더 아래 구성 파일들.

1
2
3
4
5
6

$ ll mariadb.conf.d/
    50-client.cnf
    50-mysql-clients.cnf
    50-server.cnf
    60-galera.cnf
    99-enable-encryption.cnf.preset/

data 폴더 변경

블로그 글 MariaDb 10/ MySQL 8 - data 디렉토리 변경 (Ubuntu)

참조

https://mariadb.com/kb/ko/installing-mariadb-deb-files/

Sphinx 는 python의 대표적인 문서화 도구로 reStructedText 또는 Markdown 문서를 HTML, pdf, man page 등의 결과물로 생성할 수 있다.

sphinx 설치

sphinx 는 python 환경에서 실행이 된다. 설치는 python 환경 혹은 OS 환경에 직접 설치하는 방법이 있다. 파이썬 가상환경을 권장한다.

https://www.sphinx-doc.org/en/master/usage/installation.html

- Python 환경에 설치

python 환경 혹은 python 가상환경 (venv, virtualenv 등)에 설치

pip

1

pip install sphinx

Anaconda

1

conda install sphinx

- 시스템에 직접 설치

각 OS 의 SW 로 설치한다면 package 관리자를 사용해 설치한다.

Debian/Ubuntu

1

apt install python3-sphinx

RHEL, CentOS

1

yum install python-sphinx

macOS / home-brew

1

brew install sphinx-doc

어느날 git 의 레포지토리의 파일이 모두 changed 상태로 보여 당황했다. 한 파일을 diff 로 보니 줄의 마지막에 ^M 이 보였다. 에디터가 OS에 따라서 LF 를 CRLF 로 바꿔서 보여주는 것이었다.

CRLF 상황

Linux 를 20.04 LTS 를 사용하다 os-release-upgrade 를 사용해서 22.04 LTS 로 업그레이드 했다.

그리고 한동안 다른 작업을 하다 소스를 다루게 되어서 윈도우 11에서 VS code 로 WSL의 우분투 폴더를 열어 보니 git 의 staging area 에 커밋할 파일이 엄청 들어와 있는 것이다. 찾다 보니 아래 그림 같이 줄바꿈 코드로 CRLR 로 인식을 한 것이다.

소스 파일을 수정할 일이 아니라서 검색을 좀 해보니 git 의 전역 설정에서 가능할 것 같았다. 다음 링크를 참조했다.

1. https://stackoverflow.com/questions/28076760/git-add-adds-m-to-the-end-of-every-line
2. https://github.com/Microsoft/WSL/issues/2318

git 전역 설정으로 autocrlf 를 활성화 한다. git 전역 설정에 다음 같이 autocrlf 를 true 지정하면 해결이 된다.

1

$ git config --global core.autocrlf true

Swallow Clone 은 git 저장소에 커밋된 기록중 일부만 사용하는 기술 있다. 그런데 브랜치를 추가하면 원격지와 클론한 저장소에서 탐색이 안되는 단점이 있다. 여기서 swallow clone 에서 branch를 사용하는 방법을 요약한다.

Swallow Clone 에서 branch를 사용하자.

기본적으로 swallow clone 을 사용하면 원격 저장소의 branch 를 모두 볼 수 없다. 이것은 .git/config 를 보면 refs/remotes/origin/master 로 구성되어 있어서이다. 그래서 swallow clone 한 저장소는 원격 저장소의 master 브랜치만 보게 된다.

다음은 .git/config 의 일부이다.

1
2
3

[remote "origin"]
        url = git@github.com:repository/YOUR_REPO.git
        fetch = +refs/heads/*:refs/remotes/origin/master

master 브랜치 대신 브랜치 트리 모두를 보려면 아래 같은 단계로 처리해 준다.

1. 다음 같이 원격 저장소의 모든 브랜치를 대상으로 처리해 준다.

1

git remote set-branches origin '*'

origin 의 브랜치를 master에서 * 로 지정하면 .git/config가 아래 같이 수정된다.

1
2
3

[remote "origin"]
        url = git@github.com:repository/YOUR_REPO.git
        fetch = +refs/heads/*:refs/remotes/origin/*

2. 이어서 원격 레포지토리를 업데이트 하면 관련한 브랜치 정보를 갱신한다.

1
2
3
4
5
6
7
8
9
10
11

$ git remote update
Fetching origin
remote: Enumerating objects: 306, done.
remote: Counting objects: 100% (306/306), done.
remote: Compressing objects: 100% (283/283), done.
remote: Total 285 (delta 161), reused 6 (delta 0), pack-reused 0
Receiving objects: 100% (285/285), 8.59 MiB | 4.35 MiB/s, done.
Resolving deltas: 100% (161/161), completed with 16 local objects.
From git@github.com:repository/YOUR_REPO.git
 * [new branch]      1024_dev   -> origin/1024_dev
 * [new branch]      gt_master  -> origin/gt_master

3. 업데이트 한 후에 원격 브랜치만 검색해도 잘 나타난다.

1
2
3
4
5

$ git branch -r
  origin/1024_dev
  origin/HEAD -> origin/master
  origin/gt_master
  origin/master

4. 원격지 브랜치를 체크아웃해서 사용하면 된다.

1

$ git checkout -b t origin/1024_dev

함께 보면 유용한 git 명령

간단하게 빌드/테스트 를 하기위함 이라면 `Sparse-checkout`` 과 함께 이용하면 좋다.

1. [git] Sparse Checkout
2. Swallow Clone

MariaDB 에는 보통 관계형 테이블에서 SQL을 이용해 JSON data를 사용하기 위해서 별도의 json type을 지원하고 있다.

MySQL / MariaDB json type

MariaDB 에서 일반 테이블에서 json을 지원하기 위해 json type을 지원하고 있다. 단 MySQL 의 json data type에 호환하기 위해서 MariaDB json type은 longtext 의 alias 로 제공한다.

• https://mariadb.com/kb/en/json-data-type/
• json이 sql 표준에 모순되어서 mariadb는 최소 성능에을 유지하는 벤치마크를 가지고 있다.
• 적절한 json 데이터를 유지하기 위해서 json_valid 함수를 check 구문에 사용할 수 있다. (v10.4.3)

JSON 데이터 형식에 대해서는 다양한 소개를 검색할 수 있다.

• 문자여로로 숫자, 문자, 객체, 날짜 및 자료의 배열 등의 형식을 표현할 수 있다.
• https://docs.fileformat.com/ko/web/json/

MySQL / Oracle / MariaDB / PostgreSQL / MSSQL 등에서 Bigdata 분야에서 관심 받는 No-SQL의 자료 구조로 각광받고 있다. json은 1:n 의 관계에서 유동적인 자료를 처리하기 위해서 지원하는 추세이다.

MariaDB: json datatype 사용하기

1
2
3

> USE lecture;
> 
> SHOW VARIABLES LIKE 'version';

json datatype 을 가진 테이블 생성

json_test 테이블을 생성한다. JSON 데이터 타입으로 선언하면 내부적으로 longtext로 저장 됨

1
2
3
4

> create table json_test (id  int ,  data JSON );
> DESC json_test;

> SHOW CREATE TABLE json_test;

SHOW CREATE 결과는 아래와 같을 것이다.

1
2
3
4

CREATE TABLE `json_test` (
  `id` int(11) DEFAULT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

테스트한 테이블을 제거

1

> DROP TABLE json_test;

json data validation

check() 검증 함수를 이용해서 insert하는 json data의 형식/유효성을 검증할 수 있다.

1
2
3
4
5
6

CREATE TABLE json_test (
  id  int ,  
  data JSON CHECK (JSON_VALID(data))
);

SHOW CREATE TABLE json_test;

show 결과는 아래 같다.

1
2
3
4
5

CREATE TABLE `json_test` (
  `id` int(11) DEFAULT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

json 입력

json 데이터 입력해 확인해 보자. 먼저 문자열을 입력해보자.

1
2

-- ERROR 4025 (23000): CONSTRAINT 
INSERT INTO json_test VALUES (1, 'invalid');

json 형식이 아니기 때문에 ERROR 4025 에러가 발생한다.

json datetype 컬럼에 json 데이터 형식을 가진 문자열로 입력할 수 있다.

1
2
3
4

-- string 
INSERT INTO json_test VALUES(1, '{ "name": "그르렁", "age": 30 }');
INSERT INTO json_test VALUES (2, '{"id": 1, "name": "으르렁", "age": 29, "address": "서울시"}');
SELECT * FROM json_test;

select 한 결과는 json data 컬럼에 JSON Object 형식으로 저장됨을 확인할 수 있다.

1
2

{ "name": "그르렁", "age": 30 }
{"id": 1, "name": "으르렁", "age": 29, "address": "서울시"}

또한 json datetype 컬럼에 json_object() 함수를 사용해 데이터를 입력할 수 있다.

1

json_object(key, value [,key, value])

json_object 로 입력하고 select 로 확인 해보자.

1
2
3

-- key, value, key, value 형태로 계속 입력된다.
> INSERT INTO json_test VALUES (3 , json_object('name' , '노르라' , 'Sex' , '남자' , 'age', 25, '전화' , '010-111-2222')) ;
> SELECT * FROM json_test;

문자열 JSON notation 형식으로 저장되는 data 에서 정보를 얻을 때는 JSON path 형식의 표현식을 사용한다.

json object의 값 얻기

JSON object 를 얻기위해서 다양한 JSON_ 형식의 함수를 지원한다. 함수들은 모둔 json path expression 을 사용해 json object 를 탐색한다. 아래는 대표적인 함수

1
2
3
4

- json_value(json_doc, path): READING SCALAR DATA
- json_array(json_doc, path): list 에서 JSON array 를 반화
- JSON_EXTRACT(json_doc, path[, path] ...) : return values
- JSON_QUERY(json_doc, path[, path] ...) : return json object, array, null (invalid json)

간단히 사용해 보자.

json_test 테이블에서 age 속성을 select 해보자

1
2
3
4

> SELECT JSON_VALUE(DATA, '$.age') AS age FROM json_test;
30
29
25

이번에는 name, age, 전화 속성만 추출해 보자.

1
2
3
4
5
6
7
8
9

> SELECT JSON_VALUE(DATA, '$.name') AS name,
	JSON_VALUE(DATA, '$.age') AS age,
	JSON_VALUE(DATA, '$.전화') AS 전화
FROM json_test;

"name"	"age"	"전화"
"그르렁"	"30"	\N
"으르렁"	"29"	\N
"노르라"	"25"	"010-111-2222"

JSON path expression

JSON 함수에서 JSON_Function(json_doc, path) 인자 path 는 json path expression 을 사용한다.

• MariaDB jsonpath 는 Mysql 의 jsonpath 에 근접해 구현하고, 와일드카드 ** 스텝은 표준 보다는 MySQL에 가깝게 구현하고 있다.

path expression:

1
2
3
4

path: ['lax'] '$' [step]
  `[lax]` : path mode 가 lax 지원.
  `$` : 콘텍스트 아이템 시작. path는 항상 $로 시작함.
  `[step]` : Json Object 인 object, array , wild card 가 이어진다.

object member selector

• . : json object 를 선택시.
• .* : 해당 object의 모든것

array 요소 selector

• [n] : n 번째 요소
• [*] : 모든 요소

mariadb 10.9 이후

• [-n] : 마지막에서 -n 번 인덱스
• [last-n] : 마지막에서 n번째 까지
• [M to N] : m 과 n 사이 인덱스

예를 들어보자,

1

$.name    -- json객체의 name 속성을 지시한다.

배열 접근

1

$[5]      -- 5 번재 요소

Object selector tutorial

json 객체가 있다.

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

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';

이 json 객체를 SQL 과 json 함수를 이용해 탐색하는 예이다.

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

SELECT JSON_EXTRACT(@json, '$.A');             -- A객체
SELECT JSON_EXTRACT(@JSON, '$.A[2]');           -- A객체의 2 번째 요소
SELECT JSON_EXTRACT(@json, '$.A[-8][1]');

SELECT JSON_EXTRACT(@json, '$.B');

SELECT JSON_EXTRACT(@json, '$.A[last-1]');
SELECT JSON_EXTRACT(@json, '$.A[last-1][2]');
SELECT JSON_EXTRACT(@json, '$.A[last-7][1]');

SELECT JSON_EXTRACT(@json, '$.A[3 to 5]');

SELECT JSON_EXTRACT(@json, '$.A[3 to last]');

-- wildcard step '**'

SELECT JSON_EXTRACT(@JSON, '$**[1]');

SELECT JSON_EXTRACT(@JSON, '$**.C');

json functions 사용

테스트로 생성한 json_test 테이블을 사용해서 많이 사용하는 json 함수를 사용해 보자.

JSON_VALUE:

1

JSON_VALUE(json_doc, path) : path 에 있는 스칼라 값을 반환

1

select json_value('{"key1":123}', '$.key1');

객체 안의 배열과 스칼라 값 객체의 json_valie() 결과

1
2
3
4
5

-- key1 속성에 배열과 스칼라 값 객체의 json_valie() 결과
select json_value('{"key1": [1,2,3], "key1":123}', '$.key1');
123

select json_value('{"key1": [1,2,3], "key1":123}', '$.*');

배열은 [index[last]] 형식으로 탐색

1

select JSON_VALUE('[{"key1": [1,2,3], "key1":123}]', '$[0].key1');

JSON_EXTRACT:

1

JSON_EXTRACT(json_doc, path[, path] ...) : path에 일치하는 모든 return values. null (invalid)

1
2
3
4
5
6
7
8
9
10
11

SET @json = '[1, 2, [3, 4]]';
SELECT JSON_EXTRACT(@json, '$[1]');
SELECT JSON_EXTRACT(@json, '$[last]');


SELECT id, JSON_EXTRACT(DATA, '$**.age') FROM json_test;
SELECT id, JSON_EXTRACT(DATA, '$**.전화') FROM json_test;

SELECT id, JSON_EXTRACT(DATA, '$.name') FROM json_test;
SELECT id, JSON_EXTRACT(DATA, '$.age') FROM json_test;
SELECT id, JSON_EXTRACT(DATA, '$.전화') FROM json_test;

constraint error 는 보자, 새로운 객체를 추가한다.

1
2
3

INSERT INTO json_test VALUES(4, '{ "name": "가로미", "age": 29 }');
INSERT INTO json_test VALUES(5, '{ "name": "가로미", "age": 29 }');
SELECT * FROM json_test;

기존 4번 인덱스가 있어서 다음은 constraint error 를 발생한다.

1
2

-- constraint error:
INSERT INTO json_test VALUES(4, '{ "name": "가로미", "age": 29 }, { "name": "그러릿", "age": 21, "address": "경기도 성남시" }');

json_array()

JSON array 를 생성해 준다.

1

json_array(): list 에서 JSON array 를 반화

여러 데이터를 json_array로 배열로 입력한다.

1
2
3
4
5

INSERT INTO json_test VALUES(7, JSON_ARRAY(129, 3.1416, 'My name is "Foo"', NULL) );
INSERT INTO json_test VALUES(8, JSON_ARRAY( '{ "name": "크르렁", "age": 30 }', '{ "name": "갸르릉", "age": 21 }') );

INSERT INTO json_test VALUES(6, '{ "address" : [ { "name": "고로릿", "age": 20 }, { "name": "그러릿", "age": 21 }] } ');
INSERT INTO json_test VALUES(8, JSON_OBJECT( "address", JSON_ARRAY( '{ "name": "크르렁", "age": 30 }', '{ "name": "갸르릉", "age": 21 }') ) );

입력된 결과를 쿼리한다.

1
2
3
4
5
6
7
8

SELECT * FROM json_test;

SELECT id, JSON_EXTRACT(DATA, '$.address[0]') FROM json_test;

SELECT id, JSON_EXTRACT(DATA, '$.address[-1]') FROM json_test;


DROP TABLE json_test;

JSON_QUERY()

json_query 는 json 객체 형식의 표현을 입력해서 사용한다.

1
2

-- JSON_QUERY(json_doc, path
-- JSON_QUERY(json_doc, path[, path] ...) : path의 json object, array를 반환, null (invalid json)

1
2
3
4
5

-- key1 객체
select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1');

-- key1 객체의 배열
select json_query('{"key1":123, "key1": [1,2,3]}', '$.key1');

json_table()

• mariadb 10.6 이후.
• json data 를 sql table 로 변환한다.

@json 변수에 json data 가 선언되고, sql 문에서 json_table 에 의해 관계형 테이블로 탐색된다.

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

set @json='
[
  {"name":"Laptop", "color":"black", "price":"1000"},
  {"name":"Jeans",  "color":"blue"}
]';

select * from json_table(@json, '$[*]' 
  columns(
   name  varchar(10) path '$.name', 
   color varchar(10) path '$.color',
   price decimal(8,2) path '$.price' ) 
) as jt;

SET 과 json 형식

SET 구문을 사용해서 json 변수를 선언하고 SQL 에서 사용할 수 있다.

1
2
3

SET @json = '{"key1":"60\\" Table", "key2":"1"}';

SELECT JSON_VALUE(@json,'$.key1') AS Name , json_value(@json,'$.key2') as ID;

— 참고

1. json datatype : https://mariadb.com/kb/en/json-data-type/
2. jsonpath-expressions: https://mariadb.com/kb/en/jsonpath-expressions/