Percona XtraBackup for MySQL

🔨 Percona XtraBackup이란?

Percona에서 제공하는 오픈 소스 MySQL Hot Backup Tool

• 빠르고 안정적인 데이터베이스 백업
• 백업 중 중단 없는 트랜잭션 처리
• 더 나은 압축으로 디스크 공간 및 네트워크 대역폭 절약
• 자동 백업 확인
• 더 빠른 복원 시간으로 더 높은 가동 시간
• 특정 시점 복구

🔨 지원되는 스토리지 엔진

• InnoDB , XtraDB 및 MyRocks 스토리지 엔진의 Non-Blocking 백업 지원
• 백업이 끝날 때 쓰기를 잠시 일시 중지하여 MyISAM, Merge 및 Archive 스토리지 엔진 백업 지원

🔨 다운로드

https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

---

🔨 설치

MySQL 8.0 호환성으로 인하여 동일하거나 상위 버전의 Xtrabackup 설치를 권장

🔧 Binary 설치

# tar -xf percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17.tar.gz -C /usr/local/
# cd /usr/local
# mv percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17 percona-xtrabackup

📌 시스템 환경 변수 등록

# su - mysql
$ vi ~/.bash_profile

XtraBackup=/usr/local/percona-xtrabackup/bin
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$XtraBackup

$ source ~/.bash_profile

🔧 RPM 설치

# rpm -ivh percona-xtrabackup-80-8.0.23-16.1.el7.x86_64.rpm 
경고: percona-xtrabackup-80-8.0.23-16.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
준비 중...                         ################################# [100%]
Updating / installing...
   1:percona-xtrabackup-80-8.0.23-16.1################################# [100%]

📌 Dependency Package

- libev
- perl-DBD-mysql
- perl-Digest-MD5
- rsync

---

🔨 백업에 필요한 최소 권한

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

https://www.percona.com/doc/percona-xtrabackup/LATEST/using_xtrabackup/privileges.html

---

🔨 Full Backup

mysql> create database JM;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| JM                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

🔧 Full Backup

$ xtrabackup -u bkpuser -p --no-version-check --backup --target-dir=/mysql/backup/full
xtrabackup: recognized server arguments: --datadir=/mysql/data 
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --user=bkpuser --password --backup=1 --no-version-check=1 --target-dir=/mysql/backup/full 
Enter password: 

xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)
210601 15:48:14 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 8.0.23
210601 15:48:14 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data

··· 중략 ···

xtrabackup: Transaction log of lsn (18077282) to (18077292) was copied.
210601 16:03:50 completed OK!

📌 version_check 에러 제거를 위해 --no-version-check 옵션 또는 계정 인증 플러그인 변경
     (caching_sha2_password -> mysql_native_password)

210601 15:34:11  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: 동적 오브젝트 파일을 열 수 없습니다: 그런 파일이나 디렉터리가 없습니다 at - line 1535.

mysql> alter user 'bkpuser'@'localhost' identified with mysql_native_password by 'password';
mysql> select user, host, plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| bkpuser          | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

🔧 Prepare

$ xtrabackup --prepare --target-dir=/mysql/backup/full
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/mysql/backup/full 
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)
xtrabackup: cd to /mysql/backup/full/
xtrabackup: This target seems to be not prepared yet.

··· 중략 ···

Log background threads are being closed...
Shutdown completed; log sequence number 18077708
210601 15:52:30 completed OK!

🔧 Restore

$ sudo systemctl stop mysqld.service
$ xtrabackup --copy-back --target-dir=/mysql/backup/full
xtrabackup: recognized server arguments: --datadir=/mysql/data 
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --copy-back=1 --target-dir=/mysql/backup/full 
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)
Original data directory /mysql/data is not empty!

$ mv /mysql/data /mysql/data_old
$ xtrabackup --copy-back --target-dir=/mysql/backup/full
xtrabackup: recognized server arguments: --datadir=/mysql/data 
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --copy-back=1 --target-dir=/mysql/backup/full 
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)
210601 15:56:29 [01] Copying undo_001 to /mysql/data/undo_001
210601 15:56:30 [01]        ...done
210601 15:56:30 [01] Copying undo_002 to /mysql/data/undo_002
210601 15:56:30 [01]        ...done

··· 중략 ···

210601 15:56:30 [01] ...done.
210601 15:56:30 completed OK!

$ sudo systemctl start mysqld.service

$ mysql -u root -p
Enter password: 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| JM                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

---

🔨 Incremental Backup

🔧 Full Backup

$ xtrabackup -u bkpuser -p --backup --no-version-check --target-dir=/mysql/backup/base

📌 prepare가 완료된 파일은 다시 prepare 불가능.

xtrabackup: This target seems to be already prepared.

🔧 Incremental Backup

mysql> create database JM2;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| JM                 |
| JM2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

$ xtrabackup -u bkpuser -p --no-version-check --backup --target-dir=/mysql/backup/inc --incremental-basedir=/mysql/backup/base
xtrabackup: recognized server arguments: --datadir=/mysql/data 
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --user=bkpuser --password --no-version-check=1 --backup=1 --target-dir=/mysql/backup/inc --incremental-basedir=/mysql/backup/base 
Enter password: 
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)
210601 16:23:31 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 8.0.23
210601 16:23:31 Executing LOCK INSTANCE FOR BACKUP...
incremental backup from 18105755 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data

··· 중략 ···

xtrabackup: Transaction log of lsn (18106285) to (18106295) was copied.
210601 16:23:33 completed OK!

🔧 Prepare

$ xtrabackup --prepare --apply-log-only --target-dir=/mysql/backup/base
$ xtrabackup --prepare --target-dir=/mysql/backup/base --incremental-dir=/mysql/backup/inc

📌 백업 시 커밋 되지 않은 트랜잭션이 진행 중일 수 있으며, 다음 증분 백업에서 커밋 될 가능성이 있으므로 백업 복원을 준비할 때 커밋 되지 않은 트랜잭션의 롤백 단계를 방지하기 위해 --apply-log-only 옵션 사용

📌 증분 백업이 여러 개일 경우 마지막 증분 백업만 --apply-log-only 옵션 제외

$ xtrabackup --prepare --apply-log-only --target-dir=/mysql/backup/base
$ xtrabackup --prepare --apply-log-only --target-dir=/mysql/backup/base --incremental-dir=/mysql/backup/inc1
$ xtrabackup --prepare --target-dir=/mysql/backup/base --incremental-dir=/mysql/backup/inc2

🔧 Restore

$ sudo systemctl stop mysqld.service

$ mv /mysql/data /mysql/data_old
$ xtrabackup --copy-back --target-dir=/mysql/backup/base

$ sudo systemctl start mysqld.service

$ mysql -u root -p
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| JM                 |
| JM2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)