[MariaDB] 8. dump파일을 이용하여 데이터 복원하기

1. 필수 권한
   1. CREATE문 관련 필수권한
   2. INSERT문 실행 필수권한
   3. 데이터 복원을 위한 필수 권한
2. dump 파일을 이용한 데이터 복원
   1. 터미널에서 redirection 이용
   2. MariaDB prompt에서 source 이용

---

1. 필수 권한

mysqldump를 이용하여 만든 백업파일은 옵션에 따라, 데이터를 제외한 SQL 실행문만 들어있거나, 데이터만 들어있을 수 있습니다.

1.1. CREATE문 관련 필수권한

아래와 같이, --no-data 옵션과 --routines 옵션을 설정할 경우, employees 데이터베이스의 table, view의 create문과 stored routines 들이 담긴 dump파일을 생성할 수 있습니다.

mysqldump --no-data --routines employees > employees_create_full.sql

위와같은 CREATE문을 포함한 DDL 실행 필수 권한은 CREATE, CREATE ROUTINE, ALTER ROUTINE, CREATE VIEW, DROP, SELECT 입니다.

GRANT CREATE, CREATE ROUTINE, ALTER ROUTINE, CREATE VIEW, DROP, SELECT ON employees.* TO `jini`@`localhost`;

2.2. INSERT문 실행 필수권한

아래와 같이, --no-create-info 옵션을 설정할 경우, employees 데이터베이스의 insert문 이 담긴 dump파일을 생성할 수 있습니다.

mysqldump --no-create-info --complete-insert employees > employees_data.sql

employees_data.sql 파일을 열어보면, insert문 외에도 LOCK TABLES 과, ALTER TABLE 명령어도 들어있는 것을 확인할 수 있습니다.

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `departments`
--

LOCK TABLES `departments` WRITE;
/*!40000 ALTER TABLE `departments` DISABLE KEYS */;
INSERT INTO `departments` (`dept_no`, `dept_name`) VALUES ('d009','Customer Service'),('d005','Development'),('d002','Finance'),('d003','Human Resources'),('d001','Marketing'),('d004','Production'),('d006','Quality Management'),('d008','Research'),('d007','Sales');
/*!40000 ALTER TABLE `departments` ENABLE KEYS */;
UNLOCK TABLES;

LOCK TABLES 는 다른 세션이 테이블을 수정하지 못하도록 테이블을 잠그는 기능으로, 대량의 insert문이 실행되기 전에 데이터의 일관성을 유지하기 위해 설정된 명령어입니다.

테이블을 LOCK하는 기능은 SELECT 권한도 함께 있어야 정상적으로 작동됩니다.

ALTER TABLE tbl_name DISABLE KEYS 는 insert 문 실행 전에 인덱스 체크를 비활성화하여 insert 문 실행중 에러가 나는 현상을 피합니다.
insert문이 모두 실행 된 후에는 다시 ALTER TABLE tbl_name ENABLE KEYS로 인덱스 체크를 활성화합니다.

이러한 부분 때문에, 데이터를 복원하기 위해서는 아래와 같은 권한이 필요합니다.

GRANT SELECT, LOCK TABLES, ALTER, INSERT ON employees.* TO `jini`@`localhost`;

2.3. 데이터 복원을 위한 필수 권한

CREATE문과 INSERT문을 모두 실행시키기위한 필수 권한은 CREATE, CREATE ROUTINE, ALTER ROUTINE, CREATE VIEW, DROP, SELECT, LOCK TABLES, ALTER, INSERT 입니다.

접속할 host에 복원할 대상인 database에 대하여 아래와 같은 형식으로 권한을 미리 설정한 후 복원을 진행하면 됩니다.

GRANT CREATE, CREATE ROUTINE, ALTER ROUTINE, CREATE VIEW, DROP, SELECT, LOCK TABLES, ALTER, INSERT ON employees.* TO `jini`@`localhost`;

---

2. dump 파일을 이용한 데이터 복원

dump 파일을 실행하는 방법은 터미널 상에서 redirection을 사용하는 방법과, MySQL Client에서 source 명령어를 사용하는 방법이 있습니다.

2.1. 터미널에서 redirection 이용

mysql client 명령어에서 redirection을 이용하여 dump파일을 이용할 수 있습니다.

mysql [-u 사용자명 -p] < 파일명.sql
mysql [-u 사용자명 -p] DB명 < 파일명.sql
mysql [-u 사용자명 -p] DB명 테이블명 < 파일명.sql

mysqldump와 마찬가지로, user의 plugin 설정에 따라 사용자명 및 비밀번호는 생략할 수 있습니다.

DB나 테이블을 지정하여 특정 테이블 또는 데이터베이스를 복원할 수도 있고
만일, dump파일에 CREATE 문이 포함되어있다면 DB명 지정없이 전체 복원을 할 수도 있습니다.

2.2. MariaDB prompt에서 source 이용

dump 파일이 위치한 곳에서

데이터를 복원할 database로 접속한 후,

mysql employees

source 파일명 명령어를 이용하여 파일을 실행시킬 수 있습니다.

MariaDB [employees]> source employees_create_full.sql

---

++

• MariaDB sql 실행 필수 권한
• MySQl sql 실행 필수 권한