mysql / mariadb xtrabackup

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

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| jook |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

MariaDB [(none)]>