Changes between Initial Version and Version 1 of MariaDB mysqldump


Ignore:
Timestamp:
Nov 11, 2025, 6:26:15 PM (4 weeks ago)
Author:
yongwoo
Comment:

—

Legend:

Unmodified
Added
Removed
Modified

  • MariaDB mysqldump

    v1 v1  
     1[[PageOutline]]
     2= mysqldump =
     3mysqldump를 이용하는 것이 가장 일반적인 백업 방법이다. https://mariadb.com/kb/en/mariadb-dump/
     4
     5== 1. 간단한 백업 ==
     6데이터의 논리적인 일관성이 문제가 안되는 상황에서의 간단한 백업 용도로는 다음과 같이 하면 된다.
     7
     8 '''mysqldump -R({{{--routines}}}) -E({{{--events}}}) {{{--skip-lock-tables}}} -h''' [mysql 서버 IP] '''-u''' [mysql 계정] '''-p'''[암호] '''-B({{{--databases}}})''' [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] ... '''>''' [생성할 sql 파일명]
     9
     10zstd를 이용해서 백업 파일을 실시간으로 압축을 하려면 다음과 같이 한다.
     11
     12 '''mysqldump -R({{{--routines}}}) -E({{{--events}}}) {{{--skip-lock-tables}}} -h''' [mysql 서버 IP] '''-u''' [mysql 계정] '''-p'''[암호] '''-B({{{--databases}}})''' [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] ... '''|zstd -q -o ''' [생성할 sql 파일명]'''.zst'''
     13
     14 * -R({{{--routines}}}) 옵션을 빼면 프로시저와 함수가 백업되지 않는다. 프로시저와 함수는 해당 데이터베이스가 아니라 mysql.proc 테이블에 저장이 되기 때문이다. -A({{{--all-databases}}}) 옵션을 줄 때처럼 mysql.proc 테이블도 같이 백업받는 경우에는 -R({{{--routines}}}) 옵션을 주지 않아도 된다.
     15 * -E({{{--events}}}) 옵션을 빼면 이벤트 스케줄러 정보가 백업되지 않는다. 이벤트 스케줄러 정보는 해당 데이터베이스가 아니라 mysql.event 테이블에 저장이 되기 때문이다. -A({{{--all-databases}}}) 옵션을 줘도 mysql.events 테이블 스키마만 백업하고 데이터는 다음과 같은 경고를 내면서 백업하지 않는다. Warning: Skipping the data of table mysql.event. Specify the -E({{{--events}}}) option explicitly.
     16 * {{{--skip-lock-tables}}} 옵션을 빼면 데이터베이스를 잠근다(READ LOCAL lock). 일관성이 문제가 안되는 경우에는 데이터베이스를 잠그지 않는 것이 좋다. 실제 사용하는 대규모 데이터베이스를 잠그면 문제가 심각해질 수 있다.
     17 * -p 다음에 암호를 적어줄 때 공백이 없어야 한다.
     18 * mysql이 문자셋 변환을 해주기 때문에 default-character-set을 지정하지 않는 것이 좋다. 오히려 default-character-set을 잘못 지정해서 문제가 생길 확률이 더 크기 때문이다.
     19  - 하지만 옛날 버전 중에는 변환 과정에서 데이터 손실이 생기는 버전이 있다. 버그가 있는 옛날 버전을 사용할 때는 데이터의 문자셋과 동일한 default-character-set으로 지정해줘야 한다. 4.1 버전부터는 default-character-set을 지정하지 않으면 utf8로 설정된다. 
     20 * {{{--skip-extended-insert}}} 옵션을 추가하면 데이터 하나에 SQL문이 하나씩 생성이 된다. 이를 생략하면 한 줄에 모든 데이터를 입력하는 SQL문이 생성된다.
     21
     22데이터베이스를 복원할 때는 다음처럼 한다.
     23{{{
     24mysql -u root -p[비밀번호] < backup-file.sql
     25}}}
     26
     27zstd로 압축한 백업 파일을 실시간으로 풀면서 복원하려면
     28{{{
     29zstdcat backup-file.sql.zst | mysql -u root -p[비밀번호]
     30}}}
     31
     32== 2. 디폴트 옵션 ==
     33mysqldump을 실행하면 {{{--opt}}} 옵션은 기본적으로 활성화되어 있다. 이 옵션은 다음 옵션을 지정하는 것과 동일하다.
     34* {{{--add-drop-table}}}: 테이블을 생성하는 SQL 문 앞에 다음과 같은 drop table 문을 추가한다. 이를 포함하지 않으려면 {{{--skip-add-drop-table}}}
     35{{{
     36DROP TABLE IF EXISTS `valve`;
     37}}}
     38* {{{--add-locks}}}: 이를 포함하지 않으려면 {{{--skip-add-locks}}}
     39{{{
     40LOCK TABLES `valve` WRITE;
     41... (INSERT INTO 문)
     42 UNLOCK TABLES;
     43}}}
     44* {{{--create-options}}}: ENGINE, DEFAULT CHARSET, COLLATE 같은 MariaDB 특유의 CREATE TABLE 옵션을 추가한다. 이를 포함하지 않으려면 {{{--skip-create-options}}}
     45{{{
     46CREATE TABLE `valve` (
     47  ...
     48) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
     49}}}
     50* {{{--quick}}}: 큰 테이블을 백업할 때 유용하다. 읽어들인 데이터를 메모리에 저장했다가 한꺼번에 출력하는 것이 아니라 읽어들이는 족족 바로 출력한다. 특별한 일이 없으면 이 옵션은 놔두자.
     51* {{{--extended-insert}}}: 한 INSERT 문에 여러 행의 데이터를 입력할 수 있게 한다. 이를 포함하지 않으려면 {{{--skip-extended-insert}}}
     52* {{{--lock-tables}}}: 백업을 하는 동안 특정 데이터베이스를 잠근다(READ LOCAL lock). 자세한 내용은 아래 참조. 이를 포함하지 않으려면 {{{--skip-lock-tables}}}
     53* {{{--set-charset}}}: 다음과 같은 문자셋 설정 관련 내용을 포함한다. 이를 포함하지 않으려면 {{{--skip-set-charset}}}
     54{{{
     55/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
     56/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
     57/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
     58/*!40101 SET NAMES utf8mb4 */;
     59
     60... (백업 내용)
     61
     62/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
     63/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
     64/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
     65}}}
     66* {{{--disable-keys}}}:아래와 같은 명령을 삽입한다. 모든 데이터가 삽입된 이후에 인덱스가 생성되므로 더 빠르게 복원할 수 있다. MyISAM 테이블의 unique key가 아닌 경우에만 유효하다. 이를 포함하지 않으려면 {{{--skip-disable-keys}}}
     67{{{
     68/*!40000 ALTER TABLE `valve` DISABLE KEYS */;
     69... (INSERT INTO 문)
     70/*!40000 ALTER TABLE `valve` ENABLE KEYS */;
     71}}}
     72
     73그 외에 참고할만한 디폴트 옵션은 다음과 같다.
     74* {{{--comments}}}: 테이블이나 컬럼에 대한 설명을 추가하는 것이 아니라, 프로그램 버전, 서버 버전, 호스트 등의 정보를 추가한다.
     75* {{{--default-character-set=utf8mb4}}}: 디폴트 문자셋을 utf8mb4로 지정한다.
     76
     77== 3. 특정 데이터베이스/테이블 백업 ==
     78만약 특정 데이터베이스 하나만 백업한다면 '''-B({{{--databases}}}) [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] ...''' 대신에 그냥 데이터베이스 이름 하나만 적어주면 된다.
     79{{{
     80mysqldump -R(--routines) -E(--events) -h venus.bookcube.co.kr -u root -pwisebooklogin elibarary |zstd -q -o backup.sql.zst
     81}}}
     82
     83만약 특정 테이블만 백업한다면 '''-B({{{--databases}}}) [백업할 데이터베이스 이름1] [백업할 데이터베이스 이름2] ...''' 대신에 그냥 데이터베이스 이름 뒤에 한 칸 띄고 테이블 이름을 적어주면 된다.  -R({{{--routines}}})와 -E({{{--events}}}) 옵션은 테이블에 종속된 것이 아니기 때문에 굳이 적어줄 필요는 없다. 다음 예는 occupant 데이터베이스의 house와 room 테이블을 백업하는 예이다.
     84{{{
     85mysqldump -t -n --skip-lock-tables -h occupant1.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data1.mysql
     86mysqldump -t -n --skip-lock-tables -h occupant2.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data2.mysql
     87mysqldump -t -n --skip-lock-tables -h occupant4.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data4.mysql
     88mysqldump -t -n --skip-lock-tables -h occupant5.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data5.mysql
     89mysqldump -t -n --skip-lock-tables -h occupant6.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data6.mysql
     90mysqldump -t -n --skip-lock-tables -h occupant7.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data7.mysql
     91mysqldump -t -n --skip-lock-tables -h occupant8.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data8.mysql
     92mysqldump -t -n --skip-lock-tables -h occupant9.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data9.mysql
     93mysqldump -t -n --skip-lock-tables -h occupant9.dasangng.co.kr -P 23306 -u root -p'smartgrid12#' --ssl occupant house room > occupant-data9b.mysql
     94mysqldump -t -n --skip-lock-tables -h occupant10.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data10.mysql
     95mysqldump -t -n --skip-lock-tables -h occupant11.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data11.mysql
     96mysqldump -t -n --skip-lock-tables -h occupant11b.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data11b.mysql
     97mysqldump -t -n --skip-lock-tables -h occupant12.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data12.mysql
     98mysqldump -t -n --skip-lock-tables -h occupant13.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data13.mysql
     99mysqldump -t -n --skip-lock-tables -h occupant14.dasangng.co.kr -u root -p'smartgrid12#' --ssl occupant house room > occupant-data14.mysql
     100}}}
     101
     102단, -B({{{--databases}}}) 옵션을 생략하면 SQL 문을 생성할 때 CREATE DATABASE 문을 생략한다. 따라서 복구할 때에도 데이터베이스를 지정해서 복구해야 한다.
     103{{{
     104zstdcat backup-file.sql.zst | mysql -u root -p[비밀번호] [복원할 데이터베이스]
     105}}}
     106
     107특정 테이블을 모두 백업하는 것이 아니라 조건에 맞는 것만 백업하려면 {{{--where}}} 옵션을 주면 된다.
     108{{{
     109mysqldump -t(--no-create-info) -n(--no-create-db) --skip-lock-tables occupant --where = "time < '2021-11-01'" | zstd -q -o occupant2-202110.sql.zst
     110}}}
     111
     112특정 데이터베이스가 아니라 전체 데이터베이스를 백업하려면 {{{--all-databases}}} 옵션을 주면 된다.
     113
     114== 4. 스키마와 데이터 분리 백업 ==
     115
     116스키마를 변경할 목적이라면 스키마와 데이터를 분리해서 백업받는 것이 편하다. -R({{{--routines}}})와 -E({{{--events}}}) 옵션은 스키마나 데이터 중 한 쪽에만 넣어주면 되는데, 용량이 작은 스키마 쪽에 넣어두는 것이 편하다.
     117
     118 스키마::
     119  '''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'''
     120 데이터::
     121  '''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'''
     122
     123* -B({{{--databases}}}) 옵션을 생략하면 SQL 문을 생성할 때 CREATE DATABASE 문을 생략한다.  따라서 -n({{{--no-create-db}}})는 특정 테이블만 백업할 때는 필요없다.
     124
     125== 5. 접근 권한 백업 ==
     126DB 접근 권한은 mysql 데이터베이스에 따로 저장되므로 mysqldump 명령에 의해 생성된 sql문에는 권한과 관련된 내용이 저장되지 않는다.
     127
     128mysql 데이터베이스에 저장되는 내용 중 프로시저와 함수는 -R({{{--routines}}}) 옵션으로, 이벤트 스케줄러 정보는 -E({{{--events}}}) 옵션으로 백업받을 수 있지만, 권한까지 백업받는 옵션은 없다. 따라서 권한까지 백업받으려면 직접 mysql 데이터베이스를 백업해야 한다.
     129
     130프로시저 권한은 mysql 데이터베이스의 procs_priv 테이블에 저장된다.
     131
     132 '''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'''
     133
     134DB 접근 권한은 mysql 데이터베이스의 db 테이블에 저장된다.
     135
     136 '''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'''
     137
     138마찬가지로 사용자까지 백업하려면 mysql 데이터베이스의 user 테이블을 백업하면 된다. 10.4부터는 global_priv 테이블을 백업해야 한다.
     139
     140 '''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'''
     141
     142== 6. 3.23 버전의 mysqldump ==
     1433.23 버전에서는 아래처럼 옵션을 줘서 사용하면 된다.
     144
     145mysqldump '''{{{--all}}} {{{--quick}}} -l({{{--lock-tables}}}) -h''' ''[mysql 서버 IP]'' '''-u''' ''[mysql 계정]'' '''-p'''''[암호] [백업할 데이터베이스 이름]'' > ''[생성할 sql 파일명]''
     146
     147=== 6.1. 상위 버전에서 3.x대로 다운그레이드를 할 때 주의점 ===
     148mysqldump를 이용해 생성한 sql 문을 보면 다음과 같은 부분이 있다.
     149{{{
     150/*!40000 ALTER TABLE tb_name DISABLE KEYS */;
     151... (INSERT INTO 문)
     152/*!40000 ALTER TABLE tb_name ENABLE KEYS */;
     153}}}
     154위의 구문은 4.0 미만 버전에서는 /* ... */ 을 주석처리하라는 뜻이다. 그런데 3.x 대 버전 중에는 이 구문을 제대로 해석하지 못해 에러를 발생시키는 경우가 있었다. 그래서 다음과 같이 옵션을 줘서 위 구문을 생성하지 않도록 해서 해결했었다.
     155
     156mysqldump {{{--skip-add-drop-table}}} {{{--skip-disable-keys}}} {{{--skip-add-locks}}} -h [mysql 서버 IP] -u [mysql 계정] -p[암호] -B({{{--databases}}}) [백업할 데이터베이스 이름] > [생성할 sql 파일명]
     157
     158지금은 3.x대 버전으로 되돌아갈 일도 없을 거고, 설령 그런 일이 있다 하더라도 버그가 고쳐졌을테니, 지금은 신경쓰지 않아도 될 것 같다.
     159
     160== 7. 논리적인 일관성이 중요한 경우의 백업 ==
     161현재 서비스 중이어서 계속 데이터가 변경되는 경우에는 백업받을 때 데이터의 논리적인 일관성을 신경써야 한다. 따라서 데이터가 변경되지 않도록 잠금을 걸어야 한다.
     162
     163=== 잠금의 종류 ===
     164 * READ LOCK
     165  - 읽기만 가능하게 잠금
     166 * READ LOCAL LOCK
     167  - READ LOCAL LOCK은 READ LOCK과는 달리 실행 중인 insert 문(non-conflictiong insert, concurrent insert)은 허용한다.
     168
     169=== -x({{{--lock-all-tables}}}) ===
     170- 백업을 하는 동안 모든 데이터베이스를 읽기만 가능하도록 잠근다(READ LOCK).
     171- 이 옵션을 설정하면 {{{--single-transaction}}}과 -l({{{--lock-tables}}}) 옵션은 자동으로 꺼진다.
     172- '''데이터베이스의 일관성은 보장되지만 백업이 진행되는 동안 쓰기가 불가능'''해지므로 대용량 데이터베이스인 경우엔 신중하게 해야 한다.
     173- ["MariaDB 바이너리 로그" 바이너리 로그]를 사용한다면 full 백업 이후의 변경 사항을 복구(point-in-time 복구, roll-forward)하기 위해서라도 -x({{{--lock-all-tables}}}) 명령 대신 {{{--master-data=2}}} 옵션을 주는 것이 좋다.
     174
     175=== -l({{{--lock-tables}}}) ===
     176- 특별한 옵션 지정을 하지 않았을 때 {{{--opt}}} 옵션에 의해 적용되는 디폴트 잠금이다. 따라서 사용하지 않으려면 명시적으로 {{{--skip-lock-tables}}} 이나 {{{--skip-opt}}} 옵션을 사용해야 한다.
     177- 백업을 하는 동안 특정 데이터베이스를 잠근다(READ LOCAL lock).
     178- READ LOCAL LOCK이므로 실행 중인 insert는 허용한다.
     179- 여러 데이터베이스를 백업할 때는 주의해야 한다. -l({{{--lock-tables}}})은 각 데이터베이스를 각각 잠근다. 그래서 '''데이터베이스간에 논리적인 일관성이 보장되지 않는다'''.
     180- '''백업이 진행되는 동안 특정 데이터베이스에 쓰기가 불가능'''해지므로 대용량 데이터베이스인 경우엔 신중하게 해야 한다.
     181- InnoDB나 BDB 같은 트랜잭션을 지원하는 테이블에는 {{{--single-transaction}}}이 더 나은 옵션이다. 테이블을 전혀 잠글 필요가 없기 때문이다.
     182
     183=== {{{--master-data=2}}} ===
     184- ["MariaDB 바이너리 로그" 바이너리 로그]가 활성화되어 있어야 한다.
     185- 이 옵션을 사용하기 위해선 RELOAD 권한이 필요하다.
     186- ["MariaDB 바이너리 로그" 바이너리 로그] 좌표(파일명과 위치)를 가리키는 CHANGE MASTER TO 문이 주석으로 삽입된다. 주석으로 삽입하기 때문에 실제 영향은 없다.
     187- 다음과 같이 바이너리 로그 좌표(파일명과 위치)를 이용해 full 백업 이후의 로그만 복원할 수 있다.
     188{{{
     189mysqlbinlog --start-position=1 "mysql bin로그 경로" > binlog.sql
     190}}}
     191- {{{--single-transaction}}}을 명시적으로 적어주지 않는다면 -x({{{--lock-all-tables}}}) 옵션이 자동으로 지정된다.
     192- -l({{{--lock-tables}}}) 옵션을 자동으로 끈다.
     193
     194=== {{{--single-transaction}}} ===
     195  - '''쓰기를 막지 않'''고도 일관성있게 데이터베이스를 백업할 수 있다.
     196  -  InnoDB 같은 트랜잭션을 지원하는 테이블에만 유용하다. MyISAM 같은 '''트랜잭션을 지원하지 않는 테이블에서는 논리적인 일관성이 보장되지 않는다'''.
     197  - -l({{{--lock-tables}}})과 같이 쓸 수 없다. -l({{{--lock-tables}}})을 자동으로 해제한다.
     198  - MySQL Cluster 테이블에는 사용할 수 없다. (확인 필요)
     199
     200=== 잠금 예 ===
     201예를 들어 ["MariaDB 바이너리 로그" 바이너리 로그]를 사용하지 않을 때의 full 백업은 다음과 같이 한다. full 백업을 진행하는 동안 쓰기가 금지되므로 신중하게 해야 한다.
     202{{{
     203mysqldump -x(--lock-all-tables) -E(--events) -A(--all-databases) -u [계정] -p[비밀번호] |zstd -q -o fullbackup.sql.zst
     204}}}
     205
     206트랜잭션을 지원하는 테이블만 사용한다면 다음이 더 좋은 옵션이다. 쓰기가 허용되므로 full 백업이 오래 걸려도 무방하다.
     207{{{
     208mysqldump --single-transaction -E(--events) -A(--all-databases) -u [계정] -p[비밀번호] |zstd -q -o fullbackup.sql.zst
     209}}}
     210
     211["MariaDB 바이너리 로그" 바이너리 로그]를 사용할 때의 full 백업은 ["MariaDB 증분 백업" 증분 백업] 참고.
     212
     213== 8. 주의 ==
     214* MariaDB 10.3 이상의 mysqldump여야 10.3에서 새로 생긴 mysql.transaction_registry 테이블을 백업할 수 있다. 따라서 이전 MariaDB 버전의 mysqldump는 MariaDB 10.3 이상에서 사용하면 안된다.
     215* ["MariaDB 시스템 버전드 테이블" 시스템 버전드 테이블]을 백업할 수 없다.
     216
     217----
     218[WikiStart 처음으로], ["MariaDB mysqlhotcopy" mysqlhotcopy], ["MariaDB 증분 백업" 증분 백업], ["MariaDB mariabackup" mariabackup]