mariadb 사용자 관리와 프로시저

복습

• 1:1 관계

1:1관계에서 fk에 unique조건을 거는 것은 1:1관계를 데이터베이스 단에서 보장하기 위해서지 필수요소는 아니다. 프로그램 차원에서 검증이 가능한 사안이기 때문이다.

• n:m 관계

정규화 1원칙인 원자성에 위배될 수 있기 때문에 교차 테이블을 사용하여 1:n, 1:m관계로 테이블을 나누어야 함

예시)

order테이블에 주문자와 여러 주문 상품과 수량이 있다면 정규화에 어긋남

order테이블과 product테이블이 있다면 n:m관계이기 때문에

order_product(order_details)라는 교차테이블을 생성해야함→ 주문 번호와 상품번호가 포함

 

사용자 관리

 

• 사용자 목록 조회 명령어

select * from mysql.user;

localhost - 내 컴퓨터에서만 사용 가능 % - 원격 접속이 가능

 

 

• 사용자 생성 명령어

create user 'test1'@'localhost' identified by '4321';

현재 mac 환경에서 docker에 mariadb를 설치한 상황이기 때문에

localhost로 생성하면 접속시 Access denied 에러가 발생한다.

docker는 내 컴퓨터가 아닌 가상환경을 만든 것이기 때문에 localhost가 아닌 %를 사용하여 계정을 생성해야 접속이 가능하다

• 원격 접속 가능한 사용자 생성 명령어

create user 'test1'@'%' identified by '4321';

그럼 아래와 같이 7번에 test1 사용자가 생성된다 !

 

 

그럼 이제 username : test1 pw :4321을 가지고 데이터베이스 접속이 가능하다

아래와 같은 명령어를 사용하여 권한을 부여하고 회수할 수 있다

권한을 부여하고 회수하는 작업은 root 계정에서 진행해야 한다

-- 사용자에게 select 권한 부여
grant select on board.author to 'test1'@'localhost';
grant select on board.author to 'test1'@'%';

-- 사용자 select 권한 회수
revoke select on board.author from 'test1'@'localhost';
revoke select on board.author from 'test1'@'%';

-- 환경설정을 변경 후 확정
flush privileges;

-- test1으로 로그인 후 조회
select * from board.author;

-- 권한 조회
show grants for 'test1'@'localhost';

-- 사용자 계정 삭제
drop user 'test1'@'localhost'

 

View

 

데이터베이스 테이블과 유사한 구조를 가지지만 가상의 테이블로서 실제 데이터를 저장하지 않는 데이터베이스

예시 ) 테이블의 쿼리를 제한하고 싶을 경우, 예를 들어 author 테이블에서 name, age, role만 마케팅팀에게 보여주고싶을 때 가상의 테이블인 view를 만들 수 있다.

예시 ) 팀에서 자주 쓰는 select문을 가상의 테이블로 만들어 view를 생성할 수 있음

• view 생성 명령어

create view author_for_marketing_team as select name, age, role from author;

• 테스트 계정에 view select 권한 부여

grant select on board.author_for_marketing_team to 'test1'@'%';

• view 변경

create or replace view author_for_marketing_team as select name, email, age, role from author;

• view 삭제

drop view author_for_marketing_team;

 

Procedure

 

Stored Procedure는 데이터베이스에 저장되어 실행될 수 있는 하나 이상의 SQL 문의 집합

복잡한 조건문, 반복문 등 저장 프로시저는 절차적 프로그래밍 언어로서 사용

-- 프로시저 생성
DELIMITER //
CREATE PROCEDURE test_procedure()
BEGIN
        select "hello world" ;
END
// DELIMITER ;

-- 프로시저 호출
call test_procedure();

-- 프로시저 삭제
drop procedure test_procedure;

-- 게시글 목록 조회 프로시저 생성
DELIMITER //
CREATE PROCEDURE 게시글목록조회()
BEGIN
    select * from post;
END
// DELIMITER ;

call 게시글목록조회();

-- postId를 통한 게시글 단건 조회
DELIMITER //
CREATE PROCEDURE 게시글단건조회(in postId int)
BEGIN
    select * from post where id = postId;
END
// DELIMITER ;

call 게시글단건조회(3);

-- 저자id와 제목을 통한 게시글 단건 조회
DELIMITER //
CREATE PROCEDURE 게시글단건조회(in 저자id int, in 제목 varchar(255))
BEGIN
    select * from post where author_id = 저자id and title=제목;
END
// DELIMITER ;

call 게시글단건조회(4,'hello');

-- 글쓰기 (author_id)
DELIMITER //
CREATE PROCEDURE 게시글생성(in 저자id int, in 제목 varchar(255), in 내용 varchar(3000))
BEGIN
    insert into post(title, contents, author_id) values(제목, 내용, 저자id);
END
// DELIMITER ;

call 게시글생성(1,'hello', 'world');

-- 글쓰기2 (email)
DELIMITER //
CREATE PROCEDURE 게시글생성2(in 이메일 varchar(255), in 제목 varchar(255), in 내용 varchar(3000))
BEGIN
    declare authorId int;
    select id into authorId from author where email = 이메일;
    insert into post(title, contents, author_id) values(제목, 내용, authorId);
END
// DELIMITER ;

call 게시글생성2('hong1@naver.com','hello', 'world');

-- sql에서 문자열 합치는 명령어 concat('hello','world');
-- 글 상세 조회 : input 값 postId
-- title, contents, '홍길동' + '님'

DELIMITER //
CREATE PROCEDURE 게시글상세조회(in postId int)
BEGIN
    select title, contents , concat(a.name,'님') from post p inner join author a on p.author_id = a.id where p.id = postId;
END
// DELIMITER ;

call 게시글상세조회(1);

DELIMITER //
CREATE PROCEDURE 게시글상세조회(in postId int)
BEGIN
    declare authorName varchar(255);
    select name into authorName from author where id = (select author_id from post where id = postId);
    set authorName = concat(authorName, '님')
    select title, contents , authorName from post p where p.id = postId;
END
// DELIMITER ;

call 게시글상세조회(1);

-- 등급조회
-- 글을 100개 이상 쓴 사용자는 고수입니다. 출력
-- 글을 10개 이상 100개 미만이면 중수입니다.
-- 그외는 초보입니다. 
-- input값 : email 값
DELIMITER //
CREATE PROCEDURE 등급조회(in 이메일 varchar(255))
BEGIN
    declare authorId int;
    declare count int;
    select id into authorId from author where email = 이메일;
    select count(*) into count from post where author_id = author_id;
    if count >= 100 then 
        select '고수입니다.';
    elseif count>=10 then
        select '중수입니다.';
    else
        select '초보입니다.';
    end if;
END
// DELIMITER ;
call 등급조회('hong1@naver.com');

-- 반복문을 통해 post 대량 생성
-- 사용자가 입력한 반복 횟수에 따라 글이 도배되는데, title은 '안녕하세요'
CREATE PROCEDURE 글도배(in 반복횟수 int)
BEGIN
    declare a int;
    set a = 0;
    while (a < 반복횟수) do
        insert into post(title) values('안녕하세요');
        set a = a + 1
    end while
END
// DELIMITER ;

-- 프로시저 생성문 조회
show create procedure 프로시저명;

-- 프로시저 권한 부여
grant execute on board.프로시저명 to 'test1'@'localhost';

 

mariadb(mysql) DB엔진의 종류

 

• InnoDB

ACID 트랜잭션을 지원하는 엔진

mariadb의 default 엔진

트랜잭션 지원 (COMMIT, ROLLBACK 등)

• MyISAM

트랜잭션을 지원하지 않는 비트랜잭션 엔진

transaction에 의한 행 잠금이 없으므로, 읽기 작업이 많은 애플리케이션에 적합 ( lock이 없음 )

 

DB 서버 구성

 

DB의 사용성에 문제가 생기지 않도록 하는 고가용성 확보를 위한 방안

→ HA 확보 → 서버안정성, 서버 설계

• 클러스터링

1대의 스토리지와 여러대의 서버 운영

active/active, active/stanby

DB를 한 대만 운영 할 경우에 문제점은 DB 서버가 죽으면 관련된 서비스가 전체가 중단

동일한 DB 서버를 두 대를 묶고 두 DB 서버

• 레플리카

n개의 (스토리지, 서버) 를 운영

데이터베이스 스토리지 유실에 대한 대안으로 스토리지 까지 복제

• 샤딩

같은 테이블 스키마를 가진 데이터를 다수의 데이터베이스에 분산하여 저장하는 방법

 

Proxy

 

대리인 → 로드밸런서 : 장애없는 지속가능한 서비스를 위해 사용자의 요청을 분산처리 하는 것→ HA