All backups are managed from Container 523 (backup-mgr) at 192.168.9.220 on Proxmox bm04. Cronicle job scheduler runs all tasks on schedule. All backups go to Wasabi S3 Sydney (ap-southeast-2).
| Component |
Location |
Schedule |
Destination |
| MySQL Full Dump |
192.168.8.241 (replica) |
Sunday 02:00 UTC |
smartb-db-backups/mysql/ |
| MySQL Incremental (binlogs) |
192.168.8.241 (replica) |
Mon–Sat 02:00 UTC |
smartb-db-backups/mysql-binlogs/ (restic) |
| ClickHouse Backup |
192.168.9.116 |
Daily 03:00 UTC |
smartb-db-backups/clickhouse/ |
| PBS → Wasabi Sync |
192.168.9.51 (PBS) |
Daily 04:00 UTC |
smartb-proxmox-backup/ |
| Weekly Verify |
Container 523 |
Sunday 06:00 UTC |
— |
- IP: 192.168.9.220 (Proxmox bm04, LXC 523)
- OS: Debian 12 unprivileged LXC
- Tools: restic, rclone, mc (MinIO client), mysqlbinlog, mysqldump, pigz, sshpass, Node.js 22, Cronicle
- Cronicle UI: http://192.168.9.220:3012 — login:
manojm / manojM@1289
- Public URL: https://backup.smartb.com.au (proxied via OpenResty)
- Scripts:
/opt/db-backup/
- Logs:
/var/log/db-backup/
- Env / credentials:
/opt/db-backup/.env
ALL MySQL operations run against 192.168.8.241 (smartb-db-01 replica) — never the primary. This replica has log_replica_updates=ON. If primary 8.15 fails over and 8.241 is promoted, the binlog chain continues uninterrupted.
- Script:
/opt/db-backup/mysql/backup.sh
- Databases:
sb, fantasy_sport, c4_api, c4_api_1, sb_wp, unibee, superset, c4_wp, email_tracking
sb skips large/disposable tables: ReportTemps, punters_*, RaceOddFlucs, RaceOdds, MarketRelations, racing_data_last_5_years, FailedMails, ApiRaceIdentifiers, email_sends- Flow:
mysqldump | pigz -3 → mc cp → wasabi/smartb-db-backups/mysql/YYYY-MM-DD/
- Also copies to
mysql/latest/ for quick restore
- Retention: last 3 dated folders
- Records current binlog position to
/opt/db-backup/mysql/.last_binlog
- Streams binary logs from 8.241 via
mysqlbinlog --read-from-remote-server
- Flow: binlogs →
restic backup → wasabi/smartb-db-backups/mysql-binlogs/
- Checkpoint:
/opt/db-backup/mysql/.last_binlog tracks last backed-up binlog
- Retention: last 21 restic snapshots
- Schedule: Daily 03:00 UTC
- Host: 192.168.9.116
- Trigger (container 523):
/opt/db-backup/clickhouse/backup-trigger.sh — SSHes to 9.116 and runs backup via sudo
- Script (on 9.116):
/opt/db-backup/clickhouse/backup.sh
- Tool: clickhouse-backup with S3 remote, incremental via --diff-from-remote
- Config (on 9.116):
/opt/db-backup/clickhouse/config.yml
- Destination: wasabi: smartb-db-backups/clickhouse/
- Database: edge_logs (~14 GiB, 589+ objects on Wasabi)
- Schedule: Daily 04:00 UTC
- PBS host: 192.168.9.51 (pbs01)
- Trigger (container 523):
/opt/db-backup/pbs/sync-to-wasabi.sh — SSHes to PBS and runs sync
- Sync script (on PBS):
/usr/local/bin/pbs-wasabi-sync.sh
- Destination: wasabi bucket: smartb-proxmox-backup
| Bucket |
Used For |
Region |
| smartb-db-backups |
MySQL dumps + binlogs, ClickHouse |
ap-southeast-2 (Sydney) |
| smartb-proxmox-backup |
Proxmox Backup Server (VM backups) |
ap-southeast-2 (Sydney) |
| Job Name |
Schedule |
Script |
| MySQL Backup |
Daily 02:00 UTC |
/opt/db-backup/mysql/backup.sh |
| ClickHouse Backup |
Daily 03:00 UTC |
/opt/db-backup/clickhouse/backup-trigger.sh |
| PBS → Wasabi Sync |
Daily 04:00 UTC |
/opt/db-backup/pbs/sync-to-wasabi.sh |
| Weekly Backup Verify |
Sunday 06:00 UTC |
/opt/db-backup/verify.sh |
# Download from Wasabi
mc cp wasabi/smartb-db-backups/mysql/latest/sb.sql.gz /tmp/
# Restore
pigz -d < /tmp/sb.sql.gz | mysql -h 192.168.8.15 -u root -p sb
# List snapshots
restic -r s3:https://s3.ap-southeast-2.wasabisys.com/smartb-db-backups/mysql-binlogs snapshots
# Restore binlogs
restic restore <snapshot-id> --target /tmp/binlog-restore
# Replay binlogs
mysqlbinlog /tmp/binlog-restore/*.* | mysql -h 192.168.8.15 -u root -p
# SSH to 192.168.9.116
clickhouse-backup restore-remote <backup-name>
- Access Key ID:
4U8RUFDXRIPCOP2FOJSS
- Secret Key:
43k9lDRg7CLzN88sVDonde4OIj3q5IcQ5nrmEyJp
- Region: ap-southeast-2 (Sydney)
- Endpoint:
s3.ap-southeast-2.wasabisys.com
- Bucket (DB):
smartb-db-backups
- Bucket (PBS):
smartb-proxmox-backup
- Repository:
s3:https://s3.ap-southeast-2.wasabisys.com/smartb-db-backups/mysql-binlogs
- Encryption Passphrase:
62rIwr0rKujM6VxVgoDaLM9EHnV6Y8Ox
- Uses Wasabi keys above for S3 auth (AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY)
- Backup source host:
192.168.8.241:3306 (replica — log_replica_updates=ON)
- Primary host:
192.168.8.15:3306
- User:
root
- Password:
p£w3Qr#0BJ3O9
- Container 523 (backup-mgr):
root@192.168.9.220 password: manojM@1289
- ClickHouse (9.116):
manojm@192.168.9.116 password: manojM@1289 (sudo same)
- PBS (9.51):
root@192.168.9.51 password: JohnIsTheBest1980!
- Container exec (via bm04):
pct exec 523 -- bash