내 연락처 정보
우편메소피아@프로톤메일.com
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
MySQL Bin Log
(바이너리 통나무, 바이너리 로그)는 데이터베이스에 수행된 모든 수정 사항을 기록하는 MySQL 데이터베이스의 로그 파일입니다.DML
작업(예: INSERT, UPDATE, DELETE 등)을 수행하지만SELECT 제외기다리다DQL
읽기 전용 작업입니다. Bin Log는 MySQL이 복제, 복구 및 감사를 구현하는 데 중요한 도구입니다. 자세한 내용은 다음을 참조하세요.MySQL의 DDL, DML, DQL 및 DCL
Bin Log의 주요 기능에는 마스터-슬레이브 복제, 데이터 복구, 데이터 백업 및 데이터 구독이 포함됩니다.
애플리케이션 시나리오 | 개념 | 사례 |
---|---|---|
마스터-슬레이브 복제 | 마스터 라이브러리는 binlog에 있는 업데이트 작업 기록을 슬레이브 라이브러리로 보내고, 슬레이브 라이브러리는 binlog를 읽고 SQL 문을 실행합니다. | 전자상거래 플랫폼: 메인 데이터베이스는 주문 작업을 binlog에 기록하고 데이터베이스의 주문 데이터를 동기화하여 모든 노드의 일관성을 유지합니다. |
데이터 복구 | binlog 레코드를 사용하여 SQL 문을 역방향으로 실행하여 데이터 복구 | 금융 시스템: 관리자는 데이터 무결성을 보장하고 재정적 손실을 방지하기 위해 binlog를 구문 분석하여 실수로 삭제된 거래 기록을 복구합니다. |
데이터 백업 | 증분 백업에 Binlog를 사용하여 백업 시간 및 공간 비용 절감 | 소셜 미디어 플랫폼: binlog 파일을 매일 정기적으로 백업하면 전체 데이터베이스를 완전히 백업하지 않고도 최신 상태로 빠르게 복원할 수 있습니다. |
데이터 구독 | binlog를 사용하여 데이터베이스 업데이트 작업을 실시간으로 모니터링하세요. | 유통업체의 실시간 데이터 분석 : binlog를 파싱하여 판매기록 삽입 작업을 실시간으로 캡쳐하여 데이터 분석 플랫폼으로 전송하여 판매 동향 분석을 수행합니다. |
이러한 구체적인 사례를 통해 실제 애플리케이션에서 MySQL binlog의 중요성과 다양성을 더욱 명확하게 확인할 수 있습니다. binlog 기능을 올바르게 사용하면 시스템의 신뢰성, 복구 기능 및 비즈니스 응답 속도를 크게 향상시킬 수 있습니다.
MySQL 서버가 Bin Log 기능을 활성화하면 데이터베이스에 대한 모든 수정 작업이 수행됩니다.이벤트 기록 Bin 로그 파일에. 이러한 이벤트는 실행 순서대로 저장되어 지속적인 작업 로그 시퀀스를 형성합니다. 데이터를 복원하거나 복사해야 할 때 이러한 이벤트를 재생하여 데이터베이스 상태를 다시 생성할 수 있습니다.
MySQL Bin Log에는 세 가지 레코드 형식이 있습니다.
다음은 MySQL Bin Log의 세 가지 기록 형식을 테이블 형태로 정리한 것이다.
레코드 형식 | 설명하다 | 이점 | 결점 |
---|---|---|---|
문장 기반 로깅(SBL) | 실행된 SQL문 자체를 기록 | 로그 볼륨이 작아 간단한 SQL 작업에 적합합니다. | 비결정적 함수(예: NOW())와 같은 일부 경우에는 데이터 일관성이 보장되지 않을 수 있습니다. |
행 기반 로깅(RBL) | 각 데이터 행의 구체적인 변경 사항을 기록합니다. | 보다 정확하고 복잡한 작업 및 복사에 적합 | 큰 로그 볼륨, 큰 디스크 및 네트워크 오버헤드 |
혼합 로깅(ML) | 특정 상황에 따라 문 모드와 행 모드 간을 전환합니다. | 두 가지 장점을 모두 고려해 | 구현 및 관리가 상대적으로 복잡함 |
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)
Bin Log를 활성화하려면 MySQL 구성 파일(일반적으로my.cnf
또는my.ini
) 다음 구성을 수행합니다.
[mysqld]
log-bin=mysql-bin
server-id=1
구성을 수정한 후 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)
일반적인 Bin 로그 구성 매개변수는 다음과 같습니다.
log_bin
: Bin 로그를 활성화합니다.server_id
: 복제에 사용되는 서버 고유 식별자입니다.binlog_format
: Bin Log의 형식(STATEMENT, ROW, MIXED)을 설정합니다.expire_logs_days
: Bin Log 파일의 자동 만료 및 삭제 일수를 설정합니다.max_binlog_size
: 단일 Bin 로그 파일의 최대 크기를 설정합니다.Bin 로그 파일 관리를 위한 일반적인 명령:
Bin Log 파일 목록 보기:
SHOW BINARY LOGS;
Bin 로그 파일 크기 보기:
SHOW MASTER STATUS;
오래된 Bin 로그 파일 삭제:
PURGE BINARY LOGS TO 'mysql-bin.000010';
또는:
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
-- 创建一个名为 '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');
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)
직접 열면 깨집니다.
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
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
데이터 복구를 위해 Bin Log 이벤트를 MySQL 서버로 재생할 수 있습니다.
mysqlbin logbinlog.000001 | mysql -u root -p
MySQL 마스터-슬레이브 복제의 기본 원칙은 마스터 데이터베이스가 Bin Log를 기록하고, 슬레이브 데이터베이스가 이러한 로그를 읽고 재생하여 데이터 동기화를 달성하는 것입니다. 구체적인 단계는 다음과 같습니다:
기본 라이브러리 구성:
[mysqld]
log-bin=mysql-bin
server-id=1
슬레이브 라이브러리 구성:
[mysqld]
server-id=2
relay-log=relay-bin
기본 데이터베이스에 복제 사용자 생성:
CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
슬레이브 데이터베이스에서 복제 구성:
CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='replica',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=0;
START SLAVE;
복제 상태 확인:
SHOW SLAVE STATUSG
복제 상태는 다음 명령으로 모니터링할 수 있습니다.
SHOW SLAVE STATUSG
일반적인 상태 필드 설명:
Slave_IO_Running
: IO 스레드 상태.Slave_SQL_Running
:SQL 스레드 상태.Seconds_Behind_Master
: 슬레이브 라이브러리가 마스터 라이브러리보다 지연되는 시간입니다.증분 백업은 마지막 전체 백업 또는 마지막 증분 백업 이후의 모든 변경 사항을 백업하는 것을 의미합니다. Bin Log를 사용하여 증분 백업을 수행할 수 있습니다.
전체 백업 수행:
mysqldump --all-databases --master-data=2 > full_backup.sql
현재 Bin Log 위치를 기록합니다.:
전체 백업 파일에서 다음 줄을 찾으세요.
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
백업 저장소 로그:
mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
전체 백업 복원:
mysql < full_backup.sql
증분 백업 적용:
mysql < incremental_backup.sql
MySQL Bin Log
데이터 복구, 복제 및 감사와 같은 시나리오에서 널리 사용되는 강력한 도구입니다.올바르게 구성하고 사용함으로써Bin Log
이는 MySQL 데이터베이스의 안정성과 가용성을 크게 향상시킬 수 있습니다. 실제 애플리케이션에서는 데이터베이스 관리자와 개발자가 Bin Log의 사용 기술과 최적화 방법을 익히는 것이 중요합니다.