[MySQL] dump/backup/restore (procedures, function, triggers , events) Options

1. 백업 종류

- Hot Backup : DB 서버가 온라인 상태에서 DB를 백업 하는 것

 

- Cold Backup : DB 서버를 중단시키고 백업하는 방법  

 

(1) Data 디렉토리 백업

​- Data 디렉토리를 정기적으로 백업하고 문제 발생했을 때 덮어쓰면 된다.

 

Data 디렉토리 확인

SHOW VARIABLES LIKE 'datadir';

 

(2) mysqldump를 사용하는 방법

​- 백업 시에 데이터베이스에 락을 걸수 없어 변경이 발생하면 다시 백업해야한다.

- 전체 데이터베이스 또는 특정 데이터베이스를 백업하거나 특정테이블만 백업할 수 있다.

 

(3) mysqlhotcopy를 사용하는 방법

- FLUSH TABLES, LOCK TABLES와 cp또는 scp를 이용하여 백업을 진행한다.

- MyISAM과 ARCHIVE 스토리지 엔진으로 구성된 테이블을 백업할 때 유용하다.

- Unix 계열의 OS를 사용하는 서버에서만 사용이 가능하다.

- 이 스크립트를 사용하기 위해 백업할 테이블에 대한 SELECT 권한과 FLUSH TABLES를 하기 위한 RELOAD 권한 그리고 LOCK TABLES 권한이 필요하다.

 

(4) xtraback을 사용하는 방법

- MySQL 서버를 중단하지 않고 InnoDB를 핫백업할 수 있다.

- InnoDB Hot Backup은 핫백업을 지원하나 상용이고, xtraback은 무료로 사용이 가능.

2. MySQLDump 사용 백업

 

 

(1) 사용법

 

> mysqldump [OPTIONS] database [tables] > mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] > mysqldump [OPTIONS] --all-databases [OPTIONS]

 

(2) 주요 옵션

 

① Connection Options

옵션	의미	비고
--host, -h	접속 하려는 서버의 host 정보. default값은 localhost	--host=host name, -h host name
--password, -p	접속 하려는 서버의 user의 패스워드	--password[=password], -p[password]
--port, -P	접속하려는 서버의 port	--port=port num,-P port num
--socket, -S	localhost로 접속하려는 경우 접속에 사용할 소켓 파일을 지정	--socket=path, -S path
--user, -u	접속하려는 서버의 user명	--user=user name, -u user name

 

② DDL Options

옵션	의미
--add-drop-database	각 CREATE DATABLE 문 앞에 DROP DATABLE 문을 작성합니다. 이 옵션은 일반적으로 --all-database 또는 --database 옵션과 함께 사용됩니다. 이 옵션 중 하나를 지정하지 않으면 CREATE DATABLE 문이 작성되지 않기 때문입니다.
--add-drop-table	각 CREATE TABLE 문 앞에 Drop TABLE 문을 작성합니다.
--add-drop-trigger	각 CREATE 트리거 문 앞에 DROP 트리거 문을 작성합니다.
--no-create-db,-n	--database 또는 --all-database 옵션이 제공되는 경우 출력에 다른 방법으로 포함된 CREATE DATABLE 문을 표시하지 않습니다.
--no-create-info, -t	각 덤프 테이블을 생성하는 CREATE TABLE 문을 제외합니다.

 

③ Filtering Options

옵션	의미
--all-databases, -A	모든 데이터베이스의 모든 테이블을 덤프합니다. 이것은 --database 옵션을 사용하고 명령줄에 있는 모든 데이터베이스의 이름을 지정하는 것과 같습니다.
--databases, -B	여러 데이터베이스를 덤프합니다. 일반적으로 mysqldump는 명령줄의 이름 인수를 데이터베이스 이름으로 처리하고 다음 이름을 테이블 이름으로 처리합니다. 이 옵션을 사용하면 모든 이름 인수를 데이터베이스 이름으로 처리합니다. CREATE DABASE 및 USE 문은 각 새 데이터베이스 앞에 출력에 포함됩니다. 이 옵션은 performance_schema 데이터베이스를 덤프하는 데 사용될 수 있으며, 일반적으로 --all-database 옵션에서도 덤프되지 않습니다. (또한 --skip-lock-tables 옵션을 사용합니다.)
--events, -E	출력에 덤프된 데이터베이스에 대한 이벤트 스케줄러 이벤트를 포함합니다. 이 옵션을 사용하려면 해당 데이터베이스에 대한 Event 권한이 필요합니다. --events를 사용하여 생성된 출력에는 이벤트를 생성하기 위한 CREATE EVENT 문이 포함되어 있습니다.
--routines, -R	출력에 덤프된 데이터베이스에 대한 저장 루틴(절차 및 함수)을 포함합니다. 이 옵션을 사용하려면 글로벌 SELECT 권한이 필요합니다. --routines를 사용하여 생성된 출력에는 루틴을 생성하기 위한 CREATE PROCEDURE 및 CREATE FUNCTION 문이 포함되어 있습니다.
--triggers	출력에 각 덤프 테이블에 대한 트리거를 포함합니다. 이 옵션은 기본적으로 활성화되어 있습니다.  --skip-triggers로 비활성화합니다. 테이블의 트리거를 덤프하려면 테이블에 대한 트리거 권한이 있어야 합니다.
--tables	--database 또는 -B 옵션을 재정의합니다. mysqdump는 옵션 뒤에 오는 모든 이름 인수를 테이블 이름으로 간주합니다.
--no-data, -d	테이블 행 정보를 쓰지 마십시오.(즉, 테이블 내용을 덤프하지 않음) 이 기능은 테이블의 CREATE TABLE 문만 덤프하려는 경우(예: 덤프 파일을 로드하여 테이블의 빈 복사본을 만드는 경우) 유용합니다.
--ignore-table	--ignore-table=db_name.tbl_name   지정된 테이블을 덤프하지 마십시오. 이 테이블은 데이터베이스와 테이블 이름을 모두 사용하여 지정해야 합니다. 여러 테이블을 무시하려면 이 옵션을 여러 번 사용하십시오. 이 옵션을 사용하여 보기를 무시할 수도 있습니다.
--ignore-error	--ignore-error=error[,error]...   지정된 오류를 무시합니다. 옵션 값은 sqldump 실행 중 무시할 오류를 지정하는 쉼표로 구분된 오류 번호 목록입니다. 모든 오류를 무시하도록 --force 옵션도 제공되면 --force가 우선합니다.
--where, -w	--where='where_condition', -w 'where_condition' 지정된 WHERE 조건으로 선택한 행만 덤프합니다. 명령 인터프리터의 특수 문자를 포함하는 경우 조건 주위의 따옴표는 필수입니다.

 

④ Transactional Options 

옵션	의미
--add-locks	각 테이블 덤프를 LOCK TABLE 및 잠금 해제 TABLE 문으로 둘러쌉니다. 따라서 덤프 파일이 다시 로드될 때 삽입 속도가 빨라집니다.
--flush-logs, -F	d덤프를 시작하기 전에 MySQL 서버 로그 파일을 플러시합니다. 이 옵션을 사용하려면 RELOAD 권한이 필요합니다. --all-databases 옵션과 같이 사용하는 경우 각 database를 dump 할때 마다 flush가 일어나게 되고, --lock-all-tables, --master-data, --single-transaction과 같이 실행되는 경우 정확히 같은 순간에 flush와 dump가 같이 일어납니다.
--flush-privileges	mysql 데이터베이스를 덤프한 후 덤프 출력에 PLUSH PRIGES 문을 추가합니다. 이 옵션은 덤프에 mysql 데이터베이스와 mysql 데이터베이스의 데이터에 따라 적절한 복원을 위해 달라지는 다른 데이터베이스가 들어 있을 때마다 사용해야 합니다. 덤프 파일에 PLUSH PRIGUES 문이 포함되어 있으므로 파일을 다시 로드하려면 해당 문을 실행할 수 있는 충분한 권한이 필요합니다.
--lock-all-tables, -x	모든 데이터베이스의 모든 테이블을 잠급니다. 전체 덤프 기간 동안 글로벌 읽기 잠금을 획득하면 됩니다. 이 옵션은 단일 트랜잭션 및 --lock-table을 자동으로 해제합니다..
--lock-tables, -l	각 덤프된 데이터베이스에 대해 덤프하기 전에 덤프할 테이블을 모두 잠급니다. 테이블은 READ LOCAL로 잠겨 MyISAM 테이블의 경우 동시 삽입을 허용합니다. InnoDB와 같은 트랜잭션 테이블의 경우 테이블을 잠글 필요가 없으므로 --single-transaction은 --lock-tables보다 훨씬 더 나은 옵션입니다. --lock-tables는 각 데이터베이스의 테이블을 별도로 잠그기 때문에, 이 선택사항은 덤프 파일의 테이블이 데이터베이스 간에 논리적으로 일치하는지 보장하지 않습니다. 다른 데이터베이스의 테이블은 완전히 다른 상태로 버려질 수 있습니다. --opt와 같은 일부 옵션은 자동으로 --lock-table을 활성화합니다. 이 옵션을 재정의하려면 옵션 목록 끝에 있는 --skip-lock-tables을 사용하십시오.
--single-transaction	이 옵션은 데이터를 덤프하기 전에 트랜잭션 격리 모드를 REETABLE READ로 설정하고 START TRANSION SQL 문을 서버로 보냅니다. InnoDB와 같은 트랜잭션 테이블에서만 유용하며, START TRANSION이 어떤 응용 프로그램도 차단하지 않고 실행되었을 때 데이터베이스의 일관성 있는 상태를 덤프하기 때문입니다. 이 옵션을 사용할 때는 InnoDB 테이블만 일관된 상태로 덤프됩니다. 예를 들어 이 옵션을 사용하는 동안 덤프된 MyISAM 또는 MEMORY 테이블은 여전히 상태를 변경할 수 있습니다. --single-transaction 덤프가 진행 중인 동안 유효한 덤프 파일(정확한 테이블 내용 및 이진 로그 좌표)을 보장하기 위해 다른 연결에서는 ALTER TABLE, CREAT TABLE, DROFF TABLE, TROFFNATE TABLE, TABLE 등의 문을 사용하지 마십시오. 일관된 읽기는 이러한 문과 분리되지 않으므로 덤프할 테이블에서 읽기를 사용하면 mysqldump에서 수행한 SELECT가 테이블 내용을 검색하여 잘못된 내용을 얻거나 실패할 수 있습니다. 잠금 테이블은 보류 중인 트랜잭션을 암시적으로 커밋하기 때문에 --single-transaction 옵션과 --lock-tables 옵션은 상호 배타적입니다. 큰 테이블을 덤프하려면 --single-transaction 옵션을 --quick 옵션과 결합합니다.
--no-autocommit	각각의 dump되는 table의 앞 뒤에 SET autocommit=0 과 COMMIT 구문을 넣어 작성.
--order-by-primary	데이터를 primary key 또는 가장 첫 번째 unique index에 맞게 정렬되어 작성. 이것은 MyISAM이나 InnoDB에서 유용하게 사용. 하지만, dump 작업은 시간이 더 오래 걸릴 수 있음.

 

⑤ Performance Options 

옵션	의미
--delayes-insert	트랜잭션을 지원 하지 않는 테이블(MyISAM)과 같은 테이블을 위한 지원 옵션. INSERT 대신 INSERT_DELAYED를 사용하도록 dump 파일을 작성.
--disable-keys, -K	테이블의 Insert 구문을 작성할 때 /*!40000 ALTER TABLES tbl_name DISABLE KEYS */; /*!40000 ALTER TABLE tbl_name ENABLE KETS */;를 추가하여 작성. 로딩할 때 Key에 대한 제약사항을 체크하지 않게 하여 좀 더 빨리 로딩될 수 있게 함. 이 옵션은 nonunique index를 사용하는 MyISAM 테이블인 경우 효과가 좋음.
--extended-insert, -e	여러 데이터를 한 문장의 insert문으로 insert되도록 구문을 작성.
--insert ignore	dump파일 작성시 Insert 대신에 Insert Ignore 구문을 사용하도록 작성.
--opt	-add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, --set-charset의 기능을 하는 옵션으로 일반적으로 dump받을 때 사용하는 옵션. 사람들이 가장 많이 사용하는 옵션들을 모아 놓은 것. 이 옵션은 default로 명시하지 않아도 사용. 만약 사용하고 싶지 않다면 skip-opt를 사용.
--quick, -q	이 옵션은 mysqldump 실행 시 데이터를 메모리에 로딩하지 않고 직접 읽어서 작성하게 하여 성능을 향상 시킬 수 있는 옵션.
--skip-opt	--opt 옵션을 사용하고 싶지 않은 경우 사용. 대부분의 MySQL 5.x는 --opt 옵션이 default로 enable 되어 있음.

 

⑥ Format Options 

옵션	의미
--compact	이 옵션은 다음의 옵션들을 enable. --skip-add-drop-table  --skip-add-locks --skip-comments --skip-disable-keys  --skip-set-charset
--compatible	--compatible=name 이 옵션은 다른 DBMS나 MySQL 이전 버전에 맞게 dump 파일을 만들고자 하는 경우 사용. MySQL : mysql323, mysql40 다른 DBMS : postgresql, oracle, mssql, db2, maxdb 그 외 : ansi, no_key_options, no_table_options, no_field_options 여러 값들을 사용하기 위해, 콤마로 구분하여 사용할 수 있음. 이 옵션을 사용함으로서 호환성이 보장되는 것은 아니고 호환성을 높여줄 수 있음
--complete-insert, -c	INSERT 구문 작성시 컬럼 이름을 전부 포함하여 작성.
--hex-blob	BINARY, VARBINARY, BLOB, BIT 컬럼에 대해서 값을 명시할 때 hexadecimal 형태로 기술.
--quote-names, -O	식별자를 "'"를 사용하여 모두 감쌉. 만약, ANSI_QUOTES SQL MODE인 경우네는 """를 사용하여 감쌉.  enable이 기본.

 

⑦ 그외 옵션

옵션	의미
--default-character-set	기본 character set으로 charset_name을 사용하도록 설정. default는 utf8로 사용. --default-character-set=charset name
--no-set-names, -N	--set-names 옵션을 disable
--set-charset	SET NAMES default_char_set을 기술.
--help, -?	도움말을 보여 줌.
--version, -V	버전 정보를 보여 줌.
--verbose, -v	Verbose 모드로 이 프로그램의 정보를 자세히 보여 줌.
--force, -f	에러가 발생해도 계속 작업이 진행되게 함.
--dump-date	dump 파일의 생성 일자를 comment로 추가.
--comments	dump 파일에 추가적인 정보를 기술.
--allow-keywords	컬럼 이름으로 keyword를 사용하는 것을 허용.
max_allowed_packet	client와 server 사이의 buffer의 max size를 결정하는 variable. dump시에 큰 데이터를 받아야 하는 경우 이 variable의 값을 크게 늘려 줘야 함.
net_buffer_length	이 variable은 client와 server 사이의 buffer의 initial size를 결정하는 variable. 이 variable은 여러 데이터를 하나의 INSERT 구문으로 작성하고자 하는 경우 늘려줘야 함.

​

(3) 백업 예

① 전체 데이터베이스 백업

 

MySQLDump -u아이디 -p암호 --all-databases > 파일명.sql Ex) mysqldump -uroot -ppassword --all-databases > alldatabase.sql

 

② 특정 데이터베이스 백업

 

MySQLDump -u아이디 -p암호 DB명 > 파일명.sql Ex) mysqldump -uroot -ppassword test_db > test_db.sql

 

③ 특정 테이블만 백업

 

MySQLDump -u아이디 -p암호 DB명 Table명 > 파일명.sql Ex) mysqldump -uroot -ppassword test_db test_table > test_table.sql

 

④ 특정 여러 테이블을 백업

 

MySQLDump -u아이디 -p암호 -B DB명 --tables Table명1 Table명2 Table명3 > 파일명.sql Ex) mysqldump -uroot -ppassword -B test_db test_table1 test_table2 test_table3 > test_tables.sql

 

⑤ 특정 테이블의 데이터 검색 백업

 

MySQLDump -u아이디 -p암호 DB명 Table명 -w "조건" > 파일명.sql Ex) mysqldump -uroot -ppassword test_db test_table -w "name='홍길동' > test_table.sql

 

⑥ 특정 데이터베이스 백업(데이터 미포함)

 

MySQLDump -u아이디 -p암호 [옵션] DB명 > 파일명.sql Ex) mysqldump -uroot -ppassword --no-data test_db > test_dbschema.sql

 

⑦ InnoDB에서 트리거, 프로시저, 함수 포함하여 백업하기

- 트리거는 default값으로 백업이 실행되나 저장 프로시저는 백업되지 않는다. 

- 저장 프로시져가 백업되게 하기 위해서는 옵션에 --routines 을 넣어줘야 한다.  

 

mysqldump -uroot -ppassword --routines northwind > northwind.sql

 

⑧ 트리거, 프로시저, 함수만 백업하기

mysqldump -uroot -ppassword --routines --no-create-info --no-data --no-create-db --skip-opt northwind > northwind_only_sp_trigger_function.sql

 

(4) 백업 배치 파일 생성

BackupMySQL.bat

 

 

@echo off   mysqldump -uroot -ppassword world --result-file="D:\Backup_%date%.sql" echo Done!

 

[실행] : BackupMySQL.bat

[결과] : 'Backup_2017-03-09.sql' 파일 생성

 

(5) 복원

​- DB가 존재 하지 않는 경우 미리 생성 후 진행 하여야 함.

- [주의] 복원 명령시 기존의 테이블은 제거된 후 재생성하여 복원됨.(기존 데이터는 삭제됨) 

 

MySQL -u아이디 -p암호 DB명 < 파일명.sql  MySQL -u아이디 -p암호 DB명 Table명 < 파일명.sql MySQL -u아이디 -p암호 DB명 Table명 < 파일명.sql