MySQL의 binlog, redo 로그 및 undo 로그를 사용하는 방법

1. Binlog

Binlog는 데이터베이스에서 수행되는 쓰기 작업에 대한 정보를 기록하는 데 사용되며 쿼리 작업을 제외하고 디스크에 바이너리 형식으로 저장됩니다. Binlog는 MySQL의 논리적 로그이며 서버 계층에 의해 기록됩니다. 모든 스토리지 엔진을 사용하는 MySQL 데이터베이스는 binlog 로그를 기록합니다.

• 논리적 로그: 간단히 SQL 문으로 이해될 수 있습니다.

• 물리적 로그: MySQL의 데이터는 데이터 페이지에 저장되며, 물리적 로그는 데이터 페이지의 변경 사항을 여기에 삽입합니다.

Binlog는 max_binlog_size 매개변수를 통해 각 binlog 파일의 크기를 설정할 수 있습니다. 파일 크기가 지정된 값에 도달하면 로그를 저장하기 위한 새 파일이 생성됩니다.

Binlog 사용 시나리오
프로젝트 실제 응용 프로그램에는 binlog의 두 가지 주요 사용 시나리오, 즉 마스터-슬레이브 복제와 데이터 복구가 있습니다.

• 마스터-슬레이브 복제: 마스터 측에서 binlog를 활성화한 다음 각 슬레이브 측에 binlog를 전송하여 마스터-슬레이브 데이터 일관성을 유지합니다.

• 데이터 복구: mysqlbinlog 도구를 사용하여 데이터를 복구합니다.

MySQL 마스터-슬레이브 동기화 원리

• 마스터 노드 binlog 덤프 스레드
  슬레이브 노드가 마스터 노드에 연결되면 마스터 노드는 로그 덤프 스레드를 생성하여 해당 내용을 보냅니다. binlog. binlog에서 작업을 읽을 때 이 스레드는 마스터 노드의 binlog를 잠급니다. 읽기가 완료되면 슬레이브 노드로 전송되기 전에도 잠금이 해제됩니다.

• 슬레이브 노드 I/O 스레드
  슬레이브 노드에서 슬레이브 시작 명령이 실행되면 슬레이브 노드는 I/O 스레드를 생성하여 마스터 노드에 연결하고 마스터 라이브러리에서 업데이트된 binlog를 요청합니다. I/O 스레드는 마스터 노드 binlog 덤프 프로세스에서 업데이트를 받은 후 이를 로컬 릴레이 로그에 저장합니다.

• 슬레이브 노드 SQL 스레드
  SQL 스레드는 릴레이 로그의 내용을 읽고 이를 구문 분석합니다. 궁극적으로 마스터-슬레이브 데이터의 일관성을 보장합니다.
  MySQL 데이터베이스 마스터-슬레이브 동기화 원칙

binlog의 내용
위에서 언급했듯이 binlog는 간단히 말해서 sql 문이지만 실제로는 실행된 sql 문의 반대 논리도 포함합니다. 삭제는 자체 삭제에 해당하며 업데이트에는 해당 업데이트가 실행되기 전과 후의 데이터 행에 대한 정보가 포함됩니다. 삽입에는 자체 삽입 및 해당 삭제 정보가 포함됩니다.

binlog 형식
세 가지 binlog 형식, 즉 명령문, 행 및 혼합이 있습니다. MySQL 5.7.7 이전에는 명령문을 기본으로 사용하였고, MySQL 5.7.7 이후에는 행을 기본으로 사용하였다. 로그 형식은 my.ini 구성 파일의 binlog-format을 통해 수정할 수 있습니다.
(1) 명령문: 명령문 기반 복제(SBR), 데이터를 수정하는 각 SQL 문이 binlog에 기록됩니다.

• 장점: 특정 행의 변경 사항을 구체적으로 기록할 필요가 없어 공간이 절약되고 IO가 줄어들며 성능이 향상됩니다.

• 단점: sysdate() 또는 sleep()과 같은 작업을 수행할 때 마스터와 슬레이브 데이터 불일치 상황

(2) 행: 행 기반 복제(RBR), SQL 문 컨텍스트 관련 정보를 기록하지 않지만, 어떤 레코드가 수정되었는지에 대한 세부 정보를 기록합니다.

• 장점: 각 행의 수정 내용이 매우 자세하게 기록되어 있으므로 데이터가 올바르게 복사되지 않는 상황이 없습니다.

• 단점: 각 행의 수정 내용이 자세히 기록되어 있으므로; 기록이 아주 자세하게 기록되면 많은 로그 내용이 생성됩니다. 업데이트 문이 있고 많은 레코드가 수정되었다고 가정합니다. 수정된 각 레코드는 binlog에 기록됩니다. 특히, alter table 작업의 경우 테이블 구조 변경으로 인해 레코드의 각 행이 변경되어 로그 볼륨이 갑자기 증가합니다.

(3) 혼합: 위의 명령문과 행마다 장점과 단점이 있으므로 두 가지를 혼합한 혼합 버전이 등장했습니다. 일반적인 상황에서는 명세서 형식을 사용하여 저장합니다. 명세서를 해결할 수 없는 경우 행 형식으로 전환하여 저장합니다.
특히 위에서 언급한 것처럼 새 버전(MySQL 5.7.7 이후)에서는 기본적으로 행 형식을 사용합니다. 여기에 있는 행도 테이블 변경 작업이 발생하면 기록에 사용됩니다. 다른 작업은 여전히 ​​행 형식 사용입니다.

Binlog 플러시 타이밍

InnoDB 스토리지 엔진의 경우 binlog는 트랜잭션이 제출될 때만 기록됩니다. 이때 레코드는 여전히 메모리에 남아 있으며, MySQL은 sync_binlog를 통해 binlog 플러시 타이밍을 제어합니다. 값 범위는 0-N입니다:

• 0: 디스크에 강제로 기록하지 않고 시스템이 디스크에 쓸 시기를 결정합니다.

• 1: Binlog는 각 제출 후 디스크에 기록되어야 합니다.

• N: N개의 트랜잭션마다 binlog가 기록됩니다.

위에서 볼 수 있듯이 sync_binlog의 가장 안전한 설정은 1이며, 이는 5.7.7 이후 MySQL 버전의 기본값이기도 합니다. 그러나 더 큰 값을 설정하면 데이터베이스 성능이 향상될 수 있으므로 실제 상황에서는 더 나은 성능을 얻기 위해 값을 적절하게 늘리고 일정 수준의 일관성을 희생할 수도 있습니다.

binlog의 물리적 파일 크기

my.ini 구성 파일에 있는 max_binlog_size 매개변수를 구성하여 binlog의 크기를 제어할 수 있습니다. 로그 크기가 binlog 파일의 용량 제한을 초과하는 경우 시스템은 로그를 계속 저장하기 위해 새 파일을 생성합니다. 트랜잭션이 상대적으로 크거나, 로그가 점점 많아지고, 트랜잭션이 차지하는 물리적 공간이 너무 큰 경우 어떻게 해야 합니까? MySQL은 my.ini 구성 파일에서expire_logs_days 매개변수를 구성하여 해결할 수 있는 자동 삭제 메커니즘을 제공합니다. 단위는 일입니다. 이 매개변수가 0이면 절대 삭제되지 않음을 의미하고, N이면 N일 후에 자동으로 삭제된다는 의미입니다.

2. redo log

redolog는 InnoDB 엔진의 독자적인 로그 시스템입니다. 주로 거래 내구성과 충돌 방지 기능을 달성하는 데 사용됩니다. Redolog는 SQL 문이 실행된 후 데이터 페이지의 특정 수정 사항을 기록하는 물리적 로그입니다.
우리 모두는 MySQL이 실행될 때 데이터가 디스크에서 메모리로 로드된다는 것을 알고 있습니다. 데이터를 수정하기 위해 SQL 문을 실행하면 수정된 내용은 실제로 메모리에 일시적으로만 저장됩니다. 이때 전원이 꺼지거나 다른 상황이 발생하면 수정된 내용은 손실됩니다. 따라서 데이터를 수정한 후 MySQL은 이러한 메모리 레코드를 디스크로 다시 플러시할 기회를 찾습니다. 그러나 주로 두 가지 측면에서 성능 문제가 있습니다.

InnoDB는 페이지 단위의 데이터 단위로 디스크와 상호 작용하며 트랜잭션은 전체 데이터 페이지를 디스크로 다시 플러시하는 경우 페이지에서 몇 바이트만 수정할 수 있습니다.

트랜잭션에는 여러 데이터 페이지가 포함될 수 있습니다. 이러한 데이터 페이지는 논리적으로만 연속적이지만 물리적으로 연속적이지는 않습니다.

따라서 MySQL은 특정 수정 사항을 기록하기 위해 리두로그를 설계했습니다. 트랜잭션을 통해 데이터 페이지를 삭제한 다음 다시 디스크로 다시 로그를 플러시합니다. 원래는 io를 줄이고 싶었는데, 이렇게 하면 io가 하나 더 추가되지 않을까요? InnoDB의 설계자는 설계 초기에 이러한 점을 고려했습니다. Redo 로그 파일은 일반적으로 크기가 작으며 디스크 플러시 중에 순차 I/O가 사용됩니다. 임의 I/O에 비해 성능이 더 좋습니다.

리두 로그의 기본 개념
리두로그는 두 부분으로 구성되는데, 하나는 메모리에 있는 리두 로그 버퍼이고 다른 하나는 디스크에 있는 리두 로그 파일입니다. 데이터 레코드가 수정될 때마다 이러한 수정 사항은 먼저 리두 로그 버퍼에 기록된 다음 메모리의 수정 사항을 다시 리두 로그 파일로 플러시할 적절한 기회를 기다립니다. 로그를 먼저 쓰고 디스크에 쓰는 기술이 바로 WAL(Write-Ahead Logging) 기술이다. 클러스터링된 인덱스, 보조 인덱스 및 실행 취소 페이지에 대한 수정 사항은 모두 리두로그에 기록되어야 합니다.

컴퓨터 운영 체제에서 사용자 공간의 버퍼 데이터는 일반적으로 디스크에 직접 쓸 수 없으며 운영 체제 커널 공간 버퍼(OS 버퍼)를 통과해야 합니다. 따라서 리두 로그 파일에 리두 로그 버퍼를 쓰는 것은 실제로는 OS 버퍼에 먼저 쓴 다음 시스템 호출 fsync()를 통해 리두 로그 파일에 플러시하는 것입니다.

Mysql은 세 가지를 지원합니다. 리두 로그 버퍼 종류 리두 로그 파일을 작성하는 시점은 innodb_flush_log_at_trx_commit 매개 변수를 통해 구성할 수 있습니다. 각 매개 변수 값의 의미는 다음과 같습니다. writing)

트랜잭션이 제출되면 redo는 커밋되지 않습니다. 로그 버퍼의 로그는 os 버퍼에 기록됩니다. 대신 로그는 1초마다 os 버퍼에 기록되고 이를 기록하기 위해 fsync()가 호출됩니다. 리두 로그 파일. 즉, 0으로 설정하면 시스템이 충돌할 때 약 1초마다 데이터가 디스크에 기록됩니다. 1 (실시간 쓰기, 실시간 브러싱) 트랜잭션이 제출될 때마다 리두 로그 버퍼에 있는 로그가 os 버퍼에 기록되고 fsync()가 호출되어 이를 플러시합니다. 리두 로그 파일. 이 방법은 시스템이 충돌하더라도 데이터가 손실되지 않지만 각 제출이 디스크에 기록되므로 IO 성능이 저하됩니다. 2 (실시간 쓰기, 지연된 브러싱) 각 제출물은 os 버퍼에만 기록되고, 이후 fsync()가 1초마다 호출되어 os 버퍼에 있는 로그를 redo 로그 파일에 기록합니다.

redo 로그 기록 형식
redolog는 고정된 크기의 순환 쓰기 형식을 채택합니다. redolog가 가득 차면 처음부터 다시 기록됩니다. 왜 이렇게 설계되었나요?
리두 로그의 주요 목적은 데이터 페이지 플러시 요구 사항을 줄이는 것입니다. Redolog는 데이터 페이지의 수정 사항을 기록하지만 데이터 페이지도 디스크로 다시 플러시되면 이러한 기록은 쓸모가 없게 됩니다. 따라서 MySQL이 이전 리두로그가 만료되었다고 판단하면 새 데이터가 유효하지 않은 데이터를 덮어쓰게 됩니다. 그렇다면 그것이 보장되어야 하는지 여부를 어떻게 판단할 수 있을까요?

위 그림은 redo log 파일의 개략도입니다. write pos는 현재 redolog에 기록되어 있는 로그 시퀀스 번호 LSN(로그 시퀀스 번호)을 나타냅니다. 데이터 페이지가 디스크로 다시 플러시되면 리두 로그 파일의 LSN이 업데이트되어 이 LSN 이전의 데이터가 디스크에 기록되었음을 나타냅니다. write pos와 check point 사이의 부분은 redolog의 예비 부분으로, 새로운 레코드를 기록하는 데 사용됩니다. check point와 write pos 사이의 부분은 redolog가 기록한 데이터 페이지 중 수정된 부분이지만, 데이터 페이지에는 기록되지 않은 부분입니다. 이 부분은 디스크로 다시 플러시되었습니다. 쓰기 위치가 체크포인트를 따라잡으면 먼저 체크포인트를 앞으로 밀고 위치를 비운 다음 새 로그를 기록합니다.

innodb를 시작할 때 지난번에 정상적으로 종료되었거나 비정상적으로 종료되었더라도 항상 복구 작업이 수행됩니다. Recovery 중에는 데이터 페이지의 LSN을 먼저 확인하게 되는데, 이 LSN이 Redolog의 LSN, 즉 write pos 위치보다 작은 경우에는 데이터 페이지에서 완료되지 않은 작업이 Redolog에 기록된다는 의미이며, 그런 다음 가장 가까운 체크 포인트부터 시작하여 데이터 동기화를 시작합니다.

데이터 페이지의 LSN이 리두로그의 LSN보다 클 가능성이 있나요? 대답은 물론 가능합니다. 이런 일이 발생하면 재실행 이후의 부분은 다시 수행되지 않습니다. 이는 이미 수행된 작업을 다시 수행할 필요가 없음을 의미하기 때문입니다.
리두 로그와 빈로그의 차이점

	redo 로그	binlog
파일 크기	리두 로그의 크기는 고정되어 있습니다.	Binlog는 구성 매개변수 max_binlog_size를 통해 각 binlog 파일의 크기를 설정할 수 있습니다.
구현 방법	redo 로그는 InnoDB 엔진 계층에 의해 구현되며 모든 엔진에 있는 것은 아닙니다.	Binlog는 서버 레이어로 구현됩니다. 모든 엔진은 binlog 로그를 사용할 수 있습니다.
기록 방식	redo 로그는 끝까지 기록할 때 처음으로 돌아가서 기록합니다. 루프에서.	binlog는 파일 크기가 지정된 값보다 큰 경우 후속 로그가 새 파일에 기록됩니다.
적용 가능한 시나리오	redo 로그는 충돌 복구에 적합합니다(충돌 방지)	binlog 마스터-슬레이브 복제 및 데이터 복구에 적합

binlog와 redo 로그의 차이점에서 알 수 있습니다. binlog 로그는 보관에만 사용되며 binlog에만 의존하면 충돌 방지 기능이 없습니다. 그러나 Redo 로그만 작동하지 않습니다. 왜냐하면 Redo 로그는 InnoDB에 고유하고 로그의 레코드는 디스크에 작성된 후 덮어쓰기되기 때문입니다. 따라서 데이터베이스가 다운되고 재시작될 때 데이터가 손실되지 않도록 binlog와 redo 로그를 동시에 기록해야 합니다.
2단계 제출
위에서는 redolog와 binlog에 대해 간략하게 소개했습니다. 데이터를 수정할 때 이러한 수정 사항을 저장하게 되는데, 하나는 물리적 로그이고 다른 하나는 논리적 로그입니다. 그렇다면 수정 프로세스는 어떻게 수행되었습니까?

지금 실행할 업데이트 문이 있다고 가정하고, id=2인 table_name 세트 c=c+1에서 업데이트합니다. 실행 프로세스는 다음과 같습니다.

• 먼저 레코드 id=2를 찾습니다.

• 실행자는 엔진이 제공한 데이터 행으로 이동하여 이 값에 1을 더하고 새 데이터 행을 가져온 다음 엔진 인터페이스를 호출하여 이 새 데이터 행을 작성합니다. 한 행의 데이터를 메모리에 저장하고 동시에 업데이트합니다. 작업은 현재 준비 상태인 redolog에 기록됩니다. 그런 다음 실행자는 실행이 완료되고 언제든지 트랜잭션을 제출할 수 있다는 알림을 받습니다.

• 실행자는 이 작업의 binlog를 생성하고 디스크에 binlog를 씁니다.

• 실행자는 엔진의 커밋을 호출합니다. 리두 로그가 커밋 상태로 변경되고 업데이트가 완료됩니다.

• 개략도는 다음과 같습니다.

리두 로그 작성 과정을 두 단계로 나누어 준비합니다. 커밋을 2단계 커밋이라고 합니다.

redolog와 binlog는 모두 트랜잭션의 커밋 상태를 나타내는 데 사용될 수 있으며, 2단계 커밋은 두 상태를 논리적으로 일관되게 유지하는 것입니다. 2단계 커밋을 사용하지 않고 한 단계를 먼저 작성한 다음 다른 단계를 작성하면 문제가 발생할 수 있습니다.
현재로서는 업데이트가 여전히 예시로 사용됩니다. 현재 id=2이고 필드 c=0이 있다고 가정합니다. 다음 상황을 각각 분석합니다.

redolog를 먼저 작성한 다음 binlog를 작성합니다.

redolog가 완료되었지만 binlog가 완료되지 않은 경우, MySQL은 갑작스러운 예외로 인해 다시 시작됩니다. Redolog는 이전에 작성되었기 때문에 시스템을 재시작한 후에도 수정된 레코드가 여전히 존재하므로 복구 후 이 줄의 c 값은 1입니다. 그러나 시스템 재시작으로 인해 이 기록은 binlog에 존재하지 않습니다. 나중에 로그를 백업할 때 저장된 binlog에는 이 문장이 존재하지 않습니다. 그러면 이 binlog를 사용하여 임시 라이브러리를 복원해야 하는 경우 이 명령문의 binlog가 손실되므로 이번에는 임시 라이브러리가 업데이트되지 않습니다. 복원된 행의 c 값은 0입니다. 원래 라이브러리의 값과 동일합니다.
binlog를 먼저 쓰고 redolog를 작성하세요
binlog를 먼저 쓰고 redolog를 작성할 때 시스템을 재시작하면 됩니다. 재시작 후 redolog에는 c를 수정한 기록이 없으며, 이때 c의 값은 여전히 ​​0이다. 그러나 "Change c from 0 to 1" 로그가 binlog에 기록되었습니다. 따라서 나중에 binlog를 사용하여 복원하면 트랜잭션이 하나 더 나오게 되는데, 복원된 행의 c 값은 1로 원래 데이터베이스의 값과 다릅니다.
결론적으로 특정 로그를 먼저 작성한 후 다른 로그를 작성하면, 데이터베이스의 상태가 binlog를 사용하여 복원된 라이브러리의 상태와 일치하지 않게 됩니다.
3. undo log

undolog는 주로 특정 행의 레코드가 수정되기 전의 상태를 기록하는데 사용되며, 수정되기 전의 데이터를 기록합니다. 트랜잭션이 롤백될 때 트랜잭션이 시작되기 전의 상태로 레코드를 복원하려면 undolog를 사용하십시오. 트랜잭션의 원자성 및 내구성도 로그 취소를 통해 달성됩니다. 실행 취소 로그는 주로 데이터의 논리적 변경 사항을 기록합니다. 예를 들어 INSERT 문은 DELETE 실행 취소 로그에 해당하며, 각 UPDATE 문은 반대되는 UPDATE 실행 취소 로그에 해당하므로 오류가 발생하면 롤링될 수 있습니다. 거래 전 데이터 상태로 돌아갑니다. 데이터 복구 프로세스 중에 binlog와 redolog를 조합하면 데이터 복구의 정확성을 보장할 수 있습니다.

undolog의 기능 프로세스는 다음과 같습니다.

트랜잭션이 시작되기 전에 미리 수정된 버전을 실행 취소 로그에 기록합니다.

• 수정을 시작하고 수정된 데이터를 메모리에 저장합니다.

디스크에 대한 로그 취소를 유지합니다.
데이터 페이지를 다시 디스크에 플래시합니다.
트랜잭션 제출.
실행 취소도 데이터 페이지 전에 디스크에 다시 플러시해야 합니다. 언돌로그가 완료되면, 그 안에 기록된 정보를 이용해 트랜잭션을 롤백하여 데이터를 복구할 수 있습니다.

• 한 거래에서 동일한 데이터가 여러 번 수정될 수 있는데, 수정 전의 기록을 언돌로그에 기록해야 하나요? 이 경우 실행 취소 로그의 양이 너무 많아 이때 다시 실행 로그가 작동하게 됩니다. 트랜잭션에서 동일한 레코드가 수정되면 undolog는 트랜잭션이 시작되기 전의 원본 레코드만 기록합니다. 이 레코드가 다시 수정되면 redolog는 후속 변경 사항을 기록합니다. 데이터 복구 프로세스 중에는 redolog 및 undolog 기능을 조정하고, 롤 포워드 및 롤백 작업을 조정하여 데이터 복구가 완료됩니다. 프로세스는 다음과 같습니다:
  
  

위 내용은 MySQL의 binlog, redo 로그 및 undo 로그를 사용하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!