Scheduling Binlog Rotation with systemd Timers
A cron job that flushes binary logs or wakes the archiver looks fine until the host is down at the scheduled minute and the run is simply skipped — no catch-up, no record, and a widening gap between the last rotation and the archive. Cron has no memory of missed runs, no structured logging, and no execution tracking, which are exactly the properties a recovery-critical cadence needs. This page replaces cron with systemd timers for binlog rotation and archiving cadence: a persistent timer that catches up a missed run after downtime, randomized delay to keep a fleet from stampeding object storage at the same second, and journal-integrated logging that ties every rotation to its outcome. The result is a schedule you can prove ran, not one you hope ran.
Context & Prerequisites
This hardens the scheduling side of Rotation Scheduling & Cron Automation for MySQL Binary Log Archiving and PITR; read that for the rotation-versus-archiving-cadence model. You need MySQL 8.0.22+, a Linux host with systemd, Python 3.10+ for the archiver invoked by the timer, and the archiving daemon from Automated Binlog Archiving to Object Storage. Timers pair a .timer unit (when) with a .service unit (what).
Step-by-Step Implementation
1. Define the service unit that does the work
The service runs one rotation-and-archive cycle, oneshot, so the timer controls cadence.
# /etc/systemd/system/binlog-archive.service
[Unit]
Description=Flush and archive closed MySQL binlog segments
After=mysql.service
[Service]
Type=oneshot
ExecStart=/usr/bin/mysql -e "FLUSH BINARY LOGS"
ExecStart=/opt/binlog-archiver/.venv/bin/python -m archiver.run
User=binlog-archiverPITR relevance: FLUSH BINARY LOGS closes the active segment so it becomes archivable; pairing it with the archiver run in one service keeps rotation and capture atomic per cycle.
2. Define the timer with persistence and jitter
Persistent=true catches up a missed run after downtime; RandomizedDelaySec spreads a fleet’s uploads.
# /etc/systemd/system/binlog-archive.timer
[Unit]
Description=Run binlog archive cycle every 2 minutes
[Timer]
OnCalendar=*:0/2
Persistent=true
RandomizedDelaySec=20
AccuracySec=1s
Unit=binlog-archive.service
[Install]
WantedBy=timers.targetPITR relevance: Persistent=true means a host that was down still archives the segments that accumulated during the outage on next boot, before retention purges them — directly protecting the recovery window.
3. Enable and verify the timer
Enable at boot and confirm the next elapse and last result.
sudo systemctl enable --now binlog-archive.timer
systemctl list-timers binlog-archive.timer # NEXT, LAST, PASSEDPITR relevance: list-timers gives you the execution-tracking cron lacks — you can see the last run happened and when the next is due, turning “did archiving run?” into an answerable question.
4. Align the cadence inside the retention window
Set the interval to a small fraction of binlog_expire_logs_seconds so a couple of missed cycles still leave margin before local purge.
# Python 3.10+ — sanity-check cadence against retention
def cadence_ok(interval_s: int, expire_s: int, missed_tolerance: int = 3) -> bool:
return interval_s * missed_tolerance < expire_sPITR relevance: a cadence that is a small fraction of the retention window means even a few consecutive missed runs cannot let the server purge an un-archived segment.
Configuration Snippet & Reference Table
| Directive | Value | Why it matters for PITR |
|---|---|---|
Persistent=true | timer | Catches up runs missed during downtime. |
RandomizedDelaySec | 10–30s | Prevents a fleet stampeding object storage. |
OnCalendar | fraction of retention | Leaves margin for missed cycles. |
Type=oneshot | service | One clean cycle per trigger. |
After=mysql.service | service | Waits for the database to be up. |
journalctl -u | logging | Ties each rotation to its outcome. |
Verification Checklist
Gotchas & Version-Specific Caveats
Cron silently skips missed runs. This is the core reason to switch: a cron entry has no Persistent equivalent, so a run missed during downtime is gone. A timer catches it up, which is what protects the archive after a reboot.
A whole fleet on the same OnCalendar stampedes. Without RandomizedDelaySec, every host uploads at the same instant and throttles object storage — mitigations for which are in Handling S3 Throttling During High-Throughput Binlog Archiving.
FLUSH BINARY LOGS needs privilege. The service user needs RELOAD (or the flush right); scope it minimally per Security & Access Frameworks.
Timer accuracy defaults are coarse. systemd batches timers for power efficiency; set AccuracySec=1s when you need the cadence to be tight rather than opportunistic.
Frequently Asked Questions
Why are systemd timers better than cron for archiving?
Three properties cron lacks: persistence (a missed run during downtime is caught up rather than skipped), execution tracking (list-timers shows the last and next run so you can prove archiving happened), and journal integration (each run’s output and exit status are queryable with journalctl). For a cadence that guards a recovery window, “we think it ran” is not good enough, and timers make it “we can see it ran.”
Should the timer flush binlogs or just wake the archiver?
Both, in one service. FLUSH BINARY LOGS closes the active segment so it becomes archivable, and the archiver run then captures every closed segment. Pairing them means each timer trigger produces a freshly-closed segment and immediately archives it, keeping the newest recoverable point close to now. Just be sure the flush cadence does not create segments faster than the archiver can keep pace, or lag builds.
Related
- Rotation Scheduling & Cron Automation — the rotation and cadence model this hardens.
- Configuring Logrotate for MySQL Binary Logs Safely — the safe way to age segments the timer archives.
- Handling S3 Throttling During High-Throughput Binlog Archiving — why fleet jitter matters.