MySQL Binlog 상세 설명: 데이터베이스 신뢰성 향상을 위한 핵심 기술

MySQL Binlog에 대한 자세한 설명: 데이터베이스 신뢰성을 향상시키는 핵심 기술

2024-07-12

1. 소개

1.1 MySQL이란? 큰 상자 통나무?

MySQL Bin Log（바이너리 통나무, 바이너리 로그)는 데이터베이스에 수행된 모든 수정 사항을 기록하는 MySQL 데이터베이스의 로그 파일입니다.DML작업(예: INSERT, UPDATE, DELETE 등)을 수행하지만SELECT 제외기다리다DQL 읽기 전용 작업입니다. Bin Log는 MySQL이 복제, 복구 및 감사를 구현하는 데 중요한 도구입니다. 자세한 내용은 다음을 참조하세요.MySQL의 DDL, DML, DQL 및 DCL

1.2 Bin Log의 기능 및 응용 시나리오

Bin Log의 주요 기능에는 마스터-슬레이브 복제, 데이터 복구, 데이터 백업 및 데이터 구독이 포함됩니다.

애플리케이션 시나리오	개념	사례
마스터-슬레이브 복제	마스터 라이브러리는 binlog에 있는 업데이트 작업 기록을 슬레이브 라이브러리로 보내고, 슬레이브 라이브러리는 binlog를 읽고 SQL 문을 실행합니다.	전자상거래 플랫폼: 메인 데이터베이스는 주문 작업을 binlog에 기록하고 데이터베이스의 주문 데이터를 동기화하여 모든 노드의 일관성을 유지합니다.
데이터 복구	binlog 레코드를 사용하여 SQL 문을 역방향으로 실행하여 데이터 복구	금융 시스템: 관리자는 데이터 무결성을 보장하고 재정적 손실을 방지하기 위해 binlog를 구문 분석하여 실수로 삭제된 거래 기록을 복구합니다.
데이터 백업	증분 백업에 Binlog를 사용하여 백업 시간 및 공간 비용 절감	소셜 미디어 플랫폼: binlog 파일을 매일 정기적으로 백업하면 전체 데이터베이스를 완전히 백업하지 않고도 최신 상태로 빠르게 복원할 수 있습니다.
데이터 구독	binlog를 사용하여 데이터베이스 업데이트 작업을 실시간으로 모니터링하세요.	유통업체의 실시간 데이터 분석 : binlog를 파싱하여 판매기록 삽입 작업을 실시간으로 캡쳐하여 데이터 분석 플랫폼으로 전송하여 판매 동향 분석을 수행합니다.

이러한 구체적인 사례를 통해 실제 애플리케이션에서 MySQL binlog의 중요성과 다양성을 더욱 명확하게 확인할 수 있습니다. binlog 기능을 올바르게 사용하면 시스템의 신뢰성, 복구 기능 및 비즈니스 응답 속도를 크게 향상시킬 수 있습니다.

2. Bin Log의 기본 개념

2.1 Bin Log의 작동 원리

MySQL 서버가 Bin Log 기능을 활성화하면 데이터베이스에 대한 모든 수정 작업이 수행됩니다.이벤트 기록 Bin 로그 파일에. 이러한 이벤트는 실행 순서대로 저장되어 지속적인 작업 로그 시퀀스를 형성합니다. 데이터를 복원하거나 복사해야 할 때 이러한 이벤트를 재생하여 데이터베이스 상태를 다시 생성할 수 있습니다.

2.2 Bin Log의 세 가지 형식

MySQL Bin Log에는 세 가지 레코드 형식이 있습니다.
다음은 MySQL Bin Log의 세 가지 기록 형식을 테이블 형태로 정리한 것이다.

레코드 형식	설명하다	이점	결점
문장 기반 로깅(SBL)	실행된 SQL문 자체를 기록	로그 볼륨이 작아 간단한 SQL 작업에 적합합니다.	비결정적 함수(예: NOW())와 같은 일부 경우에는 데이터 일관성이 보장되지 않을 수 있습니다.
행 기반 로깅(RBL)	각 데이터 행의 구체적인 변경 사항을 기록합니다.	보다 정확하고 복잡한 작업 및 복사에 적합	큰 로그 볼륨, 큰 디스크 및 네트워크 오버헤드
혼합 로깅(ML)	특정 상황에 따라 문 모드와 행 모드 간을 전환합니다.	두 가지 장점을 모두 고려해	구현 및 관리가 상대적으로 복잡함

3. Bin Log 구성 및 관리

3.1 빈 로그 활성화

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14

Bin Log를 활성화하려면 MySQL 구성 파일(일반적으로my.cnf또는my.ini) 다음 구성을 수행합니다.

[mysqld]
log-bin=mysql-bin
server-id=1
1
2
3

구성을 수정한 후 mysql을 다시 시작합니다.구현하다SHOW VARIABLES LIKE 'log_bin'; 값 값이 ON입니다.

mysql> show variables like "%log_bin%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+---------------------------------+--------------------------------------------------------------------------+
| Variable_name                   | Value                                                                    |
+---------------------------------+--------------------------------------------------------------------------+
| log_bin                         | ON                                                                       |
| log_bin_basename                | C:UsershiszmMySQL5.7.26databinlog       |
| log_bin_index                   | C:UsershiszmMySQL5.7.26databinlog.index |
| log_bin_trust_function_creators | OFF                                                                      |
| log_bin_use_v1_row_events       | OFF                                                                      |
| sql_log_bin                     | ON                                                                       |
+---------------------------------+--------------------------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.2 Bin 로그 매개변수 구성

일반적인 Bin 로그 구성 매개변수는 다음과 같습니다.

log_bin: Bin 로그를 활성화합니다.
server_id: 복제에 사용되는 서버 고유 식별자입니다.
binlog_format: Bin Log의 형식(STATEMENT, ROW, MIXED)을 설정합니다.
expire_logs_days: Bin Log 파일의 자동 만료 및 삭제 일수를 설정합니다.
max_binlog_size: 단일 Bin 로그 파일의 최대 크기를 설정합니다.

3.3 Bin 로그 파일 관리

Bin 로그 파일 관리를 위한 일반적인 명령:

Bin Log 파일 목록 보기：
```
SHOW BINARY LOGS;
1
```
Bin 로그 파일 크기 보기：
```
SHOW MASTER STATUS;
1
```

오래된 Bin 로그 파일 삭제：

PURGE BINARY LOGS TO 'mysql-bin.000010';
1

또는:

PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
1

3.4 Bin Log 내용 보기


-- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB;  -- 使用InnoDB存储引擎

-- 插入数据，将日期设置为当前日期
insert into `simple_table` values(1, 1, '2024-07-07');
insert into `simple_table` values(2, 2, '2024-07-07');
insert into `simple_table` values(3, 3, '2024-07-07');
insert into `simple_table` values(4, 4, '2024-07-07');
insert into `simple_table` values(5, 5, '2024-07-07');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Bin Log의 내용을 보려면 mysqlbinlog 도구를 사용하십시오.

mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2411 |
+---------------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

직접 열면 깨집니다.

mysql> show binlog events in 'binlog.000001' from 0 limit 0,4G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

*************************** 1. row ***************************
   Log_name: binlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 765
       Info: use `d`; -- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB
4 rows in set (0.01 sec)

ERROR:
No query specified

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

3.5 mysqlbinlog 도구 사용하기

mysqlbinlog는 Bin 로그 파일을 구문 분석하는 데 사용되는 명령줄 도구입니다. 일반적인 옵션은 다음과 같습니다.

--start-datetime: 파싱이 시작되는 시간을 지정합니다.
--stop-datetime: 파싱이 종료되는 시간을 지정합니다.
--start-position: 파싱이 시작되는 위치를 지정합니다.
--stop-position: 파싱이 끝나는 위치를 지정합니다.

예를 들어 특정 기간의 Bin 로그를 보려면 다음을 수행하세요.

mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001
1

3.6 Bin Log 구문 분석 및 재생

데이터 복구를 위해 Bin Log 이벤트를 MySQL 서버로 재생할 수 있습니다.

mysqlbin logbinlog.000001 | mysql -u root -p
1

4. Bin Log 복제 적용

4.1 마스터-슬레이브 복제 원리

MySQL 마스터-슬레이브 복제의 기본 원칙은 마스터 데이터베이스가 Bin Log를 기록하고, 슬레이브 데이터베이스가 이러한 로그를 읽고 재생하여 데이터 동기화를 달성하는 것입니다. 구체적인 단계는 다음과 같습니다:

기본 라이브러리는 쓰기 작업을 수행하고 이러한 작업을 Bin 로그에 기록합니다.
슬레이브 라이브러리는 메인 라이브러리에 접속하여 Bin Log를 읽어 자신의 데이터에 적용합니다.

4.2 마스터-슬레이브 복제 구성

기본 라이브러리 구성：

[mysqld]
log-bin=mysql-bin
server-id=1
1
2
3

슬레이브 라이브러리 구성：

[mysqld]
server-id=2
relay-log=relay-bin
1
2
3

기본 데이터베이스에 복제 사용자 생성：

CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
1
2

슬레이브 데이터베이스에서 복제 구성：

CHANGE MASTER TO
    MASTER_HOST='主库IP',
    MASTER_USER='replica',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='binlog.000001',
    MASTER_LOG_POS=0;
START SLAVE;
1
2
3
4
5
6
7

복제 상태 확인：
```
SHOW SLAVE STATUSG
1
```

4.3 복제 모니터링 및 관리

복제 상태는 다음 명령으로 모니터링할 수 있습니다.

SHOW SLAVE STATUSG
1

일반적인 상태 필드 설명:

Slave_IO_Running: IO 스레드 상태.
Slave_SQL_Running:SQL 스레드 상태.
Seconds_Behind_Master: 슬레이브 라이브러리가 마스터 라이브러리보다 지연되는 시간입니다.

4.4 증분 백업

증분 백업은 마지막 전체 백업 또는 마지막 증분 백업 이후의 모든 변경 사항을 백업하는 것을 의미합니다. Bin Log를 사용하여 증분 백업을 수행할 수 있습니다.

전체 백업 수행：

mysqldump --all-databases --master-data=2 > full_backup.sql
1

현재 Bin Log 위치를 기록합니다.：

전체 백업 파일에서 다음 줄을 찾으세요.
```
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
1
```

백업 저장소 로그：

mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
1

4.5 Bin Log 기반 복구

전체 백업 복원：
```
mysql < full_backup.sql
1
```
증분 백업 적용：
```
mysql < incremental_backup.sql
1
```

5. 마지막에 쓰세요

MySQL Bin Log 데이터 복구, 복제 및 감사와 같은 시나리오에서 널리 사용되는 강력한 도구입니다.올바르게 구성하고 사용함으로써Bin Log 이는 MySQL 데이터베이스의 안정성과 가용성을 크게 향상시킬 수 있습니다. 실제 애플리케이션에서는 데이터베이스 관리자와 개발자가 Bin Log의 사용 기술과 최적화 방법을 익히는 것이 중요합니다.

기술나눔