[MySQL] GTID mode 테스트 - Xtrabackup 백업 생성 및 복원

hwwwa·2024년 10월 2일
mysql
0
post-custom-banner

이전 포스트 👉 MySQL GTID 모드에서 MHA 고가용성 테스트
이번 포스트에서 다룰 주제는 GTID mode 에서의 Xtrabackup 백업 파일 생성 및 복원 테스트입니다.

Xtrabackup 테스트

standalone 구조

  • 전체 백업

    $ mysql/bin/xtrabackup --defaults-file=my.cnf --user=admin --password='****' --socket=mysql/tmp/mysql.sock --port=13306 \
  --ftwrl-wait-threshold=0 --ftwrl-wait-timeout=180 --compress --compress-threads=4 --backup \
  --target-dir=/home/user/admin/backup/full
...
2024-09-03T08:26:32.259139+09:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (41876139) to (41876139) was copied.
2024-09-03T08:26:32.371235+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ cat /home/user/admin/backup/full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 41876139
last_lsn = 41876139
flushed_lsn = 41876139
redo_memory = 0
redo_frames = 0

  • 백업 파일 준비

    $ mysql/bin/xtrabackup --decompress --decopress-threads=4 --target-dir=/home/user/admin/backup/full

$ mysql/bin/xtrabackup --prepare --target-dir=/home/user/admin/backup/full
...
2024-09-04T11:13:40.235734+09:00 0 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 9320
...
2024-09-04T11:13:41.352306+09:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2024-09-04T11:13:41.368819+09:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 41876502
2024-09-04T11:13:41.372821+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

  • 복원

    $ sudo systemctl stop mysqld.service

$ rm -rf mysql/data/*
$ mysql/bin/xtrabackup --defaults-file=mysql/my.cnf --copy-back --force-non-empty-directories --target-dir=/home/user/admin/backup/full
...
2024-09-05T17:54:43.418301+09:00 1 [Note] [MY-011825] [Xtrabackup] Creating directory ./#innodb_redo
2024-09-05T17:54:43.418386+09:00 1 [Note] [MY-011825] [Xtrabackup] Done: creating directory ./#innodb_redo
2024-09-05T17:54:43.418475+09:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to mysql/data/ibtmp1
2024-09-05T17:54:43.607132+09:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to mysql/data/ibtmp1
2024-09-05T17:54:43.624266+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ sudo systemctl start mysqld.service

  • 기동 후 확인

    $ less mysql/data/error.log 
# 별다른 에러메세지 없음

mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON          |
+-------------+
1 row in set (0.00 sec)

mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| ON                         |
+----------------------------+
1 row in set (0.00 sec)

이상 없음. 기존과 동일 커맨드 사용

MHA 고가용성 구조

https://docs.percona.com/percona-xtrabackup/8.0/create-gtid-replica.html

  • slave 에서 전체 백업 (--slave-info 옵션 추가)

    $ mysql/bin/xtrabackup --defaults-file=mysql/my.cnf --user=admin --password='****' --socket=mysql/tmp/mysql.sock --port=13306 --ftwrl-wait-threshold=0 --ftwrl-wait-timeout=180 --slave-info --compress --compress-threads=4 --backup --target-dir=/home/user/admin/backup/full
...
2024-09-10T10:43:31.562206+09:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'mysql-bin.000029', position '237', GTID of the last change '10800907-6e6c-11ef-99bc-fa163e0caee2:1-60028,83019ac0-6b64-11ef-8bdf-fa163ed63ea8:1-174415'
2024-09-10T10:43:31.562266+09:00 0 [Note] [MY-011825] [Xtrabackup] MySQL slave binlog position: master host '***', purge list '10800907-6e6c-11ef-99bc-fa163e0caee2:1-60028,83019ac0-6b64-11ef-8bdf-fa163ed63ea8:1-174415', channel name: '' 
2024-09-10T10:43:31.562374+09:00 0 [Note] [MY-011825] [Xtrabackup] Compressing /home/user/admin/backup/full/backup-my.cnf.zst
2024-09-10T10:43:31.562452+09:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing file //home/user/admin/backup/full/backup-my.cnf.zst
2024-09-10T10:43:31.563493+09:00 0 [Note] [MY-011825] [Xtrabackup] Compressing /home/user/admin/backup/full/xtrabackup_info.zst
2024-09-10T10:43:31.563601+09:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing file /home/user/admin/backup/full/xtrabackup_info.zst
2024-09-10T10:43:32.565401+09:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (161460371) to (166581668) was copied.
2024-09-10T10:43:32.679892+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ cat /home/user/admin/backup/full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 161460371
last_lsn = 166581280
flushed_lsn = 166577032
redo_memory = 0
redo_frames = 0

$ cat /home/user/admin/backup/full/xtrabackup_binlog_info
mysql-bin.000029        237     10800907-6e6c-11ef-99bc-fa163e0caee2:1-60028,83019ac0-6b64-11ef-8bdf-fa163ed63ea8:1-174415

  • 백업 파일 준비

    $ mysql/bin/xtrabackup --decompress --decopress-threads=4 --target-dir=/home/user/admin/backup/full
...
2024-09-10T10:44:47.174796+09:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./ib_buffer_pool.zst
2024-09-10T10:44:47.177695+09:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./backup-my.cnf.zst
2024-09-10T10:44:47.180634+09:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./xtrabackup_info.zst
2024-09-10T10:44:47.183464+09:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./xtrabackup_tablespaces.zst
2024-09-10T10:44:47.204615+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ mysql/bin/xtrabackup --prepare --target-dir=/home/user/admin/backup/full
...
2024-09-10T10:45:06.567492+09:00 0 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 318095
2024-09-10T10:45:06.573378+09:00 0 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 1 thread: 5882 ms.
2024-09-10T10:45:06.573605+09:00 0 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2024-09-10T10:45:06.574421+09:00 0 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-09-10T10:45:06.610406+09:00 0 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2024-09-10T10:45:06.622103+09:00 0 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
2024-09-10T10:45:06.631026+09:00 0 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
2024-09-10T10:45:06.652723+09:00 0 [Note] [MY-012976] [InnoDB] 8.0.34 started; log sequence number 166581782
2024-09-10T10:45:06.653496+09:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0
2024-09-10T10:45:06.653636+09:00 0 [Note] [MY-011825] [Xtrabackup] Completed loading of 3 tablespaces into cache in 0.000750252 seconds
2024-09-10T10:45:06.797825+09:00 0 [Note] [MY-011825] [Xtrabackup] Time taken to build dictionary: 0.144152 seconds
2024-09-10T10:45:06.820707+09:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1
2024-09-10T10:45:06.820960+09:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2024-09-10T10:45:07.809173+09:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2024-09-10T10:45:07.911256+09:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2024-09-10T10:45:07.931566+09:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 166581782
2024-09-10T10:45:07.936935+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

  • 복원

    $ sudo systemctl stop mysqld.service

$ rm -rf mysql/data/*
$ rm -rf mysql/data/.work*
$ mysql/bin/xtrabackup --defaults-file=mysql/my.cnf --copy-back --force-non-empty-directories --target-dir=/home/user/admin/backup/full
...
2024-09-10T10:51:39.765147+09:00 1 [Note] [MY-011825] [Xtrabackup] Copying ./ibtmp1 to mysql/data/ibtmp1
2024-09-10T10:51:39.785037+09:00 1 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibtmp1 to mysql/data/ibtmp1
2024-09-10T10:51:39.870578+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ sudo systemctl start mysqld.service

  • 기동 후 복제 연결

    $ cat /home/user/admin/backup/full/xtrabackup_slave_info
SET GLOBAL gtid_purged='10800907-6e6c-11ef-99bc-fa163e0caee2:1-60028,83019ac0-6b64-11ef-8bdf-fa163ed63ea8:1-174415';
CHANGE MASTER TO MASTER_AUTO_POSITION=1;

mysql> SHOW SLAVE STATUS\G
mysql> RESET SLAVE ALL;
mysql> SET GLOBAL gtid_purged='10800907-6e6c-11ef-99bc-fa163e0caee2:1-60028,83019ac0-6b64-11ef-8bdf-fa163ed63ea8:1-174415';
mysql> CHANGE MASTER TO MASTER_HOST ="***", MASTER_USER ="repl", MASTER_PASSWORD ="***", MASTER_PORT = 13306, MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: ***
                  Master_User: ***
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 18837149
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 148926
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
                            ...
           Retrieved_Gtid_Set: 10800907-6e6c-11ef-99bc-fa163e0caee2:60029-60505
            Executed_Gtid_Set: 10800907-6e6c-11ef-99bc-fa163e0caee2:1-60505,
83019ac0-6b64-11ef-8bdf-fa163ed63ea8:1-174415
                Auto_Position: 1
                            ...

  • 확인

    $ less mysql/data/error.log 


mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON          |
+-------------+

mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| ON                         |
+----------------------------+

이상 없음. 기동 후 복제 연결 시 기존과 다른 커맨드 사용 필요

profile
hwwwa
이전 포스트

[MySQL] GTID mode 테스트 - MHA 고가용성

post-custom-banner

0개의 댓글