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

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

$ mysql -u root -p

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

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

Docker 기반 mongodb

Official : https://hub.docker.com/_/mongo/
참조: https://github.com/dockerfile/mongodb

로컬에 “mongo_data” 라는 데이터 저장소가 있고 29817 포트로 실행하기를 한다면 다음 같다:

1

$ docker run -d -p 29817:27017 -v /home/its/mongo_data:/data/db --name mongodb mongo

그리고 docker로 시작한 mongodb 컨테이너에 있는 mongo 를 사용해서 데이터베이스에 접속할 수 있다.

1

$ docker exec -it mongodb mongo

인증 이용

1

$ docker run -d -p 29817:27017 -v /home/its/mongo_data:/data/db --name mongodb mongo --auth

admin 데이터에이스에서 사용자 관리 계정 등록

1
2

$ docker exec -it mongodb mongo admin
> db.createUser({ user: 'jsmith', pwd: 'some-initial-password', roles: [ { role: "userAdminAnyDatabase", db: "admin" } ] });

MongoDB에 관련 글

MongoDB Community Edition Installations 시리즈:ㅇ

1. MongoDB Community Edition 4.2 설치 - ARM64 Ubuntu/Debian Armbian
2. MongoDB 구성 설정 과 사용자 인증 사용

MongoDB Tutorials 시리즈:

1. MongoDB Tutorials: collections
2. MongoDB Tutorial

MongoDB와 Middleware Platform:

1. NodeJS Mongoose
2. Mongodb mongoose-middleware

이전 버전과 기타 자료:

1. MongoDB User Authentication
2. MongoDB Community Edition 3.6 on Ubuntu(ARM64)
3. Mongodb 3.4 install on Armv8
4. Raspberry Pi 3 64bit OS openSUSE: Build MongoDB 3.4

Mongo Database 설정과 사용자 인증 사용

이 문서는 MongoDB Community Edition 를 리눅스 플랫폼에서 시작과 운영에 필요한 구성 파일 mongod.conf 의 설정을 다루고 있다. 이를 통해 데이터베이스 서비스를 동작시키고, 인증한 사용자로서 클라이언트 프로그램으로 접속하기 위한 기본 인증방식을 구성하는 것을 정리하고 있다.

1. Mongo Database 설정
2. 기본인증 구성

이 글의 내용은 MongoDB Community Edition 3.6 이후 4.2 까지 사용이 가능하다.

MongoDB에 관련 글

MongoDB Community Edition Installations 시리즈:ㅇ

1. MongoDB Community Edition 4.2 설치 - ARM64 Ubuntu/Debian Armbian
2. MongoDB 구성 설정 과 사용자 인증 사용

MongoDB Tutorials 시리즈:

1. MongoDB Tutorials: collections
2. MongoDB Tutorial

MongoDB와 Middleware Platform:

1. NodeJS Mongoose
2. Mongodb mongoose-middleware

이전 버전과 기타 자료:

1. MongoDB User Authentication
2. MongoDB Community Edition 3.6 on Ubuntu(ARM64)
3. Mongodb 3.4 install on Armv8
4. Raspberry Pi 3 64bit OS openSUSE: Build MongoDB 3.4

Install MongoDB 4.2 Community edition 설치

이 문서는 Arm64 기반 CPU를 사용하는 Raspberry Pi, Odroid, PINE64, OrangePi 등 SBC 지원 보드 계열 위에서 Ubuntu/Debian 에서 설치 및 사용 가능한 MongoDB Community Edition 4.2 버전을 설치하고 구성하는 과정을 정리하고 있다.

MongoDB Community Edition 버전 Amd64, Arm64 의 설치는 Install MongoDB Community Edition on Debian 에서 제공하고 있다.

하지만 Arm64를 지원하는 Odroid C2, PINE64, OragePi 등 SBC 브드의 Armbian, Ubuntu 오에스에서 MongoDB Community Edition 을 저장소에서 apt 명령으로 직접 설치가 안되서 작은 트릭 이 필요하다.

• MongoDB Community Edition 4.2 설치
• systemd 사용이 가능한 mongod Unit 파일 구성

Armbian 을 사용하는 보드에서 mongodb 4.2 를 설치한다. 여기에 대한 자세한 내용은 아래 링크를 참조하고 있다.

https://docs.mongodb.com/manual/tutorial/install-mongodb-on-ubuntu/#install-mongodb-community-edition

GPG error

1

W: GPG error: https://repo.mongodb.org/apt/ubuntu bionic/mongodb-org/4.2 Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 4B7C549A058F8B6B

1
2
3
4
5

$ sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 3B4FE6ACC0B21F32
7C549A058F8B6B
gpg: key 4B7C549A058F8B6B: public key "MongoDB 4.2 Release Signing Key <packaging@mongodb.com>" imported
gpg: Total number processed: 1
gpg:               imported: 1

1

$ sudo apt install -y mongodb-org

설정

mongod.conf 를 간단하게 다음

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

storage:
  #dbPath: /var/lib/mongodb
  dbPath: /home/qkboo/Db-data/mongodb4
  journal:
    enabled: true

systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

net:
  port: 27017
  bindIp: 127.0.0.1

https://docs.mongodb.com/manual/core/security-mongodb-configuration/

인증

명령으로 mongod 로 실행이 가능한 MongoDB Server는 기본적으로 보안 모델이 없이 실행된다. 따라서 별도의 인증 절차를 가지고 있지 않습니다. 이 때문에 2017년 1월에는 이런 약점을 노린 랜섬웨어가 발생하기도 했습니다. MongoDB는 되도록이면 외부로부터의 신뢰되지 않은 접속을 허용하지 않는, 보안에 문제가 되지 않는 환경에서 보안 모델 없이 실행하는 것을 지향하고 있으나, 어쨌든 MongoDB도 ID와 비밀번호로 접근하는 기본적인 보안 모델을 가지고 있으니 이를 통해 MongoDB Server에 인증 과정을 추가해 보도록 합시다.

1. 비인증 모드에서 관리자 계정 생성
2. 인증 접속

1. 비인증 관리자 계정 생성하기

mongod로 별도의 보안 모델이 없는 MongoDB Server를 실행하고, MongoDB Shell에 접속합니다.

1
2
3
4

> mongo
MongoDB shell version v3.6.3
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.6.3

MongoDB에는 admin이라는 데이터베이스가 존재하며, 해당 데이터베이스에 사용자를 추가합니다.

1
2
3
4
5
6

> use admin
> db.createUser({
    user: 'username',
    pwd: 'password',
    roles: ['userAdminAnyDatabase']
})

db.createUser()는 현재 사용하고 있는 데이터베이스(여기서는 admin)에 사용자를 추가합니다. 여기에는 사용자 이름, 비밀번호와 함께 권한(roles)을 array로 정의합니다. MongoDB에서 빌트인으로 제공하고 있는 권한은 MongoDB Built-In Roles에서 확인할 수 있습니다.

위에서 정의한 userAdminAnyDatabase는 어느 데이터베이스든 사용자를 생성하고 제거할 수 있다는 것을 의미합니다. 모든 권한을 가지게 하려면 root를 사용하면 되고, MongoDB 2.4 이하의 경우 db.addUser()를 통해 사용자를 추가합니다.

mongod 인증하기

만약 mongod 명령으로 실행하면 MongoDB Server를 실행하도록 한다.

1

> mongod --auth

MongoDB Server 재시작하기

Ubuntu와 같은 리눅스 시스템에서 service mongod start처럼 service를 이용할 경우, config 파일을 통해 실행됩니다. 이 경우 /etc/mongod.conf를 다음처럼 수정하고 재시작(service mongod restart)하면 됩니다.

mongodb 4.x

인증을 활성화 하려면

1
2

security:
    authorization: enabled

mongodb 3.x

mongod.conf 를 사용하면 다음 옵션을 true 로 구성한다.

1

auth = true

그리고 서비스를 재시작한다.

1
2

$ sudo systemctl restart mongod.service

2. 인증 접속

mongo 쉘에 접속하여 db.auth()를 사용하는 것입니다.

1
2
3
4
5

$ mongo
MongoDB shell version v4.2.1
connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("177716fe-5b3c-41f6-bdae-d2584819abff") }
MongoDB server version: 4.2.1

쉘에서 db.auth() 를 사용해 인증에 성공하면 1이 나오면 성공이다.

1
2
3
4
5

> use admin
switched to db admin
> db.auth('admin', '!23456789');
1
>

인증 접속이 되면 로그에 아래 같은 로그를 확인할 수 있다.

1

2019-12-02T09:49:24.392+0000 I  ACCESS   [conn3] Successfully authenticated as principal admin on admin from client 127.0.0.1:52370

명령행에서 접근

인증과 함께 admin 데이터베이스로 접근하려면 아래 같이 명령어에 아이디와 비밀번호를 명시만 해 주면 된다.

1

> mongo admin -u username -p password --authenticationDatabase admin

아래 같이 mongo 명령행에서 인증을 통해 접속하면 test 라는 데이터베이스로 접속합니다.

1
2
3
4
5
6

$ mongo -u admin -p
MongoDB shell version v4.2.1
Enter password:
...
>
> use admin

사용자 계정 만들기

관리자 계정을 만들고 인증까지 수행 했으면, 일반 사용자를 만들어 데이터베이스를 사용하도록 해보자. 관리자 계정을 만들었던 것처럼 계정을 만들고자 하는 데이터베이스를 use 한 다음 db.createUser()를 실행하면 된다.

1
2
3
4
5
6
7
8
9

> use bookdiary
switched to db bookdiary
> db.createUser({
... user:'student',
... pwd:'0123456789',
... roles:['dbOwner']
... });
Successfully added user: { "user" : "student", "roles" : [ "dbOwner" ] }
>

dbOwner라는 권한은 해당 데이터베이스에 대한 모든 수정/삭제 권한을 가진다는 것을 의미합니다.

사용자 인증

db.auth()를 통해 인증을 진행해 봅시다.

1
2
3

> db.auth('username', 'password')
1
>

참고

• MongoDB Community Edition 3.6 on Ubuntu
• MongoDB: Enable authentication

2018-06-22 내용 정리, User auth 링크
{:.right-history}

이 문서는 Ubuntu/Debian 계열 정규 배포본에 3.x 버전을 지원하지 않는 플랫폼에 MongoDB Community Edition 3.6 버전을 설치하는 과정을 정리하고 있다. 내용의 기초는 MongoDB Community Edition 3.6 버전을 Amd64, Arm64 지원하는 64bit OS에 설치해 사용하기 위해서 Install MongoDB Community Edition, on-ubuntu를 따라서 진행하고 경험상 필요한 부분을 추가로 정리했다.

• MongoDB Community Edition 3.6 설치
• Mongo Database 구성

Collection

mongoDB는
mongoDB는 Collection 이 데이터베이스 테이블과 같은 개념이다. 아래 테이블은 관계형 데이터베이스 MySQL과 MongoDB의 개념을 비교해 주고 있다. [^1]

2018-06-21 설치 링크로 대체
{:.right-history}

MongoDB 설치후 데이터베이스 위치, 로그, 인증 등에 관련한 서버 구성과 설정을 정리한다.

MongoDB 2.6 과 MongoDB Community Edition 3.x 버전을 사용했다.

mongoDB 접근제어

mongoDB 는 설치과정 중에 인증과 관련해 설정하는 부분이 없어서 설치 후 누구나 DB에 접속 할 수 있다. 인증을 추가해 데이터베이스 관리자와 데이터베이스 사용자로 구분해서 이용하고, 각 데이터베이스의 사용자는 허가된 역할(Role)을 가지고 데이터베이스에 접근 가능하도록 구성한다.

2018-06-22 내용 정리, User auth 링크
{:.right-history}

이 문서는 MongoDB Community Edition 3.4 버전을 64bit OS인 Amd64, Arm64 지원 OS에 설치해 사용하기 위해서 Install MongoDB Community Edition, on-ubuntu를 따라서 진행했다.

여기서는 Arm을 사용하는 SBC[^2] 컴퓨터에 mongodb 3.4 버전을, Hardkernel Odroid C2를 사용해서 설치를 진행했다.