MySQL 바이너리 로그 관련 문제에 대한 자세한 설명

이 글의 출처:
(출처권을 갖는다는 것은 창작물이 아닙니다. 저의 겸손한 작업은 그것과 거리가 멀습니다. 있을 수 있는 일부 오류가 수정되거나 수정될 것이기 때문에 원문을 링크할 뿐입니다. 앞으로는 아무것도 추가되지 않음)

오늘 저는 Yunqi 커뮤니티에서 개최한 MySQL "시즌 1: 도전 Xuanxian의 느린 SQL 성능 최적화 대회"를 우연히 발견했습니다. 데이터를 쓰기 위해 테스트 서버에서 테스트 스크립트를 실행할 때, 오류 메시지는 다음과 같습니다.
다중 문 트랜잭션에는 'max_binlog_cache_size' 바이트 이상의 저장 공간이 필요합니다. 이 mysqld 변수를 늘리고 agagin을 시도하세요

프롬프트 max_binlog_cache_size 공간이 부족합니다. 바이너리 로그가 켜져 있기 때문에 기본값이었습니다. 이전 설정에서는 대용량 트랜잭션 작업이 없었고 만남도 없었습니다. 문제는 이번에는 처음에 대규모 트랜잭션 작업이 실패했다는 것입니다.
binlog_cache_size의 크기를 수정한 후 문제가 해결되었습니다.

기본 innodb 엔진을 사용하므로 바이너리 로그가 활성화됩니다.
트랜잭션 작업의 경우 트랜잭션이 제출되기 전에 작성된 작업이 캐시됩니다. , 전체 작업이 완료될 때까지 mysqld 프로세스는 전체 작업을 바이너리 로그에 기록합니다.
무언가가 시작되면 binlog_cache_size 시스템 변수에 지정된 값에 따라 콘텐츠 공간이 할당됩니다. 지정된 binlog_cache_size 캐시 공간이 충분하지 않으면 실행된 트랜잭션 작업이 롤백되고 실패 메시지가 표시됩니다.

그런데, 바이너리 로그와 관련 매개변수 정보를 정리해보자

바이너리 로그란 무엇인가요?
MySQL 데이터베이스에 쓰기 작업(추가, 삭제, 수정, 쿼리 제외)을 기록하는 데 사용됩니다. 이는 sqlserver의 전체 복구 모드에서 트랜잭션 로그 파일에 해당합니다.
바이너리 로그의 역할은 무엇인가요?
　1. 복제에 사용됩니다. 마스터-슬레이브 복제가 구성되면 마스터 서버는 생성한 바이너리 로그를 슬레이브에 보냅니다. 슬레이브는 이 바이너리 로그의 정보를 사용하여 마스터-슬레이브 동기화를 달성하기 위해 로컬로 다시 실행합니다.
　2. 사용자 복구를 위해 MySQL은 바이너리 로그를 사용하여 전체 백업 및 차등 백업을 기반으로 시점 또는 트랜잭션 ID를 기반으로 복구 작업을 수행할 수 있습니다. 원칙은 마스터-슬레이브 복제의 로그 다시 실행과 유사합니다.

바이너리 로그 관련 매개변수 정보

1, 바이너리 로그 활성화
바이너리 로그를 활성화하려면 log-bin 매개변수의 경로를 지정해야 합니다. 예: log_bin=/var/lib/mysql/mysql- bin
바이너리 로그를 시작하면 바이너리 로그를 관리하는 log_bin_index 파일이 자동으로 생성됩니다. log_bin 옵션도 on으로 표시되는데, 이는 바이너리 로그가 켜져 있음을 의미합니다.

　

2, 바이너리 로그 파일의 형식

바이너리 로그의 형식은 binlog_format 매개변수에 의해 제어됩니다. 바이너리 로그에는 명령문 기반, 행 기반 및 다음의 조합의 세 가지 모드가 있습니다. 처음 두 가지. 혼합 모드(mixed)
명령문 기반 바이너리 함수에는 몇 가지 결함이 있습니다(제 생각에는). 예를 들어 동일한 업데이트 명령문에서 현재 시간을 사용하는 now 업데이트 작업도 마스터에서 현재 시간을 얻습니다. 서버와 슬레이브 서버는 마스터-슬레이브 복제로 얻은 결과가 다릅니다.
행 기반 바이너리 로그 모드는 명령문 기반 모드의 단점을 일부 해결하지만 경우에 따라 많은 수의 로그가 생성됩니다. 예를 들어 업데이트 작업이 행인 경우 100만 행의 데이터가 업데이트됩니다. -바이너리 로그 기반, 결과적으로 100만 행의 데이터가 생성됩니다. 로그
위 두 가지 방법의 장점을 결합한 하이브리드 모드를 기반으로 합니다.
구성 파일에서 설정할 수 있습니다: binlog_format = MIXED

　

3. 바이너리 로그 기록 타이밍

바이너리 로그 기록은 동기식일 수 있습니다. 즉, 트랜잭션이 제출된 후 바이너리 로그에 기록될 수 있습니다. 또는 비동기식일 수 있습니다. 디스크에 쓸 시기를 파악하는 것은 운영 체제의 디스크 캐시에 달려 있습니다.
sync_binlog=n 매개변수로 제어됩니다. sync_binlog = 1로 설정되면 가장 높은 보안 수준의 쓰기를 나타냅니다(단, 트랜잭션 로그가 손실되지 않는다는 보장은 없습니다). 성능에 일정한 영향을 미칩니다.
개인적으로는 트랜잭션 엔진이라면 사물의 보안을 확보하기 위함이라고 생각하는데, sync_binlog를 1로 설정하지 않을 이유가 없습니다.

sync_binlog를 1로 설정하면 잠재적으로 트랜잭션 로그도 손실될 수 있다고 하는데 왜 손실되는지는 알 수 없습니다. 트랜잭션 엔진이므로 백업으로 실행 취소 또는 다시 실행 로그 레이어도 있기 때문입니다. ?

나중에 생각해 보세요. redo 및 undo 로그가 있기 때문에 마스터 서버에서는 사물의 일관성이 보장될 수 있습니다. 마스터-슬레이브 복제 중에 손실될 수 있는 것이 전달되지 않을 수도 있습니다. 슬레이브 서버.

　

4. 바이너리 로그의 단일 파일 크기

바이너리 로그의 크기는 일반적인 상황에서 설정된 최대 파일 크기를 초과하지 않습니다. 설정된 최대 한도를 초과하면 로그 롤링이 발생합니다. 즉, 바이너리 로그 파일이 재생성됩니다.
Max_binlog_size = 100M
여기에 표시된 104857600의 단위는 바이트입니다. 즉, 104857600/1024/1024 = 100M

　

5, 롤링 후 바이너리 로그 정리

가 생성됩니다. 로그를 저장할 새 파일 , 로그 파일은 만료된 후 자동으로 삭제됩니다. 그렇지 않으면 로그 파일의 연속 스트림이 생성됩니다. 예를 들어 만료 시간을 2로 설정할 수 있으며 구성 가능한 값은expire_logs_days = 2입니다. 일이 지난 항목은 자동으로 삭제됩니다.
　show master log 명령을 통해 현재 바이너리 로그 파일 수를 볼 수 있습니다

　

6, 바이너리 로그 파일 스크롤

　1) 일반적인 상황에서는 레코드가 가득 차면 자동으로 스크롤됩니다. 접미사 이름은 +1이 됩니다

　2 ) mysql 서비스를 다시 시작하면 지정된 최대 용량에 따라 로그 파일이 가득 찼는지 여부에 관계없이 자동으로 스크롤됩니다.
　3) 수동 스크롤, 실행 후 플러시 로그 명령을 실행합니다. 다음과 같이 로그를 플러시하면 바이너리 로그 파일이 다시 생성됩니다.

　

　4 ) 바이너리 로그를 수동으로 삭제합니다

purge 바이너리 로그를 fileName에 사용하여 지정된 fileName 앞에 있는 파일을 삭제할 수 있습니다.

　

'2017-03-10 10:10:00' 이전 바이너리 로그 제거 명령을 사용하여 지정된 시간을 삭제할 수 있습니다. 이전 파일

　

date_sub(지금()) 이전에 지정된 로그 제거 바이너리 로그를 삭제합니다. 간격은 7일입니다. );

Xiaoxiang 마스터는 date_sub(지금(), 간격 7일) 이전에 마스터 로그를 제거하는데, 이는 효과가 있어야 합니까(바이너리 및 마스터 키워드)?

7, 바이너리 로그 바인딩(또는 제외) 데이터베이스

일부 데이터베이스는 바이너리 로깅을 활성화하거나 일부 데이터베이스는 바이너리 로깅을 활성화하지 않도록 설정할 수 있습니다.

　# binlog_do_db: 마스터-슬레이브 설정 시 사용
　# binlog - ignore-db: 로그를 기록하지 않는 데이터베이스를 설정합니다.
MySQL 5.7.18(my.cnf에 구성)에 설정되어 있지만 쿼리할 때 쓸모가 없는 것 같나요?

　

8, 바이너리 로그 캐시 및 캐시 크기 구성

처음에 언급한 문제인 binlog_cache_size의 크기는 시작 시 binlog_cache_size 시스템 변수에 지정된 값에 따라 콘텐츠 공간이 할당됩니다. 지정된 binlog_cache_size 캐시 공간이 충분하지 않으면 오류가 발생합니다.

　여기서 표시되는 레코드의 단위도 바이트입니다. 1024를 2개로 나눈 값은 MB 단위의 용량입니다.
　테스트 등 대규모 트랜잭션 작업이 있는 경우 캐시를 상대적으로 크게 설정해야 합니다. 그렇지 않으면 명령문을 성공적으로 실행할 수 없습니다

　

max_binlog_cache_size와 binlog_cache_size의 차이점은 전자가 인스턴스 수준 캐시라는 점입니다. 후자는 세션 수준 캐시입니다. 동시성이 큰 경우 max_binlog_cache_size를 약간 더 크게 설정하는 것을 고려해야 합니다.

기본 max_binlog_cache_size는 4GB이고, 최대값도 4GB입니다. 여기서 테스트용 설정은 100MB(104857600/1024.0/1024.0)

　

입니다.

max_binlog_cache_size로 설정되는 최대 메모리 크기는 4GB입니다. 서버 콘텐츠가 128GB 이상인 경우에는 성공적인 동시 쓰기를 보장하기 위해 기본적으로 max_binlog_cache_size를 최대로 설정해도 문제가 되지 않습니다.
세션 수준의 binlog_cache_size는 비즈니스 상황에 따라 조정될 수 있지만, 개인적으로는 조금 더 크게 설정해도 큰 문제는 없다고 생각합니다. 및 기타 데이터 추출 또는 병합 작업으로 인해 대량의 로그가 생성될 수 있습니다.
binlog_cache_disk_use, binlog_cache_use를 보면 binlog_cache_size 조정이 필요한지 판단할 수 있다고 합니다.
　하지만 이 매개변수는 MySQL5.7.18

　

9, 바이너리 로그의 다른 매개변수

max_binlog_stmt_cache_size는 비트랜잭션 명령문용이므로 비트랜잭션 매개변수는 당분간 신경 쓰지 않습니다
어느 마스터가 innodb 엔진의 장점은 트랜잭션 지원에만 있는 것이 아니라고 말한 것을 기억합니다. 비 트랜잭션 myisam 엔진에 비해 읽기 성능의 격차가 점점 작아지고 있습니다. 따라서 MySQL은 innodb를 기본 엔진.
myisam을 버리고 innodb로 가는 것이 올바른 방법입니다.
　binlog_checksum은 복제를 위한 마스터-슬레이브 체크섬으로 사용됩니다. 이 매개 변수는 아직 연구하지 않았으므로 지금은 신경 쓰지 않겠습니다
자세한 내용은 Xiang Xiangshen의 기사를 참조하세요.

요약:

MySQL 바이너리 로그는 기능(마스터-슬레이브 복제)뿐만 아니라 바이너리 로그가 켜져 있을 때 보안(바이너리 로그) 및 트랜잭션 작업에도 사용됩니다. 환경은 필수 구성으로 간주될 수 있습니다.
　동시에 다양한 매개변수가 특정 작업에 영향을 미치므로 사용 시 데이터베이스의 기능과 가용성이 보장되도록 바이너리 로그의 매개변수에 특별한 주의를 기울여야 합니다.

참고자료:

　　 《Smear MySQL》

　　그리고 다양한 플립북, 온라인 자료

　

행동을 취하면 사고 패턴과 두려움이 바뀔 수 있습니다.

위 내용은 MySQL 바이너리 로그 관련 문제에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!