[MariaDB] 7. mysqldump를 이용한 데이터 백업

1. mysqldump
   1. 기본
   2. 옵션
2. 공통 특징
   1. 사용자명 및 비밀번호 옵션 생략
   2. root 사용자를 이용
   3. root 사용자를 이용
   4. 필수 권한
3. 예제
   1. 전체 데이터베이스 백업
   2. 특정 데이터베이스 백업
   3. 특정 테이블 백업
   4. CREATE STATEMENT만 백업
   5. INSERT STATEMENT만 백업
   6. STORED ROUTINE 포함하여 백업

---

1. mysqldump

1.1. 기본

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases
mysqldump [options] --system={options}

mysqldump client는 데이터베이스 dump파일로 추출해내기 위한 백업 프로그램입니다.
기본적으로는 CREATE TABLE과 insert문을 모두 포함시킨 sql 파일을 생성하지만, 옵션 설정에 따라 CSV 나 XML 포맷으로 만들 수도 있습니다.

db_name 다음에 tbl_name(테이블명)을 설정하지 않을 경우 지정한 데이터베이스 내에 있는 모든 테이블을 dump합니다.

mysqldump는 기본적으로 information_schema 나 performance_schema 는 dump 하지 않습니다.

사용하기 쉽고 강력하지만, global lock이 걸리기 때문에 백업 도중 전체 서비스가 정지된다는 치명적인 단점이 있습니다.

만일 백업 도중에도 서비스가 정지되면 안되는 상황이거나, 백업 시간이 많이 걸리는 경우에는 Xtra Backup나 enterprise backup을 사용하는 것을 권장합니다.

1.2. 옵션

• --all-databases, -A
  • 모든 데이터베이스 내에 있는 테이블들을 dump
  • --databases all 과 같다
• --complete-insert, -c
  • INSERT 문에 column 명을 포함시켜 생성
• --databases, -B
  • 설정한 데이터베이스들만 dump
• --host=호스트명, -h
  • MariaDB Server 의 호스트를 지정
• --no-create-info, -t
  • CREATE TABLE 문을 제외시킴
• --no-data, -d
  • INSERT 문을 포함하지 않음
• --routines, -R
  • stored routine(procedure, function)을 포함
• --password[=비밀번호], -p[비밀번호]
  • MariaDB Server 의 비밀번호를 지정
  • mysql_native_password 플러그인을 이용하여 MariaDB Server에 접근할 경우 지정합니다.
• --port=포트번호, -P 포트번호
  • 연결할 TCP/IP 포트번호 설정
• --user=사용자명, -u 사용자명
  • MariaDB Server 의 사용자명를 지정
  • mysql_native_password 플러그인을 이용하여 MariaDB Server에 접근할 경우 지정합니다.

2. 공통 특징

3.1. 사용자명 및 비밀번호 옵션 이용

mysql_native_password 플러그인을 이용하는 사용자의 경우, mysqldump 프로그램을 사용하기 위해서는 사용자명과 비밀번호 옵션을 반드시 설정해야합니다.

mysqldump -u 사용자명 -p [옵션]

2.2. 사용자명 및 비밀번호 옵션 생략

unix_socket 플러그인을 이용하는 사용자의 경우, 사용자명과 비밀번호 옵션을 생략할 수 있습니다.

단, 현재 로그인 중인 UNIX 계정명을 자동으로 이용하니 참고해야합니다.

mysqldump

2.3. root 사용자를 이용

root@localhost 사용자가 unix_socket 설정되어 있으면서
현재 로그인 중인 Linux 계정에서 sudo 권한이 있을 경우
아래와 같이 -u root -p 옵션을 생략하고 mysqldump를 사용할 수 있습니다.

sudo mysqldump

2.4. 필수 권한

TABLE, STORED ROUINTE, VIEW를 dump 하기 위한 필수 권한은 SELECT, SHOW VIEW, LOCK TABLES 입니다.

mysqldump에 이용할 계정명에 dump할 DB 및 테이블에 대한 SELECT, SHOW VIEW, LOCK TABLES 권한을 가지고 있어야 합니다.

GRANT SELECT, LOCK TABLES, SHOW VIEW ON employees.* TO `jini`@`localhost`;

3. 예제

3.1. 전체 데이터베이스 백업

mysqldump [-u 사용자명 -p] --all-databases > 파일명.sql

• --all-database
  • 전체 데이터베이스를 백업하는 옵션

3.1.1. 전체 database를 all_dump.sql 파일로 백업

information_schema, performance_schema 데이터베이스를 제외하고 접근가능한 모든 database를 백업합니다.

mysqldump --all-databases > all_dump.sql

3.2. 특정 데이터베이스 백업

mysqldump [-u 사용자명 -p] [--databases] DB명 > 파일명.sql

• --databases, -B
  • 특정 데이터베이스를 백업하고자할 때 사용하는 옵션
  • 데이터베이스 하나만 설정할 때에는 생략할 수 있습니다

3.2.1. employees database 백업

하나의 데이터베이스만 백업하는 경우에는 --databases 옵션을 생략할 수 있습니다.

mysqldump employees > employees.sql

3.2.2. employees, book database 백업

여러개의 database를 지정하고 싶다면 --databases 옵션을 반드시 설정해야합니다.

mysqldump --databases employees book > employees_book.sql

3.3. 특정 테이블 백업

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

3.3.1. employees database 내의 departments, dept_manager 테이블 백업

database명 다음에 백업하고싶은 테이블들을 나열하여 작성하면 됩니다.

mysqldump employees departments dept_manager > departments_dept_manager.sql

3.4. CREATE STATEMENT만 백업

mysqldump [-u 사용자명 -p] --no-data DB명 [테이블명] > 파일명.sql

• --no-data, -d

3.4.1. employees 데이터베이스 내의 테이블 스키마 백업

mysqldump --no-data employees > employees_create.sql

3.5. INSERT STATEMENT만 백업

mysqldump [-u 사용자명 -p] --no-create-info --complete-insert DB명 [테이블명] > 파일명.sql

• --complete-insert, -c
  • INSERT 문에 column 명을 포함시켜 생성
• --no-create-info, -t
  • CREATE TABLE 문을 제외시킴

3.5.1. employees 데이터베이스 내의 테이블 데이터 백업

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

3.6. STORED ROUTINE 포함하여 백업

mysqldump [-u 사용자명 -p] --routines DB명 [테이블명] > 파일명.sql

• --routines, -R

3.6.1. employees 데이터베이스 내의 테이블 스키마 및 stored routine 백업

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

---

+++

• How to use mysqldump in linux
• how to use mysqldump in mariadb
• how to use mysqldump in mysql