MariaDB 백업 확인 프로시저

이기현·2022년 3월 23일
0

MariaDB

목록 보기
21/29

1. 현 상황

  1. MariaDB 각 서버에서 CRON 작업으로 Innobackupex, Mariabackup 백업을 받고 있다.
  2. 백업 실패시 각 서버에 직접 접속해서 확인하기 전까지는 알 수 없다.
  3. 백업 오류 발생 시(디스크 용량 부족, 서버 오류 등) 자동으로 PUSH를 보내줄 수 있는 기능 필요

2. 기능 구현

* 전체 프로시저는 아래에서 확인

1. 각 서버에서 백업을 받을시에 --history 옵션을 사용하여 백업이 완료되면 PERCONA_SCHEMA.xtrabackup_history 테이블에 백업 정보가 저장되도록 설정

Innobackupex 및 Mariabackup 은 Percona사의 Xtrabackup 을 기반으로 만들어진 백업 Tool 이다.

2. JDBM 서버에서 DB_LINK를 통해 MariaDB 각 서버의 최종 백업 시간을 가져온다.

select @DATE = backup_date from 
	OPENQUERY([JDBM_'+@HOSTID+'], 
    ''select end_time as backup_date  from PERCONA_SCHEMA.xtrabackup_history 
    order by end_time desc limit 1

3. MariaDB 서버의 백업 주기가 '매일' or '매주' 인지 확인 후 주기에 맞게 백업이 되었는지 확인한다.

IF(@FRQ = '매일') 
	BEGIN
		IF(@DATE >= GETDATE() - 1)
        	PRINT('백업 성공')
IF(@FRQ = '매주') 
	BEGIN
		IF(@DATE >= GETDATE() - 7)
        	PRINT('백업 성공')

4. 백업이 실패시에 PUSH 발송한다.

INSERT INTO TB_PUSH(DB_SEQ, GBN, MSG) 
	VALUES (@DB_SEQ, 'BACKUP', 'MariaDB 백업 실패 ' + @DB_NM + ' (' +@HOSTID + ') 확인 필요' 
    + char(13) + char(10) + '최종 백업일자 : ' + CONVERT(CHAR(23),@DATE,20) )

5. 프로시저를 에이전트 작업으로 등록하여, 매일 오전 9시에 수행되도록 설정

3. 백업 LOG 저장

백업 오류 LOG를 확인하기 위해,
CRON 작업시 백업 로그를 저장하고, 주기적으로 로그 파일 삭제
* 전체 Shell Script는 아래에서 확인

1. 백업 LOG 저장

30 3 * * 0 /box/MARIA_BACKUP/script/mariabackup.sh >> /box/MARIA_BACKUP/result/mariadb_backup_log_`date +\%Y_\%m`.log 2>&1

2. 백업 LOG 삭제

/bin/find ${backup_dir}/result -name 'mariadb_backup_log*' -type f -mtime +30 | xargs rm -f

4. 프로시저 스크립트

ALTER PROCEDURE DBO.USP_MARIA_BACKUP  
(   
 @PUSH_FLAG VARCHAR(1)  = 'N'   -- Y 푸쉬 발송  
)  
AS  
  
-- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
SET NOCOUNT ON  
SET XACT_ABORT OFF  
  
 
DECLARE @SQL NVARCHAR(4000), @HOSTID VARCHAR(30), @FRQ VARCHAR(10), @DATE DATETIME, @CUR_DATE DATETIME,
		@DB_SEQ INT, @DB_NM VARCHAR(50)
DECLARE @INFO CURSOR

SET @DATE = getdate() -100
SET @CUR_DATE = getdate()

SET @INFO = CURSOR FOR
select HOSTID, LEFT(frequency,2) 
from OPENQUERY([JDBM_JV0291], '
select * from DBACOM.DB_BACKUP_INFO where b_program like ''%mariabackup%'' or  b_program like ''%innobackupex%'' ')
 
BEGIN

OPEN @INFO
FETCH NEXT
FROM @INFO INTO @HOSTID, @FRQ
WHILE @@FETCH_STATUS = 0

	BEGIN 
		BEGIN TRY 

			-- DB_SEQ / DB_NM 확인
			SET @SQL=N'select @DB_SEQ = DB_SEQ, @DB_NM = DB_NM from TB_DB_INFO (nolock) where svr_nm='''+@HOSTID+''''
			EXECUTE sp_executesql @SQL, N'@DB_SEQ INT OUTPUT, @DB_NM VARCHAR(50) OUTPUT', @DB_SEQ = @DB_SEQ output, @DB_NM = @DB_NM output;

			-- 백업 일자 확인
			SET @SQL=N'select @DATE = backup_date from
						OPENQUERY([JDBM_'+@HOSTID+'], ''select end_time as backup_date 
													from PERCONA_SCHEMA.xtrabackup_history order by end_time desc limit 1'')' 
			EXECUTE sp_executesql @SQL, N'@DATE datetime OUTPUT', @DATE = @DATE output;

			IF(@FRQ = '매일') 
				BEGIN
					IF(@DATE >= GETDATE() - 1)
						BEGIN
							PRINT ('########## 매일 성공 ##########');
							PRINT @HOSTID;
							PRINT @FRQ;
							PRINT @DATE;
							PRINT ('##########################');
						END
					IF(@DATE < GETDATE() - 1)
						BEGIN
							PRINT ('############ 매일 백업 실패 ##############');
							PRINT @HOSTID;
							PRINT @FRQ;
							PRINT @DATE;
							--INSERT INTO TB_PUSH(DB_SEQ, GBN, MSG) 
							--VALUES (@DB_SEQ, 'BACKUP', 'MariaDB 백업 실패 ' + @DB_NM + ' (' +@HOSTID + ') 확인 필요' 
							--	 + char(13) + char(10) + '최종 백업일자 : ' + CONVERT(CHAR(23),@DATE,20) ) 
							INSERT INTO TB_PUSH_USER(USR, GBN, MSG, SEND_FLAG) 
							VALUES ('lee.kihyun', 'BACKUP', 'MariaDB 백업 확인 실패 ' + @DB_NM + ' (' + @HOSTID +') 확인 필요'
									+ char(13) + char(10) + 'ERROR MSG : ' + ERROR_MESSAGE(),'N' ) 
						END
				END
			ELSE IF(@FRQ = '매주')
				BEGIN
					IF(@DATE >= GETDATE() - 7)
						BEGIN 
							PRINT ('########## 매주 성공 ##########');
							PRINT @HOSTID;
							PRINT @FRQ;
							PRINT @DATE;
							PRINT ('##########################');
						END
					IF(@DATE < GETDATE() - 7)
						BEGIN
							PRINT ('############ 매주 백업 실패 ##############');
							PRINT @HOSTID;
							PRINT @FRQ;
							PRINT @DATE;
							--INSERT INTO TB_PUSH(DB_SEQ, GBN, MSG) 
							--VALUES (@DB_SEQ, 'BACKUP', 'MariaDB 백업 실패 ' + @DB_NM + ' (' +@HOSTID + ') 확인 필요' 
							--	 + char(13) + char(10) + '최종 백업일자 : ' + CONVERT(CHAR(23),@DATE,20) ) 
							INSERT INTO TB_PUSH_USER(USR, GBN, MSG, SEND_FLAG) 
							VALUES ('lee.kihyun', 'BACKUP', 'MariaDB 백업 확인 실패 ' + @DB_NM + ' (' + @HOSTID +') 확인 필요'
									+ char(13) + char(10) + 'ERROR MSG : ' + ERROR_MESSAGE(),'N' ) 
						END
				END
			ELSE  
				BEGIN
					PRINT ('########## 백업 주기 확인 필요 ##########');
					PRINT @HOSTID;
					PRINT @FRQ;
					PRINT @DATE;
					--INSERT INTO TB_PUSH(DB_SEQ, GBN, MSG) 
					--VALUES (@DB_SEQ, 'BACKUP', 'MariaDB 백업 실패 ' + @DB_NM + ' (' +@HOSTID + ') 확인 필요' 
					--	 + char(13) + char(10) + '최종 백업일자 : ' + CONVERT(CHAR(23),@DATE,20) ) 
					INSERT INTO TB_PUSH_USER(USR, GBN, MSG, SEND_FLAG) 
					VALUES ('lee.kihyun', 'BACKUP', 'MariaDB 백업 주기 확인 필요 ' + @DB_NM + ' (' + @HOSTID +')'
							+ char(13) + char(10) + 'ERROR MSG : ' + ERROR_MESSAGE(),'N' ) 
						
				END


		END TRY
		BEGIN CATCH  
			PRINT('###ERROR###')
			--INSERT INTO TB_PUSH(DB_SEQ, GBN, MSG) 
			--	VALUES (@DB_SEQ, 'BACKUP', 'MariaDB 백업 확인 실패 ' + @DB_NM + ' (' + @HOSTID +') 확인 필요'
			--			+ char(13) + char(10) + 'ERROR MSG : ' + ERROR_MESSAGE() ) 
			INSERT INTO TB_PUSH_USER(USR, GBN, MSG, SEND_FLAG) 
				VALUES ('lee.kihyun', 'BACKUP', 'MariaDB 백업 확인 실패 ' + @DB_NM + ' (' + @HOSTID +') 확인 필요'
						+ char(13) + char(10) + 'ERROR MSG : ' + ERROR_MESSAGE(),'N' ) 
			SELECT     
			   ERROR_NUMBER() AS ErrorNumber      
			  ,ERROR_SEVERITY() AS ErrorSeverity      
			  ,ERROR_STATE() AS ErrorState      
			  ,ERROR_PROCEDURE() AS ErrorProcedure      
			  ,ERROR_LINE() AS ErrorLine      
			  ,ERROR_MESSAGE() AS ErrorMessage;      
    
		END CATCH 

		-- 변수 초기화
		SET @HOSTID = ''
		SET @FRQ = ''
		SET @DATE = getdate() -100

		FETCH NEXT
		FROM @INFO INTO @HOSTID, @FRQ

	END



CLOSE @INFO
DEALLOCATE @INFO


END

5. 백업 Shell Script

#!/bin/bash

backup_dir="/box/MARIA_BACKUP"
db_pw="joinsdba12#$"

cd $backup_dir

# Full Backup
if [ ! -d ./full ]
then
    echo "================================================="
    echo "$(date +"%Y%m%d %H:%M:%S") [DB full backup] start"

    # mariabackup 받고 해당 출력log를 log파일에 저장
    /MARIA/mariadb/bin/mariabackup --backup --target-dir=${backup_dir}/full --user=dbacom --password=${db_pw} --history=backup_all

    # config 파일도 복사
    /bin/cp -p /etc/my.cnf ./full

    echo "$(date +"%Y%m%d %H:%M:%S") [DB full backup] end"
    echo "==============================================="
    echo
fi

# Backup file remove and tar.gz Backup File creation

    # 2주 이상된 파일 삭제
    /bin/find ${backup_dir}/result -name 'devTMS_mariadb_backup*' -type f -mtime +15 | xargs rm -f
    /bin/find ${backup_dir}/result -name 'mariadb_backup_log*' -type f -mtime +30 | xargs rm -f
    echo "================================================="

    # 백업한 폴더 압축
    /bin/tar zcvf /${backup_dir}/result/devTMS_mariadb_backup_$(date +"%Y%m%d%H%M").tar.gz ./full

    # 다음 백업을 위해서 디렉토리 삭제
    /bin/rm -rf ./full

    echo "$(date +"%Y%m%d %H:%M:%S") [DB backup file] end"
    echo "==============================================="
    # ./full/d 로 시작하는 라인 다 지우기
    echo
    /bin/sed -i '/.\/full/d'  ${backup_dir}/script/mariabackup_`date +\%Y_\%m`.log
profile
이기현
실력을 쌓아가는 하루하루
이전 포스트

DEFINER 변경 - #1449 - The user specified as a definer ('root'@'%') does not exist

다음 포스트

GROUP BY vs DISTINCT

0개의 댓글