꿈꾸는 개발자, DBA 커뮤니티 구루비

3.1 시작과 종료

3.1.1 서비스로 등록된 경우

RPM 또는 MSI 인스톨러로 설치한 경우 자동적으로 서비스에 등록된다.


# CentOS 6 이하 버전
shell > service mysql start
shell > service mysql stop

# CentOS 7 이상버전
shell > systemctl start mariadb
shell > systemctl stop mariadb

3.1.2 서비스로 등록되지 않은 경우


shell > sh ${MariaDB설치위치}/support-files/mysql.server start
shell > sh ${MariaDB설치위치}/support-files/mysql.server stop

# 또는
shell > ${MariaDB설치위치}/bin/mysqld_safe --defaults-file=/etc/my.cnf &
shell > mysaladmin -u root -p shutdown
shell > mysaladmin -h 192.168.0.1 -P 3307 -u root -p shutdown

3.2 서버 로그인


# 로컬 로그인
[root@localhost mysql]# mysql -u root -p

# 원격지 로그인 시
[root@localhost mysql]# mysql -h 127.0.0.1 -P 3306 -u root -p


# 연결 정보 조회
MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.1.22-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		3
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.1.22-MariaDB MariaDB Server
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			3 min 39 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.027
--------------


# 안전모드로 mysql 클라이언트 실행
[root@localhost mysql]# mysql -u root -p --safe-updates;
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.22-MariaDB MariaDB Server

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

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

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table test_table(uid int);      <---- 가능
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into test_table values (1);     <---- 가능
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from test_table;               <---- 가능
+------+
| uid  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MariaDB [test]> delete from test_table;      <--- 불가능
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
MariaDB [test]> update test_table set uid = 2;   <---- 불가능
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column


# 쿼리문 실행 시 --execute 옵션 사용
[root@localhost mysql]# mysql -u root -p -D test --execute="select * from test_table"
Enter password:
+------+
| uid  |
+------+
|    1 |
+------+

3.3 데이터베이스 및 사용자 생성

3.3.1 사용자의 식별

다른 DBMS와는 다르게 클라이언트가 실행된 호스트명이나 도메인, IP주소도 계정의 일부가 된다.
예컨데 root 계정으로 접속 시 접속IP에 따라서 패스워드가 맞아도 로그인이 안될 수도 있다는 것이다.

예:

'svc_id'@'127.0.0.1' 으로 계정이 하나만 등록되어 있다면 svc_id는 로컬에서만 접속가능하다.
'svc_id'@'%' 으로 계정이 등록되어 있다면 svc_id는 모든IP에서 접속가능하다.
주의할 것은 위 2개의 계정이 모두 등록되어 있다면 접속 범위가 작은 것인 127.0.0.1이 적용되어 로컬에서만 접속가능하다.

3.3.2 권한

권한범위	권한	설명
전역	CREATE USER	사용자 생성
전역	FILE	LOAD DATA INFILE 같은 디스크의 파일 접근 시 필요한 권한
전역	PROCESS	MariaDB내의 프로세스 조회 권한
전역	RELOAD	FLUSH를 실행할 수 있는 권한
전역	SHUTDOWN	서버를 종료할 수 있는 권한
전역	SUPER	특정한 상황에서 제한을 넘어서 뭔가를 할 수 있는 권한 예) read_only 설정 시 데이터 변경 가능
데이터베이스 레벨	CREATE	새로운 데이터 베이스 생성
데이터베이스 레벨	DROP	데이터베이스 삭제
데이터베이스 레벨	GRANT OPTION	데이터베이스의 권한을 다른 사용자에게 부여할 수 있는 권한
데이터베이스 레벨	EVENT	이벤트 생성 및 삭제
데이터베이스 레벨	LOCK TABLE	명시적으로 테이블을 잠그는 권한
테이블 레벨	ALTER	테이블 구조 변경
테이블 레벨	CREATE	테이블 생성
테이블 레벨	DELETE	레코드 삭제
테이블 레벨	DROP	테이블 삭제
테이블 레벨	GRANT OPTION	테이블에 대한 권한을 다른 사용자에게 부여할 수 있는 권한
테이블 레벨	INSERT	레코드 입력
테이블 레벨	SELECT	레코드 조회
테이블 레벨	UPDATE	레코드 수정
STORED PROGRAM	ALTER ROUTIN	프로시저/함수 등 변경
STORED PROGRAM	EXECUTE	프로시저/함수 등 실행
STORED PROGRAM	GRANT OPTION	프로시저/함수 등의 권한을 다른 사용자에게 부여할 수 있는 권한

3.3.1.2 권한의 부여


# 권한만 부여
MariaDB [(none)]> grant 권한목록 on db.table to 'user'@'host';

# 계정을 생성하고 권한까지 부여
MariaDB [(none)]> grant 권한목록 on db.table to 'user'@'host' identified by 'password' with grant option;

# 글로벌 권한 부여
MariaDB [(none)]> grant super on *.* to 'jigi'@'%';

# 데이터베이스 권한 부여
MariaDB [(none)]> grant event on *.* to 'jigi'@'%';
MariaDB [(none)]> grant event on test.* to 'jigi'@'%';

# 테이블 권한 부여
MariaDB [(none)]> grant select,insert,update,delete on *.* to 'jigi'@'%';
MariaDB [(none)]> grant select,insert,update,delete on test.* to 'jigi'@'%';
MariaDB [(none)]> grant select,insert,update,delete on test.test_table to 'jigi'@'%';
MariaDB [(none)]> grant select,insert,update(uid) on test.test_table to 'jigi'@'%';   <-- update는 uid 컬럼만 가능하다.

3.3.1.3 권한 그룹


# 권한 그룹(role)생성
MariaDB [(none)]> create role dba;   <-- 해당 롤을 만든 사용자만 롤 권한 부여가능
MariaDB [(none)]> create role developer with admin jigi;  <-- 해당 롤을 만든 사용자와 jigi 계정만 롤 권한 부여가능

MariaDB [(none)]> grant all privileges on *.* to dba;    <-- 모든 권한을 dba 롤에 부여
MariaDB [(none)]> grant select,insert,update,delete on test.* to developer;   <-- test 데이터베이스의 select, DML 권한만 developer 롤에 부여


# 롤 부여 및 제거
MariaDB [(none)]> set role dba;       <-- 롤 부여
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select current_role;
+--------------+
| current_role |
+--------------+
| dba          |
+--------------+
1 row in set (0.00 sec)

MariaDB [(none)]> set role developer;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select current_role;       <--- 롤은 한 계정에 한 개만 적용된다. (2개 이상 불가능)
+--------------+
| current_role |
+--------------+
| developer    |
+--------------+
1 row in set (0.00 sec)

ariaDB [(none)]> set role none;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select current_role;       <--- 롤 제거
+--------------+
| current_role |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

3.3.2 기본 사용자


MariaDB [mysql]> select user, host from user;
+-----------+-----------------------+
| user      | host                  |
+-----------+-----------------------+
| dba       |                       |
| developer |                       |
| jigi      | %                     |
| root      | 127.0.0.1             |
| root      | ::1                   |
| root      | localhost             |
| root      | localhost.localdomain |
+-----------+-----------------------+
7 rows in set (0.00 sec)

3.3.3 기본 데이터 베이스


MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |    <-- 메타 데이터가 저장되는 데이터베이스
| mysql              |    <-- 사용자 인증정보, stored program, 이벤트 정보 등이 저장되는 기본 데이터베이스
| performance_schema |    <-- 각종 이벤트, 잠금, 잠금 대기 등의 정보를 기록하는 테이블 구조 저장, 매 기동될때 마다 초기화되며, 데이터는 메모리에 저장
| test               |    <-- 테스트용 데이터베이스(삭제하는 것이 보안상 좋음)
+--------------------+
4 rows in set (0.00 sec)

3.3.4 새로운 데이터베이스 생성 및 삭제


MariaDB [mysql]> create database firstdb;
MariaDB [mysql]> create database firstdb default character set utf8mb4;

MariaDB [mysql]> drop database firstdb;

3.3.5 사용자 생성


MariaDB [mysql]> grant show database on *.* to 'abc'@'%' identified by 'test_password';
MariaDB [mysql]> grant select, insert, update, delete on firstdb.* to 'abc'@'%';
MariaDB [mysql]> flush privileges;
* 참고사항 : 사용자 계정과 데이터베이스는 전혀 무관하게 이원화되어 관리되므로, 데이터 베이스는 따로 만들어야 된다.

3.3.6 관리자 계정준비


# root 계정은 너무 많이 알려졌으므로, 로컬만 접속가능한 별도의 관리자계정을 만드는 것을 추천한다.
MariaDB [mysql]> grant all on *.* to 'superman'@'localhost' identified by 'adminpass' with grant option;
MariaDB [mysql]> grant all on *.* to 'superman'@'127.0.0.1' identified by 'adminpass' with grant option;
MariaDB [mysql]> flush privileges;

3.4 테이블 생성 및 변경

3.4.1 테이블 생성


MariaDB [test]> create table if not exists abc (col1 int, col2 varchar(100)) engine=innodb;

MariaDB [test]> show create table abc;
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| abc   | CREATE TABLE `abc` (
  `col1` int(11) DEFAULT NULL,
  `col2` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


MariaDB [test]> desc abc;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | int(11)      | YES  |     | NULL    |       |
| col2  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3.4.2 테이블 변경

3.4.2.1 오프라인 스키마 변경

MariaDB 10.0버전 이상, MySQL 5.6 이상 버전에서 온라인 스키마 변경 기능이 도입되었다.
온라인 스키마 변경 전에는 임시테이블로 레코드를 복사하고, Rename하는 형태로 작업을 진행하였다.

3.4.2.2 MariaDB의 온라인 스키마 변경

온라인 처리가 가능한 DDL은 "컬럼의 추가와 삭제, 인덱스의 생성 및 삭제, 컬럼의 순서변경" 3가지 뿐이다.


MariaDB [test]> create table t1(fd1 int, fd2 enum('red', 'gree'));
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> alter online table t1 modify fd2 enum('red','green','blue');
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.

MariaDB [test]> alter online table t1 modify fd2 enum('red','gree','blue');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> alter online table t1 add fd3 int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.4.2.3 MySQL의 온라인 스키마 변경

LOCK와 ALGORITHM 절을 이용하여 온라인 스키마 변경의 처리방법을 제어한다.

LOCK


# LOCK = {DEFAULT | NONE | SHARED | EXCLUSIVE }
* NONE : 온라인 DDL이 실행되는 동안 테이블에 읽기/쓰기가 가능
* SHARED : 읽기만 가능
* DEFAULT : LOCK를 명시하지 않은 것과 같음

ALGORITHM


# ALGORITHM = { DEFAULT | INPLACE | COPY }
* COPY : MySQL 5.5이하 버전에서 사용되던 방법으로 데이터를 임시 테이블로 모두 복사후, rename하는 방식
* INPLACE : MySQL 5.6이상 버전에서 지원되는 방법으로, 데이터를 바로 변경하되, 변경 작업시 일어나는 DML작업들은 별도의 로그로 보관했다가 데이터 변경 마지막에 일괄 적용하는 방법
* DEFAULT : ALGORITHM를 명시하지 않은 것과 같음


# inplace 방식은 온라인 변경 로그의 공간을 충분히 할당해야 메모리 공간 부족으로 작업이 실패하지 않는다.
MariaDB [test]> alter table t1 add fd4 varchar(20), lock=none, algorithm=inplace;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# auto-increment 컬럼은 LOCK=SHARED 모드로 지정해야 된다.
MariaDB [test]> alter table t1 add fd5 int auto_increment primary key, lock=none, algorithm=inplace;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED.

온라인 스키마 변경 작업이 실패할 수 있는 경우

alter table 명령이 장시간 실행되고, 다른 트랜젝션에서 DML이 많이 실행되어 온라인 변경 로그의 공간이 부족한 경우
alter table 이후 테이블 구조에 적합하지 않는 레코드가 insert되거나 update되는 경우
스키마 변경을 위해서 필요한 잠금 수준보다 낮은 잠금 옵션이 사용된 경우
LOCK=NONE로 실행되더라도, 변경 작업의 처음과 마지막 과정에서는 잠금이 필요한 데, 이 잠금을 획득하지 못하고 타임아웃이 발생하게 되는 경우
온라인으로 인덱스 생성 시, 정렬을 위해 임시 디렉터리를 사용하는데, 이 공간이 부족하는 경우

온라인 스키마 변경 작업별 처리 방식은 "교재 96페이지의 표 3-1"을 참조하자.

3.4.2.4 pt-online-schema-change

Percona 툴킷(https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html)에 포함된 온라인 스키마 변경을 위한 3rd party 도구이다.
MariaDB와 MySQL 모두 사용가능하다.
스키마를 변경하려는 테이블과 동일한 구조의 새로운 테이블을 만든 후에 기존 테이블의 데이터를 새로운 테이블로 조금씩 복사해서 스키마 변경을 수행하는 도구이다.

수행과정

1. 기존 테이블에 insert/delete/update AFTER 트리거를 생성하여, 데이터 변경 시 새로운 테이블로 전달 할 수 있도록 한다.
2. 모든 레코드를 복사하고 나면, RENAME 명령어를 통해 기존 테이블과 새 테이블의 이름을 변경한다.
3. RENAME 명령어는 한 트랜잭션 내에서 처리할 수 있기 때문에, RENAME 수행도중 다른 트랜젝션 쿼리는 실패하지 않는다.
4. 주의사항 : 기존 테이블에 이미 AFTER 트리거가 있는 경우 사용할 수 없고, PK와 유니크 인덱스가 있는 경우 데드락 상태가 빈번하게 발생할 수 있다.

3.4.2.5 온라인 스키마 변경을 사용해도 될까?

온라인 스키마 변경 작업은 많은 시스템자원(CPU 등)을 사용하여 서비스에 악영향을 미칠 수 있으므로, 저자는 기존 3rd party 인pt-online-schema-change를 사용하는 것을 권하고 있다.

3.4.3 테이블 삭제


MariaDB [test]> drop table t1;
MariaDB [test]> drop table if exists t1;
* 주의사항 : ext3 파일 시스템에서 실제 물리적인 데이터 파일삭제 시 많은 시간을 소모하므로, 테이블 크기가 큰 경우 새벽시간에 작업하는 것이 좋다.

3.5 데이터 조작

3.5.1 INSERT


# 사용할 데이터베이스 변경
MariaDB [test]> use mysql;
MariaDB [mysql]>

# 테이블 생성
MariaDB [test]> create table tab_test(fd1 int, fd2 varchar(50), primary key(fd1)) engine = innodb;
MariaDB [test]> insert into tab_test values (1, 'matt');
MariaDB [test]> insert into tab_test values (2, 'toto');

# 데이터 입력 및 조회
MariaDB [test]> insert into tab_test values (3, 'Lee') on duplicate key update fd2='Lee';
MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   1 | matt |
|   2 | toto |
|   3 | Lee  |
+-----+------+
3 rows in set (0.00 sec)

# 동일 데이터가 이미 있을 경우 fd2를 Seonguck으로 업데이트 한다.
MariaDB [test]> insert into tab_test values (3, 'Seonguck') on duplicate key update fd2='Seonguck';
Query OK, 2 rows affected (0.00 sec)

MariaDB [test]> select * from tab_test;
+-----+----------+
| fd1 | fd2      |
+-----+----------+
|   1 | matt     |
|   2 | toto     |
|   3 | Seonguck |
+-----+----------+
3 rows in set (0.00 sec)

3.5.2 SELECT


MariaDB [test]> select * from tab_test;
+-----+----------+
| fd1 | fd2      |
+-----+----------+
|   1 | matt     |
|   2 | toto     |
|   3 | Seonguck |
+-----+----------+
3 rows in set (0.00 sec)

# 세로 형태로 보여준다.
MariaDB [test]> select * from tab_test\G
*************************** 1. row ***************************
fd1: 1
fd2: matt
*************************** 2. row ***************************
fd1: 2
fd2: toto
*************************** 3. row ***************************
fd1: 3
fd2: Seonguck
3 rows in set (0.00 sec)

3.5.3 UPDATE


MariaDB [test]> update tab_test set fd2='123' where fd1 = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   1 | matt |
|   2 | toto |
|   3 | 123  |
+-----+------+
3 rows in set (0.00 sec)

3.5.4 REPLACE

동일한 데이터가 있으면 update 처리 되고, 없으면 insert 처리된다.
Insert into ... on duplicate key update와 유사한 기능이나, REPLACE 명령어는 동일한 값이 있는 경우 기존 레코드를 삭제 후 INSERT하는 방식이다.
replace는 부하가 높으므로 가급적 Insert into ... on duplicate key update를 사용하자


MariaDB [test]> replace tab_test set fd1=1, fd2='aaa';
Query OK, 2 rows affected (0.00 sec)

MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   1 | aaa  |
|   2 | toto |
|   3 | 123  |
+-----+------+
3 rows in set (0.00 sec)

MariaDB [test]> replace tab_test set fd1=4, fd2='bbbb';
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   1 | aaa  |
|   2 | toto |
|   3 | 123  |
|   4 | bbbb |
+-----+------+
4 rows in set (0.00 sec)

# replace절에서는 where절은 사용하지 못한다.
MariaDB [test]> replace tab_test set fd1=4, fd2='bbbb' where fd1=4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where fd1=4' at line 1

3.5.5 DELETE


MariaDB [test]> delete from tab_test where fd1=1;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from tab_test;
+-----+------+
| fd1 | fd2  |
+-----+------+
|   2 | toto |
|   3 | 123  |
|   4 | bbbb |
+-----+------+
3 rows in set (0.00 sec)

03. MariaDB 기동 및 쿼리 실행 (by jiginet) [2017.03.31]