반응형
mysqldump 명령과 옵션의 기본 구성
mysqldump [DB 접속 정보] [dump 옵션] [dump 대상 옵션] > /tmp/dump_data.sql
mysqldump는 기본적으로 모든 출력물을 화면(stdout)으로 보내게 된다.
그래서 이 결과를 백업용이나 데이터 이관용으로 사용하기 위해서는 표준 출력을 파일로 리다이렉션해서 저장해야 한다.
DB 접속 정보
- --host : 데이터베이스 호스트 정보
- --user : 접속 데이터베이스 계정
- --password : 접속 데이터베이스 계정의 비밀번호
- --port : 접속 포트 번호
- --socket : 접속시 사용할 소켓 파일의 경로
dump 옵션
- --single-transaction :
dump를 하나의 트랜잭션을 이용해서 실행함
(InnoDB 스토리지 엔진을 사용하는 테이블에 대해서는 Lock없이 일관된 덤프를 받을 수 있음).
자세한 내용은 아래 "일관된 데이터 덤프 받기" 참조 - --flush-logs : dump를 시작하는 시점에 binary log를 rotation 시키도록 한다.
Binary log를 증분 백업용으로 사용하는 경우, 풀백업과 증분 백업의 구분을 명확히 할 수 있다. - --master-data :
이 옵션이 명시되면, dump 파일의 헤더 부분에 CHANGE MASTER TO 구문을 포함시키며,
이 구문에는 덤프 시작 시점의 Binary log 파일명과 위치 정보 및 호스트 정보를 포함하고 있다.
이 값을 1로 설정하면 CHANGE MASTER TO 구문이 실제 실행 가능한 형태로 포함되며,
2로 설정되면 SQL 코멘트 형태로 참조만 할 수 있도록 포함된다.
가끔 Binary log가 활성화되지 않은 서버에서 실행 시 에러를 유발하기도 하므로
반드시 먼저 테스트를 해볼 것을 권장한다. - --opt | --skip-opt :
opt 옵션은 여러 개의 옵션들을 묶어 놓은 별명과 같은 옵션이며
(--add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert,
--lock-tables, --quick, --set-charset) 옵션들을 포함한다.
때때로(create db, table등을 포함할지 말지 제어시) mysqldump의 옵션을 상세하게
제어하고자 할 경우에는 skip-opt 옵션으로 비활성화시킨 후, 필요한 것들만 직접 명시해주는 것이 좋다. - --quick :
일반적으로 mysqldump는 테이블의 데이터들을 모두 Client의 메모리에 모두 로딩한 후,
파일에 쓰기를 시작하게 되는데, 이 옵션이 활성화되면 Client의 메모리에 버퍼링하지 않고
바로 파일로 쓰거나 화면으로 출력하게 된다. 이 옵션은 opt 옵션에 포함되어서 자동으로 활성화되기 때문에,
(큰 테이블이 있는 경우) skip-opt를 사용하는 경우에는 quick 옵션을 별도로 명시해줘야 한다. - --extended-insert :
mysqldump는 테이블의 데이터를 INSERT 문장 형태로 출력하게 되는데,
이 옵션을 사용하면 확장 형태의 INSERT문장으로 데이터를 덤프하게 된다.
이런 형태의 덤프는 다시 로드하는 작업을 빠르게 해줄 수 있다.
(확장 형태의 INSERT 문장이라는 것은
"INSERT INTO tab VALUES ('1','2'),('2','3'),('3','4');"와 같은 형태의 INSERT 문장을 의미한다.) - --add-drop-database :
덤프 파일의 내용에 "DROP DATABASE ..." 명령을 포함하지 않도록 한다. - --add-drop-table :
덤프 파일의 내용에 "DROP TABLE ..." 명령을 포함하지 않도록 한다. - --no-create-db :
mysqldump 의 옵션에 --databases 또는 --all-databases 옵션이 명시되지 않으면,
기본적으로 "CREATE DATABASE ..." 명령이 포함되는데,
종종 이 명령이 필요치 않은 경우가 있을 수 있으며 이 때에는 이 옵션을 활성화 해주면 된다. - --no-create-info :
덤프 파일의 내용에 "CREATE TABLE ..." 명령을 포함하지 않도록 한다. - --create-options :
덤프 파일에 "CREATE TABLE ..." 명령이 출력되는 경우,
초기 테이블 생성시 사용했던 테이블 생성 옵션들을 모두 포함하도록 한다. - --no-data :
데이터는 덤프하지 않고 테이블의 구조만 덤프하도록 한다. - --hex-blob :
덤프 대상 테이블중에서 이진 값을 가지는 컬럼의 값들을 16진수 문자열로 출력한다. - --routines :
덤프시에 스토어드 프로시져와 함수를 출력하도록 한다. - --triggers :
덤프시에 트리거를 출력하도록 한다.
dump 대상 옵션
- --all-databases :
이 옵션을 명시하면 현재 서버의 모든 데이터베이스를 덤프하게 된다.
이 옵션으로 덤프를 받게 되면 기본적으로 "USE <데이터베이스명>;" 명령이 덤프 내용에 포함된다. - --databases [database1] [database2] ... :
이 옵션을 명시하면 현재 서버에서 명시된 데이터베이스들을 덤프하게 된다.
이 옵션으로 덤프를 받게 되면 기본적으로 "USE <데이터베이스명>;" 명령이 덤프 내용에 포함된다. - [database1] :
별도의 옵션없이 mysqldump 명령의 마지막에 특정 데이터베이스명만을 명시하면
해당 데이터베이스의 내용만을 덤프하게 된다.
이 경우에는 "USE <데이터베이스명>;" 명령이 덤프 파일에 포함되지 않는다.
그래서 데이터베이스를 덤프 받아서 동일 데이터베이스에 다시 적재하고자 하는 경우
(덤프받은 파일을 동일 서버의 다른 데이터베이스명으로 적재하고자 하는 경우) "USE <데이터베이스명>;" 때문에
작업이 쉽지 않은데 이런 경우에는 이 옵션으로 덤프를 받으면
쉽게 동일 MySQL 서버에 다른 데이터베이스명으로 적재할 수 있다. - [database1] [table1] [table2] :
별도의 옵션없이 mysqldump 명령의 마지막에 여러개의 오브젝트명을 명시하면,
mysqldump는 첫번째 오브젝트는 데이터베이스명, 그 다음부터는 테이블명으로 인식하게 된다.
그래서 해당 데이터베이스에 있는 각 테이블들만을 덤프하게 된다.
일관된 데이터 덤프 받기
mysqldump를 이용하여 일관된 데이터를 덤프받고자 한다면,
lock-tables 옵션 또는 single-transaction 옵션을 사용해야만 한다.
간단히 이 옵션들의 사용에 따른 데이터 잠금 여부를 확인해 본 결과이다.
일반적으로 InnoDB의 경우에는 single-transaction을 사용하며,
MyISAM 의 경우에는 lock-tables 옵션을 사용해야 한다. (물론 MyISAM의 경우 일관된 백업의 의미는 없다)
InnoDB에서 single-transaction 옵션이 사용되면, table에 대한 lock을 걸지 않고 MVCC의 Read view를 이용하기 때문에
mysqldump 도중에도 trasaction (insert, update, delete)이 허용된다.
- mysqldump --skip-add-locks --single-transaction --skip-lock-tables db1 db2 > test.dump.sql
==> 덤프 도중 다른 세션에서 insert, update, delete 가능 - mysqldump --single-transaction db1 db2 > test.dump.sql
==> 덤프 도중 다른 세션에서 insert, update, delete 가능 - mysqldump --lock-tables db1 db2 > test.dump.sql
==> 덤프 도중 다른 세션에서 insert, update, delete 불가능
위의 옵션들을 이용한 여러가지 목적 또는 용도로 mysqldump명령
MySQL 테이블 & 루틴 전부 덤프
mysqldump --user=root --password --opt --single-transaction --hex-blob --master-data=2 --routines --triggers
--default-character-set=utf8 --databases [데이터베이스명1] [데이터베이스명2] > `hostname`_`date '+%y%m%d'`.dump.sql
MySQL의 루틴만 덤프
mysqldump --user=root --password --routines --no-create-info --no-data
--no-create-db --skip-opt [데이터베이스명] > `hostname`_`date '+%y%m%d'`.dump.sql
View의 덤프
View 는 기본적으로 Algorithm (Merge 또는 Temptable)에 관계없이
독립적인 데이터를 가지지 않기 때문에 mysqldump로 받아도 실질적인 데이터를 가지지 않는다.
그래서 mysqldump로 --no-create-info 가 명시되지 않는 이상은 테이블과 동일하게 CREATE VIEW
문장은 백업이 가능하다.
View의 덤프
View 는 기본적으로 Algorithm (Merge 또는 Temptable)에 관계없이
독립적인 데이터를 가지지 않기 때문에 mysqldump로 받아도 실질적인 데이터를 가지지 않는다.
그래서 mysqldump로 --no-create-info 가 명시되지 않는 이상은 테이블과 동일하게 CREATE VIEW
문장은 백업이 가능하다.
Table 4.5. mysqldump Options
| Format | Option File | Description | Introduced |
|---|---|---|---|
| --add-drop-database | add-drop-database | Add a DROP DATABASE statement before each CREATE DATABASE statement | |
| --add-drop-table | add-drop-table | Add a DROP TABLE statement before each CREATE TABLE statement | |
| --add-drop-trigger | add-drop-trigger | Add a DROP TRIGGER statement before each CREATE TRIGGER statement | 5.1.47-ndb-7.1.8 |
| --add-locks | add-locks | Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | |
| --all-databases | all-databases | Dump all tables in all databases | |
| --all-tablespaces | all-tablespaces | Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB Cluster table | 5.1.6 |
| --allow-keywords | allow-keywords | Allow creation of column names that are keywords | |
| --bind-address=ip_address | bind-address | Use the specified network interface to connect to the MySQL Server | 5.1.22-ndb-6.3.4 |
| --comments | comments | Add comments to the dump file | |
| --compact | compact | Produce more compact output | |
| --compatible=name[,name,...] | compatible | Produce output that is more compatible with other database systems or with older MySQL servers | |
| --complete-insert | complete-insert | Use complete INSERT statements that include column names | |
| --create-options | create-options | Include all MySQL-specific table options in CREATE TABLE statements | |
| --databases | databases | Dump several databases | |
| --debug[=debug_options] | debug | Write a debugging log | |
| --debug-check | debug-check | Print debugging information when the program exits | 5.1.21 |
| --debug-info | debug-info | Print debugging information, memory and CPU statistics when the program exits | 5.1.14 |
| --default-character-set=charset_name | default-character-set | Use charset_name as the default character set | |
| --delayed-insert | delayed-insert | Write INSERT DELAYED statements rather than INSERT statements | |
| --delete-master-logs | delete-master-logs | On a master replication server, delete the binary logs after performing the dump operation | |
| --disable-keys | disable-keys | For each table, surround the INSERT statements with statements to disable and enable keys | |
| --dump-date | dump-date | Include dump date as "Dump completed on" comment if --comments is given | 5.1.23 |
| --events | events | Dump events from the dumped databases | 5.1.8 |
| --extended-insert | extended-insert | Use multiple-row INSERT syntax that include several VALUES lists | |
| --fields-enclosed-by=string | fields-enclosed-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --fields-escaped-by | fields-escaped-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --fields-optionally-enclosed-by=string | fields-optionally-enclosed-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --fields-terminated-by=string | fields-terminated-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --first-slave | first-slave | Deprecated; use --lock-all-tables instead | |
| --flush-logs | flush-logs | Flush the MySQL server log files before starting the dump | |
| --flush-privileges | flush-privileges | Emit a FLUSH PRIVILEGES statement after dumping the mysql database | |
| --help | Display help message and exit | ||
| --hex-blob | hex-blob | Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263) | |
| --host | host | Host to connect to (IP address or hostname) | |
| --ignore-table=db_name.tbl_name | ignore-table | Do not dump the given table | |
| --insert-ignore | insert-ignore | Write INSERT IGNORE statements rather than INSERT statements | |
| --lines-terminated-by=string | lines-terminated-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --lock-all-tables | lock-all-tables | Lock all tables across all databases | |
| --lock-tables | lock-tables | Lock all tables before dumping them | |
| --log-error=file_name | log-error | Append warnings and errors to the named file | 5.1.18 |
| --master-data[=value] | master-data | Write the binary log file name and position to the output | |
| --max_allowed_packet=value | max_allowed_packet | The maximum packet length to send to or receive from the server | |
| --net_buffer_length=value | net_buffer_length | The buffer size for TCP/IP and socket communication | |
| --no-autocommit | no-autocommit | Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements | |
| --no-create-db | no-create-db | This option suppresses the CREATE DATABASE statements | |
| --no-create-info | no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | |
| --no-data | no-data | Do not dump table contents | |
| --no-set-names | no-set-names | Same as --skip-set-charset | |
| --no-tablespaces | no-tablespaces | Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output | 5.1.14 |
| --opt | opt | Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. | |
| --order-by-primary | order-by-primary | Dump each table's rows sorted by its primary key, or by its first unique index | |
| --password[=password] | password | The password to use when connecting to the server | |
| --pipe | On Windows, connect to server using a named pipe | ||
| --port=port_num | port | The TCP/IP port number to use for the connection | |
| --quick | quick | Retrieve rows for a table from the server a row at a time | |
| --quote-names | quote-names | Quote identifiers within backtick characters | |
| --replace | replace | Write REPLACE statements rather than INSERT statements | 5.1.3 |
| --result-file=file | result-file | Direct output to a given file | |
| --routines | routines | Dump stored routines (procedures and functions) from the dumped databases | 5.1.2 |
| --set-charset | set-charset | Add SET NAMES default_character_set to output | |
| --single-transaction | single-transaction | This option issues a BEGIN SQL statement before dumping data from the server | |
| --skip-add-drop-table | skip-add-drop-table | Do not add a DROP TABLE statement before each CREATE TABLE statement | |
| --skip-add-locks | skip-add-locks | Do not add locks | |
| --skip-comments | skip-comments | Do not add comments to the dump file | |
| --skip-compact | skip-compact | Do not produce more compact output | |
| --skip-disable-keys | skip-disable-keys | Do not disable keys | |
| --skip-extended-insert | skip-extended-insert | Turn off extended-insert | |
| --skip-opt | skip-opt | Turn off the options set by --opt | |
| --skip-quick | skip-quick | Do not retrieve rows for a table from the server a row at a time | |
| --skip-quote-names | skip-quote-names | Do not quote identifiers | |
| --skip-set-charset | skip-set-charset | Suppress the SET NAMES statement | |
| --skip-triggers | skip-triggers | Do not dump triggers | |
| --skip-tz-utc | skip-tz-utc | Turn off tz-utc | 5.1.2 |
| --ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
| --ssl-capath=dir_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
| --ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
| --ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
| --ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
| --ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the host name used when connecting to the server | |
| --tab=path | tab | Produce tab-separated data files | |
| --tables | tables | Override the --databases or -B option | |
| --triggers | triggers | Dump triggers for each dumped table | |
| --tz-utc | tz-utc | Add SET TIME_ZONE='+00:00' to the dump file | 5.1.2 |
| --user=user_name | user | MySQL user name to use when connecting to server | |
| --verbose | Verbose mode | ||
| --version | Display version information and exit | ||
| --where='where_condition' | where | Dump only rows selected by the given WHERE condition | |
| --xml | xml | Produce XML output |
반응형