기존 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/

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/

클라이언트에서 원격 서버 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