[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