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

728x90

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

저작자표시 비영리

'퍼블릭 클라우드' 카테고리의 다른 글

[draft] Amazon Linux 2023에서 MySQL 서버와 클라이언트를 설치하는 방법 (0)	2024.04.18
[draft] Amazon Linux 2023에 MySQL 클라이언트를 설치하는 방법 (0)	2024.04.17
AWS CodeDeploy를 사용하여 EC2 인스턴스에 배포하는 파이프라인을 구성하는 방법 (1)	2023.11.20
AWS CodeDeploy Agent를 프록시 서버를 통해 구성하는 방법 (0)	2023.11.08
Amazon EC2 t4g.small 인스턴스에서 Nginx와 PHP-FPM을 연동하여 테스트 페이지를 설정하는 방법 (0)	2023.11.04

변군이글루 미러 사이트(Mirror Site)

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

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

Percona XtraBackup 설치

Xtrabackup 데이터 백업

Xtrabackup 데이터 복원

'퍼블릭 클라우드' 카테고리의 다른 글

티스토리툴바

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

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

Percona XtraBackup 설치

Xtrabackup 데이터 백업

Xtrabackup 데이터 복원

'퍼블릭 클라우드' 카테고리의 다른 글

'퍼블릭 클라우드' Related Articles

티스토리툴바