xtrabackup - (5) 복구 : incremental
2024. 11. 26. 00:25ㆍMySQL/Class
반응형
# 우선 이 포스팅을 보기 전에 "백업 : increment" 편에서 어떻게 백업을 진행했고, 어떤 시점에 어떤 데이터가 존재했는지를 확인하고 보시는 것을 추천드립니다.
1. base 백업본(전체백업)까지만 복구
(1) 백업 파일 가져오기
- crash 가 발생된 DB로 백업 파일을 복사합니다.
[root@mac18-02 data]# scp -r backup/ mysql@192.168.56.3:/data/.
mysql@192.168.56.3's password:
base.xbstream 100% 40MB 45.5MB/s 00:00
inc1.xbstream 100% 303KB 23.3MB/s 00:00
inc2.xbstream 100% 302KB 19.9MB/s 00:00
(2) 압축풀기
# xbstream 풀기
[root@mac18-01 data]# cd /data/decompress/
[root@mac18-01 decompress]# /mysql/xtrabackup/bin/xbstream -x < /data/backup/base.xbstream
[root@mac18-01 decompress]# ls
backup-my.cnf.qp mysql xtrabackup_binlog_info.qp
ibdata01.qp percona xtrabackup_checkpoints
ibdata02.qp performance_schema xtrabackup_info.qp
ibdata03.qp test xtrabackup_logfile.qp
# decompress
[root@mac18-01 decompress]# /mysql/xtrabackup/bin/xtrabackup --decompress --remove-original --target-dir=/data/decompress
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments: --decompress=1 --remove-original=1 --target-dir=/data/decompress
/mysql/xtrabackup/bin/xtrabackup version 2.4.25 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 90fe9d0)
241120 15:46:52 [01] decompressing ./ibdata01.qp
241120 15:46:53 [01] removing ./ibdata01.qp
.
.
.
241120 15:46:57 [01] decompressing ./xtrabackup_info.qp
241120 15:46:57 [01] removing ./xtrabackup_info.qp
241120 15:46:57 completed OK!
(3) prepare
[root@mac18-01 decompress]# /mysql/xtrabackup/bin/xtrabackup --prepare --target-dir=/data/decompress
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata01:1024M;ibdata02:1024M;ibdata03:1024M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=1073741824 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=100 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/data/decompress
.
.
.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 300670504
241120 15:48:33 completed OK!
(4) copy
[root@mac18-01 data_5529]# /mysql/xtrabackup/bin/xtrabackup --defaults-file=/etc/my_5529.cnf --user=root --copy-back --target-dir /data/decompress --socket=/tmp/mysql_5529.sock
xtrabackup: recognized server arguments:
.
.
.
241120 15:53:44 [01] ...done
241120 15:53:44 completed OK!
(5) 인스턴스 실행
[root@mac18-01 data]# chown -R mysql:mysql data_5529
[root@mac18-01 data]# /etc/init.d/mysqld_5529 start
Starting MySQL.. SUCCESS!
(6) 데이터 확인
2. 1차 증분백업본까지만 복구
# 전체 백업본 prepare
/mysql/xtrabackup/bin/xtrabackup --prepare --apply-log-only --target-dir=/data/decompress/base
# 1차 increment 백업본 prepare
/mysql/xtrabackup/bin/xtrabackup --prepare --incremental-dir=/data/decompress/inc1 --target-dir=/data/decompress/base
# 데이터 영역으로 copy
/mysql/xtrabackup/bin/xtrabackup --defaults-file=/etc/my_5529.cnf --user=root --copy-back --target-dir=/data/decompress/base
(1) 압축풀기
# base.xbstream 과 inc1.xbstream 각각 풀어 줄 폴더를 만들어줘야 합니다.
mkdir /data/decompress/base
mkdir /data/decompress/inc1
# base.xbstream 및 압축 풀기
/mysql/xtrabackup/bin/xbstream -x < /data/backup/base.xbstream
/mysql/xtrabackup/bin/xtrabackup --decompress --remove-original --target-dir=/data/decompress/base
# inc1.xbstream 및 압축 풀기
/mysql/xtrabackup/bin/xbstream -x < /data/backup/inc1.xbstream
/mysql/xtrabackup/bin/xtrabackup --decompress --remove-original --target-dir=/data/decompress/inc1
(2) full backup prepare
[root@mac18-01 inc1]# /mysql/xtrabackup/bin/xtrabackup --prepare --apply-log-only --target-dir=/data/decompress/base
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata01:1024M;ibdata02:1024M;ibdata03:1024M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=1073741824 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=100 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/data/decompress/base
/mysql/xtrabackup/bin/xtrabackup version 2.4.25 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 90fe9d0)
.
.
.
InnoDB: xtrabackup: Last MySQL binlog file position 193324693, file name ./bin.000005
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 300670513
InnoDB: Number of pools: 1
241120 17:17:48 completed OK!
(3) increment prepare
[root@mac18-01 inc1]# /mysql/xtrabackup/bin/xtrabackup --prepare --incremental-dir=/data/decompress/inc1 --target-dir=/data/decompress/base
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata01:1024M;ibdata02:1024M;ibdata03:1024M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=1073741824 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=100 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --incremental-dir=/data/decompress/inc1 --target-dir=/data/decompress/base
/mysql/xtrabackup/bin/xtrabackup version 2.4.25 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 90fe9d0)
incremental backup from 300621603 is enabled.
.
.
.
InnoDB: 5.7.35 started; log sequence number 300670513
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 300670532
241120 17:17:57 completed OK!
(4) copy
[root@mac18-01 decompress]# /mysql/xtrabackup/bin/xtrabackup --defaults-file=/etc/my_5529.cnf --user=root --copy-back --target-dir=/data/decompress/base
xtrabackup: recognized server arguments: --datadir=/data/data_5529 --innodb_adaptive_hash_index=0 --innodb_io_capacity=400 --innodb_use_native_aio=1 --server-id=400 --log_bin=bin --innodb_file_per_table=1 --innodb_buffer_pool_size=500M --innodb_data_file_path=ibdata01:1024M;ibdata02:1024M;ibdata03:1024M:autoextend --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_doublewrite=0 --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=1024M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=70 --innodb_flush_method=O_DIRECT
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql_5529.sock --target-dir=/data/backup --user=root --copy-back=1 --target-dir=/data/decompress/base
/mysql/xtrabackup/bin/xtrabackup version 2.4.25 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 90fe9d0)
241120 17:19:31 [01] Copying ib_logfile0 to /data/data_5529/ib_logfile0
241120 17:19:33 [01] ...done
241120 17:19:33 [01] Copying ib_logfile1 to /data/data_5529/ib_logfile1
241120 17:19:35 [01] ...done
241120 17:19:35 [01] Copying ib_logfile2 to /data/data_5529/ib_logfile2
241120 17:19:37 [01] ...done
241120 17:19:37 [01] Copying ibdata01 to /data/data_5529/ibdata01
.
.
.
241120 17:19:43 [01] ...done
241120 17:19:43 [01] Copying ./ibtmp1 to /data/data_5529/ibtmp1
241120 17:19:43 [01] ...done
241120 17:19:43 completed OK!
(5) start
[root@mac18-01 data_5529]# chown -R mysql:mysql *
[root@mac18-01 data_5529]# /etc/init.d/mysqld_5529 start
Starting MySQL.. SUCCESS!
(6) 데이터 확인
3. 2차 증분백업본까지만 복구
# base 와 inc1은 앞서 처리를 했기때문에 생략하도록 하겠습니다. 2차 증분백업본까지 하고자 한다면 위 1차 증분백업본 설명의 prepare까지 처리를 하면 됩니다.
(1) 폴더 및 압축풀기
mkdir /data/decompress/inc2[root@mac18-01 inc2]# /mysql/xtrabackup/bin/xbstream -x < /data/backup/inc2.xbstream
[root@mac18-01 inc2]# ls
backup-my.cnf.qp ibdata03.delta.qp test
ibdata01.delta.qp ibdata03.meta xtrabackup_binlog_info.qp
ibdata01.meta mysql xtrabackup_checkpoints
ibdata02.delta.qp percona xtrabackup_info.qp
ibdata02.meta performance_schema xtrabackup_logfile.qp
[root@mac18-01 inc2]# /mysql/xtrabackup/bin/xtrabackup --decompress --remove-original --target-dir=/data/decompress/inc2
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments: --decompress=1 --remove-original=1 --target-dir=/data/decompress/inc2
/mysql/xtrabackup/bin/xtrabackup version 2.4.25 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 90fe9d0)
241121 08:33:26 [01] decompressing ./ibdata01.delta.qp
241121 08:33:26 [01] removing ./ibdata01.delta.qp
.
.
.
241121 08:33:26 [01] decompressing ./xtrabackup_info.qp
241121 08:33:26 [01] removing ./xtrabackup_info.qp
241121 08:33:26 completed OK!
(2) base & inc1 prepare
# 앞서 처리하였기에 생략하도록 하겠습니다.
(3) inc2 prepare
[root@mac18-01 inc2]# /mysql/xtrabackup/bin/xtrabackup --prepare --incremental-dir=/data/decompress/inc1 --target-dir=/data/decompress/base
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata01:1024M;ibdata02:1024M;ibdata03:1024M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=1073741824 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=100 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --incremental-dir=/data/decompress/inc1 --target-dir=/data/decompress/base
/mysql/xtrabackup/bin/xtrabackup version 2.4.25 based on MySQL server 5.7.35 Linux (x86_64) (revision id: 90fe9d0)
incremental backup from 300621603 is enabled.
xtrabackup: cd to /data/decompress/base/
xtrabackup: This target seems to be already prepared with --apply-log-only.
.
.
.
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 300670560
241121 08:59:42 completed OK!
(4) copy
[root@mac18-01 decompress]# /mysql/xtrabackup/bin/xtrabackup --defaults-file=/etc/my_5529.cnf --user=root --copy-back --target-dir=/data/decompress/base
xtrabackup: recognized server arguments: --datadir=/data/data_5529 --innodb_adaptive_hash_index=0 --innodb_io_capacity=400 --innodb_use_native_aio=1 --server-id=400 --log_bin=bin --innodb_file_per_table=1 --innodb_buffer_pool_size=500M --
.
.
.
241121 09:04:43 [01] ...done
241121 09:04:43 [01] Copying ./ibtmp1 to /data/data_5529/ibtmp1
241121 09:04:43 [01] ...done
241121 09:04:43 completed OK!
(5) start
[root@mac18-01 data]# chown -R mysql:mysql data_5529
[root@mac18-01 data]# /etc/init.d/mysqld_5529 start
Starting MySQL.. SUCCESS!
(6) 데이터 확인
4. replication 연결
# 최종 데이터는 9건이 되어야 합니다. 이것은 binlog 를 통해 데이터가 동기화 되면 마지막 데이터까지 복구가 될 것입니다.
(1) binlog_file 과 binlog_position 확인
# inc2 백업본 파일에 존재하는 xtrabackup_info 파일 확인
[root@mac18-01 inc2]# cat xtrabackup_info
.
.
binlog_pos = filename 'bin.000005', position '193325887'
.
.
(2) replication 설정
MariaDB [test]> reset slave all;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> change master to master_host='192.168.56.4', master_port=3306, master_user='replicator', master_password='1234', master_log_file='bin.000005', master_log_pos=193325887;
Query OK, 0 rows affected (0.02 sec)
MariaDB [test]> start slave;
Query OK, 0 rows affected (0.00 sec)
(3) slave 상태 확인
MariaDB [test]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.4
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000005
Read_Master_Log_Pos: 193326435
Relay_Log_File: relay.000002
Relay_Log_Pos: 1071
Relay_Master_Log_File: bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 193326435
Relay_Log_Space: 1355
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
1 row in set (0.00 sec)
(4) 데이터 확인
반응형
'MySQL > Class' 카테고리의 다른 글
| xtrabackup - (4) 백업 : incremental (2) | 2024.11.25 |
|---|---|
| xtrabackup - (3) qpress 설치 (0) | 2024.11.24 |
| xtrabackup - (2) 복구 : 기본편 (0) | 2024.11.23 |
| xtrabackup - (1) 백업 : 기본편 (0) | 2024.11.22 |
| Load Data : 파일 내용 넣기 (0) | 2024.11.20 |