mysqladmin CLI 주요 사용

mysqladmin, mysqldump 명령 사용

mysqladmin 명령 사용하기

  • MYSQL의 root 패스워드 변경하기
  • MYSQL의 일반계정 사용자 패스워드 변경하기
  • 새로운 데이터베이스 생성하기
  • 사용중인 데이터베이스 삭제하기
  • MYSQL의 현재상황 살펴보기
  • MYSQL에 접속한 클라이언트(threads)리스트 확인하기
  • MYSQL 캐쉬 데이터 동기화하기
  • MYSQL 종료하기
  • MYSQL 실행 환경변수 확인하기
  • MYSQL에 접속한 사용자 접속끊기
  • MYSQL의 버전 및 여러가지 실행 정보들 확인하기
  • MYSQL이 정상적으로 살아있는지 죽었는지 확인하기
  • 기타 MYSQL관리에 필요한 유용한 설정 및 정보확인

사용법

1
2
3
4
mysqladmin [-h서버] -u아이디 -p 데이터베이스명 <명령어>

<명령어>: reload, shutdown, create, status등

- MYSQL의 root 패스워드 변경하기

- MYSQL의 일반계정 사용자 패스워드 변경하기

- 새로운 데이터베이스 생성 / 사용중인 데이터베이스 삭제하기

새로운 스키마를 생성할 때 create 명령을 사용한다.

1
mysqladmin -u root -p create 새로운데이터베이스명

기존 스키마를 삭제 할 때 drop 명령을 사용한다.

1
mysqladmin -u root -p drop  데이터베이스명

- MYSQL의 현재상황 살펴보기

status 명령으로 MySQL의 상태를 확인할 수 있다.

  • MYSQL의 총 실행시간
  • 현재 처리중인 스레드(Threads)수
  • 오픈된 데이터베이스 및 테이블 수
  • 초당 평균 처리속도
  • Slow query, Flush tables등
1
mysqladmin -u root -p status

i 인자에 상태 확인 간격을 주어 계속 관찰이 가능하다.

1
2
# 5초 간격
mysqladmin -i5 status -u root -p

- MYSQL의 현재상황 자세히 살펴보기

extended-status라는 명령어는 SQL 명령의 SHOW STATUS 로 보는 시스템 상태와 거의 동일.

1
mysqladmin -u root -p extended-status

- MYSQL에 접속한 클라이언트(threads)리스트 확인하기

- MYSQL 캐쉬 데이터 동기화하기

reload, flush-privileges 를 사용해서 테이블 및 권한 테이블의 캐시를 갱신한다.

MYSQL의 모든 실제 데이터들은 각각의 데이터베이스 내에 존재하는 테이블(table)에 저장되므로 MYSQL의 테이블(table)을 reload 혹은 priviliges 테이블을 flush 해서 갱신한다.

1
2
mysqladmin -u root -p reload
mysqladmin -u root -p flush-privileges

- MYSQL 실행 환경변수 확인하기

mysql client 로 접속해 show variables; 명령을 mysqladmin 명령으로 사용이 가능하다.

1
mysqladmin -u root -p variables

- MYSQL 실행 프로세스 확인하기

mysql client 로 접속해 processlist; 로 mysqladmin 명령으로 사용이 가능하다.

1
mysqladmin -u root -p processlist

- MYSQL에 접속한 사용자 접속끊기

보통 processlist 로 실행중인 ID 를 끊을 수 있다.

1
2
3
4
+----+-----------+------------------+-----------+---------+------+----------+-----------------+----------+
| Id | User | Host | db | Command | Time | State | Info
+----+-----------+-----------------+-----------+---------+------+----------+------------------+----------+
| 31 | stockmart | 192.168.0.2:564 | fedb | Sleep | 18 |

processlist 의 Id컬럼에 있는 접속 Id를 끊는다.

1
mysqladmin -u root -p kill 사용자ID

- MYSQL의 버전 및 여러가지 실행 정보들 확인하기

  • MYSQL의 버전

사용형식 : mysqladmin -u root -p version

  1. MYSQL이 죽었는지 살았는지 확인하기

사용형식 : mysqladmin -u root -p ping

  • MYSQL 소켓파일 위치정보

  • MYSQL의 총 실행시간 정보

- 기타 MYSQL관리에 필요한 유용한 설정 및 정보확인

  • MYSQL의 총 스레드(threads)수

  • MYSQL의 오픈된 데이터베이스와 테이블 수

  • MYSQL의 초당 응답완료 시간

- MYSQL 종료하기



mysqldump 명령

예: 문자셋 latin1 데이터 덤프

문자셋을 mysql> show variables like 'c%' 등으로 확인해서 적절히 덤프를 한다.

1
mysql> mysqldump -u [username] –p[password] --default-character-set=utf8 -N --routines --skip-triggers --databases [database_name] > [dump_file.sql]

덤플 사례: https://itzone.tistory.com/711

MySqLI CLI 주요 명령

mysql CLI 에서 MySQL/MariaDB 관리자로서 사용할 수 있는 명령을 요약한다.

  • create user, grant, drop, delete, remove …

MySqLI CLI Client 로 접속하기

1
mysql [-h서버] -u아이디 -p 데이터베이스명

데이터베이스 스키마

1
mysql> SHOW DATABASES;

use 명령을 사용하여 사용할 데이터베이스/스키마를 선택

1
mysql> USE TESTDB;

선택된 데이터베이스 안의 테이블 확인

1
2
mysql> SHOW TABLES;
mysql> SHOW TABLES LIKE 't%'; # t로 시작하는 테이블

특정 스키마 혹은 테이블의 생성 쿼리를 출력

1
2
mysql> SHOW CREATE DATABSE SAKILA;
mysql> SHOW CREATE TABLE STUDENTS;

데이터베이스의 생성 및 삭제

1
mysql> CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_general_ci;
  • utf8_general_ci 는 대소문자를 구분하지 않는다.
  • 대소문자를 구분하려면 binary 타입으로 지정. 예) “utf8_bin”

캐릭터셋과 COLLATE 를 생략하면 서버 설치시 지정한 기본 값으로 설정

1
mysql> CREATE DATABASE testdb;

스키마 제거

1
mysql> DROP [SCHEMA]DATABASE testdb;

시스템 환경 확인

서버의 환경 변수

1
2
# 모든 변수
mysql> SHOW VARIABLES;

DBMS 버전

1
2
3
mysql> select @@version;       -- DBMS version

mysql> SHOW VARIABLES LIKE 'version';
1
mysql> SHOW VARIABLES LIKE 'innodb_%';

지원하는 문자세트 구성

1
2
3
mysql> SHOW CHARACTER SET;

mysql> SHOW CHARACTER SET LIKE 'utf%';

문자세트 확인

1
2
-- 환경변수에서 확인
mysql> SHOW VARIABLES LIKE 'c%';

지원하는 콜레이션

1
2
3
mysql> SHOW COLLATION;

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';

외부 파일 실행하기

첫 번째는 mysql cli 에 지정하여 실행하는 방법

1
2
3
$ mysql -u dbuser -p testdb < insert.sql

Enter password: ****

두 번째 방법을 mysql cli에서 source 명령을 사용

1
mysql> SOURCE C:\Users\USERID\insert.sql;


User & Privileges

사용자 db

mysql 스키마의 user 테이블을 사용한다.

SELECT Host,User,plugin,authentication_string FROM mysql.user;

사용자 추가

사용자를 생성시 호스트 주소에 ‘%’, ‘localhost’로 호스트 범위를 지정한다

1
2
3
mysql> CREATE USER 'USERID'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE USER 'USERID'@'%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

grant 명령: [권한 부여]

사용자가 특정 자원에 접근하기 위해서 grant 명령 사용.

1
2
3
4
5
6
GRANT ALL PRIVILEGES ON DB이름.테이블이름 TO 아이디@호스트 IDENTIFIED BY '비밀번호' with grant option;

- ALL PRIVILEGES : 모든 권한 추가
- SELECT, INSERT, UPDATE, DELETE, ... : 권한을 일부분을 추가
- with grant option : GRANT를 사용할 수 있는 권한 추가
- Grant 로 주어지는 권한은 여기 [grant: privilege-levels](https://mariadb.com/kb/en/grant/#privilege-levels) 명령에서 찾을 수 있다.

아래는 userid 사용자가 특정 sampledb 에만 모든 권한을 부여하고 있다.

1
2
mysql> GRANT ALL PRIVILEGES ON sampledb.* TO 'USERID'@'localhost';
mysql> FLUSH PRIVILEGES;

다음 GRANT 명령에서 ALL PRIVILEGES ON *.* 는 모든 권한(ALL PRIVILEGES)을 모든 스카마의 모든 테이블 *.* 에 준다는 의미.

1
2
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERID'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
  • WITH GRANT OPTION 의 의미는 다른 사용자에게 자신이 가진 권한을 주거나 회수할 수 있다는 의미.
  • ALL PRIVILEGES 부분에는 SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER 등 권한 명칭을 콤마로 분리해서 나열하는 방식으로 특정 권한만을 줄 수도 있다.

[권한 제거]

권한을 없앨때는 REVOKE 명령을 사용한다. 두가지 형식을 사용한다.

1
2
3
REVOKE priv_type ON db.tables FROM user[,user]  ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

아래 명령으로 ‘localhost’ 에서 접속가능한 ‘testdbuser’ 에게서 모든 권한을 제외한다.

1
mysql> REVOKE ALL PRIVILEGES *.* FROM 'USERID'@'localhost';

GRANT 명령과는 달리 REVOKE 명령은 모든 권한을 제거해도 mysql.user 테이블 사용자 정보는 완전히 삭제되지 않는다.

사용자 정보의 완전한 제거를 원한다면 DROP USER 명령을 사용한다.

1
mysql> DROP USER 'USERID'@'localhost';

권한 조회

사용자별 권한 확인

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

접속된 계정 권한 확인

1
mysql> SHOW GRANTS FOR CURRENT_USER;


실행 프로세스 확인

현재 접속자를 확인하고, lock 이 걸린 프로세스를 죽이거나 하는 작업

  • 프로세스 리스트보기
1
2
3
4
5
6
7
8
mysql> SHOW PROCESSLIST;
+------+-----------+----------------------+-----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info
| Progress |
+------+-----------+----------------------+-----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
| 1371 | dbkmart | 205.22.168.143:29762 | yourdb | Sleep | 2673 | | NULL
| 0.000 |
|
  • 프로세스 죽이기(프로세스 아이디는 리스트에 나오는 Id.)
1
mysql> kill 프로세스아이디

ex: DB 생성해 사용자 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. mysql/mariadb 데이터베이스 생성
create database webdb;

-- 2. mysql/mariadb 계정 생성
create user 'webdb'@'192.168.1.%' identified by 'webdb';

-- 3. 권한주기
grant all privileges on webdb.* to 'webdb'@'192.168.1.%';

-- 4. flush privileges;

-- 5. 사용자 계정 삭제
drop user 'webdb'@'192.168.1.%';

-- 6. 데이터베이스 삭제
drop database webdb;

  1. grant: privilege-levels : https://mariadb.com/kb/en/grant/#privilege-levels
> 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()

@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/MariaDB Server SSH Over Tunneling

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

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

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 암호화 연결(2)

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

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

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

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

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 를 기본 데이터 디렉토리로 지정하고 있다.

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

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

1
~$ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
  1. 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 ..
  1. data 이동

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

1
$ sudo rsync -avzph /var/lib/mysql/ /data/
  1. 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 5.x 시작 (3)

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;

참조

MySQL 5.x 소스 빌드 (2)

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

참조