mysql / mariadb xtrabackup

2020년 11월 13일2020년 11월 13일 add4s

mysql xtrabackup은 innodb 환경에서 전체 DB를 Hot Backup 하는데 유용한 도구 입니다.

작업 환경 : CentOS 7

##### DB 백업

1. percona-release yum repository 추가

[root@localhost ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Loaded plugins: fastestmirror, langpacks
percona-release-0.1-3.noarch.rpm | 6.4 kB 00:00:00
Examining /var/tmp/yum-root-0TkUGU/percona-release-0.1-3.noarch.rpm: percona-release-0.1-3.noarch
Marking /var/tmp/yum-root-0TkUGU/percona-release-0.1-3.noarch.rpm to be installed
Resolving Dependencies
–> Running transaction check
—> Package percona-release.noarch 0:0.1-3 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================
Package Arch Version Repository Size
=================================================================================================================================
Installing:
percona-release noarch 0.1-3 /percona-release-0.1-3.noarch 5.8 k

Transaction Summary
=================================================================================================================================
Install 1 Package

~ 중간 생략

2. percona-xtrabackup 설치

[root@localhost ~]# yum install percona-xtrabackup

Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.kakao.com
* extras: mirror.kakao.com
* updates: mirror.kakao.com
Resolving Dependencies
–> Running transaction check
—> Package percona-xtrabackup.x86_64 0:2.3.10-1.el7 will be installed
–> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-2.3.10-1.el7.x86_64
–> Running transaction check
—> Package libev.x86_64 0:4.15-7.el7 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================
Package Arch Version Repository Size
=================================================================================================================================
Installing:
percona-xtrabackup x86_64 2.3.10-1.el7 percona-release-x86_64 5.0 M
Installing for dependencies:
libev x86_64 4.15-7.el7 extras 44 k

Transaction Summary
=================================================================================================================================
Install 1 Package (+1 Dependent package)

~ 중간 생략

3. innobackupex 명령으로 DB 백업실행

[root@localhost ~]# innobackupex –user root –password xxxxx /BACKUP/DB

201111 17:26:35 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints “completed OK!”.

201111 17:26:36 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup’ as ‘root’ (using password: NO).
201111 17:26:36 version_check Connected to MySQL server
201111 17:26:36 version_check Executing a version check against the server…
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don’t want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at – line 237.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don’t want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at – line 237.

# A software update is available:
201111 17:26:38 version_check Done.

~ 중간 생략

4. 백업 경로에 로그 백업 추가 실행

[root@localhost ~]# innobackupex –user root –apply-log /BACKUP/DB/2020-11-11_17-26-35/

201111 17:28:27 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints “completed OK!”.

innobackupex version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
xtrabackup: cd to /BACKUP/DB/2020-11-11_17-26-35/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1597945)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by –use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1597945 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer…
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1597945

~ 중간 생략

5. 백업된 데이터 확인

[root@localhost 2020-11-11_17-26-35]# ll
total 30740
-rw-r—– 1 root root 386 Nov 11 17:26 backup-my.cnf
-rw-r—– 1 root root 18874368 Nov 11 17:28 ibdata1
-rw-r–r– 1 root root 5242880 Nov 11 17:28 ib_logfile0
-rw-r–r– 1 root root 5242880 Nov 11 17:28 ib_logfile1
drwx—— 2 root root 20 Nov 11 17:26 jook
drwx—— 2 root root 4096 Nov 11 17:26 mysql
drwx—— 2 root root 4096 Nov 11 17:26 performance_schema
drwx—— 2 root root 20 Nov 11 17:26 test
-rw-r—– 1 root root 113 Nov 11 17:28 xtrabackup_checkpoints
-rw-r—– 1 root root 419 Nov 11 17:26 xtrabackup_info
-rw-r—– 1 root root 2097152 Nov 11 17:28 xtrabackup_logfile

##### 백업 DB 복구

1. DB 서비스 종료

[root@localhost ~]# systemctl stop mariadb.service

2. DB 데이터 디렉토리 이름 변경 후, 빈 디렉토리 생성

[root@localhost ~]# cd /var/lib/
[root@localhost lib]# mv mysql/ mysql_old
[root@localhost lib]# mkdir mysql

3. 백업된 경로 입력하여 DB 복원

[root@localhost lib]# innobackupex –copy-back /BACKUP/DB/2020-11-11_17-26-35/

201111 17:29:45 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints “completed OK!”.

innobackupex version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
201111 17:29:45 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/db.frm to /var/lib/mysql/mysql/db.frm
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/db.MYI to /var/lib/mysql/mysql/db.MYI
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/host.frm to /var/lib/mysql/mysql/host.frm
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/host.MYI to /var/lib/mysql/mysql/host.MYI
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/host.MYD to /var/lib/mysql/mysql/host.MYD
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/user.MYI to /var/lib/mysql/mysql/user.MYI
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/func.frm to /var/lib/mysql/mysql/func.frm
201111 17:29:45 [01] …done
201111 17:29:45 [01] Copying ./mysql/func.MYI to /var/lib/mysql/mysql/func.MYI
201111 17:29:45 [01] …done

~ 중간 생략

4. 복구된 디렉토리 소유권 변경

[root@localhost lib]# chown mysql.mysql -R /var/lib/mysql

5. DB서비스 실행 후, 접속 확인

[root@localhost lib]# systemctl start mariadb.service
[root@localhost lib]# mysql -u root -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server

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

MariaDB [(none)]>