Mysql 5.7 버전부터 소개된 기능으로 논리적 백업을 수행하는 유틸리티입니다. 기존에 mysqldump라는 유틸리티가 존재하지만, 몇가지 기능과 특징들을 더 가지고 있습니다.
Mysql5.7 배포당시에는 병렬백업 MVCC에 의한 데이터의 동기화가 되지 않는다는 치명적인 결함이 있었습니다. 그 때문에 병렬 덤프를 실행하려면 변경 작업을 멈춰햐 했으므로 mysqldump의 대체품으로 사용할 수 없었습니다.
Mysql5.7.11에서 이런 문제가 해결되어 서버 변경 중에 병렬 덤프를 실행해도 정합성 있는 백업을 할 수 있게 되었습니다. InnoDB를 일관되게 백업하려면 mysqldump와 마찬가지로 --single-transaction 옵션을 지정해야 합니다.
--users는 사용자 계정을 백업하기 위한 옵션입니다. 이 옵션을 설정하지 않으면 사용자 계정은 백업되지 않습니다.
Mysqlpump 특징
-병렬처리 (속도 향상)
- 데이터베이스와 Object(테이블,계정,Stored program) 더 잘 제어 가능
- 사용자 계정 정보를 mysql system 스키마에 insert하기보다 (CREATE USER,GRANT) 명령문으로 DUMP
- exp시 압축 가능
- 진행절차 확인가능
-imp시 innoDB의 데이터를 먼저 넣고 인덱스를 생성하기 때문에 속도 향상
mysqlpump는 덤프된 테이블에 대한 select 권한, 덤프된 뷰, 덤프된 트리거와 --single-transaction 옵션을 사용하지 않는 LOCK TABLE의 경우 SHOW VIEW 권한이 요구됩니다. imp시 덤프파일안에 명령문을 실행하는데 필요한 권한이 있어야 합니다. (CREATE 권한같은 권한)
EXP의 경우 아래의 명령어로 사용할 수 있습니다.
mysqlpump [options] > dump.sqlIMP의 경우 아래와 같은 명령어로 사용할 수 있습니다.
mysql < dump.sql
옵션의 경우 아래의 글을 펼치면 확인할 수 있습니다.
# mysqlpump -?
mysqlpump Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2014, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
MySQL utility for dumping data from databases to external file.
Usage: mysqlpump [OPTIONS] [--all-databases]
OR mysqlpump [OPTIONS] --databases DB1 [DB2 DB3...]
OR mysqlpump [OPTIONS] database [tables]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: client mysql_dump mysqlpump
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file,
except for login file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--login-path=# Read this path from the login file.
-?, --help Display this help message and exit.
--add-drop-database Add a DROP DATABASE before each CREATE DATABASE.
--add-drop-table Add a DROP TABLE before each CREATE TABLE.
--add-drop-user Add a DROP USER before each CREATE USER.
--add-locks Wrap data inserts on table with write lock on that table
in output. This doesn't work with parallelism.
-A, --all-databases Dump all databases. This is default behaviour if no
positional options are specified. Specifying this option
is mutually exclusive with --databases.
--bind-address=name IP address to bind to.
--character-sets-dir=name
Directory for character set files.
--column-statistics Add an ANALYZE TABLE statement to regenerate any existing
column statistics.
--complete-insert Use complete insert statements, include column names.
-C, --compress Use compression in server/client protocol.
--compress-output=name
Compresses all output files with LZ4 or ZLIB compression
algorithm.
--compression-algorithms=name
Use compression algorithm in server/client protocol.
Valid values are any combination of
'zstd','zlib','uncompressed'
-B, --databases Dump selected databases, specified in positional options.
Specifying this option is mutually exclusive with
--all-databases.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check[=#] This is a non-debug version. Catch this and exit.
--debug-info[=#] This is a non-debug version. Catch this and exit.
--default-character-set=name
Set the default character set.
--default-parallelism=#
Specifies number of threads to process each parallel
queue for values N > 0. if N is 0 then no queue will be
used. Default value is 2. If N > 1 then objects in dump
file can have lines intersected. Usage of values greater
than 1 is mutually exclusive with --single-transaction.
--default-auth=name Default authentication client-side plugin to use.
--defer-table-indexes
Defer addition of indexes of table to be added after all
rows are dumped.
(Defaults to on; use --skip-defer-table-indexes to disable.)
--events Dump event scheduler events.
(Defaults to on; use --skip-events to disable.)
--exclude-databases=name
Specifies comma-separated list of databases to exclude.
--exclude-events=name
Specifies comma-separated list of events to exclude.
--exclude-routines=name
Specifies comma-separated list of stored procedures or
functions to exclude.
--exclude-tables=name
Specifies comma-separated list of tables to exclude.
--exclude-triggers=name
Specifies comma-separated list of triggers to exclude.
--exclude-users=name
Specifies comma-separated list of users to exclude.
--extended-insert=# Allow usage of multiple-row INSERT syntax that include
several VALUES lists. Specifies number of rows to include
in single INSERT statement. Must be greater than 0
--get-server-public-key
Get public key from server
--hex-blob Dump binary strings (in fields of type BINARY, VARBINARY,
BLOB, ...) in hexadecimal format.
-h, --host=name Connect to host.
--include-databases=name
Specifies comma-separated list of databases and all of
its objects to include. If there are no exclusions then
only included objects will be dumped. Otherwise all
objects that are not on exclusion lists or are on
inclusion list will be dumped.
--include-events=name
Specifies comma-separated list of events to include. If
there is no exclusions then only included objects will be
dumped. Otherwise all objects that are not on exclusion
lists or are on inclusion list will be dumped.
--include-routines=name
Specifies comma-separated list of stored procedures or
functions to include. If there is no exclusions then only
included objects will be dumped. Otherwise all objects
that are not on exclusion lists or are on inclusion list
will be dumped.
--include-tables=name
Specifies comma-separated list of tables to include. If
there is no exclusions then only included objects will be
dumped. Otherwise all objects that are not on exclusion
lists or are on inclusion list will be dumped.
--include-triggers=name
Specifies comma-separated list of triggers to include. If
there is no exclusions then only included objects will be
dumped. Otherwise all objects that are not on exclusion
lists or are on inclusion list will be dumped.
--include-users=name
Specifies comma-separated list of users to include. If
there is no exclusions then only included objects will be
dumped. Otherwise all objects that are not on exclusion
lists or are on inclusion list will be dumped.
--insert-ignore Use INSERT IGNORE INTO for dumped rows instead of INSERT
INTO.
--log-error-file=name
Append warnings and errors to specified file.
--max-allowed-packet=#
The maximum packet length to send to or receive from
server.
--net-buffer-length=#
The buffer size for TCP/IP and socket communication.
--no-create-db Suppress CREATE DATABASE statements.
-t, --no-create-info
Suppress CREATE TABLE statements.
--parallel-schemas=name
[N:]<list of: schema_name separated with ','>. Process
tables in specified schemas using separate queue handled
by --default-parallelism threads or N threads, if N is
specified. Can be used multiple times to specify more
parallel processes.
-p, --password[=name]
Password to use when connecting to server. If password is
not given, it's solicited on the tty.
--plugin-dir=name Directory for client-side plugins.
-P, --port=# Port number to use for connection.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
--replace Use REPLACE INTO for dumped rows instead of INSERT INTO.
--result-file=name Direct all output generated for all objects to a given
file.
--routines Dump stored procedures and functions.
(Defaults to on; use --skip-routines to disable.)
--server-public-key-path=name
Path to file containing server public key
--set-charset Add 'SET NAMES default_character_set' to the output to
keep charsets consistent.
(Defaults to on; use --skip-set-charset to disable.)
--set-gtid-purged=name
Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible
values for this option are ON, OFF and AUTO. If ON is
used and GTIDs are not enabled on the server, an error is
generated. If OFF is used, this option does nothing. If
AUTO is used and GTIDs are enabled on the server, 'SET
@@GLOBAL.GTID_PURGED' is added to the output. If GTIDs
are disabled, AUTO does nothing. If no value is supplied
then the default (AUTO) value will be considered.
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. This option is mutually exclusive with
--add-locks option.
--skip-definer Skip DEFINER and SQL SECURITY clauses for Views and
Stored Routines.
-d, --skip-dump-rows
Skip dumping rows of all tables to output.
-S, --socket=name The socket file to use for connection.
--ssl-ca=name CA file in PEM format.
--ssl-capath=name CA directory.
--ssl-cert=name X509 cert in PEM format.
--ssl-cipher=name SSL cipher to use.
--ssl-crl=name Certificate revocation list.
--ssl-crlpath=name Certificate revocation list path.
--ssl-fips-mode=name
SSL fips mode to use.
--ssl-key=name X509 key in PEM format.
--ssl-mode=name SSL connection mode.
--tls-ciphersuites=name
TLS v1.3 cipher to use.
--tls-version=name TLS version to use.
--triggers Dump triggers.
(Defaults to on; use --skip-triggers to disable.)
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
(Defaults to on; use --skip-tz-utc to disable.)
-u, --user=name User for login if not current user.
--users Dump users with their privileges in GRANT format.
Disabled by default.
-V, --version Output version information and exit.
--watch-progress Shows periodically dump process progress information on
error output. Progress information include both completed
and total number of tables, rows and other objects
collected.
(Defaults to on; use --skip-watch-progress to disable.)
--zstd-compression-level=#
Use this compression level in the client/server protocol,
in case --compression-algorithms=zstd. Valid range is
between 1 and 22, inclusive. Default is 3.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
add-drop-database FALSE
add-drop-table FALSE
add-drop-user FALSE
add-locks FALSE
all-databases FALSE
bind-address (No default value)
character-sets-dir (No default value)
column-statistics FALSE
complete-insert FALSE
compress FALSE
compress-output (No default value)
compression-algorithms (No default value)
databases FALSE
default-character-set UTF8MB4
default-parallelism 2
default-auth (No default value)
defer-table-indexes TRUE
events TRUE
exclude-databases (No default value)
exclude-events (No default value)
exclude-routines (No default value)
exclude-tables (No default value)
exclude-triggers (No default value)
exclude-users (No default value)
extended-insert 250
get-server-public-key FALSE
hex-blob FALSE
host (No default value)
include-databases (No default value)
include-events (No default value)
include-routines (No default value)
include-tables (No default value)
include-triggers (No default value)
include-users (No default value)
insert-ignore FALSE
log-error-file (No default value)
max-allowed-packet 25165824
net-buffer-length 1047552
no-create-db FALSE
no-create-info FALSE
parallel-schemas (No default value)
password (No default value)
plugin-dir (No default value)
port 0
protocol (No default value)
replace FALSE
result-file (No default value)
routines TRUE
server-public-key-path (No default value)
set-charset TRUE
set-gtid-purged AUTO
single-transaction FALSE
skip-definer FALSE
skip-dump-rows FALSE
socket (No default value)
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
ssl-fips-mode (No default value)
ssl-key (No default value)
ssl-mode (No default value)
tls-ciphersuites (No default value)
tls-version (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
users FALSE
watch-progress TRUE
zstd-compression-level 3
전체 데이터베이스를 백업받는 명령어
INFORMATION_SCHEMA,SYS,MYSQL,PERFORMANCE_SCHEMA에 있는 권한 테이블은 백업되지 않습니다.
mysqlpump --all-databases > all2.sql -uroot -p
Enter password:
Dump progress: 1/1 tables, 0/0 rows
Dump progress: 32/33 tables, 510169/2830641 rows
Dump progress: 32/33 tables, 1174419/2830641 rows
Dump progress: 32/33 tables, 1856669/2830641 rows
Dump progress: 32/33 tables, 2471419/2830641 rows
Dump completed in 4971
---exclude-databases --include-databases 옵션을 사용해서 특정 스키마만 백업받거나 백업에 제외할 수 있습니다.
마찬가지로, table,triggers,routines,events,users가 exclude와 include를 붙여써서 명령어로 사용가능합니다.
병렬 프로세싱
default로 mysqlpump는 2개의 쓰레드를 하나의 큐에 할당합니다. 큐는 기본적으로 하나뿐입니다.
--default-parallelism=N 옵션을 사용하여 각 큐에서 사용하는 쓰레드의 수를 설정할 수 있습니다.
--parallel-schemas=[N:]데이터베이스명 옵션을 사용하여 큐를 증가시킬 수 있습니다. 이 옵션은 여러번 지정하는게 가능해서 지정할 때마다 큐가 하나씩 늘어납니다. N은 큐에 소속되는 쓰레드 수로, 정수로 지정합니다.
각각의 쓰레드는 대상 Mysql서버에 개별적으로 접속합니다. 데이터베이스를 여러개 지정하는 경우에는 콤마(,)로 구분하여 지정합니다. --parallel-schemas 옵션을 지정해도 기본큐는 반드시 작성됩니다. 옵션으로 지정되지 않은 데이터베이스는 기본 큐로 처리됩니다.
예제)
mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3
--parallel-schemas=5이므로 db1과 db2는 5개의 쓰레드를 이용하여 하나의 큐로 처리됩니다, db3은 parallel-schemas가 3이므로 3개의 쓰레드를 이용하여 하나의 큐로 처리됩니다.
mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
--default-parallelism=4--default-parallelism=4이므로 기본적으로 4개의 쓰레드가 할당됩니다. db1,db2는 4개의 쓰레드로 하나의 큐로 처리되고, db3은 4개의 쓰레드로 하나의 큐로 처리됩니다.
제약사항(Restrictions)
mysqlpump는 performance_schema,ndbinfo,sys 스키마를 default로 dump할 수 없습니다. 이 스키마들을 dump하려면 명시적으로 이름을 작성해주어야 합니다.(--databases 옵션이나 --include-databases 옵션 사용)
information_Schema 스키마는 dump할 수 없습니다.
InnoDB CREATE TABLESPACE문을 dump할 수 없습니다.
mysqlpump는 CREATE USER 및 GRANT문을 사용하여 사용자 계정을 dump합니다.(위에서 언급함)
또하나의 큰 제약은 하나의 테이블을 여러 개의 쓰레드로 덤프할 수 없다는 것입니다. 테이블수가 쓰레드 수보다 적으면 쓰레드가 남아있어도 활용할 수 없습니다.
테스트
테스트 결과 5개의 프로세스가 추가되었고 4개의 프로세스가 sleep상태에 빠졌습니다. --defaultsparallelism=4로 설정했음에도 5개의 프로세스가 추가된것은 조금더 알아봐야겠습니다..
//4개의 쓰레드를 기본으로 사용하는 mysqlpump 명령어
# mysqlpump =uroot -p --default-parallelism=4 --include-databases=test --include-tables=test_dummy,student,professor > test_dummy2.sql
Enter password:
Dump progress: 1/2 tables, 0/0 rows
Dump progress: 2/3 tables, 555000/2694810 rows
Dump progress: 2/3 tables, 1216750/2694810 rows
Dump progress: 2/3 tables, 1868750/2694810 rows
Dump progress: 2/3 tables, 2460250/2694810 rows
Dump completed in 4765
//작업전 프로세스 리스트
mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 351578 | Waiting on empty queue | NULL |
| 27 | root | localhost | test | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
2 rows in set (0.00 sec)
//작업 중
mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 351811 | Waiting on empty queue | NULL |
| 27 | root | localhost | test | Query | 0 | init | show processlist |
| 37 | root | localhost | NULL | Sleep | 4 | | NULL |
| 38 | root | localhost | NULL | Sleep | 4 | | NULL |
| 39 | root | localhost | NULL | Sleep | 4 | | NULL |
| 40 | root | localhost | NULL | Sleep | 4 | | NULL |
| 41 | root | localhost | NULL | Query | 4 | executing | SELECT `a`,`b` FROM `test`.`test_dummy` |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------------+
7 rows in set (0.00 sec)
출처 : Mysql 8.0 Referenct Manual , dung-beetle.tistory.com/60
'다른 DBMS > MySQL&MariaDB' 카테고리의 다른 글
| InnoDB 소개 (0) | 2021.01.08 |
|---|---|
| mysqlslap (0) | 2021.01.06 |
| [에러 해결] Error: 1290 secure-file-priv 관련 (0) | 2021.01.02 |
| Mysql의 Program (0) | 2021.01.01 |
| 권한(Privileges) (0) | 2020.12.30 |
댓글