MariaDB 강좌 / 최종 실습: 주문 데이터베이스 구축부터 권한·백업·복원까지

최종 실습은 주문 데이터베이스를 구축하고 제한된 계정으로 주문·재고 변경을 검증한 뒤, 백업을 별도 DB에 복원해 같은 결과가 나오는지 확인하는 과정입니다. 스키마 생성·업무 변경·권한·복원 검증을 각각 확인해야 SQL 실행 성공만으로 완성을 판단하지 않게 됩니다.

완료 기준과 역할을 먼저 정하기

이 실습은 final48_lab이라는 새 학습 DB를 사용하며 실제 주문·결제 시스템에 연결하지 않습니다. 고객·상품·주문·상세를 만들고 새 주문 105를 확정한 상태를 백업합니다. 관리자와 DDL용 학습 계정, 제한된 앱 계정을 구분합니다. 실제 앱에는 입력 검증·매개변수 쿼리·인증·중복 요청 방지 등 추가 구현이 필요합니다.

전용 DB에 전체 구조와 기초 데이터 만들기

관리자 연결에서 final48_lab·final48_restore 이름과 final48_app 계정이 기존에 사용되지 않는지 확인합니다. 이미 있으면 덮어쓰지 말고 다른 실습 이름을 정합니다. 다음 전체 SQL을 실행하고 course_user가 새 실습 DB를 관리할 권한이 필요한 경우 그 범위에만 부여합니다.

CREATE DATABASE final48_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE final48_lab;
CREATE TABLE c48_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c48_order_customer FOREIGN KEY (customer_id)
  REFERENCES c48_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c48_item_order FOREIGN KEY (order_id)
  REFERENCES c48_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c48_item_product FOREIGN KEY (product_id)
  REFERENCES c48_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c48_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c48_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c48_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c48_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

GRANT ALL PRIVILEGES ON final48_lab.* TO 'course_user'@'127.0.0.1';

고객·상품 각 4, 주문 4, 상세 6이 초기 상태입니다. 수량·가격 제약과 외래키를 SHOW CREATE TABLE로 확인하고, 상품 현재가와 상세의 주문 당시 단가가 별개의 사실이라는 점을 유지합니다.

필요한 작업만 허용하는 앱 계정으로 접속하기

관리자가 예제 암호를 새 값으로 바꾸고 아래 계정을 만듭니다. 앱에는 테이블 생성·삭제·다른 사용자 관리 권한을 부여하지 않습니다. 이 학습 앱은 새 주문과 상세 입력, 재고 변경만 하므로 주문 상태 수정·삭제 권한도 부여하지 않았습니다.

CREATE USER 'final48_app'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON final48_lab.* TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_orders TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_items TO 'final48_app'@'127.0.0.1';
GRANT UPDATE(stock) ON final48_lab.c48_products TO 'final48_app'@'127.0.0.1';
SHOW GRANTS FOR 'final48_app'@'127.0.0.1';

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=final48_app --password --default-character-set=utf8mb4 final48_lab

새 연결에서 CURRENT_USER()로 계정을 확인하고 상품 조회가 되는지 검사합니다. 앱 계정에서 ALTER TABLE 같은 허용되지 않은 작업을 시도하면 거절되어야 합니다. 실제 운영 권한은 이 예제보다 더 좁은 테이블·열·역할 단위로 정할 수 있습니다. 권한 범위와 부여 문법을 참고하세요.

재고·주문·상세를 같은 트랜잭션에서 검증하기

USE final48_lab;
START TRANSACTION;
SELECT product_id,price,stock FROM c48_products WHERE product_id=1 FOR UPDATE;
UPDATE c48_products SET stock=stock-2 WHERE product_id=1 AND stock>=2;
SELECT ROW_COUNT() AS reserved_rows;
INSERT INTO c48_orders(order_id,customer_id,ordered_at,status,shipping_fee)
VALUES(105,4,'2026-03-01 10:00:00','paid',1000);
INSERT INTO c48_items(order_id,product_id,quantity,unit_price)
VALUES(105,1,2,3500);
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c48_items WHERE order_id=105 GROUP BY order_id;

기초 데이터 그대로라면 reserved_rows=1이고 주문 금액은 7000.00입니다. 재고 예약이 0이거나 오류가 나면 다음 INSERT를 실행하지 말고 ROLLBACK합니다. 성공한 경로에서만 아래 COMMIT을 선택합니다. 이 블록은 알려진 입력의 수동 실습이며 프로그램은 실패 시 분기와 전체 트랜잭션 취소를 구현해야 합니다.

COMMIT;
SELECT stock FROM c48_products WHERE product_id=1;
SELECT COUNT(*) FROM c48_orders;
SELECT COUNT(*) FROM c48_items;

확정 후 재고는 18, 주문 수는 5, 상세 수는 7입니다. 105를 그대로 재실행하면 중복 키 오류가 예상됩니다. 오류를 숨기는 IGNORE·REPLACE로 바꾸지 말고 중복 요청을 어떻게 식별할지 실제 프로그램에서 설계합니다. 이미 확정한 작업은 나중의 ROLLBACK으로 취소되지 않습니다.

확정한 상태를 백업하고 새 빈 DB에 복원하기

앱 계정이 아닌 백업 권한이 있는 course_user로 덤프합니다. 파일은 새 이름을 사용하고 종료 코드가 성공했는지 확인합니다. 그다음 관리자가 비어 있는 final48_restore를 만들고 필요한 학습 계정 권한만 부여합니다. 덤프 안에 다른 DB로 이동하는 문장이 없는지도 먼저 확인합니다.

mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=C:/mariadb-lab/final48-checked.sql final48_lab

CREATE DATABASE final48_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON final48_restore.* TO 'course_user'@'127.0.0.1';

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

SELECT DATABASE();
SHOW TABLES;
SOURCE C:/mariadb-lab/final48-checked.sql;

InnoDB 스냅샷·동시 DDL·클라이언트 호환성의 조건은 mariadb-dump 설명을 따릅니다. 계정·권한과 서버 설정은 이 DB 덤프만으로 복구되지 않으며 앱 계정을 검증용 복원 DB에 자동 연결하지 않습니다.

원본·복원본·실패 조건까지 검증하고 기록하기

SELECT (SELECT COUNT(*) FROM final48_restore.c48_customers) AS customers,
 (SELECT COUNT(*) FROM final48_restore.c48_products) AS products,
 (SELECT COUNT(*) FROM final48_restore.c48_orders) AS orders_count,
 (SELECT COUNT(*) FROM final48_restore.c48_items) AS detail_rows;
SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM final48_restore.c48_orders o JOIN final48_restore.c48_items i ON i.order_id=o.order_id
WHERE o.status='paid';
SELECT stock FROM final48_restore.c48_products WHERE product_id=1;
SHOW CREATE TABLE final48_restore.c48_items;

행 수는 4·4·5·7, paid 상품 금액 46000.00, 재고 18이 예상됩니다. 원본과 복원본의 기본키별 값·제약·주요 JOIN 결과까지 대조하고, 잘못된 외래키·중복 키·권한 없는 변경이 거절되는지 별도로 확인합니다. 성공 메시지와 행 수만 같다는 이유로 내용까지 일치한다고 판단하지 않습니다.

도구·서버 버전, 덤프 해시, 검증 쿼리와 결과, 권한 범위를 보관합니다. 모든 실습 계정 연결을 끝내고 필요 없어진 계정·DB를 정리할 때는 이 글에서 새로 만든 대상임을 확인하고 관리자에게 영향·보관 필요성을 확인합니다. 원본·백업·검증본을 자동 삭제하는 문장은 제공하지 않습니다.