Xtrabackup 2.49 설치

DBA의 역할중 가장 중요한 건 Data 백업과 튜닝, 점검시 downtime 최소화..등등..

DB의 데이터가 쌓여..100G가 이상 되면 슬슬 mysqldump로는 한계를 만나게 된다.

이때 다른 백업을 찾게 되는데..

여러 오픈소스중 성능이 우수한 Xtrabackup 을 설치하고 테스트한다.

 

환경 : OS CentOS 6.9

 

1) 설치파일 확인

-- xtrabacup 설치하기 위해 기본적인 RPM 이 설치되어있는지 확인하고 없으면 설치해야한다.

#su권한 [root@~]# rpm -qa | grep perl-DBD perl-DBD-MySQL-4.013-3.el6.x86_64 [root@~]# rpm -qa | grep perl-Time perl-Time-HiRes-1.9721-144.el6.x86_64 [[root@~]# rpm -qa | grep libev libevent-1.4.13-4.el6.x86_64 -->libev 없다. 찾아서 설치한다.

 

 

2) xtrabackup 설치(rpm 설치)

#설치 파일은 2개

percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm

percona-xtrabackup-24-debuginfo-2.4.9-1.el6.x86_64.rpm

 

-->libev 없는거 먼저 설치 [root@~]# rpm -Uvh libev-4.03-3.el6.x86_64.rpm 경고: libev-4.03-3.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY 준비 중...               ########################################### [100%]    1:libev                  ########################################### [100%]    확인) rpm -qa | grep libev libev-4.03-3.el6.x86_64 설치) [root@~]# rpm -Uvh percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm 경고: percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY 준비 중...               ########################################### [100%]    1:percona-xtrabackup-24  ########################################### [100%]    [root@~]# rpm -Uvh percona-xtrabackup-24-debuginfo-2.4.9-1.el6.x86_64.rpm 경고: percona-xtrabackup-24-debuginfo-2.4.9-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY 준비 중...               ########################################### [100%]    1:percona-xtrabackup-24-d########################################### [100%]    [root@~]# 설치 끝 ~~~!! 어디에 설치된거야?? RPM 설치의 단점.. 실행파일은 여기에 있다. /usr/bin/innobackupex

 

3) 백업

-- 쉘 스크립트로 만들어 Crontab 에 등록해서 편하게 쓴다.

쉘은 구글링~~!!

./xtrabackup.sh

 

vi xtrabackup.sh ---------------------------------------------------------------------------------- /usr/bin/innobackupex \     --host=127.0.0.1 \     --user=root \     --password='DB패스워드' \     --ibbackup=xtrabackup \     --no-lock \     --no-timestamp \     ${BACKUP_LOCATED}/${NOW_DATE} \     1> $PWD/logs/innobackupex_${NOW_DATE}.err 2> $PWD/logs/innobackupex_${NOW_DATE}.log  -->스크립트 내 xtrabackup 옵션들 중에 가장 중요한 것은 실시간 CRUD가 일어나는 DB는 mysql 엔진을 innodb를 쓰기 때문에 핫백업을 하려면 ibbackup 과 no-lock 옵션이 가장 중요하다.

 

4) 재생성

 

4-1) 현재 사용중인 DB 중단

service mysqld stop

 

4-2) 디스크 비우고 백업

mv mysqldb_data mysqldb_data_bak
mkdir mysqldb_data
chown -Rf mysql:mysql mysqldb_data

 

4-3) xtrabacup apply 로그 복구

[root@~]# innobackupex --defaults-file=/etc/my.cnf --datadir=/home/mysql/mysqldb_data --apply-log /backup/innobackup/20190906_06/   190906 16:04:06 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.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4) xtrabackup: cd to /home/mysql/backup/innobackup/20190906_06/ xtrabackup: This target seems to be already prepared. InnoDB: Number of pools: 1 xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'. xtrabackup: using the following InnoDB configuration for recovery: xtrabackup:   innodb_data_home_dir = . xtrabackup:   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:10M:autoextend xtrabackup:   innodb_log_group_home_dir = . xtrabackup:   innodb_log_files_in_group = 4 xtrabackup:   innodb_log_file_size = 268435456 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup:   innodb_data_home_dir = . xtrabackup:   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:10M:autoextend xtrabackup:   innodb_log_group_home_dir = . xtrabackup:   innodb_log_files_in_group = 4 xtrabackup:   innodb_log_file_size = 268435456 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. InnoDB: Removed temporary tablespace data file: "ibtmp1" InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File './ibtmp1' size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: 5.7.13 started; log sequence number 2347797778603 InnoDB: xtrabackup: Last MySQL binlog file position 7587824, file name ./mysql-bin.000001 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2347797779605 InnoDB: Number of pools: 1 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup:   innodb_data_home_dir = . xtrabackup:   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:10M:autoextend xtrabackup:   innodb_log_group_home_dir = . xtrabackup:   innodb_log_files_in_group = 4 xtrabackup:   innodb_log_file_size = 268435456 InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. InnoDB: Removed temporary tablespace data file: "ibtmp1" InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File './ibtmp1' size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: 5.7.13 started; log sequence number 2347797779605 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2347797780598 190906 16:04:11 completed OK!

 

4-4) xtrabackup 데이터복구

[root@~]# innobackupex --defaults-file=/etc/my.cnf --datadir=/home/mysql/mysqldb_data --copy-back /backup/innobackup/20190906_06/ ... 190906 16:34:27 [01] Copying ./WWW/TB_COM_CD_DTL.frm to /home/mysql/mysqldb_data/WWW/TB_COM_CD_DTL.frm 190906 16:34:27 [01]        ...done 190906 16:34:27 [01] Copying ./WWW/TB_CM_WWW_CTGR_SORT.frm to /home/mysql/mysqldb_data/WWW/TB_CM_WWW_CTGR_SORT.frm 190906 16:34:27 [01]        ...done 190906 16:34:27 [01] Copying ./WWW/TB_COM_WWW_CTGR.frm to /home/mysql/mysqldb_data/WWW/TB_COM_WWW_CTGR.frm 190906 16:34:27 [01]        ...done 190906 16:34:27 [01] Copying ./WWW/TB_PM_WWW_010PAY_RESERVE.frm to /home/mysql/mysqldb_data/WWW/TB_PM_WWW_010PAY_RESERVE.frm 190906 16:34:27 [01]        ...done 190906 16:34:27 [01] Copying ./WWW/TB_SM_WWW_INFO.ibd to /home/mysql/mysqldb_data/WWW/TB_SM_WWW_INFO.ibd 190906 16:34:27 [01]        ...done 190906 16:34:27 [01] Copying ./WWW/TB_CM_WWW_CTGR_SORT.ibd to /home/mysql/mysqldb_data/WWW/TB_CM_WWW_CTGR_SORT.ibd 190906 16:34:27 [01]        ...done 190906 16:34:27 [01] Copying ./WWW/TB_PM_WWW_AUTOPAY.frm to /home/mysql/mysqldb_data/WWW/TB_PM_WWW_AUTOPAY.frm 190906 16:34:27 [01]        ...done 190906 16:34:27 [01] Copying ./WWW/TB_WWW_ACCT_RCVM_HIST.ibd to /home/mysql/mysqldb_data/WWW/TB_WWW_ACCT_RCVM_HIST.ibd 190906 16:34:27 [01]        ...done 190906 16:34:27 [01] Copying ./WWW/TB_WWW_ACCT_RCVM_DRW_HIST.frm to /home/mysql/mysqldb_data/WWW/TB_WWW_ACCT_RCVM_DRW_HIST.frm 190906 16:34:27 [01]        ...done 190906 16:34:27 completed OK!  [root@~]#

 

4-5) DB 시작

[root@~]# service mysqld start Starting MySQL.190906 17:35:42 mysqld_safe Logging to '/home/mysql/mariadb_logs/error/mysql.err'. 190906 17:35:42 mysqld_safe Starting mysqld daemon with databases from /home/mysql/mariadb_data .                                                          [실패] 190906 17:35:43 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode' 190906 17:35:43 [ERROR] InnoDB: The system tablespace must be writable! 190906 17:35:43 [ERROR] Plugin 'InnoDB' init function returned error. 190906 17:35:43 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 190906 17:35:43 [Note] Plugin 'FEEDBACK' is disabled. 190906 17:35:43 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 190906 17:35:43 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 190906 17:35:43 [Note] Server socket created on IP: '::'. 190906 17:35:43 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 190906 17:35:43 mysqld_safe mysqld from pid file /home/mysql/mariadb_data/test.pid ended'   --> 권한 오류 확인 /mariadb_data 아래 파일이 root 권한으로 인해 발생한 문제로 권한을 mysql로 수정한다. [root@~]# chown -Rf mysql:mysql mysqldb_data [root@~]# chown -Rf mysql:mysql mariadb_logs   [mysql@~]# service mysqld start Starting MySQL.190906 17:52:46 mysqld_safe Logging to '/home/mysql/mariadb_logs/error/mysql.err'. 190906 17:52:46 mysqld_safe Starting mysqld daemon with databases from /home/mysql/mariadb_data ..                                                         [  OK  ] 끝~~!!