[mysql] binary log (feat. 증분 백업.. 할 수 있을까..?)

 

증분백업 공부 해보려니 기초가 바이너리 로그...바이너리 로그 너 뭐야..?

바이너리 로그란?

테이블 생성 작업이나 테이블 데이터 변경과 같은 데이터베이스 변경 사항을 설명하는 "이벤트" 로그

insert, update, delete 등

데이터를 수정하지않는 select, show 등 제외 

 

서버의 바이너리 로그는 데이터베이스 내용의 수정 사항을 설명하는 " 이벤트 " 를 포함하는 파일로 구성됩니다 . 서버는 이러한 파일을 바이너리 형식으로 작성합니다. 텍스트 형식으로 내용을 표시하려면 mysqlbinlog 유틸리티를 사용합니다. 릴레이 로그는 바이너리 로그와 동일한 형식을 가지고 있기 때문에 복제 설정에서 복제 서버에서 작성한 릴레이 로그 파일의 내용을 표시하는 데 mysqlbinlog를 사용할 수도 있습니다. 바이너리 로그와 릴레이 로그는 섹션 7.4.4, "바이너리 로그" 및 섹션 19.2.4, "릴레이 로그 및 복제 메타데이터 리포지토리" 에서 자세히 설명합니다 .

바이너리 로그의 목적

replication 복제

• 복제의 경우 복제 소스 서버의 바이너리 로그는 복제본으로 전송될 데이터 변경 사항의 기록을 제공합니다. 소스는 바이너리 로그에 포함된 정보를 복제본으로 전송하고 복제본은 해당 트랜잭션을 재생성하여 소스에서 수행된 것과 동일한 데이터 변경 사항을 수행합니다. 섹션 19.2, "복제 구현"을 참조하세요 .

recovery 복구

• 특정 데이터 복구 작업에는 바이너리 로그를 사용해야 합니다. 백업이 복원된 후 백업이 만들어진 후 기록된 바이너리 로그의 이벤트가 다시 실행됩니다. 이러한 이벤트는 백업 지점부터 데이터베이스를 최신 상태로 만듭니다. 섹션 9.5, "지정 시점(증분) 복구"를 참조하십시오 .

바이너리 로깅을 활성화한 서버를 실행하면 성능이 약간 느려집니다. 그러나 복제를 설정하고 복원 작업을 할 수 있게 해주는 바이너리 로그의 이점은 일반적으로 이러한 사소한 성능 저하보다 더 큽니다.

로깅 방식

statement

MySQL의 복제 기능은 원래 소스에서 복제본으로 SQL 문을 전파하는 데 기반을 두었습니다. 이를 문장 기반 로깅 이라고 합니다 . 이 형식을 사용하려면 서버를 .으로 시작하면 됩니다 --binlog-format=STATEMENT.

행 row (default)

행 기반 로깅 (기본값) 에서 소스는 개별 테이블 행이 어떻게 영향을 받는지 나타내는 이벤트를 바이너리 로그에 기록합니다. .으로 시작하면 서버가 행 기반 로깅을 사용하도록 할 수 있습니다 --binlog-format=ROW.

mixed

세 번째 옵션인 mixed logging 도 사용할 수 있습니다. mixed logging . mixed logging 을 사용하면 기본적으로 명령문 기반 로깅이 사용되지만 로깅 모드는 아래에 설명된 대로 특정 경우에 자동으로 행 기반으로 전환됩니다. 옵션으로 mysqld를 시작하여 MySQL이 mixed logging 을 명시적으로 사용하도록 할 수 있습니다 --binlog-format=MIXED.

 

바이너리 로그 상태 확인

# on off
SHOW VARIABLES LIKE 'log_bin';
# 바이너리 로그 방식
SHOW VARIABLES LIKE 'binlog_format';
# 적재된 바이너리 로그 확인
SHOW BINARY LOGS;

바이너리 로그 형식 설정

보통 gobal로 관리하나, 세션값(권한 필요)으로도 관리가 가능

방법은 시스템 변수 수정 또는 binlog_format 값 수정

SET GLOBAL binlog_format = 'STATEMENT|ROW|MIXED';

 

클라이언트가 세션별로 바이너리 로깅을 설정하는 경우

• 데이터베이스에 많은 작은 변경을 가하는 세션의 경우 행 기반 로깅을 사용하는 것이 좋습니다.
• 절의 많은 행과 일치하는 업데이트를 수행하는 세션에서는 WHERE많은 행보다 몇 개의 명령문을 로깅하는 것이 더 효율적이므로 명령문 기반 로깅을 사용하는 것이 좋습니다.
• 일부 명령문은 소스에서 많은 실행 시간을 요구하지만, 몇 개의 행만 수정되는 결과를 낳습니다. 따라서 행 기반 로깅을 사용하여 복제하는 것이 유익할 수 있습니다.

주의/참조 사항

1. 테이블을 사용하고 InnoDB 트랜잭션 격리 수준이 READ COMMITTED또는 READ UNCOMMITTED이면 행 기반 로깅만 사용 가능

2. ROW 방식이여도, DDL(CREATE, ALTER, DROP 등) 문은 명령문 기반 형식을 사용

3. ROW방식인 경우  binlog_row_event_max_size 시스템 변수와 해당 시작 옵션은 --binlog-row-event-max-size행 이벤트의 최대 크기에 소프트 한계를 설정합니다. 기본값은 8192바이트이며, 값은 서버 시작 시에만 변경할 수 있습니다. 가능한 경우 바이너리 로그에 저장된 행은 이 설정 값을 초과하지 않는 크기의 이벤트로 그룹화됩니다. 이벤트를 분할할 수 없는 경우 최대 크기를 초과할 수 있습니다.

이 --binlog-row-event-max-size 옵션은 행 기반 복제가 가능한 서버에서 사용할 수 있습니다. 행은 이 옵션의 값을 초과하지 않는 바이트 크기를 갖는 청크로 바이너리 로그에 저장됩니다. 값은 256의 배수여야 합니다. 기본값은 8192입니다.

경고
복제에 명령문 기반 로깅을 사용할 때 명령문이 데이터 수정이 비결정적 (즉, 쿼리 최적화 프로그램에 맡겨짐)이 되도록 설계된 경우 소스와 복제본의 데이터가 다를 수 있습니다. 일반적으로 이는 복제 외부에서도 좋은 관행이 아닙니다. 이 문제에 대한 자세한 설명은 섹션 B.3.7, "MySQL의 알려진 문제"를 참조하십시오 .

바이너리 로깅 옵션 및 변수

바이너리 로깅과 함께 사용되는 시작 옵션

--binlog-row-event-max-size=N

row 기반 바이너리 로깅을 사용하는 경우 이 설정은 바이트 단위의 행 기반 바이너리 로그 이벤트의 최대 크기에 대한 소프트 제한입니다. 가능한 경우 바이너리 로그에 저장된 행은 이 설정의 값을 초과하지 않는 크기의 이벤트로 그룹화됩니다. 이벤트를 분할할 수 없는 경우 최대 크기를 초과할 수 있습니다. 값은 256의 배수여야 합니다(그렇지 않으면 반올림). 기본값은 8192바이트입니다.

명령줄 형식 Command-Line Format	--binlog-row-event-max-size=#
시스템 변수 System Variable	binlog_row_event_max_size
범위 Scope	Global
동적 Dynamic	No
set_var hint 적용	No
유형 Type	Integer
기본값 Default Value	8192
최소값 Minimum Value	256
최대값(64비트 플랫폼) Maximum Value (64-bit platforms)	18446744073709551615
최대값(32비트 플랫폼)  Maximum Value (32-bit platforms)	4294967295
단위 Unit	bytes

 

--log-bin[=base_name]

바이너리 로그 파일에 사용할 기본 이름을 지정

Command-Line Format	--log-bin=file_name
Type	File name

• 바이너리 로그 파일에 사용할 기본 이름을 지정합니다. 바이너리 로깅이 활성화되면 서버는 데이터를 변경하는 모든 명령문을 백업 및 복제에 사용되는 바이너리 로그에 로깅합니다. 바이너리 로그는 기본 이름과 숫자 확장자가 있는 일련의 파일입니다. --log-bin옵션 값은 로그 시퀀스의 기본 이름입니다. 서버는 기본 이름에 숫자 접미사를 추가하여 순서대로 바이너리 로그 파일을 만듭니다.이진 로그 파일의 기본 위치는 데이터 디렉토리입니다. 이 --log-bin옵션을 사용하여 기본 이름에 선행 절대 경로 이름을 추가하여 다른 디렉토리를 지정하여 대체 위치를 지정할 수 있습니다. 서버가 사용된 이진 로그 파일을 추적하는 이진 로그 인덱스 파일에서 항목을 읽을 때 항목에 상대 경로가 포함되어 있는지 확인합니다. 상대 경로가 있으면 경로의 상대 부분이 옵션 을 사용하여 설정한 절대 경로로 바뀝니다 --log-bin. 이진 로그 인덱스 파일에 기록된 절대 경로는 변경되지 않습니다. 이 경우 인덱스 파일을 수동으로 편집하여 새 경로 또는 경로를 사용할 수 있도록 해야 합니다. 이진 로그 파일 기본 이름과 지정된 경로는 시스템 log_bin_basename변수로 사용할 수 있습니다.--skip-log-bin 바이너리 로깅을 비활성화하려면 시작 시 또는 옵션을 지정할 수 있습니다 --disable-log-bin . 이러한 옵션 중 하나가 지정되고 --log-bin또한 지정된 경우 나중에 지정된 옵션이 우선합니다. 바이너리 로깅이 비활성화되면 log_bin 시스템 변수가 OFF로 설정됩니다.및 옵션은 바이너리 로깅을 요구합니다. 바이너리 로깅을 비활성화하는 경우 이러한 옵션을 생략하거나 및 를 지정하십시오 . MySQL은 or 가 지정 되면 기본적으로 이러한 옵션을 비활성화합니다 . or 와 함께 or --log-replica-updates를 지정하면 경고 또는 오류 메시지가 발생합니다. --replica-preserve-commit-order--log-replica-updates=OFF--skip-replica-preserve-commit-order--skip-log-bin--disable-log-bin--log-replica-updates--replica-preserve-commit-order--skip-log-bin--disable-log-bin바이너리 로그의 형식 및 관리에 대한 정보는 섹션 7.4.4, "바이너리 로그"를 참조하세요 .
• 이진 로깅이 활성화된 경우 서버는 기본 서버 ID로 시작할 수 있지만 시스템 변수를 설정하여 서버 ID를 명시적으로 지정하지 않으면 정보 메시지가 발행됩니다 server_id . 복제 토폴로지에서 사용되는 서버의 경우 각 서버에 대해 고유한 0이 아닌 서버 ID를 지정해야 합니다.
• 서버에서 GTID가 사용 중일 때 비정상 종료 후 서버를 다시 시작할 때 바이너리 로깅을 비활성화하면 일부 GTID가 손실되어 복제가 실패할 가능성이 있습니다. 정상적인 종료에서는 현재 바이너리 로그 파일의 GTID 세트가 테이블에 저장됩니다 mysql.gtid_executed. 이것이 발생하지 않은 비정상 종료 후에는 복구 중에 바이너리 로깅이 여전히 활성화되어 있는 경우 바이너리 로그 파일에서 GTID가 테이블에 추가됩니다. 서버 재시작 시 바이너리 로깅이 비활성화된 경우 서버는 바이너리 로그 파일에 액세스하여 GTID를 복구할 수 없으므로 복제를 시작할 수 없습니다. 정상적인 종료 후에는 바이너리 로깅을 안전하게 비활성화할 수 있습니다.
• MySQL 9.0에서는 옵션을 지정하든 지정하지 않든 기본적으로 바이너리 로깅이 활성화됩니다 --log-bin. 예외는 바이너리 로깅이 기본적으로 비활성화된 경우 또는 옵션으로 mysqld를 호출하여 수동으로 데이터 디렉토리를 초기화하는 경우입니다 . 이 경우 옵션을 지정하여 바이너리 로깅을 활성화할 수 있습니다 . 바이너리 로깅이 활성화된 경우 서버에서 바이너리 로깅 상태를 표시하는 시스템 변수가 ON으로 설정됩니다. --initialize--initialize-insecure--log-binlog_bin

옵션을 제공하지 않으면 --log-binMySQL은 binlog바이너리 로그 파일의 기본 기반 이름으로 사용합니다. 이전 릴리스와의 호환성을 위해 --log-bin문자열 없이 또는 빈 문자열로 옵션을 제공하면 기본 이름은 host_name-bin호스트 머신의 이름을 사용하여 기본적으로 로 설정됩니다.

--log-bin-index[=file_name]

binary log를 만들때 파일 이름에 index를 붙여서 생성하고자 할때 사용

Command-Line Format	--log-bin-index=file_name
System Variable	log_bin_index
Scope	Global
Dynamic	No
SET_VAR Hint Applies	No
Type	File Name 파일 이름

기본적으로 --log-bin 옵션과 .index 확장을 사용하여 binary log 파일에 지정된 값과 동일한 위치 및 기본 이름을 갖습니다. --log-bin을 지정하지 않으면 기본 binary log 인덱스 파일 이름은 binlog.index입니다. 문자열이나 빈 문자열이 없는 --log-bin 옵션을 지정하면 기본 binary log  파일 이름은 호스트 컴퓨터의 이름을 사용하여 host_name-bin.index입니다.

바이너리 로그의 형식과 관리에 대한 자세한 내용은 섹션 7.4.4, "바이너리 로그"를 참조하세요.

 

문장 선택 옵션.  다음 목록의 옵션은 바이너리 로그에 기록되는 문장과 복제 소스 서버에서 복제본으로 전송되는 문장에 영향을 미칩니다. 또한 소스에서 수신된 문장 중 실행하거나 무시해야 하는 문장을 제어하는 ​​복제본 옵션도 있습니다. 자세한 내용은 섹션 19.1.6.3, "복제본 서버 옵션 및 변수"를 참조하십시오 .

• --binlog-do-db=db_name이 옵션은 복제에 영향을 미치는 것과 유사한 방식으로 바이너리 로깅에 영향을 미칩니다 --replicate-do-db.문장 기반 로깅.  기본 데이터베이스(즉, 에서 선택한 데이터베이스 USE)가 있는 바이너리 로그에 해당 문장만 기록됩니다 db_name. 두 개 이상의 데이터베이스를 지정하려면 이 옵션을 각 데이터베이스에 대해 한 번씩 여러 번 사용합니다. 그러나 이렇게 하면 다른 데이터베이스(또는 데이터베이스 없음)가 선택된 동안 과 같은 데이터베이스 간 문장이 로깅되지 않습니다 .UPDATE some_db.some_table SET foo='bar'명령문 기반 로깅을 사용할 때 예상대로 작동하지 않는 사례는 다음과 같습니다. 서버가 시작되고 --binlog-do-db=sales다음 명령문을 실행하면 UPDATE명령문이 로깅되지 않습니다 .이 " 기본 데이터베이스만 확인 " 동작 의 주된 이유 는 명령문만으로는 복제해야 하는지 알기 어렵기 때문입니다(예: 여러 테이블 DELETE명령문을 사용하거나 여러 데이터베이스에서 작동하는 여러 테이블 UPDATE 명령문을 사용하는 경우). 또한 필요 없다면 모든 데이터베이스를 확인하는 것보다 기본 데이터베이스만 확인하는 것이 더 빠릅니다.

  USE sales;
  UPDATE prices.discounts SET percentage = percentage + 10;

  명령문이 발행될 sales때 기본 데이터베이스이기 때문에 기록됩니다. UPDATEUPDATE

  USE prices;
  UPDATE sales.february SET amount=amount+100;

  february데이터베이스 의 테이블 에 대한 변경 사항은 명령문 sales에 따라 기록됩니다 UPDATE. 이는 명령문이 발행되었는지 여부와 관계없이 발생합니다 USE. 그러나 행 기반 로깅 형식 및 를 사용하는 경우 --binlog-do-db=sales다음에 의해 수행된 변경 사항은 UPDATE 기록되지 않습니다.USE prices명령문을 로 변경 하더라도 USE sales해당 UPDATE명령문의 효과는 바이너리 로그에 기록되지 않습니다.

  USE db1;
  UPDATE db1.table1, db2.table2 SET db1.table1.col1 = 10, db2.table2.col2 = 20;

  문장 기반 로깅을 사용하는 경우 두 테이블의 업데이트가 바이너리 로그에 기록됩니다. 그러나 행 기반 형식을 사용하는 경우 에 대한 변경 사항만 table1로깅됩니다. table2는 다른 데이터베이스에 있으므로 에 의해 변경되지 않습니다 . 이제 문장 UPDATE대신 문장이 사용되었다고 가정합니다. USE db1USE db4이 경우, UPDATE 문장 기반 로깅을 사용할 때 문장은 바이너리 로그에 기록되지 않습니다. 그러나 행 기반 로깅을 사용할 때, 에 대한 변경 사항은 table1기록되지만, 해당 에 대한 변경 사항은 기록되지 않습니다. table2즉, 에 의해 명명된 데이터베이스의 테이블에 대한 변경 사항만 --binlog-do-db기록되고, 기본 데이터베이스의 선택은 이 동작에 영향을 미치지 않습니다.

• USE db4;
  UPDATE db1.table1, db2.table2 SET db1.table1.col1 = 10, db2.table2.col2 = 20;

• --binlog-do-db행 기반 로깅과 대조적으로 문장 기반 로깅을 처리하는 데 있어서 또 다른 중요한 차이점은 여러 데이터베이스를 참조하는 문장과 관련하여 발생합니다. 서버가 로 시작되고 --binlog-do-db=db1다음 문장이 실행된다고 가정합니다.

• USE prices;
  UPDATE prices.march SET amount=amount-25;

• 행 기반 로깅.  로깅은 database 로 제한됩니다 db_name. 에 속한 테이블에 대한 변경 사항만 db_name로깅됩니다. 기본 database 는 이에 영향을 미치지 않습니다. 서버가 로 시작되고 --binlog-do-db=sales행 기반 로깅이 적용되고 다음 명령문이 실행된다고 가정합니다.
• 옵션을 설정할 때 지정하지 않았는데도 주어진 데이터베이스가 복제되는 경우 자명하지 않을 수 있는 또 다른 사례가 발생합니다. 서버가 로 시작되면 설정 시 포함되지 않았 더라도 --binlog-do-db=sales다음 명령문이 기록됩니다 . UPDATEprices--binlog-do-db

• USE prices;
  UPDATE sales.january SET amount=amount+1000;

• 경고
  여러 데이터베이스를 지정하려면 이 옵션의 여러 인스턴스를 사용해야 합니다 . 데이터베이스 이름에는 쉼표가 포함될 수 있으므로 쉼표로 구분된 목록을 제공하면 목록이 단일 데이터베이스의 이름으로 처리됩니다.
• 이 옵션의 효과는 명령문 기반 또는 행 기반 로깅 형식이 사용 중인지에 따라 달라지며, --replicate-do-db명령문 기반 또는 행 기반 복제가 사용 중인지에 따라 효과가 달라지는 것과 같은 방식입니다. 주어진 명령문을 로깅하는 데 사용된 형식이 값에서 나타내는 형식과 반드시 ​​같지 않을 수 있다는 점에 유의해야 합니다 . 예를 들어 및 와 binlog_format같은 DDL 명령문은 적용되는 로깅 형식과 관계없이 항상 명령문으로 로깅되므로 명령문이 로깅되는지 여부를 결정하는 데 다음 명령문 기반 규칙이 항상 적용됩니다. CREATE TABLEALTER TABLE--binlog-do-db
• 명령줄 형식유형

  --binlog-do-db=name

  끈

• --binlog-ignore-db=db_name이 옵션은 복제에 영향을 미치는 것과 유사한 방식으로 바이너리 로깅에 영향을 미칩니다 --replicate-ignore-db.문장 기반 로깅.  서버에 기본 데이터베이스(즉, 에서 선택한 데이터베이스 USE)가 있는 곳에서는 어떤 문장도 로깅하지 말라고 지시합니다 db_name.행 기반 형식.  서버에 데이터베이스의 어떤 테이블에도 업데이트를 기록하지 말라고 지시합니다 db_name. 현재 데이터베이스는 효과가 없습니다.

  USE prices;
  UPDATE sales.january SET amount=amount+1000;

  이 경우 명령문은 기본 데이터베이스(명령문에 의해 결정됨)에만 적용되므로 기록 됩니다 UPDATE. 데이터베이스 가 명령문에 명시적으로 지정되었기 때문에 명령문 은 필터링되지 않았습니다. 그러나 행 기반 로깅을 사용하는 경우 명령문의 효과는 바이너리 로그에 기록되지 않으므로 테이블에 대한 변경 사항이 기록되지 않습니다. 이 경우 소스의 데이터베이스 복사본에서 테이블에 대한 모든 변경 사항 이 바이너리 로깅 목적으로 무시됩니다 .--binlog-ignore-dbUSEsalesUPDATEsales.january--binlog-ignore-db=salessales여러 데이터베이스 간의 업데이트를 사용하고 이러한 업데이트를 기록하지 않으려는 경우에는 이 옵션을 사용하면 안 됩니다.
• 무시할 데이터베이스를 두 개 이상 지정하려면 이 옵션을 각 데이터베이스에 대해 한 번씩 여러 번 사용합니다. 데이터베이스 이름에 쉼표가 포함될 수 있으므로 쉼표로 구분된 목록을 제공하면 목록이 단일 데이터베이스의 이름으로 처리됩니다.
• 문장 기반 로깅을 사용할 때 다음 예는 예상대로 작동하지 않습니다. 서버가 시작되고 --binlog-ignore-db=sales다음 문장을 발행한다고 가정합니다.
• 기본 데이터베이스가 없는 경우 --binlog-ignore-db옵션이 적용되지 않으며 이러한 명령문은 항상 기록됩니다. (버그 #11829838, 버그 #60188)
• 이 옵션의 효과는 명령문 기반 또는 행 기반 로깅 형식이 사용 중인지에 따라 달라지며, --replicate-ignore-db명령문 기반 또는 행 기반 복제가 사용 중인지에 따라 효과가 달라지는 것과 같은 방식입니다. 주어진 명령문을 로깅하는 데 사용된 형식이 값에서 나타내는 형식과 반드시 ​​같지 않을 수 있다는 점에 유의해야 합니다 . 예를 들어 및 와 binlog_format같은 DDL 명령문은 적용되는 로깅 형식과 관계없이 항상 명령문으로 로깅되므로 명령문이 로깅되는지 여부를 결정하는 데 다음 명령문 기반 규칙이 항상 적용됩니다. CREATE TABLEALTER TABLE--binlog-ignore-db
• 명령줄 형식유형

  --binlog-ignore-db=name

  끈

체크섬 옵션.  MySQL은 바이너리 로그 체크섬의 읽기 및 쓰기를 지원합니다. 이는 여기에 나열된 두 가지 옵션을 사용하여 활성화됩니다.

• --binlog-checksum={NONE|CRC32}이 옵션을 활성화하면 소스가 바이너리 로그에 기록된 이벤트에 대한 체크섬을 작성합니다. NONE비활성화하려면 로 설정하거나 체크섬을 생성하는 데 사용할 알고리즘의 이름을 설정합니다. 현재는 CRC32 체크섬만 지원되며 CRC32가 기본값입니다. 트랜잭션 내에서 이 옵션의 설정을 변경할 수 없습니다.
• 명령줄 형식유형기본값유효한 값

  --binlog-checksum=type

  끈

  CRC32

  NONE CRC32

복제본의 체크섬 읽기를 제어하려면(릴레이 로그에서) 해당 --replica-sql-verify-checksum 옵션을 사용합니다.

테스트 및 디버깅 옵션.  다음 바이너리 로그 옵션은 복제 테스트 및 디버깅에 사용됩니다. 일반적인 작업에는 사용할 수 없습니다.

• --max-binlog-dump-events=N이 옵션은 MySQL 테스트 모음에서 복제 테스트 및 디버깅을 위해 내부적으로 사용됩니다.
• 명령줄 형식유형기본값

  --max-binlog-dump-events=#

  정수

  0

• --sporadic-binlog-dump-fail이 옵션은 MySQL 테스트 모음에서 복제 테스트 및 디버깅을 위해 내부적으로 사용됩니다.
• 명령줄 형식유형기본값

  --sporadic-binlog-dump-fail[={OFF|ON}]

  부울

  OFF

참조

https://dev.mysql.com/doc/refman/9.0/en/binary-log.html

https://dev.mysql.com/doc/refman/9.0/en/replication-options-binary-log.html#replication-optvars-binlog