[mysql] db backup & restore

개요

mysqldump 프로그램을 이용한 DB 백업하는 방법 정리한다. 

mysql 콘솔프로그램을 이용한 DB 복구하는 방법을 정리한다.

 

전체 백업 및 복구

rem #backup 
mysql\bin\mysqldump -h 127.0.0.1 --port 3306 -u user --password=pwd dbname > .\backup_dir\xxxx_20220820_backup.sql

rem #restore 
mysql\bin\mysql -h 127.0.0.1 --port 3306 -u root -p -D dbname < .\backup_dir\xxxx_20220820_backup.sql

 

특정 테이블 백업(스키마 + 데이터)

rem #backup : tab_test 테이블의 스키마와 데이터를 백업
mysql\bin\mysqldump -h 127.0.0.1 --port 3306 -u user --password=pwd --tables=tab_test db_test > .\backup_dir\db_test_tab_test_20220820_backup.sql

rem #restore 
mysql\bin\mysql -h 127.0.0.1 --port 3306 -u root -p -D db_test < .\backup_dir\db_test_tab_test_20220820_backup.sql

 

특정 테이블 데이터만 백업

rem #backup : tab_test 테이블의 데이터(--no-create-info)만 백업
mysql\bin\mysqldump -h 127.0.0.1 --port 3306 -u user --password=pwd --no-create-info --tables=tab_test db_test > .\backup_dir\db_test_tab_test_20220820_backup.sql

rem #restore 
mysql\bin\mysql -h 127.0.0.1 --port 3306 -u root -p -D db_test < .\backup_dir\db_test_tab_test_20220820_backup.sql

 

특정 테이블 스키마만 백업

rem #backup : tab_test 테이블의 스키마(--no-data)만 백업
mysql\bin\mysqldump -h 127.0.0.1 --port 3306 -u user --password=pwd --no-data --tables=tab_test db_test > .\backup_dir\db_test_tab_test_20220820_backup.sql

rem #restore 
mysql\bin\mysql -h 127.0.0.1 --port 3306 -u root -p -D db_test < .\backup_dir\db_test_tab_test_20220820_backup.sql

 

Procedure, Function, Trigger 백업 및 복구

rem #backup 
mysql\bin\mysqldump -h 127.0.0.1 --port 3306 -u user --password=pwd --routines --no-create-info --no-data --no-create-db --skip-opt dbname > .\backup_dir\xxxx_20220820_backup.sql

rem #restore 
mysql\bin\mysql -h 127.0.0.1 --port 3306 -u root -p -D dbname < .\backup_dir\xxxx_20220820_backup.sql

 

 

 

mysqldump.exe 

mysqldump는 데이터베이스 백업 프로그램.

mysqldump를 호출하는 데에는 일반적으로 세 가지 방법 존재함.

mysqldump [options] db_name [tables]

mysqldump [options] --databases db_name1 [db_name2 db_name3...]

mysqldump [options] --all-databases

db_name 다음에 테이블을 지명하지 않았거나 --databases 또는 --all-databases 옵션을 사용한다면, 전체 데이터베이스가 덤프된다.

 

mysqldump는 아래의 옵션을 지원한다:

• --help, -?

도움말을 출력하고 종료.

• --add-drop-database

DROP DATABASE 명령문은 각각의 CREATE DATABASE 명령문 전에 추가 한다.

• --add-drop-table

DROP TABLE 명령문을 각각의 CREATE TABLE 명령문 전에 추가한다.

• --add-locks

LOCK TABLES 과 UNLOCK TABLES 명령문을 사용해서 각각의 테이블 덤프를 둘러 싼다(surround). 이렇게 하면 덤프 파일을 다시 읽어올 때 보다 빠른 삽입을 실행할 수가 있다. Section 7.2.16, “INSERT 명령문의 속도”를 참조.

• --all-databases, -A

모든 데이터 베이스에 있는 모든 테이블을 덤프한다. 이것은 --databases 옵션을 사용해서 명령어 라인에서 모든 데이터 베이스 이름을 입력하는 것과 동일한 기능을 실행한다.

• --allow-keywords

키 워드 이름을 사용해서 컬럼을 생성하는 것을 허용한다.

• --character-sets-dir=path

문자 셋이 설치되어 있는 디렉토리. Section 5.11.1, “데이터 및 정렬을 위해 사용되는 문자 셋”를 참조할 것.

• --comments, -i

프로그램 버전, 서버 버전, 그리고 호스트와 같은 추가적인 정보를 덤프 파일에 기록한다. 이 옵션은 디폴트로 활성화 된다. --skip-comments를 사용하면, 디폴트 활성화를 없앨 수 있다.

• --compact

간략한 결과를 만들게 한다. 이 옵션은 코맨트를 없애주며 --skip-add-drop-table, --no-set-names, --skip-disable-keys, 그리고 --skip-add-locks 옵션을 활성화 시킨다.

• --compatible=name

다른 데이터 시스템 또는 구형 MySQL 서버와의 호환성을 보다 많이 갖도록 결과를 만든다. name의 값은 ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, 또는 no_field_options가 될 수 있다. 여러 개의 값을 사용하기 위해서는, 각각을 콤마로 구분시킨다. 이러한 값들은 서버 SQL 모드를 설정하기 위한 대응 값들과 동일한 의미를 갖게 된다. Section 5.2.5, “서버 SQL 모드”를 참조할 것.

 

이 옵션은 다른 서버와의 호환성을 보장하지는 않는다. 단지 덤프를 한 결과가 다른 SQL 서버와 호환성을 보다 많이 가지도록 만들어줄 뿐이다. 예를 들면, --compatible=oracle는 오라클 타입의 데이터 또는 코멘트 신텍스와 매핑되는 것은 아니다.

• --complete-insert, -c

컬럼 이름을 가지고 있는 완벽한 INSERT 명령문을 사용한다.

• --compress, -C

클라이언트 및 서버가 압축을 지원할 경우, 두 서버간에 전달되는 정보를 압축한다.

• --create-options

CREATE TABLE 명령문에 모든 MySQL 관련 테이블 옵션을 포함시킨다.

• --databases, -B

여러 개의 데이터 베이스를 덤프한다. 일반적으로, mysqldump는 명령어라인에 있는 첫 번째 이름을 데이터 베이스 이름을 간주하고 그 다음의 이름을 테이블 이름으로 간주한다. 이 옵션을 사용하면, 모든 이름 인수를 데이터 베이스 이름으로 간주하게 된다. CREATE DATABASE 및 USE 명령문은 각각의 새로운 데이터 베이스 전에 결과에 포함된다.

• --debug[=debug_options], -# [debug_options]

디버깅 로그를 작성한다. debug_options 스트링은 종종 'd:t:o,file_name'가 된다. 디폴트는 'd:t:o,/tmp/mysqldump.trace'.

• --default-character-set=charset_name

charset_name를 디폴트 문자 셋으로 사용한다. Section 5.11.1, “데이터 및 정렬을 위해 사용되는 문자 셋”을 참조. 만일 지정하지 않으면, mysqldump은 utf8를 사용한다.

• --delayed-insert

INSERT DELAYED 명령문을 INSERT 명령문 대신에 작성한다.

• --delete-master-logs

마스터 리플리케이션 서버에서, 덤프 연산을 실행한 후에 바이너리 로그를 삭제한다. 이 옵션은 자동으로 --master-data를 활성화 시킨다.

• --disable-keys, -K

각각의 테이블에 대해서, INSERT 명령문을 /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; 그리고 /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; 명령문을 사용해서 둘러싼다(surround). 이것은 모든 열이 삽입된 후에 인덱스가 생성되기 때문에 덤프 파일을 읽어 오는데 보다 빠른 속도가 나오게 된다. 이 옵션은 MyISAM 테이블에 대해서만 효과가 있다.

• --extended-insert, -e

여러 개의 VALUES 리스트를 가지고 있는 다중-열 INSERT 신텍스를 사용한다.이렇게 하면 덤프 파일이 작아지고 파일을 다시 읽어 올 때 삽입 속도를 빠르게 할 수가 있다.

• --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=..., --lines-terminated-by=...

이들 옵션은 -T 옵션과 함께 사용되며 LOAD DATA INFILE에 대한 대응 구문과 같은 의미를 가진다. Section 13.2.5, “LOAD DATA INFILE 신텍스”를 참조.

• --first-slave, -x

기능 삭제됨. 현재는 --lock-all-tables로 바뀌었음.

• --flush-logs, -F

덤프를 시작하기 전에 MySQL 서버 로그 파일을 플러시한다. 이 옵션은 RELOAD 권한을 필요로 한다. 만일 여러분이 이 옵션을 --all-databases (또는 -A) 옵션과 함께 결합해서 사용한다면, 로그는 각각의 덤프된 데이터 베이스에 대해서 플러시 된다는 점을 알아야 한다. 한가지 예외는 --lock-all-tables 또는 --master-data를 사용하는 경우이다: 이와 같은 경우, 로그는 모든 테이블이 잠기는 시점에 오직 한번만 플러시된다. 만일 동일한 시점에 덤프 및 로그 플러시가 일어나도록 하기 위해서는, --flush-logs를 --lock-all-tables 또는 --master-data와 함께 사용하도록 한다.

• --force, -f

테이블 덤프를 하는 동안 SQL 에러가 발생하더라도 계속 진행 시킨다.

• --host=host_name, -h host_name

주어진 호스트에 있는 MySQL 서버에서 데이터를 덤프한다. 디폴트 호스트는 localhost.

• --hex-blob

16진법(hexadecimal)을 사용해서 바이너리 컬럼을 덤프한다 (예를 들면, 'abc'는 0x616263가 된다). 이렇게 할 수 있는 데이터 타입은 BINARY, VARBINARY, 그리고 BLOB가 된다. MySQL 5.0.13까지는, BIT 컬럼도 해당된다.

• --ignore-table=db_name.tbl_name

주어진 테이블을 덤프하지 않는데, 이것은 데이터 베이스 및 테이블 이름을 사용해서 지정해야 한다. 여러 개의 테이블을 무시하기 위해서는, 이 옵션을 여러 번 사용한다.

• --insert-ignore

INSERT 명령문을 IGNORE 옵션과 함께 작성한다.

• --lock-all-tables, -x

모든 데이터 베이스에 걸쳐서 모든 테이블을 잠근다. 이것은 전체 덤프 주기에 대한 글로벌읽기 잠금을 통해 얻을 수 있다. 이 옵션은 자동으로 --single-transaction --lock-tables를 오프(Off)시킨다.

• --lock-tables, -l

덤프를 하기 전에 모든 테이블을 잠근다. MyISAM 테이블의 경우에는 동시 삽입을 허용하기 위해서 테이블을 READ LOCAL로 잠근다. InnoDB 및 BDB와 같은 트랜젝션이 되는 테이블의 경우, --single-transaction이 보다 좋은 옵션이 되는데, 그 이유는 이것은 테이블을 전혀 잠글 필요가 없기 때문이다.

 

여러 개의 데이터 베이스를 덤프할 때에는, --lock-tables은 각각의 데이터 베이스에 대해서 테이블을 개별적으로 잠근다는 점을 알아두기 바란다. 따라서, 이 옵션은 덤프 파일에 있는 테이블이 데이터 베이스간에 논리적으로 일관성을 가지는 것에 대해서는 보장을 하지 않는다. 서로 다른 데이터 베이스에 있는 테이블들은 완벽하게 틀린 상태에서 덤프가 된다.

• --master-data[=value]

바이너리 로그 파일 이름과 위치(position)을 결과에 작성한다. 이 옵션은 RELOAD 권한이 필요하고 바이너리 로그는 반드시 활성화 되어야 한다. 만일 이 옵션 값이 1 이면, 그 위치 및 파일 이름은 CHANGE MASTER 명령문 형태로 덤프 결과에 작성되는데, 이것은 여러분이 슬레이브를 설정하기 위해 이 SQL 덤프를 사용하는 경우에 슬레이브 서버로 하여금 마스터의 바이너리 로그에 있는 올바른 위치에서 시작을 하도록 만든다. 만일 이 옵션 값이 2와 같다면, CHANGE MASTER 명령문은 SQL 코멘트처럼 작성된다. 만일 값이 생략되면, 이것이 디폴트 동작이 된다.

 

--master-data 옵션은 --single-transaction을 함께 지정하지 않는 한, --lock-all-tables를 온(ON) 시킨다 (이와 같은 경우, 글로벌 읽기 잠금은 덤프가 시작되는 짧은 시점에만 얻을 수 있다). --single-transaction에 대한 설명을 함께 참조한다. 모든 경우에, 로그 상의 모든 동작은 정확히 덤프가 일어나는 시점에 발생을 한다. 이 옵션은 자동으로 --lock-tables를 오프(Off) 시킨다.

• --no-autocommit

각각의 덤프된 테이블에 대한 INSERT 명령문을 SET AUTOCOMMIT=0 과COMMIT 명령문안에 넣는다.

• --no-create-db, -n

이 옵션은 --databases 또는 --all-databases 옵션이 주어질 경우에 결과에 포함되는 CREATE DATABASE 명령문을 무력화 시킨다.

• --no-create-info, -t

각각의 덤프된 테이블을 다시 생성하는 CREATE TABLE 명령문을 작성하지 않는다.

• --no-data, -d

테이블에 대한 어떠한 열 정보도 작성하지 않는다. 이것은 테이블에 대해서 CREATE TABLE 명령문만을 덤프하고자 할 경우에 매우 유용하다.

• --opt

이 옵션은 축약형이다; 이것은 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset를 지정하는 것과 같다. 이 옵션은 빠른 덤프 연산을 실행하며 MySQL 서버로 빠르게 다시 읽혀지는 덤프 파일을 만들어 낸다.

이 옵션은 디폴트로 활성화 되어 있지만, --skip-opt를 사용해서 비 활성화 시킬 수가 있다. –opt에 의해서 활성화된 특정 옵션만을 비활성화 시키기 위해서는, 해당 옵션의 --skip 형태를 사용한다; 예를 들면, --skip-add-drop-table 또는 --skip-quick.

• --order-by-primary

주요(primary) 키 또는 맨 처음의 유니크 인덱스(만일 인덱스가 존재한다면)를 사용해서 각각의 테이블 열을 정렬한다. 이것은 InnoDB 테이블 안으로 집어넣을 MyISAM 테이블을 덤프할 때 유용하게 사용되지만, 덤프 자체를 매우 오래 걸리게 한다.

• --password[=password], -p[password]

서버에 접속을 할 때 사용하는 패스워드.

• --port=port_num, -P port_num

접속용으로 사용할 TCP/IP 포트 번호.

• --protocol={TCP|SOCKET|PIPE|MEMORY}

접속용 프로토콜.

• --quick, -q

이 옵션은 대형 테이블을 덤프할 때 유용하다. 이것은 mysqldump로 하여금 테이블에 대한 열을 서버에서 한번에 한 열씩 추축하도록 만들고 추출한 열을 쓰기 전에 메모리에 버퍼링 하도록 만든다.

• --quote-names, -Q

인용 부호를 사용해서 데이터 베이스, 테이블, 그리고 컬럼 이름을 둘러 쌓도록 한다. 만일 ANSI_QUOTES SQL 모드가 활성화 되어 있다면, 그 이름도 인용 부호화 시킨다. 이 옵션은 디폴트로 활성화 되어 있다. 이것은 --skip-quote-names으로 비 활성화 시킬 수 있으나, 이 옵션은 --quote-names을 활성화 시킬 수 있는 –-compatible과 같은 옵션 다음에 주어져야 한다.

• --result-file=file, -r file

주어진 파일로 결과를 직접 넣는다. 이 옵션은 윈도우에서 새 라인 문자 ‘\n’가 ‘\r\n’ 캐리지 리턴/새 라인 시퀀스로 변환되지 못하도록 하기 위해서 사용된다.

• --routines, -R

덤프된 데이터 베이스에서 스토어드 루틴(함수 및 프로시저)를 덤프한다. --routines을 사용해서 만들어지는 결과는 CREATE PROCEDURE 루틴을 재 생성하기 위한 CREATE FUNCTION 명령문을 갖게 된다. 하지만, 이러한 명령문들은 루틴 생성 및 수정 타임 스탬프와 같은 속성을 가지지 않는다. 이것은 루틴이 리로드(reload)될 때, 리로드 시간과 동일한 타임 스탬프를 가지고서 생성된다는 것을 의미한다.

 

만일 여러분이 재 생성될 루틴이 원래의 타임 스탬프 속성을 가지도록 하기 위해서는, --routines를 사용하지 말도록 한다. 대신에, mysql 데이터 베이스에 대해 적절한 권한을 가지고 있는 MySQL 계정을 사용해서 mysql.proc 테이블의 내용물을 직접 덤프 및 리로드 하도록 한다.

 

이 옵션은 MySQL 5.0.13 에 추가되었다. 이 버전 이전에는 스토어드 루틴을 덤프할 수가 없었다. 루틴 DEFINER 값은 5.0.20 이후에 덤프가 되었다. 이것은 5.0.20 이전에는, 루틴이 리로드될 때, 리로딩 사용자에 대해서 디파이너(definer) 셋을 가지고 생성된다는 것을 의미하는 것이다. 만일 루틴이 원래의 디파이너를 가지고 재 생성되도록 하고자 한다면, 앞에서 설명한 방식으로 mysql.proc 테이블의 내용물을 직접 덤프 및 로드한다.

• --set-charset

SET NAMES default_character_set를 결과에 추가한다. 이 옵션은 디폴트로 활성화 된다. SET NAMES 명령문을 무시하기 위해서는, --skip-set-charset를 사용한다.

• --single-transaction

이 옵션은 서버에서 데이터를 덤프하기 전에 BEGIN SQL 명령문을 실행한다. 이것은 InnoDB 및 BDB와 같은 트랜젝션이 되는 테이블에서만 유용한데, 그 이유는 이것이 BEGIN이 다른 어플리케이션을 블러킹하지 않은 채로 입력될 때 데이터 베이스를 일관성 있게 담프하기 때문이다.

이 옵션을 사용할 때, 여러분은 InnoDB 테이블만이 일관성 있게 덤프된다는 점을 알고 있어야 한다. 예를 들면, 이 옵션을 사용할 때 덤프되는 MyISAM 또는 MEMORY 테이블은 상태가 변경될 수도 있다.

 

--single-transaction 옵션과 --lock-tables 옵션은 상호 배타적인데(mutually exclusive), 그 이유는 LOCK TABLES이 암묵적으로 실행되는 트랜젝션을 연기 시키기 때문이다.

대형 테이블을 덤프하기 위해서는, 이 옵션을 –quick과 결합해서 사용한다.

• --socket=path, -S path

localhost에 접속하는 경우, 유닉스 소켓 파일 또는, 윈도우의 네임드 파이프 이름.

• --skip-comments

--comments 옵션에 대한 설명을 참조한다.

• --tab=path, -T path

탭으로 구분된 데이터 파일을 만든다. 각가의 덤프 테이블의 경우, mysqldump은 테이블을 생성하는 CREATE TABLE 명령문을 갖는 tbl_name.sql 파일과, 그것의 데이터를 가지고 있는 tbl_name.txt 파일을 생성한다. 이 옵션 값은 파일을 작성하는 디렉토리가 된다.

디폴트로는t, .txt 데이터 파일이 컬럼 값과 각 라인의 끝에 있는 새 라인(newline) 값 사이에 탭 문자를 사용해서 포맷된다. 이 포맷은 --fields-xxx 및 --lines--xxx 옵션을 사용해서 명확하게 지정될 수 있다.

 

Note: 이 옵션은 mysqldump가 mysqld 서버가 구동되는 서버에서 실행될 때에만 사용될 수 있다. 여러분은 반드시 FILE 권한이 있어야 하고, 또한 서버는 반드시 여러분이 지정하는 디렉토리에 파일을 작성할 수 있어야 한다.

• --tables

--databases 또는 -B 옵션을 무력화 시킨다. 이 옵션 다음에 나오는 모든 이름 인수는 테이블 이름으로 간주된다.

• --triggers

각각의 덤프 테이블에 대한 트리거를 덤프한다. 이 옵션은 디폴트로 활성화 되어 있다; --skip-triggers로 비 활성화 시킬 수 있다.이 옵션은 MySQL 5.0.11 에 추가 되었다. 이전에는, 트리거를 덤프할 수 없었다.

• --tz-utc

SET TIME_ZONE='+00:00'를 덤프 파일에 추가해서 TIMESTAMP 컬럼이 서로 다른 타임 존에 있는 서버간에 덤프되고 리로드될 수 있도록 한다. 이 옵션을 사용하지 않으면, TIMESTAMP 컬럼은 로컬 및 목적 서버의 타임 존에 덤프 및 리로드 되고, 이 결과로 인해 값이 변하게 된다. --tz-utc는 디폴트로 활성화 되어 있고, --skip-tz-utc를 사용해서 비활성화 시킬 수가 있다. 이 옵션은 MySQL 5.0.15 에서 추가 되었다.

• --user=user_name, -u user_name

서버에 접속할 때 사용되는 MySQL 사용자 이름.

• --verbose, -v

버보스 모드 (Verbose mode). 프로그램이 실행하는 정보를 보다 자세히 출력한다.

• --version, -V

버전 정보를 출력하고 빠져 나온다.

• --where='where_condition', -w 'where_condition'

주어진 WHERE 조건문에 의해 선택된 열만을 덤프한다. 만일 스페이스 또는 다른 문자가 여러분이 사용하는 명령어 해석기에서 특별하게 인식되는 경우에는 인용부호를 사용해야 한다.

Examples:

--where="user='jimf'"

-w"userid>1"

-w"userid<1"

• --xml, -X

덤프 결과를 XML 형태로 출력한다.

또한, --var_name=value 신텍스를 사용해서 아래의 변수를 지정할 수도 있다:

• max_allowed_packet

클라이언트/서버 통신용 버퍼의 최대 크기. 최대 크기는 1GB.

• net_buffer_length

클라이언트/서버 통신용 버퍼의 초기 크기. 다중-열-삽입 명령문을 생성할 때 (--extended-insert or –opt 옵션을 사용하는 것과 같이), mysqldump는 열을 최대 net_buffer_length 길이 만큼 만든다. 만일 여러분이 이 변수의 값을 늘린다면, MySQL 서버에 있는 net_buffer_length 변수가 최소한 이 만큼의 크기가 되는지를 확인해야 한다.