빈 로그란?
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
완전한
================================================== =========================