반응형
mysqldump는 mysql, mariadb 에서 데이터 백업시 주로 사용하는 백업방법 중 하나이다.
개발 DB에서 사용할 때는 문제가 없지만
서비스 DB에서 이걸 사용한다면 한가지 주의할 점이 있다.
모르고 그냥 사용하게 된다면 TABLE LOCK 때문에 서비스 장애를 일으킬 수 있다.
mysqldump는 여러 옵션이 있는데 그것을 모르고
default 옵션만 놓고 사용한다면 문제가 발생한다.
1. default 옵션사용시
ex)
mysqldump -uroot -p비번 xxxxDB TB_TEST_HIST > TB_TEST_HIST_20230110.sql
MariaDB [(none)]> show processlist;
+----------+-------------+----------------------+-------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----------+-------------+----------------------+-------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 32 | xxxbat | 192.168.141.77:47128 | xxxxDB | Sleep | 2 | | NULL | 0.000 |
| 13659427 | xxxapp | 192.168.141.75:47836 | xxxxDB | Sleep | 42 | | NULL | 0.000 |
| 13665622 | xxxapp | 192.168.141.75:48626 | xxxxxxDB | Sleep | 45 | | NULL | 0.000 |
| 13665657 | xxxapp | 192.168.141.75:48632 | xxxxxxDB | Sleep | 45 | | NULL | 0.000 |
| 13666109 | xxxapp | 192.168.141.75:48686 | xxxxxxDB | Sleep | 45 | | NULL | 0.000 |
| 13666154 | xxxapp | 192.168.141.75:48690 | xxxxxxDB | Sleep | 15 | | NULL | 0.000 |
| 13666211 | xxxapp | 192.168.141.75:48700 | xxxxxxDB | Sleep | 15 | | NULL | 0.000 |
| 13666735 | xxxapp | 192.168.141.75:48756 | xxxxDB | Sleep | 42 | | NULL | 0.000 |
| 13667304 | xxxapp | 192.168.141.75:48820 | xxxxxxDB | Sleep | 15 | | NULL | 0.000 |
| 13667820 | xxxapp | 192.168.141.75:48888 | xxxxxxDB | Sleep | 15 | | NULL | 0.000 |
| 13667892 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
| 13668007 | root | localhost | xxxxDB | Query | 146 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ xxxx, xxxx, xxxxx, xxxx, xxxx | 0.000 |
| 13668013 | xxxbat | 192.168.141.77:45250 | xxxxDB | Query | 144 | Waiting for table metadata lock | INSERT INTO TB_TEST_HIST ( xxxx ,xxxx ,xxxx ,P | 0.000 |
| 13668017 | xxxbat | 192.168.141.77:45260 | xxxxDB | Query | 128 | Waiting for table metadata lock | INSERT INTO TB_TEST_HIST ( xxxx ,xxxx ,xxxx ,P | 0.000 |
| 13668019 | xxxbat | 192.168.141.77:45268 | xxxxDB | Sleep | 111 | | NULL | 0.000 |
| 13668026 | xxxbat | 192.168.141.77:45282 | xxxxDB | Sleep | 107 | | NULL | 0.000 |
| 13668033 | xxxbat | 192.168.141.77:45298 | xxxxDB | Query | 103 | Waiting for table metadata lock | INSERT INTO TB_TEST_HIST ( xxxx ,xxxx ,xxxx ,P | 0.000 |
| 13668037 | xxxbat | 192.168.141.77:45310 | xxxxDB | Query | 83 | Waiting for table metadata lock | UPDATE TB_TEST_HIST SET xxxx = ( SELECT xxxx FROM xxxx WHERE xxxx | 0.000 |
| 13668040 | xxxbat | 192.168.141.77:45316 | xxxxDB | Sleep | 71 | | NULL | 0.000 |
| 13668044 | xxxbat | 192.168.141.77:45326 | xxxxDB | Query | 69 | Waiting for table metadata lock | INSERT INTO TB_TEST_HIST ( xxxx ,xxxx ,xxxx ,P | 0.000 |
| 13668047 | xxxbat | 192.168.141.77:45342 | xxxxDB | Sleep | 52 | | NULL | 0.000 |
| 13668055 | xxxbat | 192.168.141.77:45360 | xxxxDB | Query | 45 | Waiting for table metadata lock | INSERT INTO TB_TEST_HIST ( xxxx ,xxxx ,xxxx ,P | 0.000 |
| 13668062 | xxxbat | 192.168.141.77:45378 | xxxxDB | Sleep | 22 | | NULL | 0.000 |
| 13668069 | xxxbat | 192.168.141.77:45392 | xxxxDB | Sleep | 16 | | NULL | 0.000 |
| 13668072 | xxxapp | 192.168.141.75:48930 | xxxxxxDB | Sleep | 15 | | NULL | 0.000 |
| 13668077 | xxxbat | 192.168.141.77:45410 | xxxxDB | Sleep | 12 | | NULL | 0.000 |
| 13668078 | xxxbat | 192.168.141.77:45412 | xxxxDB | Sleep | 11 | | NULL | 0.000 |
| 13668079 | xxxbat | 192.168.141.77:45414 | xxxxDB | Sleep | 10 | | NULL | 0.000 |
| 13668080 | xxxbat | 192.168.141.77:45416 | xxxxDB | Sleep | 10 | | NULL | 0.000 |
| 13668081 | xxxbat | 192.168.141.77:45418 | xxxxDB | Sleep | 9 | | NULL | 0.000 |
| 13668082 | xxxbat | 192.168.141.77:45420 | xxxxDB | Query | 7 | Waiting for table metadata lock | UPDATE TB_TEST_HIST SET xxxx = ( SELECT xxxx FROM xxxx WHERE xxxx | 0.000 |
+----------+-------------+-------------------+-------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+----------+
show processlist로 세션 로그를 보면
mysqldump 를 처리하려고 Sending data | SELECT /*!40001 SQL_NO_CACHE */ xxxx, xxxx, xxxxx, xxxx, xxxx ~ 를 수행하자
아래로 백업테이블의 DML query가 전부 LOCK 이걸리는 것을 확인할 수 있다.
(insert 와 update 에서 Waiting for table metadata lock)
이것을 모르고 그냥 둔다면 수초 내로 세션이 늘어나면서 DB 세션이 MAX까지 찰것이고,
결국엔 메모리 부족으로 DB가 죽을 수도 있고, 버틴다면 too many connection 에러를 보고 서비스 접속이 어려워질 수 있다.
서비스 DB에서는 굳이 mysqldump를 안하면 제일 좋긴한데 ...
어쩔 수없이 테이블 백업이 필요한 경우라면?
2. LOCK 없이 백업방법
1) 트랜잭션 옵션 --single-transaction 적용방법
innoDB 엔진일 때만 사용가능하다.
이 옵션을 지정하면 백업 대상 MYSQL 서버에 백업을 위해 접속한 세션의 트랜잭션 격리 수준을 REPEATABLE READ 로 변경하고,
백업 시작 직전에 START TRANSACTION 명령을 실행한 뒤 백업을 진행한다.
이 옵션을 이용하면 백업을 수행할 때 InnoDB 스토리지 엔진과 같이 트랜잭션을 지원하는 스토리지 엔진을 사용하는 테이블에 대해서는
START TRANSACTION 구문이 실행된 시점의 일관된 데이터를 백업할 수 있다.
(MyISAM 엔진 테이블에서는 사용 x)
이 옵션을 이용해 백업을 수행할 때는 다른 세션에서는 DDL 명령을 실행해서는 안된다.
큰 테이블을 백업해야 한다면 --single-transaction 과 --quick 옵션을 함께 사용하는 것을 권장
#1-1) 대용량 테이블 백업시
--quick 성능 관련 옵션추가
백업 대상 데이터베이스에서 백업할 행 데이터 전체를 한 번에 읽어서 백업한다.
만일 이 옵션을 사용하지 않는다면 행데이터를 백업 파일에 기록하기 전에 메모리에 먼저 버퍼링하고 백업이 수행되므로 백업 소요 시간이 길어질 수 있다.
그러므로 이 옵션은 백업 대상 테이블 크기가 클 때 유용하게 사용.
ex)
mysqldump -uroot -p비번 --single-transaction xxxxDB TB_TEST_HIST > TB_TEST_HIST_20230110.sql
MariaDB [(none)]> show processlist;
+----------+-------------+--------------------+-------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----------+-------------+--------------------+-------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 32 | xxxbat | 192.168.141.77:47128 | xxxxDB | Sleep | 1 | | NULL | 0.000 |
| 13864455 | xxxapp | 192.168.141.75:53594 | xxxxDB | Sleep | 48 | | NULL | 0.000 |
| 13864458 | xxxapp | 192.168.141.75:53596 | xxxxDB | Sleep | 20 | | NULL | 0.000 |
| 13864520 | xxxapp | 192.168.141.75:53608 | xxxxxDB | Sleep | 21 | | NULL | 0.000 |
| 13865142 | xxxapp | 192.168.141.75:53680 | xxxxxDB | Sleep | 21 | | NULL | 0.000 |
| 13865509 | xxxapp | 192.168.141.75:53724 | xxxxxDB | Sleep | 21 | | NULL | 0.000 |
| 13865563 | xxxapp | 192.168.141.75:53732 | xxxxxDB | Sleep | 21 | | NULL | 0.000 |
| 13865618 | xxxapp | 192.168.141.75:53740 | xxxxxDB | Sleep | 21 | | NULL | 0.000 |
| 13866108 | xxxapp | 192.168.141.75:53778 | xxxxxDB | Sleep | 51 | | NULL | 0.000 |
| 13866170 | xxxapp | 192.168.141.75:53784 | xxxxxDB | Sleep | 51 | | NULL | 0.000 |
| 13866229 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
| 13866233 | xxxapp | 192.168.141.75:53792 | xxxxxDB | Sleep | 51 | | NULL | 0.000 |
| 13867099 | xxxbat | 192.168.141.77:55098 | xxxxDB | Sleep | 118 | | NULL | 0.000 |
| 13867106 | xxxbat | 192.168.141.77:55112 | xxxxDB | Sleep | 114 | | NULL | 0.000 |
| 13867113 | xxxbat | 192.168.141.77:55132 | xxxxDB | Sleep | 110 | | NULL | 0.000 |
| 13867121 | xxxbat | 192.168.141.77:55148 | xxxxDB | Sleep | 106 | | NULL | 0.000 |
| 13867128 | xxxbat | 192.168.141.77:55164 | xxxxDB | Sleep | 102 | | NULL | 0.000 |
| 13867135 | xxxbat | 192.168.141.77:55178 | xxxxDB | Sleep | 98 | | NULL | 0.000 |
| 13867142 | xxxbat | 192.168.141.77:55194 | xxxxDB | Sleep | 95 | | NULL | 0.000 |
| 13867149 | xxxbat | 192.168.141.77:55208 | xxxxDB | Sleep | 91 | | NULL | 0.000 |
| 13867156 | xxxbat | 192.168.141.77:55222 | xxxxDB | Sleep | 88 | | NULL | 0.000 |
| 13867163 | xxxbat | 192.168.141.77:55236 | xxxxDB | Sleep | 85 | | NULL | 0.000 |
| 13867171 | xxxbat | 192.168.141.77:55252 | xxxxDB | Sleep | 81 | | NULL | 0.000 |
| 13867178 | xxxbat | 192.168.141.77:55268 | xxxxDB | Sleep | 77 | | NULL | 0.000 |
| 13867185 | xxxbat | 192.168.141.77:55284 | xxxxDB | Sleep | 73 | | NULL | 0.000 |
| 13867192 | xxxbat | 192.168.141.77:55300 | xxxxDB | Sleep | 70 | | NULL | 0.000 |
| 13867199 | xxxbat | 192.168.141.77:55316 | xxxxDB | Sleep | 67 | | NULL | 0.000 |
| 13867206 | xxxbat | 192.168.141.77:55330 | xxxxDB | Sleep | 62 | | NULL | 0.000 |
| 13867214 | xxxbat | 192.168.141.77:55354 | xxxxDB | Sleep | 59 | | NULL | 0.000 |
| 13867221 | xxxbat | 192.168.141.77:55368 | xxxxDB | Sleep | 55 | | NULL | 0.000 |
| 13867228 | xxxbat | 192.168.141.77:55382 | xxxxDB | Sleep | 52 | | NULL | 0.000 |
| 13867235 | xxxbat | 192.168.141.77:55400 | xxxxDB | Sleep | 49 | | NULL | 0.000 |
| 13867242 | xxxbat | 192.168.141.77:55414 | xxxxDB | Sleep | 45 | | NULL | 0.000 |
| 13867249 | xxxbat | 192.168.141.77:55432 | xxxxDB | Sleep | 42 | | NULL | 0.000 |
| 13867256 | xxxbat | 192.168.141.77:55448 | xxxxDB | Sleep | 39 | | NULL | 0.000 |
| 13867264 | xxxbat | 192.168.141.77:55466 | xxxxDB | Sleep | 35 | | NULL | 0.000 |
| 13867271 | xxxbat | 192.168.141.77:55480 | xxxxDB | Sleep | 32 | | NULL | 0.000 |
| 13867278 | xxxbat | 192.168.141.77:55494 | xxxxDB | Sleep | 29 | | NULL | 0.000 |
| 13867285 | xxxbat | 192.168.141.77:55510 | xxxxDB | Sleep | 24 | | NULL | 0.000 |
| 13867292 | xxxbat | 192.168.141.77:55528 | xxxxDB | Sleep | 21 | | NULL | 0.000 |
| 13867299 | xxxbat | 192.168.141.77:55542 | xxxxDB | Sleep | 17 | | NULL | 0.000 |
| 13867313 | root | localhost | xxxxDB | Query | 11 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ xxxx, xxxx, xxxxx, xxxx, xxxx | 0.000 |
| 13867315 | xxxbat | 192.168.141.77:55576 | xxxxDB | Sleep | 9 | | NULL | 0.000 |
| 13867322 | xxxbat | 192.168.141.77:55592 | xxxxDB | Sleep | 6 | | NULL | 0.000 |
| 13867329 | xxxbat | 192.168.141.77:55608 | xxxxDB | Sleep | 3 | | NULL | 0.000 |
| 13867336 | xxxbat | 192.168.141.77:55626 | xxxxDB | Sleep | 0 | | NULL | 0.000 |
+----------+-------------+--------------------+-------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
55 rows in set (0.000 sec)
MariaDB [(none)]>별도 LOCK이 없는 것을 확인할 수 있다.
2) OUTFILE 사용하는 방법
파티션 테이블등 대용량 테이블의 경우 OUTFILE 을 사용한다.
특수문자등이 없다면 가장 빠르게 백업할 수 있는 방법이다.
ex)
mysql> select * from TB_TEST_HIST
INTO OUTFILE '/data/mariadb_tmp/TB_TEST_HIST_20230110.out';
MariaDB [(none)]> show processlist;
+----------+-------------+-------------------+-------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----------+-------------+-------------------+-------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 32 | xxxbat | 192.168.141.77:47128 | xxxxDB | Sleep | 18 | | NULL | 0.000 |
| 13659427 | xxxapp | 192.168.141.75:47836 | xxxxDB | Sleep | 59 | | NULL | 0.000 |
| 13666735 | xxxapp | 192.168.141.75:48756 | xxxxDB | Sleep | 59 | | NULL | 0.000 |
| 13667304 | xxxapp | 192.168.141.75:48820 | xxxxxxDB | Sleep | 1 | | NULL | 0.000 |
| 13667820 | xxxapp | 192.168.141.75:48888 | xxxxxxDB | Sleep | 1 | | NULL | 0.000 |
| 13667892 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
| 13668072 | xxxapp | 192.168.141.75:48930 | xxxxxxDB | Sleep | 1 | | NULL | 0.000 |
| 13668093 | xxxapp | 192.168.141.75:48942 | xxxxxxDB | Sleep | 1 | | NULL | 0.000 |
| 13668154 | xxxapp | 192.168.141.75:48948 | xxxxxxDB | Sleep | 31 | | NULL | 0.000 |
| 13668508 | xxxbat | 192.168.141.77:46352 | xxxxDB | Sleep | 119 | | NULL | 0.000 |
| 13668515 | xxxbat | 192.168.141.77:46370 | xxxxDB | Sleep | 111 | | NULL | 0.000 |
| 13668522 | xxxbat | 192.168.141.77:46386 | xxxxDB | Sleep | 108 | | NULL | 0.000 |
| 13668529 | xxxbat | 192.168.141.77:46400 | xxxxDB | Sleep | 102 | | NULL | 0.000 |
| 13668544 | xxxbat | 192.168.141.77:46430 | xxxxDB | Sleep | 86 | | NULL | 0.000 |
| 13668552 | xxxbat | 192.168.141.77:46446 | xxxxDB | Sleep | 82 | | NULL | 0.000 |
| 13668559 | xxxbat | 192.168.141.77:46464 | xxxxDB | Sleep | 79 | | NULL | 0.000 |
| 13668566 | xxxbat | 192.168.141.77:46478 | xxxxDB | Sleep | 75 | | NULL | 0.000 |
| 13668573 | xxxbat | 192.168.141.77:46496 | xxxxDB | Sleep | 71 | | NULL | 0.000 |
| 13668580 | xxxbat | 192.168.141.77:46510 | xxxxDB | Sleep | 68 | | NULL | 0.000 |
| 13668588 | xxxbat | 192.168.141.77:46530 | xxxxDB | Sleep | 64 | | NULL | 0.000 |
| 13668595 | xxxapp | 192.168.141.75:48996 | xxxxxxDB | Sleep | 31 | | NULL | 0.000 |
| 13668596 | xxxbat | 192.168.141.77:46544 | xxxxDB | Sleep | 61 | | NULL | 0.000 |
| 13668603 | xxxbat | 192.168.141.77:46558 | xxxxDB | Sleep | 57 | | NULL | 0.000 |
| 13668610 | xxxbat | 192.168.141.77:46576 | xxxxDB | Sleep | 53 | | NULL | 0.000 |
| 13668617 | xxxbat | 192.168.141.77:46592 | xxxxDB | Sleep | 50 | | NULL | 0.000 |
| 13668620 | root | localhost | xxxxDB | Query | 3 | Sending data | select * from TB_TEST_HIST
INTO OUTFILE '/data/mariadb_tmp/TB_TEST_HIST_20230110.out' | 0.000 |
| 13668625 | xxxbat | 192.168.141.77:46608 | xxxxDB | Sleep | 46 | | NULL | 0.000 |
| 13668632 | xxxbat | 192.168.141.77:46622 | xxxxDB | Sleep | 43 | | NULL | 0.000 |
| 13668640 | xxxbat | 192.168.141.77:46638 | xxxxDB | Sleep | 39 | | NULL | 0.000 |
| 13668647 | xxxbat | 192.168.141.77:46654 | xxxxDB | Sleep | 35 | | NULL | 0.000 |
| 13668653 | xxxapp | 192.168.141.75:49000 | xxxxxxDB | Sleep | 31 | | NULL | 0.000 |
| 13668655 | xxxbat | 192.168.141.77:46668 | xxxxDB | Sleep | 29 | | NULL | 0.000 |
| 13668662 | xxxbat | 192.168.141.77:46682 | xxxxDB | Sleep | 22 | | NULL | 0.000 |
| 13668669 | xxxbat | 192.168.141.77:46700 | xxxxDB | Sleep | 5 | | NULL | 0.000 |
| 13668670 | xxxbat | 192.168.141.77:46702 | xxxxDB | Sleep | 4 | | NULL | 0.000 |
| 13668671 | xxxbat | 192.168.141.77:46704 | xxxxDB | Sleep | 3 | | NULL | 0.000 |
| 13668672 | xxxapp | 192.168.141.75:49010 | xxxxxxDB | Sleep | 1 | | NULL | 0.000 |
+----------+-------------+-------------------+-------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
44 rows in set (0.000 sec)
별도 LOCK이 없는 것을 확인할 수 있다.
3. 실제 서비스DB에서는 어떤 것을 사용?
각 회사마다 sacle-out 방식이 MHA, maxscale, Galera Cluster, Xpand 등등.. 달라서
뭐라고 딱집어서 말하긴 그렇지만
많이 사용하는 MHA 구조에서는
서비스DB는 Master-Slave 로 이중화되어 있고
보통 전체 daily 백업은 Slave DB에서 xtrabackup을 사용한다.
테이블 단위로 백업이 필요하다면 DB상태와 테이블 사이즈를 봐서
Slave DB에서 mysqldump나 OUTFILE을 적절하게 사용한다.
Master DB는 웬만하면 안 건드린다.
끝.
참조:
https://jmkim.tistory.com/54
https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=theswice&logNo=60210698273
반응형
'RDB > mysql' 카테고리의 다른 글
| mysqld got signal 6 error / DB 재시작 에러 해결 1 (0) | 2023.05.04 |
|---|---|
| mysql stored procedure 내에서 NAME_CONST() 함수 사용으로 성능저하 문제 (0) | 2023.02.02 |
| mysql 특정 테이블에 권한 추가 에러 해결 / ERROR 1144 (42000): Illegal GRANT/REVOKE command (0) | 2022.10.14 |
| mysql int(10) / int(11) / int(20) / bigint(20) 괄호 안에 숫자 의미는 ? (0) | 2022.08.16 |
| .TRG 파일 이건 뭐야 (0) | 2021.03.23 |
dung beetle
취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기
