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

data 폴더 위치를 변경해 이동한다.

• 여기서는 MariaDB 를 사용하고 있다.
• rsync 를 사용해서 복사한다.

위치 이동

my.cnf 에 있는 기본 디렉토리를 /data 폴더로 변경하고 적용한다.

1. 기본 data 디레토리 확인

my.cnf 에서 datadir 로 지정된 항목을 찾아 보자.

1
2

~$ grep datadir /etc/mysql/mariadb.conf.d/*
/etc/mysql/mariadb.conf.d/50-server.cnf:#datadir                 = /var/lib/mysql

기본으로 Ubuntu 종류에서는 /var/lib/mysql 를 기본 데이터 디렉토리로 지정하고 있다.

2. data 디렉토리를 /data 로 변경한다.

my.cnf 를 수정해서 /data로 변경하자

1

~$ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

3. data 폴더 권한

mysqld / mariadbd 데몬은 mysql 사용자, mysql 그룹으로 사용권이 실행되고 있다. 데이터베이스에서 접근하는 모든 자원(폴더, 파일, 로그 등등)은 mysql 사용자로 접근이 가는해야 한다. 그래서 /data 디렉토리 권한을 변경한다.

mysql 사용자와 그룹 확인한다.

1
2
3
4
5

$ grep mysql /etc/passwd
mysql:x:111:117:MySQL Server,,,:/nonexistent:/bin/false

$ grep mysql /etc/group
mysql:x:117:

소유권을 mysql 사용자로 변경한다.

1
2
3
4
5

$ sudo chown -R mysql:mysql /data
[sudo] 암호:
$ ls -al /data
drwxr-xr-x  3 mysql mysql  4096  5월  6 22:35 .
drwxr-xr-x 20 root  root   4096  5월  6 17:15 ..

4. data 이동

rsync 를 사용해서 /var/lib/mysql 데이터를 /data 아래로 복사한다.

1

$ sudo rsync -avzph /var/lib/mysql/ /data/

5. mysqld 데몬을 재시작하고 확인한다.

1
2
3
4

$ sudo systemctl restart mysql
$ sudo systemctl status mysql

● mariadb.service - MariaDB 10.11.2 database server

데이터베이스에 접속해 데이터를 확인해 보자. @@datadir 환경변수를 출력해서 /data 가 출력되면 my.cnf 의 설정이 잘 구성된 상태를 확인할 수 있다.

1
2
3
4
5
6
7
8

$ sudo mysql -u root -p

> select @@datadir;
+-----------+
| @@datadir |
+-----------+
| /data/    |
+-----------+

rsync 로 복사한 실제 데이터들은 각 스키마에서 확인해 보자.

MySQL

사용자 관리

mysql-server 를 설치하며 만든 root 사용자 패스워드를 사용해서 데이터베이스에 접속한다.

1

$ mysql -u root -p

사용자 데이터베이스

사용자가 사용할 데이터베이스를 만든다.

1
2

mysql>create database mydb;
Query OK, 1 row affected (0.00 sec)

그리고 CREATE USER, INSERT 로 새 사용자를 추가할 수 있다.

create user userid@HOST identified by ‘PASSWORD’;

사용자 foo 를 localhost 와 모든 것을 의미하는 패턴 %로 추가하면:

1
2

mysql > create user foo@localhost identified by 'password';
mysql > create user 'foo'@'%' identified by 'password';

혹은

1	insert into user (host, user, password) values ('localhost', 'hiru', 'password('hirururu'));

사용자 제거
mysql > drop user ‘hiru’;
mysql > delete from user where user =’hiru’

사용자 생성시 다음같이 1396 에러는 CREATE USER/GRANT 명령으로 사용자와 권한을 추가/관리해야 하는데 mysql.db, mysql.user 테이블을 직접 조작하다가 일관성이 깨졌기 때문

1
2

mysql> create user 'shopuser'@'localhost' identified by ')12345';
ERROR 1396 (HY000): Operation CREATE USER failed for 'shopuser'@'localhost'

제대로 사용자를 삭제하고

drop user shopuser@localhost

flush privileges; 로 갱신해 준다.

권한 주기

권한을 추가하고 삭제하기 위해서, GRANT와 REVOKE의 명령을 사용한다. GRANT 명령 등으로 데이터베이스 사용자가 데이터베이스 자원에 접근하는 권한을 만들 수 있다.

GRANT ALL privileges ON DB_NAME.TABLE TO USER_ID@[HOST] IDENTIFIED BY ‘PASSWORD’
GRANT [SELECT,DELETE,INSERT,UPDATE,] ON DB_NAME.TABLE TO USER_ID@[HOST] IDENTIFIED BY ‘PASSWORD’

• DB_NAME,TABLE 등에 * 패턴을 사용할 수 있다.
• HOST: 접근하는 소스 호스트
• PASSWORD: 패스워드

http://www.w3big.com/ko/mysql/mysql-administration.html

현재 머신에서만 접속할 수 있는 사용자 계정, 외부, 원격에서 접속할 수 있는 사용자 계정을 추가해 준다.

1
2
3

mysql> use mysql; # mysql system db
mysql> GRANT ALL privileges ON mydb.* TO foo@localhost IDENTIFIED BY '*****';
mysql> GRANT ALL privileges ON mydb.* TO foo@'%' IDENTIFIED BY '*****';

혹은

1
2

grant select, insert, update, delete on mydb.* to foo@host identified by 'password';
mysql > grant select, insert, update, delete on dbname.table to userid@'192.168.%' identified by 'password';

권한을 확인하는 방법

1
2

mysql > show grants for foo@localhost
mysql > show grants for 'foo'@'%';

변경된 권한을 적용하기

1

mysql > flush privileges;

권한을 삭제하는 방법

1

mysql > revoke all on dbname.table from username@host

추가한 사용자는 SELECT로 확인할 수 있다.

1

mysql> select host,authentication_string from user where user='foo';

사용자 데이터베이스 사용

새로 생성한 사용자 ID로 로그인을 해서 데이터베이스 정보를 확인해 보자.

1
2
3
4
5
6
7
8
9
10

$ mysql -u foo -p
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
+--------------------+

그리고 데이터베이스를 사용하려면 use [DATABASE] 로 변경한다.

1

mysql> use mydb;

Character Set

mysql에서 한글이 ?로 표시되는 경우.

1
2
3
4
5
6
7
8

mysql> SELECT * FROM department;
+----+------+
| id | name |
+----+------+
|  1 | ???  |
|  2 | ???  |
+----+------+
2 rows in set (0.00 sec)

my.cnf의 문자셋과 터미널 문자셋이 일치하지 않아서 그렇다. MySQL은 설치시 지정하지 않았다면 기본적으로 문자셋이 ‘latin1’으로 설정되어 있다.

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

mysql> show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | latin1_swedish_ci          |
| collation_database       | latin1_swedish_ci          |
| collation_server         | latin1_swedish_ci          |
| completion_type          | NO_CHAIN                   |
| concurrent_insert        | AUTO                       |
| connect_timeout          | 10                         |
| core_file                | OFF                        |
+--------------------------+----------------------------+
15 rows in set (0.00 sec)

MySQL 설정 파일에서 문자셋을 변경할 수 있다. 다믕 같이 자신의 my.cnf 파일을 작성한다. client, mysqld, mysql 에 대해서 utf8 사용을 선언해 준다.

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

[client]
..
default-character-set=utf8

[mysqld]
character-set-client-handshake=FALSE
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

mysql> show variables like ‘char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)

사용자 관리

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO ‘zara‘@’localhost’
-> IDENTIFIED BY ‘zara123’;

SQL

Structural Query Language로 튜플 간의 관계를 계산해서 결과를 도촐한다.

• SEQUEL: Structured English QUEry Language; part of SYSTEM R, 1974
• SQL/86: ANSI & ISO standard
• SQL/89: ANSI & ISO standard
• SQL/92 or SQL2: ANSI & ISO standard
• SQL3: in the works…
• SQL2 supported by ORACLE, SYBASE, INFORMIX, IBM DB2, SQL SERVER, OPENINGRES,…

SQL의 구성

• SQL consists of the following parts:
• Data Definition Language (DDL)
• Interactive Data Manipulation Language (Interactive DML)
• Embedded Data Manipulation Language (Embedded DML)
• Views
• Integrity
• Transaction Control
• Authorization
• Catalog and Dictionary Facilities

교수 학습 연습

데이터 유형

http://www.w3big.com/ko/mysql/mysql-data-types.html

table professor

파일 Python-Database-class-student.sql

컬럼명	테이터 타입	비고
scode	varchar(4)	학번, 기본키
sname	varchar(20)	이름
sdept	varchar(20)	학과
sphone	varchar(15)	전화번호

1
2
3
4
5
6

CREATE TABLE professor (
    pcode varchar(4) NOT NULL PRIMARY KEY,
    pname varchar(10),
    pdept varchar(12),
    pphone varchar(8)
);

table student

파일 Python-Database-class-student.sql

컬럼명	테이터 타입	비고
scode	varchar(4)	학번, 기본키
sname	varchar(20)	이름
sdept	varchar(20)	학과
sphone	varchar(15)	전화번호

create student table

1
2
3
4
5
6

CREATE TABLE student (
    scode char(4) NOT NULL PRIMARY KEY,
    sname char(10),
    sdept char(12),
    sphone char(8)
);

table course

파일 Python-Database-class-student.sql

컬럼명	테이터 타입	비고
ccode	varchar(4)	과목코드, 기본키
cname	varchar(20)	과목명
ctime	int	강의 시수
croom	varchar(15)	강의실

create course table

1
2
3
4
5
6

CREATE TABLE course (
    ccode varchar(4) NOT NULL PRIMARY KEY,
    cname varchar(10),
    ctime integer,
    croom varchar(8)
);

table lecture

파일 Python-Database-class-student.sql

컬럼명	테이터 타입	비고
lpcode	varchar(4)	교수코드, 기본키
lccode	varchar(4)	과목코드, 기본키

create lecture table

1
2
3
4
5

CREATE TABLE lecture (
    lpcode char(4) NOT NULL,
    lccode char(4) NOT NULL,
    PRIMARY KEY (lpcode, lccode)
);

table advice

파일 Python-Database-class-student.sql

컬럼명	테이터 타입	비고
apcode	varchar(4)	교수코드, 기본키
asccode	varchar(4)	학번, 기본키

create advice table

1
2
3
4
5

CREATE TABLE advise (
    apcode char(4) NOT NULL,
    ascode char(4) NOT NULL,
    PRIMARY KEY (apcode, ascode)
);

table register

파일 Python-Database-class-student.sql

컬럼명	테이터 타입	비고
rscode	varchar(4)	학번, 기본키
rcccode	varchar(4)	과목코드, 기본키

create register table

1
2
3
4
5

CREATE TABLE register (
    rscode char(4) NOT NULL,
    rccode char(4) NOT NULL,
    PRIMARY KEY (rscode, rccode)
);

데이터 입력

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

INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P001','김 구','컴퓨터공학과','0001');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P002','안창호','컴퓨터공학과','0002');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P003','이육사','국문학과','0003');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P004','박종화','국문학과','0004');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P005','심 훈','사학과','0005');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P006','한용운','사학과','0006');

외부 데이터 파일 이용

파일에 필드 간의 ‘,’로 구분한 데이터 파일이 있다. 물론 필드 구별 문자는 데이터의 내용에 따라 사용자가 임의로 정 할 수 있다.
파일: studens.txt

1
2
3
4
5
6

S001, 박소명, 컴퓨터공학과, 123-4567
S002, 최민국, 컴퓨터공학과, 234-5678
S003, 이승호, 국문학과, 345-6789
S004, 정수봉, 국문학과, 456-7890
S005, 김상진, 사학과, 567-8901
S006, 황정숙, 사학과, 678-9012

studnets.txt 파일을 읽어들이는 것은 스크립트 파일을 작성하거나 mysql에서 직접 실행할 수 있다.
먼저 스크립트 파일 students.sql은 다음과 같다.

1
2
3

use mydb;
load data local infile "student.txt" into table student
fields terminated by ',' ;

이제 mysql 클라이언트에서 데이터를 읽어 들인다.

1
2

mysql>source students.sql;

데이터 조회

SELECT [DISTINCT] select _expr essi on
FROM table_list
WHERE where_definition
ORDER BY col_name [ASC|DESC]
GROUP BY col _name_list
LIMIT [offset ], rows

예제-1) 전체 교수 리스트를 출력하는 SQL 검색 문을 작성하라.
mysql > sel ect * fromprof;

예제-2) 전체 교수 리스트를 이름순서로 출력하는 검색 문을 작성하라.
mysql > sel ect * fromprof order by pname;

예제-5) 전체 교수 리스트를 이름 역순으로 출력하는 검색 문을 작성하라.
mysql > sel ect * fromprof order by pname desc;

전체 교수 리스트를 학과별로 출력하는 검색 문을 작성하라.
mysql > sel ect * fromprof order by pdept, pname;

예제-4) 국문학과 교수 리스트를 이름순서로 출력하는 검색 문을 작성하라.
mysql> select * from professor where pdept =’국문학과’;

JOIN

“FROM 테이블명 AS 별명” 구문은 SQL 문장에서 별명으로 테이블을 참조하 는 역할은 한다.

예제-6) MySQL 과목을 강의하는 교수님의 이름, 전화번호와 강의실을 검색 하는 문장을 작성하라.
mysql> select p.pname, p.pphone, c.croom from professor p, course c, lecture l
where c.cname=’MySQL’ and c.ccode=l.lccode
and l.lpcode=p.pcode;

예제-7) ‘김구’ 교수님이 강의하는 과목명, 강의 시수와 강의실을 검색하는 문장을 작성하라.

1
2
3
4

select c.cname, c.ctime, c.croom
from professor as p, course as c, lecture as l
where p.pname = '김 구' and p.pcode = l.lpcode
     and l.lccode = c.ccode;

예제-8) 각 학생이 수강 신청한 과목에 대해서 학생이름, 전화번호, 과목명, 강의실, 강의 시수를 검색하는 문장을 작성하라.

1
2
3
4

select s.sname, s.sphone, c.cname, c.ctime, c.croom
from student as s , course as c, register as r
where s.scode = r.rscode and r.rccode = c.ccode
order by s.sname, c.cname;

sub-query

예제-9) 각 학생이 신청한 총 학점을 구하는 검색식을 작성하라.

1
2
3
4

select s.sname, s.sdept, s.sphone, sum(c.ctime)
from student as s , course as c, register as r
where s.scode = r.rscode and r.rccode = c.ccode
group by s.sname;

• WHERE 조건절에 해당하는 결과를 GROUP BY 구절에 명시된 s.sname 필드에 따라 그룹으로 결과를 분류하고 난 후, SELECT 필드에 SUM(c.cti me) 함수를 사용해서 c.cti me 필드에 대한 합을 구함으로써 각 학 생이 신청한 총 학점를 구할 수 있다.

예제-10) 각 학과별 교수님은 몇 분인지 구하는 검색식을 작성하라.

1
2
3

select pdept, count(*)
from professor
group by pdept;

LIMIT 구절

예제-11) 페이지 크기가 2 일 때, (예제-8)의 결과에서 두 번째 페이지를 검색하는 SQL문장은 작성하라.
select s.sname, s.sphone, c.cname, c.ctime, c.croom fromstudent as s , course as c, regi ster as r
where s.scode = r.rscode and r.rccode = c.ccode order by s.sname, c.cname
limit 2, 2;

• 마지막 행의 limit 2, 2구절에서, 첫 번째 인자는 오프셋(offset)으로 검 색 결과 레코드들의 순번을 의미한다. 오프셋 값은 0 부터 지정하기 때문에 오프셋값2는전체레코드중에서세번째레코드를가리킨다. 두번째는 인자는 출력하는 레코드 수(rows)를 의미한다. 따라서, 레코드 수 2 는 2 개 의 레코드를 출력하라는 의미가 된다.

Update

UPDATE tbl_name
SET col_name1 = expr1, col_name2 = expr2, …
[WHERE where_definition] [LIMIT rows];

예제-12) 교수테이블에서 ‘김 구’ 선생님의 이름을 ‘하은용’ 교수님으로 변 경하는 문장을 작성하라.
update prof set pname =’하은용’ where pname =’김구’;

예제-13) 지도 테이블의 교수코드가 ‘P007’ 인 레코드들을 모두 ‘P005’ 로 변경하라.
update advise set apcode =’P005’ where apcode =’P007’;

예제-14) 강의 시수가 2인 과목들의 강의 시수를 하나 증가 시키고, 강의실 을 Lab1로 변경하라.
update course set ctime=ctime + 1, croom=’Lab1’ where ctime=2;

Delete

DELETE FROM tbl_name
[WHERE where_definition] [LIMIT rows]

예제-15 ) 국문학과 학생 레코드를 삭제하는 문장을 작성하라.
delete fromstudent where sdept =’국문학과’;

PyMySQL 튜토리얼

PyMySQL 설치

1

$ pip install PyMySQL

만약 pip 로 설치가 안되면 다음 같이 setup.py를 이용해 직접 설치 할 수 있다.
$ # X.X is the desired PyMySQL version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

1
2
3
4
5
6

CREATE TABLE users (
    'id' int(11) NOT NULL AUTO_INCREMENT,
    'email' varchar(255) COLLATE utf8_bin NOT NULL,
    'password' varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

데이터베이스 삭제

1

mysql> drop database mydb;

사용자 비밀번호 변경

1

mysql> set password for ''myid''@''localhost'' = password(''password'');

테이블 생성

show 명령으로 데이터베이스 자원 현환을 볼 수 있다.

mysql> help show

다음은 데이터베이스 목록을 보고, ‘mydb’를 사용합니다.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql>use mydb
...

테이블 현황

1
2
3
4
5
6
7

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

테이블 만들기

1
2
3
4
5
6
7
8

mysql>CREATE TABLE users (\
    id int(11) NOT NULL AUTO_INCREMENT,\
    email varchar(255) COLLATE utf8_bin NOT NULL,\
    password varchar(255) COLLATE utf8_bin NOT NULL,\
    PRIMARY KEY (id)\
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin\
AUTO_INCREMENT=1 ;

• mysql> 터미널에서 여러 줄의 명령을 입력하기 위해서 줄의 끝에 ‘'를 사용해서 여러줄을 입력했다.

이럴게 만들어진 테이블은 show 명령으로 작성 스크립트를 확인할 수 있다.

1
2
3
4
5
6
7
8
9

mysql> show create table users;
| Table | Create Table                                                         | users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_bin NOT NULL,
  `password` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

1 row in set (0.00 sec)

테이블의 구성 요소는 desc 명령을 확인할 수 있다.

1
2
3
4
5
6
7
8
9

mysql> desc users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| email    | varchar(255) | NO   |     | NULL    |                |
| password | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

데이터 입력

1
2
3
4

mysql> insert into users values (0, 'aaa@example.com', 'sldfjslfj');
mysql> insert into users values (0, 'bbb@example.com', 'sldfjslfj');
mysql> insert into users values (0, 'ccc@example.com', 'sldfjslfj');
mysql> insert into users values (0, 'ddd@example.com', 'sldfjslfj');

Alter

1
2
3
4
5
6
7
8

mysql> #컬럼 추가
mysql> alter table users add first_name varchar(10);
mysql> alter table users add last_name char(10);
mysql> alter table users add point int(5);
mysql> alter table users add gener int(5);

mysql> #컬럼 삭제
mysql> alter table users drop gener;

참조

• https://pythonschool.net/category/databases.html

MySQL

소스 빌드

mysql 계정 생성

groupadd mysql // 시스템에 mysql 그룹 생성

useradd -g mysql -M -s /bin/false mysql // 시스템 로그인이 불가하며 홈디렉터리를 제외하여 mysql 계정을 생성

mysql 소스 파일 다운로드 및 압축 해제

mysql 설치에 필요한 필수 패키지 사전 설치

1

yum install -y cmake bison gcc gcc-c++ ncurses-devel

mysql 데이터베이스 서버를 구축하기 위하여 mysql 최신 버전의 소스를 다운로드 받아 압축을 해제.

1
2
3
4

$ wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.28.tar.gz
$ tar xzvf mysql-5.6.28.tar.gz
$ cd mysql-5.6.28

mysql db 설치에 필요한 사전 작업이 완료되면 설치를 진행할 수 있다. Mysql 은 5.5 버전 이후의 버전은 configure 명령어가 아닌 아래와 같이 cmake 명령어를 이용하여 configure 를 진행.

• mysql cmake command 정리

1

$ cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql - DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 - DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 - DDEFAULT_COLLATION=utf8_general_ci -Dwith_ZLIB=system - DENABLE_DTRACE=0

mysql 환경 설정파일 및 mysql 초기화

mysql 설치작업이 끝나면 데몬을 구동하기 위한 초기화 작업이 필요하다. 다음과 같이 작업이 완료되면 최초 설치 작업은 마무리 된다.

• mysql 환경설정 기본 파일 복사

1

$ cp ./support-files/my-default.cnf /etc/my.cnf

mysql 초기화

1
2

$ cd /usr/local/mysql
$ /usr/local/mysql/scripts/mysql_install_db –user=mysql

mysql 서비스 스크립트 및 서비스 설정

Mysql 설정이 완료되면 부가적으로 서비스의 스크립트와, 부팅 시 자동으로 서비스가 올라오도록 아래와 같은 기본 설정을 추가한다.

• mysql 서비스 스크립트 및 서비스 runlevel 등재

1
2

$ cd /usr/local/mysql
$ cp -a support-files/mysql.server /etc/init.d/mysqld # ln -s /etc/init.d/mysqld /etc/rc3.d/S90mysqld

Start

mysql-server 를 설치하며 만든 root 사용자 패스워드를 사용해서 데이터베이스에 접속한다.

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

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

사용자가 사용할 데이터베이스를 만든다.

1
2

mysql>create database mydb;
Query OK, 1 row affected (0.00 sec)

계속해서 새로운 사용자를 만든다.

1
2
3
4

# 현재 머신에서만 접속할 수 있는 사용자 계정
mysql> GRANT ALL privileges ON *.* TO ID@localhost IDENTIFIED BY '*****';
# 외부, 원격에서 접속할 수 있는 사용자 계정
mysql> GRANT ALL privileges ON *.* TO ID@'%' IDENTIFIED BY '*****';

localhost 머신의 MySQL 데이터베이스에 myid라는 이름의 아이디를 만들고, 패스워드는 password로 설정

사용자 데이터베이스 사용

새로 생성한 사용자 ID로 로그인을 해서 데이터베이스를 만든다.

1
2
3

root@a5d2a69fa410:/# mysql -u qkboo -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \\g.

MySQL 설정

MySQL에서 설정파일을 읽는 순서는 다음과 같다.

/etc/my.cnf
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnf

/etc/my.cnf

utf-u 문자셋을 기본으로 설정하기 위해서 my.cnf 파일을 다음 같이 사용한다.

MySQL 설정 파일에서 문자셋을 변경할 수 있다. 다믕 같이 자신의 my.cnf 파일을 작성한다. client, mysqld, mysql 에 대해서 utf8 사용을 선언해 준다.

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

[client]
..
default-character-set=utf8

[mysqld]
character-set-client-handshake=FALSE
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

참조

• https://pythonschool.net/category/databases.html