MySQL 바이너리 로깅

SHOW VARIABLES LIKE 'log_bin';
-- log_bin  ON

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
server_id = 1          -- required for replication; good practice to set always
binlog_expire_logs_seconds = 604800  -- auto-purge logs older than 7 days
max_binlog_size = 100M -- rotate to a new file when this size is reached

SHOW VARIABLES LIKE 'binlog_format';
SET SESSION binlog_format = 'ROW';  -- change for current session

SHOW BINARY LOGS;

SHOW BINARY LOG STATUS;

SHOW BINLOG EVENTS IN 'mysql-bin.000003' LIMIT 20;
SHOW BINLOG EVENTS IN 'mysql-bin.000003' FROM 154 LIMIT 10;

# Read a local binlog file
mysqlbinlog /var/log/mysql/mysql-bin.000003

# Filter by time range
mysqlbinlog --start-datetime="2024-06-15 10:00:00" \
            --stop-datetime="2024-06-15 12:00:00" \
            /var/log/mysql/mysql-bin.000003

# Decode ROW format events (shows actual data changes)
mysqlbinlog --verbose /var/log/mysql/mysql-bin.000003

# Read directly from a running server
mysqlbinlog --read-from-remote-server \
            --host=127.0.0.1 --user=root --password \
            mysql-bin.000003

mysqldump --single-transaction --source-data=2 --all-databases \
  -u root -p > backup.sql

mysql -u root -p < backup.sql

head -50 backup.sql | grep "SOURCE_LOG_FILE"
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000003', SOURCE_LOG_POS=154;

mysqlbinlog --start-position=154 \
            --stop-datetime="2024-06-15 11:59:00" \
            mysql-bin.000003 mysql-bin.000004 | mysql -u root -p

-- Purge all logs before a specific log file
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- Purge all logs older than a date
PURGE BINARY LOGS BEFORE '2024-06-01 00:00:00';

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 7 days in seconds

SET SESSION sql_log_bin = 0;
-- ... operations that won't be logged ...
SET SESSION sql_log_bin = 1;

SHOW BINARY LOGS;
-- Sum the File_size column