Python MySQL Replication ? python으로 MySQL binary log 이벤트를 가져올 수 있는 라이브러리
MySQL 변수값 세팅
MySQL 8.0.14 이상 버전 : 글로벌 변수 binlog_row_metadata='FULL' 와 binlog_row_image='FULL'로 설정
- binlog_row_metadata : 행 기반 로깅을 사용할 때 바이너리 로그에 추가되는 테이블 메타데이터의 양을 구성
- MINIMAL (default) : SIGNED flags, column character set and geometry types와 관련된 메타데이터만 로깅
- FULL : column name, 열 이름 이나 문자열 값, ENUM or SET string values, PRIMARY KEY 정보 등과 같은 테이블에 대한 전체 메타데이터가 로깅
- binlog_row_image : 행 이미지가 바이너리 로그에 기록되는 방식을 결정
- full (default) : 모든 열을 기록
- minimal : 변경된 열과 행을 식별하는 데 필요한 열만 기록
- noblob : 불필요한 BLOB 및 TEXT 열을 제외한 모든 열을 기록
먼저, 변수 값 확인
mysql> show variables like 'binlog_row_image';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| binlog_row_metadata | MINIMAL |
+---------------------+---------+
1 row in set (0.01 sec)
mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| binlog_row_image | FULL |
+------------------+-------+
1 row in set (0.00 sec)
'FULL' 이 아닌 변수는 세팅을 다시 해준다
mysql> SET GLOBAL binlog_row_metadata = 'FULL';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL binlog_row_metadata = 'FULL';
Query OK, 0 rows affected (0.00 sec)
설치
pip로 설치 가능
pip install mysql-replication
사용 예제
화면에서 binlog 출력 보기
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication import row_event
import configparser
import pymysqlreplication
parser = configparser.ConfigParser()
parser.read("pipeline.conf")
hostname = parser.get("mysql_config", "hostname")
port = parser.get("mysql_config", "port")
username = parser.get("mysql_config", "username")
password = parser.get("mysql_config", "password")
mysql_settings = {
"host" : hostname,
"port" : int(port),
"user" : username,
"passwd" : password
}
b_stream = BinLogStreamReader(
connection_settings= mysql_settings,
server_id=10,
only_events=[row_event.DeleteRowsEvent,
row_event.WriteRowsEvent,
row_event.UpdateRowsEvent]
)
for event in b_stream:
event.dump()
b_stream.close()
- BinlogStreamerReader() : Connect to replication stream and read event
- connection_setting : Connection settings
- server_id : a unique server ID for each replication server, in the range from 1 to 232 − 1
- only_events: Array of allowed events
- row event : This events are send by MySQL when data are modified
- row_event.DeleteRowsEvent : This event is trigger when a row in the database is removed
- row_event.WriteRowsEvent : This event is triggered when a row in database is added
- row_event.UpdateRowsEvent : This event is triggered when a row in the database is changed
- row event : This events are send by MySQL when data are modified
=== WriteRowsEvent ===
Date: 2025-03-20T11:21:47
Log position: 1856
Event size: 29
Read bytes: 12
Table: pipeline.orders
Affected columns: 3
Changed rows: 1
Column Name Information Flag: False
Values:
--
* OrderID : 1
* OrderStatus : Shipped
* LastUpdated : 2020-06-09 02:50:00
=== WriteRowsEvent ===
Date: 2025-03-20T11:21:47
Log position: 2171
Event size: 29
Read bytes: 12
Table: pipeline.orders
Affected columns: 3
Changed rows: 1
Column Name Information Flag: False
Values:
--
* OrderID : 3
* OrderStatus : Shipped
* LastUpdated : 2020-07-11 17:00:00
출력이 잘 되는 것을 확인할 수 있다~~ !
* Binary Logging Options and Variables
https://dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html
* Replication and Binary Logging Options and Variables
https://dev.mysql.com/doc/refman/8.4/en/replication-options.html
* python-mysql-replication
https://github.com/julien-duponchelle/python-mysql-replication/tree/main
* Python MySQL Replication’s documentation
https://python-mysql-replication.readthedocs.io/en/latest/index.html
'! > python' 카테고리의 다른 글
| configparser 사용하기 (0) | 2025.03.21 |
|---|---|
| pymysql 사용하기 (0) | 2025.03.20 |
| python 문법 가이드 (0) | 2025.03.14 |
| python test (2) | 2024.09.26 |
| [Error] ValueError: setting an array element with a sequence (0) | 2023.08.08 |
