mysql / mariadb xtrabackup

mysql / mariadb xtrabackup


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


작업 환경 : CentOS_7




##### DB 백업




1. percona-release yum repository 추가


[[email protected] ~]# 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 설치


[[email protected] ~]# 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 백업실행


[[email protected] ~]# 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. 백업 경로에 로그 백업 추가 실행


[[email protected] ~]# 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. 백업된 데이터 확인


[[email protected] 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 서비스 종료


[[email protected] ~]# systemctl stop mariadb.service




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


[[email protected] ~]# cd /var/lib/

[[email protected] lib]# mv mysql/ mysql_old

[[email protected] lib]# mkdir mysql




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


[[email protected] 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. 복구된 디렉토리 소유권 변경


[[email protected] lib]# chown mysql.mysql -R /var/lib/mysql




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


[[email protected] lib]# systemctl start mariadb.service

[[email protected] 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)]>


8개의 댓글이 있습니다.

7일 전

잘 살펴 보겠습니다~

Reply

댓글 남기기

댓글을 남기기 위해서는 로그인이 필요합니다.

로그인 회원가입

1st 5stars

7일 전

DB 뿐만 아니라 실행 환경까지 모두 백업 했다가 복구하는 툴인가 보네요~

Reply

댓글 남기기

댓글을 남기기 위해서는 로그인이 필요합니다.

로그인 회원가입

7일 전

좋은정보입니다.추후 도움될일이 있을것 같네요. 감사합니다.

Reply

댓글 남기기

댓글을 남기기 위해서는 로그인이 필요합니다.

로그인 회원가입

7일 전

정보감사드립니다....^^

Reply

댓글 남기기

댓글을 남기기 위해서는 로그인이 필요합니다.

로그인 회원가입

8일 전

mysql xtrabackup이 장단점이 있더라구요
몇년 운영 해봤는데 DB 사이즈에 따라 복구 문제도 있구요
좋은 정보입니다. 수고하셨어요

Reply

댓글 남기기

댓글을 남기기 위해서는 로그인이 필요합니다.

로그인 회원가입

10일 전

와우 감사합니다.

Reply

댓글 남기기

댓글을 남기기 위해서는 로그인이 필요합니다.

로그인 회원가입

10일 전

감사합니다.

Reply

댓글 남기기

댓글을 남기기 위해서는 로그인이 필요합니다.

로그인 회원가입

11일 전

좋은 정보 감사드립니다.

Reply

댓글 남기기

댓글을 남기기 위해서는 로그인이 필요합니다.

로그인 회원가입

댓글 남기기

댓글을 남기기 위해서는 로그인이 필요합니다.

로그인 회원가입