[MySQL][Backup n Recovery] mysqlpump

■ mysqlpump 소개

mysqlpump 클라이언트 유틸리티는 논리 백업을 수행합니다. 논리백업을 수행할 백업을 테이블 하나하나 순차적으로 백업하는게 아니라 동시에 쓰레드를 여러개 생성하여 병렬로 백업을 받게 됩니다. 그만큼 속도가 빠릅니다.

지금부터 mysqlpump 특징에 대해 알아보겠습니다.

 

■ mysqlpump 기능

덤프 프로세스의 속도를 높이기 위해 데이터베이스 데이터베이스 개체의 병렬 처리

덤프 데이터베이스 데이터베이스 개체 (테이블, 저장 프로그램, 사용자 계정)를보다 효과적으로 제어

사용자 계정을 mysql 시스템 데이터베이스에 삽입하는 대신 계정 관리 명령문 (CREATE USER, GRANT)으로 덤프

압축 출력 생성 기능

진행률 표시기 (값은 추정치 )

덤프 파일 다시로드의 경우 삽입 인덱스를 추가하여 InnoDB 테이블에 대한 보조 인덱스 생성 속도를 높입니다.

 

■ mysqlpump 백업옵션

mysqldump 백업 옵션과 거의 같습니다. 그러나 mysqlpump 명령에서만 쓸수 있는 옵션이 있습니다.

--default-parallelism --parallel-schemas 옵션입니다.

mysqlpump 덤프 프로세스를 여러 하위 작업으로 나눈 다음 이러한 하위 작업을 다중 스레드 대기열에 추가합니다. 큐는 작업이 병렬로 수행 있도록 N개의 스레드에 의해 처리됩니다.(기본적으로 2 )

스레드는 MySQL 서버에 연결하여 필요한 모든 정보를 검색한 다음 작업을 시작합니다.

mysqldump 옵션 : https://myinfrabox.tistory.com/28

 

병렬 옵션

1. default-parallelism

큐에 할당할 쓰레드 갯수입니다. 기본적으로 2개입니다.

 

2. parallel-schemas = [N :] db_list

b_list 입력된 데이터베이스를 덤프하기 위한 처리 대기열을 설정하고 선택적으로 대기열이 사용하는 스레드 수를 지정합니다. db_list 쉼표로 구분 데이터베이스 이름 목록입니다. 옵션이 N :으로 시작하면 큐는 N 개의 스레드를 사용합니다. 그렇지 않으면 --default-parallelism 옵션이 대기열 스레드 수를 결정합니다. 데이터베이스 목록의 이름은 % _ 와일드 카드 문자를 포함 있습니다. 또한 --parallel-schemas 옵션의 여러 인스턴스는 여러 대기열을 만듭니다.

 

■ mysqlpump 백업사용 예제

▶︎ 기본 백업

특정 데이터베이스 accout, inventory 스키마(데이터베이스) 백업.

mysqlpump --databases accounts inventory --user=root --password > all_backup.sql

 

메타 데이터 정보만 내보내고 테이블과 관련된 모든 데이터를 건너 뛰려면 --skip-dump-rows 옵션을 사용할 있습니다.

mysqlpump --databases accounts inventory --skip-dump-rows --user=root --password > partial_backup.sql

 

• db1, db2 데이터베이스(스키마) 백업.

mysqlpump --user=root --password --include-databases=db1,db2 --result-file=db1_db2_backup.sql

 

이름이 db 시작하는 모든 데이터베이스(스키마) 백업

mysqlpump --user=root --password --include-databases=db% --result-file=all_db_backup.sql

 

• db1 db2 제외한 모든 데이터베이스(스키마) 백업.

mysqlpump --user=root --password --exclude-databases=db1,db2 --result-file=partial_backup.sql

 

예는 모든 데이터베이스(스키마) 있을 있는 't'라는 이름의 테이블을 제외하고 모든 데이터베이스(스키마)에서 모든 테이블을 백업.

mysqlpump --user=root --password --exclude-tables=t --result-file=partial_backup.sql

 

모든 데이터베이스(스키마) (dbuser, STuser, 45user )에서 '__user' 패턴과 일치하는 테이블 이름을 제외한 모든 데이터베이스(스키마)에서 모든 테이블을 백업.

mysqlpump --user=root --password --exclude-tables=__user --result-file=partial_backup.sql

 

• mysql 시스템 데이터베이스(스키마) 제외한 모든 데이터베이스(스키마)에서 이름이 'ev2' 이벤트와 이름이 'p1' 루틴만 백업.

mysqlpump --user=root --password --include-events=ev2 --include-routines=p1 --exclude-databases=mysql --result-file=partial_backup.sql

 

• mysql.user 테이블에있는 사용자만 덤프합니다. -–users 옵션은 mysql.user 테이블에 삽입 명령문이 아닌 CREATE USER, GRANT 명령문의 형태로 논리적 사용자 정의를 덤프합니다.

mysqlpump --user=root --password --exclude-databases=% --users

 

루트 사용자를 제외하고 mysql.user 테이블에있는 모든 사용자를 덤프합니다.

mysqlpump --user=root --password --users --exclude-users=root

 

▶︎ 병렬 스레드 큐를 사용한 백업

• 4개의 병렬 쓰레드를 이용하여 데이터베이스 전체 백업.

mysqlpump --user=root --password --default-parallelism=4 > all_dump.sql

 

데이터베이스 db1, db2 처리하는 1, 큐당 스레드 3개로 다른 모든 큐를 처리하는 1, 2개의 작업자 큐를 생성하는 예제.

(덤프 태스크를 완료하기 위해 기본적으로 큐당 스레드 2 개가 작성 )

mysqlpump --user=root --password --parallel-schemas=db1,db2 --default-parallelism=3 > all_dump.sql

 

• db1, db2 처리될 작업할 5개의 스레드를 가지는 첫번째 큐와 나머지 데이터베이스에서 작업할 3개의 스레드를 가진 기본 큐를 가지고 백업.

mysqlpump --user=root --password --parallel-schemas=5:db1,db2 --default-parallelism=3 > all_dump.sql

 

• db1, db2 처리될 5개의 스레드를 가지는 번째 ,  db3, db4 처리될 2개의 쓰레드를 가지는 두번째 ,  다른 모든 데이터베이스에 처리될 3개의 스레드를 가지는 기본큐를 가지고 백업.

mysqlpump --user=root --password --parallel-schemas=5:db1,db2 --parallel-schemas=2:db3,db4 --default-parallelism=3 > all_backup.sql

 

 

▶︎ 백업 출력 예제

테이블 확인

mysql> use sbtest2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_sbtest2 |
+-------------------+
| sbtest1           |
| sbtest10          |
| sbtest11          |
| sbtest12          |
| sbtest13          |
| sbtest14          |
| sbtest15          |
| sbtest16          |
| sbtest17          |
| sbtest18          |
| sbtest19          |
| sbtest2           |
| sbtest20          |
| sbtest3           |
| sbtest4           |
| sbtest5           |
| sbtest6           |
| sbtest7           |
| sbtest8           |
| sbtest9           |
+-------------------+
20 rows in set (0.00 sec)

 

프로세스 리스트 확인

테이블이 5개씩 순서대로 백업을 받습니다.(스레드 5 할당.)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State             | Info                                                 |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting          | show processlist                                     |
|  9 | root | localhost | NULL | Sleep   |   11 |                   | NULL                                                 |
| 10 | root | localhost | NULL | Sleep   |   13 |                   | NULL                                                 |
| 11 | root | localhost | NULL | Query   |   12 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest10` |
| 12 | root | localhost | NULL | Query   |   12 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest1`  |
| 13 | root | localhost | NULL | Query   |   12 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest11` |
| 14 | root | localhost | NULL | Query   |   12 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest12` |
| 15 | root | localhost | NULL | Query   |   12 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest13` |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State             | Info                                                 |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting          | show processlist                                     |
|  9 | root | localhost | NULL | Sleep   |   20 |                   | NULL                                                 |
| 10 | root | localhost | NULL | Sleep   |   22 |                   | NULL                                                 |
| 11 | root | localhost | NULL | Query   |    7 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest15` |
| 12 | root | localhost | NULL | Query   |    8 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest14` |
| 13 | root | localhost | NULL | Query   |    7 | Sending data      | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest17` |
| 14 | root | localhost | NULL | Query   |    7 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest16` |
| 15 | root | localhost | NULL | Query   |    7 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest18` |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State             | Info                                                 |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting          | show processlist                                     |
|  9 | root | localhost | NULL | Sleep   |   32 |                   | NULL                                                 |
| 10 | root | localhost | NULL | Sleep   |   34 |                   | NULL                                                 |
| 11 | root | localhost | NULL | Query   |    7 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest2`  |
| 12 | root | localhost | NULL | Query   |    7 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest20` |
| 13 | root | localhost | NULL | Query   |    7 | Sending data      | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest4`  |
| 14 | root | localhost | NULL | Query   |    7 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest3`  |
| 15 | root | localhost | NULL | Query   |    7 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest19` |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State             | Info                                                |
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting          | show processlist                                    |
|  9 | root | localhost | NULL | Sleep   |   48 |                   | NULL                                                |
| 10 | root | localhost | NULL | Sleep   |   50 |                   | NULL                                                |
| 11 | root | localhost | NULL | Query   |   12 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest6` |
| 12 | root | localhost | NULL | Query   |   12 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest8` |
| 13 | root | localhost | NULL | Query   |   11 | Sending to client | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest9` |
| 14 | root | localhost | NULL | Query   |   12 | Sending data      | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest7` |
| 15 | root | localhost | NULL | Query   |   12 | Sending data      | SELECT `id`,`k`,`c`,`pad`  FROM `sbtest2`.`sbtest5` |
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------+
8 rows in set (0.00 sec)

 

덤프 콘솔 화면

[root@singledb ~]# mysqlpump -uroot -p --databases sbtest2 --databases sbtest2 --parallel-schemas=5:sbtest2 > sbtest2.sql
Enter password: 
Dump progress: 0/11 tables, 250/10846220 rows
Dump progress: 0/20 tables, 454750/19720400 rows
Dump progress: 0/20 tables, 865250/19720400 rows
Dump progress: 0/20 tables, 1256750/19720400 rows
Dump progress: 0/20 tables, 1565000/19720400 rows
Dump progress: 0/20 tables, 1995000/19720400 rows
Dump progress: 0/20 tables, 2408250/19720400 rows
Dump progress: 0/20 tables, 2742500/19720400 rows
Dump progress: 0/20 tables, 3075000/19720400 rows
Dump progress: 0/20 tables, 3396750/19720400 rows
Dump progress: 0/20 tables, 3649250/19720400 rows
Dump progress: 0/20 tables, 4030500/19720400 rows
Dump progress: 0/20 tables, 4367000/19720400 rows
Dump progress: 0/20 tables, 4715750/19720400 rows
Dump progress: 5/20 tables, 5139250/19720400 rows
Dump progress: 5/20 tables, 5642000/19720400 rows
Dump progress: 5/20 tables, 5882000/19720400 rows
Dump progress: 5/20 tables, 6323250/19720400 rows
Dump progress: 5/20 tables, 6769250/19720400 rows
Dump progress: 5/20 tables, 7283000/19720400 rows
Dump progress: 5/20 tables, 7737250/19720400 rows
Dump progress: 5/20 tables, 8258500/19720400 rows
Dump progress: 5/20 tables, 8763750/19720400 rows
Dump progress: 5/20 tables, 9247250/19720400 rows
Dump progress: 5/20 tables, 9683250/19720400 rows
Dump progress: 9/20 tables, 10089000/19720400 rows
Dump progress: 10/20 tables, 10329000/19720400 rows
Dump progress: 10/20 tables, 10662000/19720400 rows
Dump progress: 10/20 tables, 11132500/19720400 rows
Dump progress: 10/20 tables, 11591500/19720400 rows
Dump progress: 10/20 tables, 12079500/19720400 rows
Dump progress: 10/20 tables, 12664500/19720400 rows
Dump progress: 10/20 tables, 13145000/19720400 rows
Dump progress: 10/20 tables, 13703000/19720400 rows
Dump progress: 10/20 tables, 14205000/19720400 rows
Dump progress: 10/20 tables, 14669500/19720400 rows
Dump progress: 14/20 tables, 15138750/19720400 rows
Dump progress: 15/20 tables, 15526500/19720400 rows
Dump progress: 15/20 tables, 15707000/19720400 rows
Dump progress: 15/20 tables, 15969000/19720400 rows
Dump progress: 15/20 tables, 16446000/19720400 rows
Dump progress: 15/20 tables, 17017500/19720400 rows
Dump progress: 15/20 tables, 17531000/19720400 rows
Dump progress: 15/20 tables, 18121000/19720400 rows
Dump progress: 15/20 tables, 18517500/19720400 rows
Dump progress: 15/20 tables, 18895000/19720400 rows
Dump progress: 15/20 tables, 19078000/19720400 rows
Dump progress: 15/20 tables, 19510250/19720400 rows
Dump progress: 15/20 tables, 19640750/19720400 rows
Dump completed in 55222 milliseconds

'Databases > MySQL' 카테고리의 다른 글

[MySQL] Performance Schema 상태 모니터링  (0) 2021.01.24
[MySQL] Performance Schema 소개 및 사용방법  (0) 2021.01.24
[MySQL][Backup n Recovery] mysqlpump  (0) 2021.01.23
[MySQL] Timeout 종류  (0) 2021.01.21
[MySQL] Global Status  (0) 2021.01.17
[MySQL] Server SQL Modes  (0) 2021.01.15

댓글(0)

Designed by JB FACTORY