What Are MySQL Binlogs?
Binary logs, or simply binlogs, are a special type of log file in MySQL that records every data-changing operation performed on the database. whether it’s an INSERT, UPDATE, DELETE, or DDL changes.
They act like a complete transaction history of your database, which makes them extremely useful for:
- Replication
- Point-in-time recovery
- Auditing
- Troubleshooting
In Amazon RDS, enabling binlogs allows you to capture these changes for multiple purposes like compliance, disaster recovery, and operational visibility.
Why They Matter
Binlogs are crucial because they provide a complete and chronological record of all data changes in your database. This level of detail helps in several important ways:
Auditing and Compliance: You can track exactly who changed what and when, which is essential for security audits and regulatory requirements.
Troubleshooting: When something goes wrong, binlogs let you identify and understand the changes that led to the issue.
Point-in-Time Recovery: In case of accidental data loss or corruption, binlogs enable you to restore your database to a specific moment in time, minimizing downtime and data loss.
Replication: Binlogs are the backbone of MySQL replication, allowing you to maintain standby or read-only copies of your database for load balancing or disaster recovery.
By archiving and analyzing binlogs, you gain greater control and visibility over your data, making them an indispensable tool for any production environment.
How to Use Them
Using MySQL binlogs effectively starts with enabling binary logging on your Amazon RDS instance. Once enabled, these logs are automatically generated and can be downloaded or streamed for further analysis. To keep a long-term record and enable detailed auditing, you can archive the binlogs by exporting them to Amazon S3. From there, you can process the logs using tools like mysqlbinlog to read and interpret the changes, or integrate them into auditing and monitoring systems. Additionally, these archived binlogs can be used for point-in-time recovery or to replicate data to other database instances. Automating this process with scheduled scripts or AWS Lambda functions ensures your logs are safely stored and easily accessible when needed.
Now, let’s get practical! Follow these step-by-step instructions to set up and manage MySQL binlogs on Amazon RDS and archive them to S3.
First: Understand RDS MySQL limitation
-
You cannot directly control MySQL server my.cnf on RDS.
-
Binlog can be enabled via RDS Parameter Groups.
-
Saving binlog directly to S3 is not natively supported out of box but you can extract binlogs periodically and upload them to S3.
Second Enable Binary Logging on RDS MySQL
-
Go to RDS Console > your DB instance > Configuration.
-
Check current parameter group attached.
- Modify or create a new parameter group for MySQL (make sure it matches your version).
– In the parameter group, set:
binlog_format value ROW (or MIXED or STATEMENT depending on your use case)
- binlog_row_image value is FULL (or minimal if you want smaller binlogs)
- log_bin value is 1 (for MySQL 5.7; for MySQL 8+ RDS manages log_bin automatically if binary logging is enabled)
Apply parameter group (may require reboot).
– Verify binlog is enabled
Login to MySQL:
mysql -h <rds-endpoint> -u <user> -p
SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;
** Setup automated export to S3 **
Unfortunately:
RDS doesn’t automatically export binlogs to S3.
We need to implement binlog extraction.
– Use mysqlbinlog tool from external EC2 or local machine
Install necessary tools:
For Ubuntu:
sudo apt update
sudo apt install mysql-client awscli -y
Create directory structure
sudo mkdir -p /opt/binlog_sync/binlogs
sudo chown -R ec2-user:ec2-user /opt/binlog_sync
(Use your actual user instead of ec2-user if different)
Create binlog puller script
Create file:
sudo nano /opt/binlog_sync/binlog_fetch.sh
Paste the following (modify accordingly):
#!/bin/bash
# Config
RDS_HOST="your-rds-endpoint"
MYSQL_USER="repl"
MYSQL_PASS="your-replication-password"
BINLOG_DIR="/opt/binlog_sync/binlogs"
S3_BUCKET="s3://your-bucket-name/binlogs"
cd $BINLOG_DIR
# Get the latest binlog file from RDS
LATEST_FILE=$(ls -1 | sort | tail -n 1)
if [ -z "$LATEST_FILE" ]; then
START_BINLOG="mysql-bin.000001"
else
START_BINLOG=$LATEST_FILE
fi
# Fetch binlogs using mysqlbinlog
mysqlbinlog
--read-from-remote-server
--host=$RDS_HOST
--user=$MYSQL_USER
--password=$MYSQL_PASS
--raw
--stop-never
--result-file=$BINLOG_DIR/
$START_BINLOG &
# Background upload to S3 every 60 seconds
while true; do
aws s3 sync $BINLOG_DIR $S3_BUCKET
sleep 60
done
IMPORTANT:
- Replace all placeholders (your-rds-endpoint, etc.)
- This will continuously stream binlogs and sync every 60 seconds to S3.
Make it executable:
sudo chmod +x /opt/binlog_sync/binlog_fetch.sh
– Create systemd service
Create:
sudo nano /etc/systemd/system/binlog-sync.service
Paste:
[Unit]
Description=MySQL Binlog Sync to S3
After=network.target
[Service]
Type=simple
User=ec2-user
ExecStart=/opt/binlog_sync/binlog_fetch.sh
Restart=always
RestartSec=10
[Install]
WantedBy=multi-user.target
– Start and enable service
sudo systemctl daemon-reload
sudo systemctl enable binlog-sync.service
sudo systemctl start binlog-sync.service
Check status:
sudo systemctl status binlog-sync.service
- Verify
Check files in /opt/binlog_sync/binlogs/
Check files in S3: aws s3 ls s3://your-bucket-name/binlogs/
Conclusion:
In this guide, we successfully enabled MySQL binary logging on AWS RDS and built a fully automated solution to continuously stream binlogs to Amazon S3 using mysqlbinlog, systemd, and AWS CLI. This approach allows you to securely archive binary logs for long-term retention, point-in-time recovery, and advanced auditing all while keeping full control and automation within your own AWS environment.
Happy Learning