[MYSQL] BACKUP PERIOD USE log bin - fourslickz/notes GitHub Wiki

1. Enable Binary Logging (If Not Already Enabled)

SHOW VARIABLES LIKE 'log_bin';

my.cnf

[mysqld]
log-bin=mysql-bin
binlog-format=ROW
expire_logs_days=7  # Automatically delete logs older than 7 days

2. Identify the Binlog Files and Positions

SHOW BINARY LOGS;
SHOW MASTER STATUS;

3. Extract Logs for a Specific Date Range

mysqlbinlog --start-datetime="2024-02-01 00:00:00" \
            --stop-datetime="2024-02-10 23:59:59" \
            /var/lib/mysql/mysql-bin.000123 > filtered_backup.sql

restore data

mysql -u root -p your_database < filtered_backup.sql

4. Automate Binlog Backup by Period

#!/bin/bash
START_DATE="2024-02-01 00:00:00"
END_DATE="2024-02-10 23:59:59"
BINLOG_DIR="/var/lib/mysql"

# Get the latest binlog file
BINLOG_FILE=$(mysql -u root -p -e "SHOW MASTER STATUS\G" | awk '/File:/ {print $2}')

# Extract logs for the date range
mysqlbinlog --start-datetime="$START_DATE" \
            --stop-datetime="$END_DATE" \
            "$BINLOG_DIR/$BINLOG_FILE" > /backup/binlog_backup.sql

schedule backup by cron

0 0 * * * /path/to/binlog_backup.sh