AWS RDS를 Percona XtraBackup을 사용하여 백업 및 복원하는 방법

퍼블릭 클라우드

AWS RDS를 Percona XtraBackup을 사용하여 백업 및 복원하는 방법

변군이글루 2024. 4. 16. 10:36

AWS RDS(MySQL DB 인스턴스)를 Percona XtraBackup을 사용하여 백업 및 복원하는 방법

Percona XtraBackup 설치

Percona XtraBackup 2.4
- Installing Percona XtraBackup on Debian and Ubuntu
Percona XtraBackup 8.0
- Use an APT repository to install Percona XtraBackup 8.0

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

$ sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
Selecting previously unselected package percona-release.
(Reading database ... 174669 files and directories currently installed.)
Preparing to unpack percona-release_latest.jammy_all.deb ...
Unpacking percona-release (1.0-27.generic) ...
Setting up percona-release (1.0-27.generic) ...
* Enabling the Percona Original repository
<*> All done!
==> Please run "apt-get update" to apply changes
* Enabling the Percona Release repository
<*> All done!
==> Please run "apt-get update" to apply changes
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.

For example, to enable the Percona Server 8.0 repository use:

  percona-release setup ps80

Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.

For more information, please visit:
  https://www.percona.com/doc/percona-repo-config/percona-release.html

sudo apt-get update

sudo apt-get install percona-xtrabackup-80

$ sudo apt-get install percona-xtrabackup-80
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcurl4-openssl-dev libdbd-mysql-perl libdbi-perl libev4
Suggested packages:
  libcurl4-doc libidn11-dev libkrb5-dev libldap2-dev librtmp-dev libssh2-1-dev libssl-dev libclone-perl libmldbm-perl libnet-daemon-perl
  libsql-statement-perl
The following NEW packages will be installed:
  libcurl4-openssl-dev libdbd-mysql-perl libdbi-perl libev4 percona-xtrabackup-80
0 upgraded, 5 newly installed, 0 to remove and 34 not upgraded.
Need to get 44.4 MB of archives.
After this operation, 140 MB of additional disk space will be used.
Do you want to continue? [Y/n]
...

xtrabackup --version

$ xtrabackup --version
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)

qpress(파일 압축 사용) 설치

sudo apt-get install -y qpress

MySQL 데이터베이스에 백업 사용자 추가

mysql -u root -p

xbackup_user 사용자 생성

CREATE USER 'xbackup_user'@'%' IDENTIFIED BY 'xPassword';

xbackup_user 사용자 권한 부여

GRANT BACKUP_ADMIN, RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, PROCESS, SUPER, CREATE, INSERT, SELECT ON *.* TO 'xbackup_user'@'%';

GRANT SELECT ON performance_schema.log_status TO 'xbackup_user'@'%';

GRANT SELECT ON performance_schema.keyring_component_status TO 'xbackup_user'@'%';

권한 변경 사항 적용

FLUSH PRIVILEGES;

728x90

Xtrabackup 데이터 백업

백업 디렉토리 생성

mkdir -p /data/backups

백업 실행

xtrabackup --defaults-file=/usr/local/mysql/my.cnf \
--backup \
--no-lock \
--user=xbackup_user \
--password=xPassword \
--target-dir=/data/backups

$ xtrabackup --defaults-file=/usr/local/mysql/my.cnf \
> --backup \
> --no-lock \
> --user=xbackup_user \
> --password=xPassword \
> --target-dir=/data/backups
2024-04-16T09:17:20.191515+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/usr/local/mysql/data 
2024-04-16T09:17:20.191691+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --backup=1 --no-lock=1 --user=xbackup_user --password=* --target-dir=/data/backups 
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
240416 09:17:20  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'xbackup_user'  (using password: YES).
240416 09:17:20  version_check Connected to MySQL server
240416 09:17:20  version_check Executing a version check against the server...
240416 09:17:20  version_check Done.
2024-04-16T09:17:20.302366+09:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: xbackup_user, password: set, port: 3306, socket: /tmp/mysql.sock
2024-04-16T09:17:20.308375+09:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.36
2024-04-16T09:17:20.321028+09:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2024-04-16T09:17:20.321067+09:00 0 [Note] [MY-011825] [Xtrabackup] cd to /usr/local/mysql/data
2024-04-16T09:17:20.321090+09:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1048576
2024-04-16T09:17:20.321141+09:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2024-04-16T09:17:20.321159+09:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2024-04-16T09:17:20.321171+09:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2024-04-16T09:17:20.321219+09:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2024-04-16T09:17:20.321236+09:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2024-04-16T09:17:20.321252+09:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2024-04-16T09:17:20.322764+09:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2024-04-16T09:17:20.397255+09:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: xbackup_user, password: set, port: 3306, socket: /tmp/mysql.sock
2024-04-16T09:17:20.402073+09:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2024-04-16T09:17:20.498138+09:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (20298551)
2024-04-16T09:17:20.499937+09:00 0 [Note] [MY-012953] [InnoDB] Disabling background ibuf IO read threads.
2024-04-16T09:17:20.706774+09:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
...
2024-04-16T09:17:26.813115+09:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (20298551) to (20298561) was copied.
2024-04-16T09:17:27.026593+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ ls -l /data/backups/
total 70708
-rw-r----- 1 root root      447 Apr 16 09:17 backup-my.cnf
-rw-r----- 1 root root      157 Apr 16 09:17 binlog.000004
-rw-r----- 1 root root       16 Apr 16 09:17 binlog.index
-rw-r----- 1 root root     5734 Apr 16 09:17 ib_buffer_pool
-rw-r----- 1 root root 12582912 Apr 16 09:17 ibdata1
drwxr-x--- 2 root root      143 Apr 16 09:17 mysql
-rw-r----- 1 root root 26214400 Apr 16 09:17 mysql.ibd
drwxr-x--- 2 root root     8192 Apr 16 09:17 performance_schema
drwxr-x--- 2 root root       28 Apr 16 09:17 sys
-rw-r----- 1 root root 16777216 Apr 16 09:17 undo_001
-rw-r----- 1 root root 16777216 Apr 16 09:17 undo_002
-rw-r----- 1 root root       18 Apr 16 09:17 xtrabackup_binlog_info
-rw-r----- 1 root root      127 Apr 16 09:17 xtrabackup_checkpoints
-rw-r----- 1 root root      537 Apr 16 09:17 xtrabackup_info
-rw-r----- 1 root root     2560 Apr 16 09:17 xtrabackup_logfile
-rw-r----- 1 root root       39 Apr 16 09:17 xtrabackup_tablespaces

MySQL 엔진 중지

bin/mysqladmin -u root -p shutdown

MySQL 데이터 파일 삭제

sudo rm -rf /usr/local/mysql/data/*

Xtrabackup 데이터 복원

백업 복구 준비

sudo xtrabackup --defaults-file=/usr/local/mysql/my.cnf \
--prepare \
--target-dir=/data/backups

복구 실행

sudo xtrabackup --defaults-file=/usr/local/mysql/my.cnf \
--copy-back \
--target-dir=/data/backups

$ sudo xtrabackup --defaults-file=/usr/local/mysql/my.cnf --copy-back --target-dir=/data/backups
2024-04-16T09:36:46.453306+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/usr/local/mysql/data 
2024-04-16T09:36:46.453477+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --copy-back=1 --target-dir=/data/backups 
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
2024-04-16T09:36:46.453525+09:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/backups/
...
2024-04-16T09:36:49.785271+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

$ ls -l /usr/local/mysql/data/
total 82976
drwxr-x--- 2 root root        6 Apr 16 09:36 '#innodb_redo'
-rw-r----- 1 root root      157 Apr 16 09:36  binlog.000004
-rw-r----- 1 root root       14 Apr 16 09:36  binlog.index
-rw-r----- 1 root root     5734 Apr 16 09:36  ib_buffer_pool
-rw-r----- 1 root root 12582912 Apr 16 09:36  ibdata1
-rw-r----- 1 root root 12582912 Apr 16 09:36  ibtmp1
drwxr-x--- 2 root root      143 Apr 16 09:36  mysql
-rw-r----- 1 root root 26214400 Apr 16 09:36  mysql.ibd
drwxr-x--- 2 root root     8192 Apr 16 09:36  performance_schema
drwxr-x--- 2 root root       28 Apr 16 09:36  sys
-rw-r----- 1 root root 16777216 Apr 16 09:36  undo_001
-rw-r----- 1 root root 16777216 Apr 16 09:36  undo_002
-rw-r----- 1 root root      537 Apr 16 09:36  xtrabackup_info

data 소유자 설정

chown -R mysql:mysql /usr/local/mysql/data

MySQL 엔진 시작

./bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf &

MySQL 접속

$ ./bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

참고URL

- AWS Documentation : Percona XtraBackup과 Amazon S3를 사용하여 MySQL에서 물리적으로 마이그레이션

- NHN Cloud Document : 백업 및 복원

- BeyondScale : Restoring MySQL DB from on-premise to Amazon RDS

728x90

저작자표시 비영리