wiki:MariaDB mysqldump

Version 1 (modified by yongwoo, 4 weeks ago) ( diff )

—

  1. mysqldump
    1. 1. 간단한 백업
    2. 2. 디폴트 옵션
    3. 3. 특정 데이터베이스/테이블 백업
    4. 4. 스키마와 데이터 분리 백업
    5. 5. 접근 권한 백업
    6. 6. 3.23 버전의 mysqldump
      1. 6.1. 상위 버전에서 3.x대로 다운그레이드를 할 때 주의점
    7. 7. 논리적인 일관성이 중요한 경우의 백업
      1. 잠금의 종류
      2. -x(--lock-all-tables)
      3. -l(--lock-tables)
      4. --master-data=2
      5. --single-transaction
      6. 잠금 예
    8. 8. 주의

mysqldump

mysqldump를 이용하는 것이 가장 일반적인 백업 방법이다. ​https://mariadb.com/kb/en/mariadb-dump/

1. 간단한 백업

데이터의 논리적인 일관성이 문제가 안되는 상황에서의 간단한 백업 용도로는 다음과 같이 하면 된다.

mysqldump -R(--routines) -E(--events) --skip-lock-tables -h [mysql 서버 IP] -u [mysql 계정] -p[암호] -B(--databases) [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] … > [생성할 sql 파일명]

zstd를 이용해서 백업 파일을 실시간으로 압축을 하려면 다음과 같이 한다.

mysqldump -R(--routines) -E(--events) --skip-lock-tables -h [mysql 서버 IP] -u [mysql 계정] -p[암호] -B(--databases) [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] … |zstd -q -o [생성할 sql 파일명].zst

  • -R(--routines) 옵션을 빼면 프로시저와 함수가 백업되지 않는다. 프로시저와 함수는 해당 데이터베이스가 아니라 mysql.proc 테이블에 저장이 되기 때문이다. -A(--all-databases) 옵션을 줄 때처럼 mysql.proc 테이블도 같이 백업받는 경우에는 -R(--routines) 옵션을 주지 않아도 된다.
  • -E(--events) 옵션을 빼면 이벤트 스케줄러 정보가 백업되지 않는다. 이벤트 스케줄러 정보는 해당 데이터베이스가 아니라 mysql.event 테이블에 저장이 되기 때문이다. -A(--all-databases) 옵션을 줘도 mysql.events 테이블 스키마만 백업하고 데이터는 다음과 같은 경고를 내면서 백업하지 않는다. Warning: Skipping the data of table mysql.event. Specify the -E(--events) option explicitly.
  • --skip-lock-tables 옵션을 빼면 데이터베이스를 잠근다(READ LOCAL lock). 일관성이 문제가 안되는 경우에는 데이터베이스를 잠그지 않는 것이 좋다. 실제 사용하는 대규모 데이터베이스를 잠그면 문제가 심각해질 수 있다.
  • -p 다음에 암호를 적어줄 때 공백이 없어야 한다.
  • mysql이 문자셋 변환을 해주기 때문에 default-character-set을 지정하지 않는 것이 좋다. 오히려 default-character-set을 잘못 지정해서 문제가 생길 확률이 더 크기 때문이다.
    • 하지만 옛날 버전 중에는 변환 과정에서 데이터 손실이 생기는 버전이 있다. 버그가 있는 옛날 버전을 사용할 때는 데이터의 문자셋과 동일한 default-character-set으로 지정해줘야 한다. 4.1 버전부터는 default-character-set을 지정하지 않으면 utf8로 설정된다.
  • --skip-extended-insert 옵션을 추가하면 데이터 하나에 SQL문이 하나씩 생성이 된다. 이를 생략하면 한 줄에 모든 데이터를 입력하는 SQL문이 생성된다.

데이터베이스를 복원할 때는 다음처럼 한다. 

mysql -u root -p[비밀번호] < backup-file.sql

zstd로 압축한 백업 파일을 실시간으로 풀면서 복원하려면 

zstdcat backup-file.sql.zst | mysql -u root -p[비밀번호]

2. 디폴트 옵션

mysqldump을 실행하면 --opt 옵션은 기본적으로 활성화되어 있다. 이 옵션은 다음 옵션을 지정하는 것과 동일하다.

  • --add-drop-table: 테이블을 생성하는 SQL 문 앞에 다음과 같은 drop table 문을 추가한다. 이를 포함하지 않으려면 --skip-add-drop-table 
    DROP TABLE IF EXISTS `valve`;
  • --add-locks: 이를 포함하지 않으려면 --skip-add-locks 
    LOCK TABLES `valve` WRITE;
... (INSERT INTO 문)
 UNLOCK TABLES;
  • --create-options: ENGINE, DEFAULT CHARSET, COLLATE 같은 MariaDB 특유의 CREATE TABLE 옵션을 추가한다. 이를 포함하지 않으려면 --skip-create-options 
    CREATE TABLE `valve` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
  • --quick: 큰 테이블을 백업할 때 유용하다. 읽어들인 데이터를 메모리에 저장했다가 한꺼번에 출력하는 것이 아니라 읽어들이는 족족 바로 출력한다. 특별한 일이 없으면 이 옵션은 놔두자.
  • --extended-insert: 한 INSERT 문에 여러 행의 데이터를 입력할 수 있게 한다. 이를 포함하지 않으려면 --skip-extended-insert
  • --lock-tables: 백업을 하는 동안 특정 데이터베이스를 잠근다(READ LOCAL lock). 자세한 내용은 아래 참조. 이를 포함하지 않으려면 --skip-lock-tables
  • --set-charset: 다음과 같은 문자셋 설정 관련 내용을 포함한다. 이를 포함하지 않으려면 --skip-set-charset 
    /*!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 */;

... (백업 내용)

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  • --disable-keys:아래와 같은 명령을 삽입한다. 모든 데이터가 삽입된 이후에 인덱스가 생성되므로 더 빠르게 복원할 수 있다. MyISAM 테이블의 unique key가 아닌 경우에만 유효하다. 이를 포함하지 않으려면 --skip-disable-keys 
    /*!40000 ALTER TABLE `valve` DISABLE KEYS */;
... (INSERT INTO 문)
/*!40000 ALTER TABLE `valve` ENABLE KEYS */;

그 외에 참고할만한 디폴트 옵션은 다음과 같다.

  • --comments: 테이블이나 컬럼에 대한 설명을 추가하는 것이 아니라, 프로그램 버전, 서버 버전, 호스트 등의 정보를 추가한다.
  • --default-character-set=utf8mb4: 디폴트 문자셋을 utf8mb4로 지정한다.

3. 특정 데이터베이스/테이블 백업

만약 특정 데이터베이스 하나만 백업한다면 -B(--databases) [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] … 대신에 그냥 데이터베이스 이름 하나만 적어주면 된다. 

mysqldump -R(--routines) -E(--events) -h venus.bookcube.co.kr -u root -pwisebooklogin elibarary |zstd -q -o backup.sql.zst

만약 특정 테이블만 백업한다면 -B(--databases) [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] … 대신에 그냥 데이터베이스 이름 뒤에 한 칸 띄고 테이블 이름을 적어주면 된다. -R(--routines)와 -E(--events) 옵션은 테이블에 종속된 것이 아니기 때문에 굳이 적어줄 필요는 없다. 다음 예는 occupant 데이터베이스의 house와 room 테이블을 백업하는 예이다. 

mysqldump -t -n --skip-lock-tables -h occupant1.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data1.mysql
mysqldump -t -n --skip-lock-tables -h occupant2.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data2.mysql
mysqldump -t -n --skip-lock-tables -h occupant4.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data4.mysql
mysqldump -t -n --skip-lock-tables -h occupant5.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data5.mysql
mysqldump -t -n --skip-lock-tables -h occupant6.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data6.mysql
mysqldump -t -n --skip-lock-tables -h occupant7.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data7.mysql
mysqldump -t -n --skip-lock-tables -h occupant8.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data8.mysql
mysqldump -t -n --skip-lock-tables -h occupant9.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data9.mysql
mysqldump -t -n --skip-lock-tables -h occupant9.dasangng.co.kr -P 23306 -u root -p'smartgrid12#' --ssl occupant house room > occupant-data9b.mysql
mysqldump -t -n --skip-lock-tables -h occupant10.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data10.mysql
mysqldump -t -n --skip-lock-tables -h occupant11.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data11.mysql
mysqldump -t -n --skip-lock-tables -h occupant11b.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data11b.mysql
mysqldump -t -n --skip-lock-tables -h occupant12.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data12.mysql
mysqldump -t -n --skip-lock-tables -h occupant13.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data13.mysql
mysqldump -t -n --skip-lock-tables -h occupant14.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data14.mysql

단, -B(--databases) 옵션을 생략하면 SQL 문을 생성할 때 CREATE DATABASE 문을 생략한다. 따라서 복구할 때에도 데이터베이스를 지정해서 복구해야 한다. 

zstdcat backup-file.sql.zst | mysql -u root -p[비밀번호] [복원할 데이터베이스]

특정 테이블을 모두 백업하는 것이 아니라 조건에 맞는 것만 백업하려면 --where 옵션을 주면 된다. 

mysqldump -t(--no-create-info) -n(--no-create-db) --skip-lock-tables occupant --where = "time < '2021-11-01'" | zstd -q -o occupant2-202110.sql.zst

특정 데이터베이스가 아니라 전체 데이터베이스를 백업하려면 --all-databases 옵션을 주면 된다.

4. 스키마와 데이터 분리 백업

스키마를 변경할 목적이라면 스키마와 데이터를 분리해서 백업받는 것이 편하다. -R(--routines)와 -E(--events) 옵션은 스키마나 데이터 중 한 쪽에만 넣어주면 되는데, 용량이 작은 스키마 쪽에 넣어두는 것이 편하다.

스키마
mysqldump -d(--no-data) -R(--routines) -E(--events) --skip-lock-tables -h [mysql 서버 IP] -u [mysql 계정] -p[암호] -B(--databases) [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] … |zstd -q -o [생성할 sql 파일명].zst
데이터
mysqldump -t(--no-create-info) -n(--no-create-db) --skip-lock-tables -h [mysql 서버 IP] -u [mysql 계정] -p[암호] -B(--databases) [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] … |zstd -q -o [생성할 sql 파일명].zst
  • -B(--databases) 옵션을 생략하면 SQL 문을 생성할 때 CREATE DATABASE 문을 생략한다. 따라서 -n(--no-create-db)는 특정 테이블만 백업할 때는 필요없다.

5. 접근 권한 백업

DB 접근 권한은 mysql 데이터베이스에 따로 저장되므로 mysqldump 명령에 의해 생성된 sql문에는 권한과 관련된 내용이 저장되지 않는다.

mysql 데이터베이스에 저장되는 내용 중 프로시저와 함수는 -R(--routines) 옵션으로, 이벤트 스케줄러 정보는 -E(--events) 옵션으로 백업받을 수 있지만, 권한까지 백업받는 옵션은 없다. 따라서 권한까지 백업받으려면 직접 mysql 데이터베이스를 백업해야 한다.

프로시저 권한은 mysql 데이터베이스의 procs_priv 테이블에 저장된다.

mysqldump -n(--no-create-db) -t(--no-create-info) --skip-lock-tables --where="Db in ( '[백업할 데이터베이스 이름1]', '[백업할 데이터베이스 이름2]', … )" -h [mysql 서버 IP] -u [mysql 계정] -p[암호] mysql procs_priv |zstd -q -o [생성할 sql 파일명].zst

DB 접근 권한은 mysql 데이터베이스의 db 테이블에 저장된다.

mysqldump -n(--no-create-db) -t(--no-create-info) --skip-lock-tables --where="Db in ( '[백업할 데이터베이스 이름1]', '[백업할 데이터베이스 이름2]', … )" -h [mysql 서버 IP] -u [mysql 계정] -p[암호] mysql db |zstd -q -o [생성할 sql 파일명].zst

마찬가지로 사용자까지 백업하려면 mysql 데이터베이스의 user 테이블을 백업하면 된다. 10.4부터는 global_priv 테이블을 백업해야 한다.

mysqldump -n(--no-create-db) -t(--no-create-info) --skip-lock-tables --where="User in ( '[백업할 사용자1]', '[백업할 사용자2]', … )" -h [mysql 서버 IP] -u [mysql 계정] -p[암호] mysql global_priv |zstd -q -o [생성할 sql 파일명].zst

6. 3.23 버전의 mysqldump

3.23 버전에서는 아래처럼 옵션을 줘서 사용하면 된다.

mysqldump --all --quick -l(--lock-tables) -h [mysql 서버 IP] -u [mysql 계정] -p[암호] [백업할 데이터베이스 이름] > [생성할 sql 파일명]

6.1. 상위 버전에서 3.x대로 다운그레이드를 할 때 주의점

mysqldump를 이용해 생성한 sql 문을 보면 다음과 같은 부분이 있다. 

/*!40000 ALTER TABLE tb_name DISABLE KEYS */;
... (INSERT INTO 문)
/*!40000 ALTER TABLE tb_name ENABLE KEYS */;

위의 구문은 4.0 미만 버전에서는 /* … */ 을 주석처리하라는 뜻이다. 그런데 3.x 대 버전 중에는 이 구문을 제대로 해석하지 못해 에러를 발생시키는 경우가 있었다. 그래서 다음과 같이 옵션을 줘서 위 구문을 생성하지 않도록 해서 해결했었다.

mysqldump --skip-add-drop-table --skip-disable-keys --skip-add-locks -h [mysql 서버 IP] -u [mysql 계정] -p[암호] -B(--databases) [백업할 데이터베이스 이름] > [생성할 sql 파일명]

지금은 3.x대 버전으로 되돌아갈 일도 없을 거고, 설령 그런 일이 있다 하더라도 버그가 고쳐졌을테니, 지금은 신경쓰지 않아도 될 것 같다.

7. 논리적인 일관성이 중요한 경우의 백업

현재 서비스 중이어서 계속 데이터가 변경되는 경우에는 백업받을 때 데이터의 논리적인 일관성을 신경써야 한다. 따라서 데이터가 변경되지 않도록 잠금을 걸어야 한다.

잠금의 종류

  • READ LOCK
    • 읽기만 가능하게 잠금
  • READ LOCAL LOCK
    • READ LOCAL LOCK은 READ LOCK과는 달리 실행 중인 insert 문(non-conflictiong insert, concurrent insert)은 허용한다.

-x(--lock-all-tables)

  • 백업을 하는 동안 모든 데이터베이스를 읽기만 가능하도록 잠근다(READ LOCK).
  • 이 옵션을 설정하면 --single-transaction과 -l(--lock-tables) 옵션은 자동으로 꺼진다.
  • 데이터베이스의 일관성은 보장되지만 백업이 진행되는 동안 쓰기가 불가능해지므로 대용량 데이터베이스인 경우엔 신중하게 해야 한다.
  • 바이너리 로그를 사용한다면 full 백업 이후의 변경 사항을 복구(point-in-time 복구, roll-forward)하기 위해서라도 -x(--lock-all-tables) 명령 대신 --master-data=2 옵션을 주는 것이 좋다.

-l(--lock-tables)

  • 특별한 옵션 지정을 하지 않았을 때 --opt 옵션에 의해 적용되는 디폴트 잠금이다. 따라서 사용하지 않으려면 명시적으로 --skip-lock-tables 이나 --skip-opt 옵션을 사용해야 한다.
  • 백업을 하는 동안 특정 데이터베이스를 잠근다(READ LOCAL lock).
  • READ LOCAL LOCK이므로 실행 중인 insert는 허용한다.
  • 여러 데이터베이스를 백업할 때는 주의해야 한다. -l(--lock-tables)은 각 데이터베이스를 각각 잠근다. 그래서 데이터베이스간에 논리적인 일관성이 보장되지 않는다.
  • 백업이 진행되는 동안 특정 데이터베이스에 쓰기가 불가능해지므로 대용량 데이터베이스인 경우엔 신중하게 해야 한다.
  • InnoDB나 BDB 같은 트랜잭션을 지원하는 테이블에는 --single-transaction이 더 나은 옵션이다. 테이블을 전혀 잠글 필요가 없기 때문이다.

--master-data=2

  • 바이너리 로그가 활성화되어 있어야 한다.
  • 이 옵션을 사용하기 위해선 RELOAD 권한이 필요하다.
  • 바이너리 로그 좌표(파일명과 위치)를 가리키는 CHANGE MASTER TO 문이 주석으로 삽입된다. 주석으로 삽입하기 때문에 실제 영향은 없다.
  • 다음과 같이 바이너리 로그 좌표(파일명과 위치)를 이용해 full 백업 이후의 로그만 복원할 수 있다. 
    mysqlbinlog --start-position=1 "mysql bin로그 경로" > binlog.sql
  • --single-transaction을 명시적으로 적어주지 않는다면 -x(--lock-all-tables) 옵션이 자동으로 지정된다.
  • -l(--lock-tables) 옵션을 자동으로 끈다.

--single-transaction

  • 쓰기를 막지 않고도 일관성있게 데이터베이스를 백업할 수 있다.
  • InnoDB 같은 트랜잭션을 지원하는 테이블에만 유용하다. MyISAM 같은 트랜잭션을 지원하지 않는 테이블에서는 논리적인 일관성이 보장되지 않는다.
  • -l(--lock-tables)과 같이 쓸 수 없다. -l(--lock-tables)을 자동으로 해제한다.
  • MySQL Cluster 테이블에는 사용할 수 없다. (확인 필요)

잠금 예

예를 들어 바이너리 로그를 사용하지 않을 때의 full 백업은 다음과 같이 한다. full 백업을 진행하는 동안 쓰기가 금지되므로 신중하게 해야 한다. 

mysqldump -x(--lock-all-tables) -E(--events) -A(--all-databases) -u [계정] -p[비밀번호] |zstd -q -o fullbackup.sql.zst

트랜잭션을 지원하는 테이블만 사용한다면 다음이 더 좋은 옵션이다. 쓰기가 허용되므로 full 백업이 오래 걸려도 무방하다. 

mysqldump --single-transaction -E(--events) -A(--all-databases) -u [계정] -p[비밀번호] |zstd -q -o fullbackup.sql.zst

바이너리 로그를 사용할 때의 full 백업은 증분 백업 참고.

8. 주의

  • MariaDB 10.3 이상의 mysqldump여야 10.3에서 새로 생긴 mysql.transaction_registry 테이블을 백업할 수 있다. 따라서 이전 MariaDB 버전의 mysqldump는 MariaDB 10.3 이상에서 사용하면 안된다.
  • 시스템 버전드 테이블을 백업할 수 없다.

처음으로, mysqlhotcopy, 증분 백업, mariabackup

Note: See TracWiki for help on using the wiki.