mysqldump (ft. mariadb-dump)

########################################################################################

# 개요

########################################################################################

새로 구축한 서버에 Dump를 해야하는 일이 생겼습니다.

개발일 시작한지 3년차이지만 Dump 백업하는 것은 처음이라 부족한 사항이 많습니다.

참고만 하시고! 꼭 Reference를 확인하세요! 링크는 아래에 넣어 놓겠습니다.

 

 

########################################################################################

# 준수 사항

########################################################################################

그럼 본론으로 넘어가면 dump 진행은 다음과 같은 사항을 준수하여 진행할 예정입니다.

1. 매일 1번의 백업( crontab )

2. 파일 압축

3. 백업된 스크립트 실행 시 이상없도록 스크립트 생성

 

 

 

########################################################################################

# 실행 스크립트

########################################################################################

한글이 깨질 수도 있어 영어로 작성했습니다.

의미만 전달 되도록 작성하였습니다.

 

실제 백업에 실행 할 스크립트 내용

 

 

########################################################################################

# mariadb-dump 명령어 옵션 설명

# 중요 포인트: xz 압축 / 생성될 스크립트에 LOCK 무시

########################################################################################

우선, mysqldump 명령어도 사용이 가능합니다. mariadb-dump 명령어는 mysqldump의 symlink로 등록이 되어 있어

동일한 명령어이고, DB서버 자체를 MariaDB로 구축하여 mariadb-dump 명령어로 사용했습니다.

 

MariaDB Reference를 살펴보면 예시로 제공하는 명령어는 다음과 같은 형식으로 작성되었습니다.

mysqldump -u admin -p -x -a > /backup_path/backup_file_name

하지만, 개발자 분들이라면 아시다시피 똑같이 하면 윗분들한테 너~무 혼나시죠...ㅜㅜ

그래서 Reference에 가서 이것 저것 확인을 해봤습니다.

위의 사진의 스크립트 중 정말 중요한 사항의 리스트만 다시 한번 설명 드리겠습니다.

 

 

 

########################################################################################

# 중요 포인트 Dump Command Option

########################################################################################

1. '-x'

이 옵션은 dump된 스크립트를 보시면 row를 INSERT할때, 해당 테이블에 Lock을 걸어버립니다.

문서상에서는 Read-Only, 즉 Slave DB 서버에서만 속도 향상을 위해 추천하는 방식입니다.

제가 구축한 서버의 MariaDB는 Galera클러스터링을 사용하여 두 서버가 Primary(Active) 상태이므로, 과감히 옵션을 뺐습니다.

Lock을 걸지 않아야 한다면 '--skip-lock-tables'를 사용하여 옵션값 넣어주시면 됩니다

 

2. --database; -B

전체 DataBase 복사가 아닌 특정 DB만 Dump할때 사용하는 옵션입니다.

제가 작성한 스크립트 상에서는 --database만 설명해 놨는데 -B 또한 같은 옵션입니다.

저는 보기 편하게 -B로 하였습니다.

 

3. --compatible=name

이 옵션은 정말 유용하고 중요한 옵션입니다. 실제 DB서버상에서 구동되는 DB가 MySQL 혹은 MariaDB인 경우 다른 DB스크립트로 dump하여 스크립트 상에 문제가 없도록 지원해주는 값입니다.

mysql323, mysql40의 경우 버전입니다. 각각 3.23 / 4.0의 버전으로 하위버전으로 생각하시면 될거 같습니다.

주요 DB인 Oracle, MS-SQL, Postgresql도 지원이 가능합니다.

당연히! default값은 Mysql, MariaDB입니다.

 

4. --order-by-primary

스크립트 실행할때 테이블의 row를 정렬할 것인지에 대해 묻는 옵션입니다. 현재 작업하는 DB서버의 데이터가 엄청나게 많은 데이터가 아니어서 그 체감은 못 느꼈지만 정말 데이터 많은 경우는 해당 값없으면 DB서버에서 쿼리 실행도중 먹통이 되는 경우가 있다고 합니다. DBA분께 직접들은 내용이라 해당 내용은 다시 한번 API문서 꼭! 확인해보세요!

default값은 "FALSE" 입니다.

 

5. --skip-add-locks

아까는 테이블자체의 Lock을 제외하여준 사항, 비슷한 맥락에서 스크립트 자체에서 LOCK 실행 쿼리를 제외시켜주는 옵션입니다. 저는 막상 없어야 맘이 편해서 넣어줬습니다. 스크립트 상에서 더러워 보이면 이상하지 않을까요?^^

 

 

 

########################################################################################

# 파일 백업

########################################################################################

백업하는 사항에서 한번 혼났습니다. 무턱대고 tar로 압축하려고 했다가 차이는 알고 쓰냐며 물으셨는데 아무 말도 못하겠더라고요... 그래서! 잠깐 알아보고 가시죠!

우선 tar, xz, gzip, bzip등 여러가지 압축 명령어가 있습니다. 그중 tar는 멀티소스를 압축하는 옵션입니다. xz는 단일 파일 자체를 압축하는 명령어로 압축효율에서 차이가 정말 많이 납니다.

tar로 압축하는 경우는 class파일, jsp파일, properties파일 등 여러가지의 복합적인 파일을 압축하는 시스템입니다.

xz는 단순 log파일이나 txt파일 같은 단일 파일을 압축하는 옵션으로 xz로 압축 진행시 최대 90퍼센트 까지 압축되는 압축 효율이 나오는 사항을 확인하였습니다.

 

하여, 백업진행할때 pipe로 xz압축 진행하는데, 해당 내용을 stdio인 파일을 바로 xz로 압축할 수 있도록 dump파일 백업을 진행하였습니다. 글을 보시는 분들중, 그러면 나중엔 다시 압축을 해제해야 하나? 라고 생각하시는 분들이 있으실텐데 제가 그 질문을 여쭤봤다가 혼난 결론을 말씀드립니다.

 

'xzcat' 명령어가 답이었습니다. 해당 명령어로 실행하면 바이너리 형태가 아닌 문자형식으로 stdout내용을 보여주게 됩니다. 자세한 사항은 참조문서 확인해주세요!

 

 

 

########################################################################################

# 다른 많은 mysqldump 블로그가 있는데 제가 직접 API문서만 확인하고 DBA분께 여쭤가면서 한 사항이라

# 누락된 사항도 있고 과하게 설정된 사항도 있으리라 생각합니다. 그래도 참조하시는데 도움되셨으면 좋겠습니다~ 

# Fin.

########################################################################################

※ 추후 업로드 내용( 학습 내용 )

   - Private 망에서 Galera 클러스터링 및 MariaDB 튜닝

   - Postgresql 설치부터 Replication

   - Netty의 정의와 구성 및 개발

   - Java Project logrotate ( Spring x; Only Use Java )

 

※ mariadb-dump 전체 스크립트

#!/bin/bash
###########################################################################
#Declare DB Info Value Section
#BACKUP_DIR     =       Directory Path; DB Data Backup Path
#DATABASE_NAME  =       Script import Value
#                       To Backup specify Schema DataBase declare DataBase Name
#                       if use not specify DataBase, use All DataBase Backup; replace DATABASE_NAME '-A' or '--all-databases'
#                       ref) '--all' command  is Deprecated
#DATABASE_ID    =       To mariadb-dump declare User ID, Should Check Priviliges
#DATABASE_PWD   =       Password of ${DATABASE_ID}
###########################################################################
BACKUP_DIR={백업할 데이터 디렉토리 경로}
DATABASE_NAME={DATABASE 명}
DATABASE_ID={DB 계정}

###########################################################################
#Custom Value Section
#By Chaeson
#BACKUP_NAME    =       Backup data file name declare
#                       In case use this format: FILE_NAME_YYYYMMDD.sql
#DEL_FILE       =       To delete file declare Delete full Path
#                       1 week ago File Delete
#                       ref) n type ago ; 'n' is number, type can replace to "second", "minute", "hour", "day", "week", "month", "year"
#DUMP_FILE      =       execute MariaDB Dump Date; BACKUP_NAME_YYYYMMDD.sql
###########################################################################
BACKUP_NAME=MariaDB_Backup
DEL_FILE="$BACKUP_DIR"/"$BACKUP_NAME"_`date -d '3 month ago' "+%Y%m%d"`.sql.xz
DUMP_FILE="$BACKUP_NAME"_`date "+%Y%m%d"`.sql.xz

###########################################################################
#Execute Script Section
#if declared old file Exist in path, Delete File; else Skip
#By Chaeson
###########################################################################

if [ -e "$DEL_FILE" ]; then
rm -rf "$DEL_FILE"
fi

###########################################################################
#To MariaDB DataBase data Backup, mariadb-dump backup path is BACKUP_DIR
#mariadb-dump Command Section
###########################################################################
#'-x' command option
#Lock all tables accross all databases; '--skip-lock-tables' is opposition command;
#In case use 'InnoDB', '--single-transaction' option is better than '-x'
###########################################################################
#'-n' command option
#Except CREATE TABLE ~ IF EXIST; if use '-all-databases' or '--databases' option, 'ignore' -n option
###########################################################################
#--order-by-primary command option
#Sort each tables rows by primary key, or first unique key, if such a key exists.
#Useful when dumping a MyISAM to be loaded into an InnoDB.
###########################################################################
#--compatible=name option
#Change compatible database version default tables dumped format: MariaDB, MySQL
#Other mode: ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options
###########################################################################
#--skip-add-locks command option
#In Backup SQL of MariaDB Query; "LOCK TABLES" NO USE
###########################################################################

mariadb-dump -u "$DATABASE_ID" -p="{DB 패스워드}" -B "$DATABASE_NAME" --skip-lock-tables --skip-add-locks --order-by-primary=TRUE -n | xz > "$BACKUP_DIR"/"$DUMP_FILE"

exit 0

 

 

 

########################################################################################

# 참조 문서

########################################################################################

-- mariadb-dump

https://mariadb.com/kb/en/mysqldump/

mysqldump

-- xz

https://linux.die.net/man/1/xz

xz(1): Compress/decompress .xz/.lzma files - Linux man page