slave online hot backup 구축

고객이 늘면서 1000만 row가 넘어가는 테이블이 속속들이 생기고 있다.

mysql 은 100만 row만 넘어도 대용량 테이블로 보고 관련테이블을 join으로 쓰는 쿼리는 최소한의 function 사용, procedure 사용지양하여

open table cache를 최소한으로 쓸 수있는 방법과 index 튜닝을 해왔는데..

이제는 그것만으로 버티긴 힘들다. 그래서 디스크를 SAS 에서 SSD로 교체작업을 진행중이고

Master 교체후 이제 Slave를 교체하는 작업을 진행하였다.

예전에는 서비스 중단후 slave 교체작업을 했었지만 이제는 xtrabackup의 도움을 받아 한번 도전해보고 싶다.

결론은 상용 서버에서 100% 무중단으로 slave 구축에 성공했고, 작업 전에 테스트기에서 했던 내용을 남긴다.

 

mysql replication은 참 편리한 기능이다.
하지만 replication 관련 에러가 났을 때 그것을 해결할 만한 레퍼런스는 많지 않다.
3년 전에 그렇게 한 5일을 고생하고 .. 결국 찾은 원인은 federated table 때문이었지만..
그 이후에는 그냥 점검할 떄 웬만하면 서비스 중단 후 작업을 진행했다.

 

이번에 온라인으로 상용기 slave를 구축하는 목적은 추후 로드밸런싱이나 모니터링 툴 도입시 slave를 추가로

구축할 수도 있어서 미리 해보기로 했다.

참조사이트는 성동찬님의 xtrabackup 부분을 참조했다.

 

# 테스트기 환경

1) 백업전 준비

실시간 트렌젝션은 지속된다.(상용 서버에 작업한다는 전제하에 최대한 동일한 환경을 구성)

Master는 계속해서 insert update가 일어나고 binlog에 데이터는 쌓인다.

--> xtrabackup 전에 임시테이블을 생성하고 insert into로 10만건을 밀어넣도록 했다. 백업 끝날때까지 안끝나도록 하는 것이 목적이었다. binlog 와 pos가 변경되고 xtrabackup 후에 apply log가 변경지점까지 잘따라갔는지 확인하기 위해서다.

 

2) 백업

Master DB<------------Slave DB

Slave 에서 Master로 가서 xtrabackup으로 데이터를 끌어온다.

ssh -o StrictHostKeyChecking=no mysql@192.168.10.101 \
 "innobackupex \
 --host="127.0.0.1" \
 --port=3306 \
 --user=root \
 --password="DB비번" \
 --no-lock \
 --stream=xbstream \
 /backup/temp | pv --rate-limit 50000000" 2> innobackupex.log \
 | xbstream -x 2> xbstream.log

 

흠.. 뭐 바로 될거라고 생각안했지만 일단 xtrabackup shell이 제대로 동작을 안한다.

구글링해보니 xtrabackup 2.4.12 ver이상부터 된다는 얘기가 있다.

음... 지금보니 xtarbackup 2.4.9다. version 패치한다.

 

[root@mysqldb_tmp]# rpm -Uvh percona-xtrabackup-24-2.4.19-1.el6.x86_64.rpm
경고: percona-xtrabackup-24-2.4.19-1.el6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
준비 중...               ########################################### [100%]
   1:percona-xtrabackup-24  ########################################### [100%]
[root@mysqldb_tmp]# rpm -Uvh percona-xtrabackup-24-debuginfo-2.4.19-1.el6.x86_64.rpm
경고: percona-xtrabackup-24-debuginfo-2.4.19-1.el6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
준비 중...               ########################################### [100%]
   1:percona-xtrabackup-24-d########################################### [100%]

 

xtarbackup 2.4.19로 올리니 잘된다. version 문제였다.

백업이 잘되었는지 여부는

마지막에 xbstream.log에 아무것도 없고, innodbackupex.log에 completed 를 확인하면된다.

 

 

# shell 분석

# xtrabackup 옵션

--stream=name
이 옵션에 명시된 포맷으로 백업 데이터를 stream한다. 현재는 xbstream과 tar를 지원한다.
스트리밍 기능을 사용하려면 --stream 옵션을 사용하여 스트림 형식은 (tar 또는 xbstream)을 사용하고
임시 파일을 저장할 위치를 제공해야 합니다.

--> stream의 임시저장 경로(/backup/temp) 은 어디에 만들어야할까?  Master다. Slave에서는 데이터 끌어올 위치에서 실행하면 그 위치에 데이터가 쌓이는 것이지 실제 쉘에서 쓰는 /backup/temp는 마스터에 있어야할 폴더이고 이게 없으면 innodbackup.log에서 에러를 뱉어내고 실행 안된다.

 

--no-lock
이 옵션을 사용하게 되면 FLUSH TABLES WITH READ LOCK을 실행하지 않는다. 이옵션을 사용하지 않아도
일관성에 문제가 없다고 판단하는 경우에만 사용하도록 한다. 만약 백업 도중에 DDL이 발생할 수 있다면
사용하지 않는 것이 좋다. 이 옵션을 백업을 위해 락을 잡는데 문제가 된다고 생각하는 경우에만 사용하도록 한다.
--safe-slave-backup 을 사용하여 잠시라도 리플리케이션 슬레이브 스레드를 중지하게 되면 성공적으로 백업을
진행할 수 있게 도움을 줄 수 있다. 하지만, 이 옵션에 의존적일 필요는 없다.
xtrabackup_binlog_info 파일은 이 옵션을 사용하면 생성되지 않는다. 대신에 xtrabackup_binlog_pos_innodb 파일을
사용할 수 있다.

--> xtrabackup을 하게되면 xtrabackup_binlog_info, xtrabackup_binlog_pos_innodb 둘다 binlog 파일이름과 pos가 생긴다. 둘중에 뭘 써야할 지 몰랐는데 이번에 shell분석하면서 확실히 알게되었다. no-lock 는 xtrabackup_binlog_pos_innodb 다.

 

# pv 옵션

- linux 에서 대용량 파일 복사시 진행 상황 표시 및 중단시 이어서 복사 (cp 명령어 대체)
pv 는 콘솔 프로그램으로 다음과 같은 특징이 있습니다.
•pipe 기반으로 동작하여 속도가 빠름
•파일의 복사/이동 진행 상태를 확인할 수 있음
•총 전송량과 예상 시간을 표시
•중단후 다시 시작할 경우 이후부터 시작

--rate-limit 옵션
이 옵션을 사용하여 처리 속도를 제한할 수 있다. 이 옵션의 인수로는 하나의 숫자와 하나의 접미어(예: MB/초를 나타내는 m)가 있다

--> 온라인 hot 백업시 가장 큰 고민이었다. 분명 ssh로 끌어온다면 Master의 네트웍 부하를 최소화해야하는데 방법을 고민했는데, pv 라는 좋은 것을 알게 되었다.

50M로 끊어서 전송한다.

 

3) 재생성

 

3-1) apply log 복구

 

xtrabackup은 백업 도중에 Master에서 발생한 트랜젝션을 xtrabackup_logfile에 쌓아놓고 xtrabackup이 끝날 때까지 쌓는다.

apply log를 실행하게되면 xtrabackup_logfile 파일을 읽어 xtrabackup 동안 추가된 트랜젝션 데이터를 /data/영역에 merge하고 마지막 binlog 파일과 pos를 xtrabackup_binlog_pos_innodb에 남긴다.

--> xtrabackup_logfile 을 한번 보고 싶어서 mysqlbinlog로 열어봤는데 안열린다. 다른 파일포맷인가보다...

일단 임시테이블 생성하고 발생한 트랜젝션이 apply log 실행으로 제대로 merge 되는지 확인했다.

백업시작시 0건에서 시작한 임시테이블 count(*)가 xtrabackup이 끝났을 떄 54689건이 되었다.

xtrabackup 도중에 발생한 트랜젝션이 제대로 쌓였음을 확인했다.

 

[root@backup]# /usr/bin/innobackupex --defaults-file=/etc/my.cnf --datadir=/data/mysqldb_data  --apply-log /backup2/temp/temp

xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=268435456 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 --datadir=/data/mysqldb_data 
xtrabackup: recognized client arguments: 
200403 15:37:35 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

/usr/bin/innobackupex version 2.4.19 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c2d69da)
xtrabackup: cd to /backup2/temp/temp/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=682229760, start_lsn=(232065897958)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 682229760
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 682229760
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 232065897958
InnoDB: Doing recovery: scanned up to log sequence number 232071140352 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 232076383232 (1%)
InnoDB: Doing recovery: scanned up to log sequence number 232081626112 (2%)
InnoDB: Doing recovery: scanned up to log sequence number 232086868992 (3%)
InnoDB: Doing recovery: scanned up to log sequence number 232092111872 (4%)
InnoDB: Doing recovery: scanned up to log sequence number 232097354752 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 232102597632 (6%)
InnoDB: Doing recovery: scanned up to log sequence number 232107840512 (6%)
InnoDB: Doing recovery: scanned up to log sequence number 232113083392 (7%)
InnoDB: Doing recovery: scanned up to log sequence number 232118326272 (8%)
InnoDB: Doing recovery: scanned up to log sequence number 232123569152 (9%)
InnoDB: Doing recovery: scanned up to log sequence number 232128812032 (10%)
InnoDB: Doing recovery: scanned up to log sequence number 232134054912 (11%)
InnoDB: Doing recovery: scanned up to log sequence number 232139297792 (12%)
InnoDB: Doing recovery: scanned up to log sequence number 232144540672 (12%)
InnoDB: Doing recovery: scanned up to log sequence number 232149783552 (13%)
InnoDB: Doing recovery: scanned up to log sequence number 232155026432 (14%)
InnoDB: Doing recovery: scanned up to log sequence number 232160269312 (15%)
InnoDB: Doing recovery: scanned up to log sequence number 232165512192 (16%)
InnoDB: Doing recovery: scanned up to log sequence number 232170755072 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 232175997952 (18%)
InnoDB: Doing recovery: scanned up to log sequence number 232181240832 (19%)
InnoDB: Doing recovery: scanned up to log sequence number 232186483712 (19%)
InnoDB: Doing recovery: scanned up to log sequence number 232191726592 (20%)
InnoDB: Doing recovery: scanned up to log sequence number 232196969472 (21%)
InnoDB: Doing recovery: scanned up to log sequence number 232202212352 (22%)
InnoDB: Doing recovery: scanned up to log sequence number 232207455232 (23%)
InnoDB: Doing recovery: scanned up to log sequence number 232212698112 (24%)
InnoDB: Doing recovery: scanned up to log sequence number 232217940992 (25%)
InnoDB: Doing recovery: scanned up to log sequence number 232223183872 (25%)
InnoDB: Doing recovery: scanned up to log sequence number 232228426752 (26%)
InnoDB: Doing recovery: scanned up to log sequence number 232233669632 (27%)
InnoDB: Doing recovery: scanned up to log sequence number 232238912512 (28%)
InnoDB: Doing recovery: scanned up to log sequence number 232244155392 (29%)
InnoDB: Doing recovery: scanned up to log sequence number 232249398272 (30%)
InnoDB: Doing recovery: scanned up to log sequence number 232254641152 (31%)
InnoDB: Doing recovery: scanned up to log sequence number 232259884032 (31%)
InnoDB: Doing recovery: scanned up to log sequence number 232265126912 (32%)
InnoDB: Doing recovery: scanned up to log sequence number 232270369792 (33%)
InnoDB: Doing recovery: scanned up to log sequence number 232275612672 (34%)
InnoDB: Doing recovery: scanned up to log sequence number 232280855552 (35%)
InnoDB: Doing recovery: scanned up to log sequence number 232286098432 (36%)
InnoDB: Doing recovery: scanned up to log sequence number 232291341312 (37%)
InnoDB: Doing recovery: scanned up to log sequence number 232296584192 (38%)
InnoDB: Doing recovery: scanned up to log sequence number 232301827072 (38%)
InnoDB: Doing recovery: scanned up to log sequence number 232307069952 (39%)
InnoDB: Doing recovery: scanned up to log sequence number 232312312832 (40%)
InnoDB: Doing recovery: scanned up to log sequence number 232317555712 (41%)
InnoDB: Doing recovery: scanned up to log sequence number 232322798592 (42%)
InnoDB: Doing recovery: scanned up to log sequence number 232328041472 (43%)
InnoDB: Doing recovery: scanned up to log sequence number 232333284352 (44%)
InnoDB: Doing recovery: scanned up to log sequence number 232338527232 (44%)
InnoDB: Doing recovery: scanned up to log sequence number 232343770112 (45%)
InnoDB: Doing recovery: scanned up to log sequence number 232349012992 (46%)
InnoDB: Doing recovery: scanned up to log sequence number 232354255872 (47%)
InnoDB: Doing recovery: scanned up to log sequence number 232359498752 (48%)
InnoDB: Doing recovery: scanned up to log sequence number 232364741632 (49%)
InnoDB: Doing recovery: scanned up to log sequence number 232369984512 (50%)
InnoDB: Doing recovery: scanned up to log sequence number 232375227392 (51%)
InnoDB: Doing recovery: scanned up to log sequence number 232380470272 (51%)
InnoDB: Doing recovery: scanned up to log sequence number 232385713152 (52%)
InnoDB: Doing recovery: scanned up to log sequence number 232390956032 (53%)
InnoDB: Doing recovery: scanned up to log sequence number 232396198912 (54%)
InnoDB: Doing recovery: scanned up to log sequence number 232401441792 (55%)
InnoDB: Doing recovery: scanned up to log sequence number 232406684672 (56%)
InnoDB: Doing recovery: scanned up to log sequence number 232411927552 (57%)
InnoDB: Doing recovery: scanned up to log sequence number 232417170432 (57%)
InnoDB: Doing recovery: scanned up to log sequence number 232422413312 (58%)
InnoDB: Doing recovery: scanned up to log sequence number 232427656192 (59%)
InnoDB: Doing recovery: scanned up to log sequence number 232432899072 (60%)
InnoDB: Doing recovery: scanned up to log sequence number 232438141952 (61%)
InnoDB: Doing recovery: scanned up to log sequence number 232443384832 (62%)
InnoDB: Doing recovery: scanned up to log sequence number 232448627712 (63%)
InnoDB: Doing recovery: scanned up to log sequence number 232453870592 (63%)
InnoDB: Doing recovery: scanned up to log sequence number 232459113472 (64%)
InnoDB: Doing recovery: scanned up to log sequence number 232464356352 (65%)
InnoDB: Doing recovery: scanned up to log sequence number 232469599232 (66%)
InnoDB: Doing recovery: scanned up to log sequence number 232474842112 (67%)
InnoDB: Doing recovery: scanned up to log sequence number 232480084992 (68%)
InnoDB: Doing recovery: scanned up to log sequence number 232485327872 (69%)
InnoDB: Doing recovery: scanned up to log sequence number 232490570752 (70%)
InnoDB: Doing recovery: scanned up to log sequence number 232495813632 (70%)
InnoDB: Doing recovery: scanned up to log sequence number 232501056512 (71%)
InnoDB: Doing recovery: scanned up to log sequence number 232506299392 (72%)
InnoDB: Doing recovery: scanned up to log sequence number 232511542272 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 232516785152 (74%)
InnoDB: Doing recovery: scanned up to log sequence number 232522028032 (75%)
InnoDB: Doing recovery: scanned up to log sequence number 232527270912 (76%)
InnoDB: Doing recovery: scanned up to log sequence number 232532513792 (76%)
InnoDB: Doing recovery: scanned up to log sequence number 232537756672 (77%)
InnoDB: Doing recovery: scanned up to log sequence number 232542999552 (78%)
InnoDB: Doing recovery: scanned up to log sequence number 232548242432 (79%)
InnoDB: Doing recovery: scanned up to log sequence number 232553485312 (80%)
InnoDB: Doing recovery: scanned up to log sequence number 232558728192 (81%)
InnoDB: Doing recovery: scanned up to log sequence number 232563971072 (82%)
InnoDB: Doing recovery: scanned up to log sequence number 232569213952 (82%)
InnoDB: Doing recovery: scanned up to log sequence number 232574456832 (83%)
InnoDB: Doing recovery: scanned up to log sequence number 232579699712 (84%)
InnoDB: Doing recovery: scanned up to log sequence number 232584942592 (85%)
InnoDB: Doing recovery: scanned up to log sequence number 232590185472 (86%)
InnoDB: Doing recovery: scanned up to log sequence number 232595428352 (87%)
InnoDB: Doing recovery: scanned up to log sequence number 232600671232 (88%)
InnoDB: Doing recovery: scanned up to log sequence number 232605914112 (89%)
InnoDB: Doing recovery: scanned up to log sequence number 232611156992 (89%)
InnoDB: Doing recovery: scanned up to log sequence number 232616399872 (90%)
InnoDB: Doing recovery: scanned up to log sequence number 232621642752 (91%)
InnoDB: Doing recovery: scanned up to log sequence number 232626885632 (92%)
InnoDB: Doing recovery: scanned up to log sequence number 232632128512 (93%)
InnoDB: Doing recovery: scanned up to log sequence number 232637371392 (94%)
InnoDB: Doing recovery: scanned up to log sequence number 232642614272 (95%)
InnoDB: Doing recovery: scanned up to log sequence number 232647857152 (95%)
InnoDB: Doing recovery: scanned up to log sequence number 232653100032 (96%)
InnoDB: Doing recovery: scanned up to log sequence number 232658342912 (97%)
InnoDB: Doing recovery: scanned up to log sequence number 232663585792 (98%)
InnoDB: Doing recovery: scanned up to log sequence number 232668828672 (99%)
InnoDB: Doing recovery: scanned up to log sequence number 232672354749 (100%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Doing recovery: scanned up to log sequence number 232067601408 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 232072844288 (1%)
InnoDB: Doing recovery: scanned up to log sequence number 232078087168 (2%)
InnoDB: Doing recovery: scanned up to log sequence number 232083330048 (2%)
InnoDB: Doing recovery: scanned up to log sequence number 232088572928 (3%)
InnoDB: Doing recovery: scanned up to log sequence number 232093815808 (4%)
InnoDB: Doing recovery: scanned up to log sequence number 232099058688 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 232104301568 (6%)
InnoDB: Doing recovery: scanned up to log sequence number 232109544448 (7%)
InnoDB: Doing recovery: scanned up to log sequence number 232114787328 (8%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232120030208 (8%)
InnoDB: Doing recovery: scanned up to log sequence number 232125273088 (9%)
InnoDB: Doing recovery: scanned up to log sequence number 232130515968 (10%)
InnoDB: Doing recovery: scanned up to log sequence number 232135758848 (11%)
InnoDB: Doing recovery: scanned up to log sequence number 232141001728 (12%)
InnoDB: Doing recovery: scanned up to log sequence number 232146244608 (13%)
InnoDB: Doing recovery: scanned up to log sequence number 232151487488 (14%)
InnoDB: Doing recovery: scanned up to log sequence number 232156730368 (14%)
InnoDB: Doing recovery: scanned up to log sequence number 232161973248 (15%)
InnoDB: Doing recovery: scanned up to log sequence number 232167216128 (16%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232172459008 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 232177701888 (18%)
InnoDB: Doing recovery: scanned up to log sequence number 232182944768 (19%)
InnoDB: Doing recovery: scanned up to log sequence number 232188187648 (20%)
InnoDB: Doing recovery: scanned up to log sequence number 232193430528 (21%)
InnoDB: Doing recovery: scanned up to log sequence number 232198673408 (21%)
InnoDB: Doing recovery: scanned up to log sequence number 232203916288 (22%)
InnoDB: Doing recovery: scanned up to log sequence number 232209159168 (23%)
InnoDB: Doing recovery: scanned up to log sequence number 232214402048 (24%)
InnoDB: Doing recovery: scanned up to log sequence number 232219644928 (25%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232224887808 (26%)
InnoDB: Doing recovery: scanned up to log sequence number 232230130688 (27%)
InnoDB: Doing recovery: scanned up to log sequence number 232235373568 (27%)
InnoDB: Doing recovery: scanned up to log sequence number 232240616448 (28%)
InnoDB: Doing recovery: scanned up to log sequence number 232245859328 (29%)
InnoDB: Doing recovery: scanned up to log sequence number 232251102208 (30%)
InnoDB: Doing recovery: scanned up to log sequence number 232256345088 (31%)
InnoDB: Doing recovery: scanned up to log sequence number 232261587968 (32%)
InnoDB: Doing recovery: scanned up to log sequence number 232266830848 (33%)
InnoDB: Doing recovery: scanned up to log sequence number 232272073728 (33%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232277316608 (34%)
InnoDB: Doing recovery: scanned up to log sequence number 232282559488 (35%)
InnoDB: Doing recovery: scanned up to log sequence number 232287802368 (36%)
InnoDB: Doing recovery: scanned up to log sequence number 232293045248 (37%)
InnoDB: Doing recovery: scanned up to log sequence number 232298288128 (38%)
InnoDB: Doing recovery: scanned up to log sequence number 232303531008 (39%)
InnoDB: Doing recovery: scanned up to log sequence number 232308773888 (40%)
InnoDB: Doing recovery: scanned up to log sequence number 232314016768 (40%)
InnoDB: Doing recovery: scanned up to log sequence number 232319259648 (41%)
InnoDB: Doing recovery: scanned up to log sequence number 232324502528 (42%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232329745408 (43%)
InnoDB: Doing recovery: scanned up to log sequence number 232334988288 (44%)
InnoDB: Doing recovery: scanned up to log sequence number 232340231168 (45%)
InnoDB: Doing recovery: scanned up to log sequence number 232345474048 (46%)
InnoDB: Doing recovery: scanned up to log sequence number 232350716928 (46%)
InnoDB: Doing recovery: scanned up to log sequence number 232355959808 (47%)
InnoDB: Doing recovery: scanned up to log sequence number 232361202688 (48%)
InnoDB: Doing recovery: scanned up to log sequence number 232366445568 (49%)
InnoDB: Doing recovery: scanned up to log sequence number 232371688448 (50%)
InnoDB: Doing recovery: scanned up to log sequence number 232376931328 (51%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232382174208 (52%)
InnoDB: Doing recovery: scanned up to log sequence number 232387417088 (53%)
InnoDB: Doing recovery: scanned up to log sequence number 232392659968 (53%)
InnoDB: Doing recovery: scanned up to log sequence number 232397902848 (54%)
InnoDB: Doing recovery: scanned up to log sequence number 232403145728 (55%)
InnoDB: Doing recovery: scanned up to log sequence number 232408388608 (56%)
InnoDB: Doing recovery: scanned up to log sequence number 232413631488 (57%)
InnoDB: Doing recovery: scanned up to log sequence number 232418874368 (58%)
InnoDB: Doing recovery: scanned up to log sequence number 232424117248 (59%)
InnoDB: Doing recovery: scanned up to log sequence number 232429360128 (59%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232434603008 (60%)
InnoDB: Doing recovery: scanned up to log sequence number 232439845888 (61%)
InnoDB: Doing recovery: scanned up to log sequence number 232445088768 (62%)
InnoDB: Doing recovery: scanned up to log sequence number 232450331648 (63%)
InnoDB: Doing recovery: scanned up to log sequence number 232455574528 (64%)
InnoDB: Doing recovery: scanned up to log sequence number 232460817408 (65%)
InnoDB: Doing recovery: scanned up to log sequence number 232466060288 (65%)
InnoDB: Doing recovery: scanned up to log sequence number 232471303168 (66%)
InnoDB: Doing recovery: scanned up to log sequence number 232476546048 (67%)
InnoDB: Doing recovery: scanned up to log sequence number 232481788928 (68%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232487031808 (69%)
InnoDB: Doing recovery: scanned up to log sequence number 232492274688 (70%)
InnoDB: Doing recovery: scanned up to log sequence number 232497517568 (71%)
InnoDB: Doing recovery: scanned up to log sequence number 232502760448 (72%)
InnoDB: Doing recovery: scanned up to log sequence number 232508003328 (72%)
InnoDB: Doing recovery: scanned up to log sequence number 232513246208 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 232518489088 (74%)
InnoDB: Doing recovery: scanned up to log sequence number 232523731968 (75%)
InnoDB: Doing recovery: scanned up to log sequence number 232528974848 (76%)
InnoDB: Doing recovery: scanned up to log sequence number 232534217728 (77%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232539460608 (78%)
InnoDB: Doing recovery: scanned up to log sequence number 232544703488 (78%)
InnoDB: Doing recovery: scanned up to log sequence number 232549946368 (79%)
InnoDB: Doing recovery: scanned up to log sequence number 232555189248 (80%)
InnoDB: Doing recovery: scanned up to log sequence number 232560432128 (81%)
InnoDB: Doing recovery: scanned up to log sequence number 232565675008 (82%)
InnoDB: Doing recovery: scanned up to log sequence number 232570917888 (83%)
InnoDB: Doing recovery: scanned up to log sequence number 232576160768 (84%)
InnoDB: Doing recovery: scanned up to log sequence number 232581403648 (85%)
InnoDB: Doing recovery: scanned up to log sequence number 232586646528 (85%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232591889408 (86%)
InnoDB: Doing recovery: scanned up to log sequence number 232597132288 (87%)
InnoDB: Doing recovery: scanned up to log sequence number 232602375168 (88%)
InnoDB: Doing recovery: scanned up to log sequence number 232607618048 (89%)
InnoDB: Doing recovery: scanned up to log sequence number 232612860928 (90%)
InnoDB: Doing recovery: scanned up to log sequence number 232618103808 (91%)
InnoDB: Doing recovery: scanned up to log sequence number 232623346688 (91%)
InnoDB: Doing recovery: scanned up to log sequence number 232628589568 (92%)
InnoDB: Doing recovery: scanned up to log sequence number 232633832448 (93%)
InnoDB: Doing recovery: scanned up to log sequence number 232639075328 (94%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232644318208 (95%)
InnoDB: Doing recovery: scanned up to log sequence number 232649561088 (96%)
InnoDB: Doing recovery: scanned up to log sequence number 232654803968 (97%)
InnoDB: Doing recovery: scanned up to log sequence number 232660046848 (97%)
InnoDB: Doing recovery: scanned up to log sequence number 232665289728 (98%)
InnoDB: Doing recovery: scanned up to log sequence number 232670532608 (99%)
InnoDB: Doing recovery: scanned up to log sequence number 232672354749 (100%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: xtrabackup: Last MySQL binlog file position 33415270, file name mysql-bin.000012
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: page_cleaner: 1000ms intended loop took 76339ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.26 started; log sequence number 232672354749
InnoDB: xtrabackup: Last MySQL binlog file position 33415270, file name mysql-bin.000012

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 232672356123
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 268435456
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 256 MB
InnoDB: Progress in MB:
 100 200
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB:
 100 200
InnoDB: Setting log file ./ib_logfile2 size to 256 MB
InnoDB: Progress in MB:
 100 200
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=232672356123
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 232672356364
InnoDB: Doing recovery: scanned up to log sequence number 232672356373 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 33415270, file name mysql-bin.000012
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: page_cleaner: 1000ms intended loop took 12821ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.26 started; log sequence number 232672356373
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 232672356392
200403 15:39:10 completed OK!

 

3-2) 데이터 복구

copy-back 이나 move-back를 하면된다. 하기전에 /data/mysqldb_data는 비우고 새로 생성해야한다.

/usr/bin/innobackupex --defaults-file=/etc/my.cnf --datadir=/data/mysqldb_data --copy-back /backup2/temp/temp

 

 

4) slave replication 연결

--> 현재는 Master는 10만건의 데이터가 전부 임시테이블에 들어가 있고 slave는 DB올리고 보니 54689이 쌓여있음을 확인했다. 데이터유실 없는 replication이 되려면 임시테이블의 데이터가 replication 후 10만건이 되면 된다.

vi xtrabackup_binlog_info
mysql-bin.000012        61288234

vi xtrabackup_binlog_pos_innodb
mysql-bin.000012        61287127

 

둘 중에 아래꺼를 slave 복구 때 적용하면 된다.

 

change master to
master_host='192.168.10.101',
master_user='repl',
master_password='repl',
master_port=3306,
master_log_file='mysql-bin.000012',
master_log_pos=61287127;

slave start;

 

--> show slave status\G; 로 동기화완료후 확인해보니 slave 임시테이블의 데이터가 10만건임을 확인했다.

replication 동기화 끝!! 데이터 유실없이 online slave 구축이 가능함을 확인했다.

 

참조사이트

https://gywn.net/tag/xtrabackup/