IT Tech blogging

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

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

MySqLI CLI Client 로 접속하기

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

데이터베이스 스키마

mysql> SHOW DATABASES;

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

mysql> USE TESTDB;

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

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

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

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

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

mysql> CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_general_ci;

• utf8_general_ci 는 대소문자를 구분하지 않는다.
• 대소문자를 구분하려면 binary 타입으로 지정. 예) “utf8_bin”

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

mysql> CREATE DATABASE testdb;

스키마 제거

mysql> DROP [SCHEMA]DATABASE testdb;

시스템 환경 확인

서버의 환경 변수

# 모든 변수
mysql> SHOW VARIABLES;

DBMS 버전

mysql> select @@version;       -- DBMS version

mysql> SHOW VARIABLES LIKE 'version';

mysql> SHOW VARIABLES LIKE 'innodb_%';

지원하는 문자세트 구성

mysql> SHOW CHARACTER SET;

mysql> SHOW CHARACTER SET LIKE 'utf%';

문자세트 확인

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

지원하는 콜레이션

mysql> SHOW COLLATION;

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';

외부 파일 실행하기

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

$ mysql -u dbuser -p testdb < insert.sql

Enter password: ****

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

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

User & Privileges

사용자 db

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

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

사용자 추가

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

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

grant 명령: [권한 부여]

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

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 에만 모든 권한을 부여하고 있다.

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

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

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 명령을 사용한다. 두가지 형식을 사용한다.

REVOKE priv_type ON db.tables FROM user[,user]  ...

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

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

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

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

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

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

권한 조회

사용자별 권한 확인

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

접속된 계정 권한 확인

mysql> SHOW GRANTS FOR CURRENT_USER;

실행 프로세스 확인

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

• 프로세스 리스트보기

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.)

mysql> kill 프로세스아이디

ex: DB 생성해 사용자 추가

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