빈 로그란?
MySQL beanlog 또는 바이너리 로그는 MySQL 서버 인스턴스의 데이터 변경에 대한 정보가 포함된 로그 파일 집합입니다.
여기에는 바이너리 로그 자체에 대한 오류 코드 및 메타데이터와 같은 다양한 데이터가 포함됩니다.
기본적으로 트랜잭션 커밋 시점에 기록되며 데이터 수정 순서를 보장하는 것이 특징입니다.
바이너리 로그는 주로 복제 및 복구 목적으로 사용되며 복제 중에 보조 노드는 기본 노드에서 binlog 데이터를 수신하고 기록합니다.
(그리고 이 수신되고 기록되는 프로토콜을 릴레이 프로토콜이라고 합니다.
)
MySQL에서 제공하는 바이너리 로그에는 세 가지 유형이 있습니다.
(1) 문 기반 로깅
삽입, 업데이트 및 삭제를 위한 SQL 문이 포함됩니다.
명령문별로 복제가 수행되는 경우 이를 명령문 기반 복제(SBR)라고 합니다.
이 절차는 로그 파일에 기록되는 데이터가 적고 로그 파일에 필요한 저장 공간이 줄어든다는 장점이 있습니다.
Replay와 같이 백업복원이 이루어지며, 신속한 복원이 가능합니다.
그러나 로그 기반 복구에는 몇 가지 제한 사항이 있는데, 예를 들어 RAND(), LOAD_FILE, UUID()와 같은 비결정적 작업에 대해서는 정확한 복제가 불가능함을 알 수 있습니다.
(물론 RAND()를 복원하여 얻은 두 결과가 같을 가능성은 없습니다!
)
(2) 라인 기반 로깅
이 방법은 각 행의 변경 사항을 기록합니다.
RBR(행 기반 복제)이라는 행 기반 로깅을 사용하여 기본 → 보조에서 복제를 수행할 수 있습니다.
RBL의 경우 각 행 변경이 바이너리 로그에 기록되기 때문에 로그 파일 크기가 매우 빠르게 증가하고 지연이 발생할 수 있습니다.
또한 임시 테이블은 RBL을 기반으로 복제되지 않기 때문에 임시 테이블을 참조하는 문장을 문장 기반으로 기록해야 한다.
(3) 혼합형
MySQL 5.1 이상부터는 행 기반 로깅과 혼합된 형식이 지원됩니다.
기본적으로 명령어 기반 로깅을 사용하지만 스토리지 엔진과 특정 명령어에 따라 라인 기반 로깅으로 로그가 자동으로 기록된다.
mysqlbinlog 유틸리티를 사용하여 바이너리 로그의 내용을 쉽게 볼 수 있습니다.
바이너리 로그와 혼동되는 개념은 MySQL 트랜잭션 로그 개념입니다.
binlog는 데이터베이스의 기록 및 업데이트 내역을 로깅하는 개념으로 이를 기반으로 상태 복구의 핵심으로 사용할 수 있는 증분 백업을 지원합니다.
한편, 트랜잭션 로그(redo log)는 트랜잭션 처리, 롤백, 크래시 복구, 특정 시점 복구 등에 사용된다.
MySQL의 트랜잭션은 InnoDB 스토리지 엔진만 사용하므로 MyISAM과 같은 스토리지 엔진은 binlog만 사용합니다.
point-in-time recovery는 트랜잭션 로그에서 지원하기 때문에 데이터베이스 솔루션을 사용할 때 트랜잭션 로그 지원 여부를 살펴봐야 합니다.
================================================== =========================
DB 서버(my.cnf) 외부에서 설정
DB 구성 파일에 정의
vi /etc/my.cnf
================================================== =========================
server-id=000001 # MySQL 서버 ID
사용자 = mysql # mysqlduser
log-bin=mysql-bin # Binlog 파일 이름
datadir=/data/mysql # binlog 등을 출력하기 위한 디렉토리
binlog_format=MIXED # binlog 형식 ////(0:MIXED, 1:STATEMENT, 2:ROW) ★
socket=/tmp/mysql.sock # 유닉스 도메인 소켓
binlog_cache_size = 2M #이진 로그 캐시 크기
max_binlog_size = 512M #최대 바이너리 로그 크기
expire_logs_days = 10 #보유 기간(만료 기간)
** bin 로그 형식의 형식 설명
기본값은 MySQL 5.7.6까지의 statement이고 그 이후는 row가 기본값이다.
(클러스터가 유난히 혼재) 빈 로그 포맷은 동적 변수이기 때문에 DB가 실행 중일 때도 set로 변경이 가능하지만 복제의 경우에는 master/slave를 다운으로 변경하는 것이 좋다.
Bean 로그 형식은 3가지가 있으며 상황에 따라 적절하게 사용해야 합니다.
-의견:
질의문으로 기록되기 때문에 용량도 적고 버전 특성에 구애받지 않는다.
그러나 복구 중 일관된 데이터에 대한 보장은 거의 없습니다(sysdate() 및 now() 등과 다른 결과).
쿼리 기반이기 때문에 복구(동기화)가 매우 느릴 수 있습니다.
-열:
Statement 형식과 달리 쿼리문이 아닌 변경된 데이터를 기준으로 기록한다.
또한 장단점은 진술과 반대이며 용량이 증가하고 복구 시 일관성을 보장할 수 있으며 빠른 복구(동기화)가 가능합니다.
-혼합:
스테이트먼트와 라인 방식의 장점을 결합한 형태를 나타냅니다.
원칙적으로 스테이트먼트 방식을 사용하되, 필요에 따라(정합성 보장 등) 라인 방식에 포함합니다.
데이터 일관성을 위해 혼합 또는 행 방법을 선택하는 것이 100배 더 쉽습니다.
주의
격리 수준이 읽기 커밋된 경우 다른 트랜잭션에서 동일한 데이터가 커밋되면 현재 트랜잭션이 완료되지 않더라도 변경된 값이 현재 트랜잭션에서 표시됩니다.
이러한 환경에서 statement 방식을 사용하면 트랜잭션 단위로 로깅이 이루어지므로 복구나 슬레이브 동기화 시 다른 결과가 나타날 수 있습니다.
따라서 read-committed 상태에서는 반드시 row 방식이나 혼합 방식을 사용해야 하며 이 경우 자동으로 row 형태로 전환된다.
================================================== =========================
DB 재시작
/etc/init.d/mysqld 재시작
systemctl이 mysql을 다시 시작합니다.
systemctl이 mysqld를 다시 시작합니다.
systemctl 재시작 mariadb
systemctl은 MariaDB를 다시 시작합니다.
DB 서버의 bin 로그 확인
mysql -u 루트 -p
바이너리 로그 파일 목록 확인
mysql> 바이너리 로그 표시;
바이너리 로그 캐시 크기 확인
mysql> ‘binlog_cache_size’와 같은 변수 표시;
바이너리 로그의 최대 크기 확인
mysql> ‘max_binlog_size’와 같은 변수 표시;
바이너리 로그 보관 기간 확인
mysql> ‘expire_logs_days’와 같은 변수 표시;
================================================== =========================
빈 로그 관리
1. 서버 외부에서 my.cnf 설정 변경
expire_logs_days = 10 #보존기간 변경(만료기간)
구성 파일 변경 후 재시작
2. DB 서버 내부(실행 중인 MySQL 내부)
mysql> ‘%expire%’와 같은 변수 표시; # 현재 유통기한 확인
mysql> set global expire_logs_days=7; # 유효기간을 7일로 변경
바이너리 로그 삭제
mysql> 바이너리 로그 표시;
mysql > mysql-bin.******에서 마스터 로그 지우기;
예
mysql> “mysql-bin.000016″에서 마스터 로그 지우기; 지정된 바이너리를 제외한 모든 “오래된” 바이너리 로그 파일 삭제
# (mysql-bin.000016 이전의 모든 로그는 삭제됩니다.
)
================================================== =========================
bin 로그로 데이터 복구
일반 쿼리로 전환하는 방법 (서버 외부 서버 접속 시 현재 디렉터리 기준)
mysqlbinlog mysql-binlog.00001 > backup1.sql
특정 데이터베이스에서 특정 날짜 시간 동안 사용된 특정 시간대 복구
시간 기반 복구 애플리케이션
mysqlbinlog –database=dbname –start-date=”startdate” –stop-date=”enddate” “mysql binlog 경로” > binlog1.sql
예)
mysqlbinlog –database=funshop_db –start-datetime=”20140101 00:00:00″ –stop-datetime=”20140101 23:59:59″ mysql-binlog.00001 > backup1.sql
특정 데이터베이스의 특정 위치 간에 binlog 텍스트 변환
mysqlbinlog –database=DBname –start-position=1 –stop-position=100000 “mysql binlog 경로” > binlog1.sql
mysqlbinlog 옵션
–database (-d) : 지정된 데이터베이스에 대한 쿼리만 추출
–start-datetime, –stop-datetime: 복원 시작 날짜와 마지막 날짜를 지정합니다.
–short-form (-s): 주석이 달린 콘텐츠는 추출에서 제외됩니다.
mysqlbinlog: binlog 백업이 진행 중일 때 알 수 없는 변수 “default-character-set=utf8”
-> my.cnf 설정에 default-character-set=utf8 옵션을 추가하여 mysqlbinlog를 실행할 때 발생하는 오류
–no-defaults 옵션을 사용하여 mysqlbinlog를 실행하여 기본 옵션을 제외하십시오.
mysqlbinlog –no-defaults -s –start-datetime=”2015-11-24 16:53:21″ –stop-datetime=”2015-11-26 17:50:30″ -d 대상 db- 이름 mysql -bin.000081 > 복구1.sql
또는
mysqlbinlog –no-defaults –database=(dbname) ./mysql-bin.0* > .(dbname).sql
================================================== =========================
스크립트
원격 Bean 로그 백업의 단점은 원본 서버가 다시 시작되거나 다른 이유로 연결이 끊어지면 연결이 자동으로 재설정되지 않는다는 것입니다.
또한 백업할 빈 로그 파일 이름을 실행문 끝에 지정해야 하는데 매번 파일 이름을 확인해야 하는 번거로움이 있다.
따라서 스크립트로 작성하는 것이 좋습니다.
================================================== =========================
#!
/빈/배시
mysqlbinlog=”/sbin/mysqlbinlog”
mysql_server=”192.168.0.49″
서버 이름=”db_server01″
backup_dir=”/data1/binlog_backup/$server_name”
사용자=”루트”
password=’데이터베이스 비밀번호’
mkdir -p $backup_dir
하는 동안 ( : ) ;
하다
last_file=`ls -1 $backup_dir | grep -v 원본 | 꼬리 -n 1`
if ( -z “$last_file” ); 그 다음에
last_file=`echo “바이너리 로그 보기” | mysql -Ns -u $user -p$password -h $mysql_server | awk ‘{print if(NR==1)$1}”
다른
지금=`날짜 +”%s”`
file_size=`stat -c%s $backup_dir/$last_file`
if ( “$file_size” -gt “0” ); 그 다음에
mv $backup_dir/$last_file $backup_dir/$last_file.orig_$now
파이
$backup_dir/$last_file 터치
파이
$mysqlbinlog –raw –read-from-remote-server –stop-never –host=$mysql_server –user=$user –password=$password –result-file=$backup_dir/ $last_file
수면 60
완전한
================================================== =========================