⚡ Backer

A production-quality TypeScript CLI tool for performing automated, logical SQL dumps of MySQL/MariaDB databases and uploading them securely to Cloudflare R2 (or any S3-compatible storage API).

It is uniquely designed to stream large databases row-by-row into compressed and AES-256 encrypted multipart uploads, bypassing disk intermediate storage to prevent memory and disk starvation.

Features

• Direct Connections: Connects with mysql2/promise avoiding system-level dependencies.
• Batched Streaming Architecture: Streams rows using strictly batched queries piped into lib-storage directly.
• Secure by Default: Features optional aes-256-cbc encryption out-of-the-box using the native Node.js crypto module.
• Robustness: Includes multi-part exponential backoff retry upload to S3/R2 endpoints.
• Enterprise Ready Logging: Strictly adheres to structured JSON logging format suitable for datadog, splunk, and fluentd.

---

🚀 Setup Instructions

1. Clone and Install

   git clone https://github.com/mafineeek/backer.git
   cd backer
   npm install

2. Configuration Copy the .env.example file to .env:

   cp .env.example .env

   Fill out the environment variables in .env matching your R2 metrics and DB server:

   # Database Configuration
   DB_HOST=127.0.0.1
   DB_PORT=3306
   DB_USER=root
   DB_PASSWORD=secret
   DB_NAME=my_production_db
   
   # R2 / S3 Configuration
   R2_ACCOUNT_ID=cloudflare_account_hash
   R2_ACCESS_KEY_ID=access_key
   R2_SECRET_ACCESS_KEY=secret_key
   R2_BUCKET_NAME=my-db-backups
   # Use variables: {YYYY}, {MM}, {dbname}, {timestamp}
   R2_UPLOAD_PATH=mysql/{YYYY}/{MM}/{dbname}-{timestamp}.sql.gz.enc
   
   # Advanced: AES-256 Encryption
   ENABLE_ENCRYPTION=true
   # Must be exactly 32 bytes (64 hex characters or 32 raw UTF-8 characters)
   BACKUP_ENCRYPTION_KEY=0123456789abcdef0123456789abcdef

3. Build the CLI Compile TypeScript to JS into the dist/ directory.

   npm run build

---

🏃 Run Instructions

You can run the service dynamically in development or execute the built output.

Development:

npm run dev

Production:

npm start

Encryption Explanation

Encryption operates by appending a 16-byte cryptographically secure pseudorandom Initial Vector (IV) at the start of the output stream before passing chunked, encrypted content utilizing aes-256-cbc.

When ENABLE_ENCRYPTION=true, you must provide exactly a 32-character key for BACKUP_ENCRYPTION_KEY.

To decrypt backups fetched from R2 (e.g., using Node CLI or OpenSSL): You extract the first 16 bytes for the IV, and decrypt the remaining binary using the same 32 character key. If encryption is disabled (ENABLE_ENCRYPTION=false), the stream bypasses crypto directly to Gzip upload.

---

⏱️ Cron Job Example

Often, this system will be invoked automatically on a system scale. Be secure when dealing with env variables. Since cron jobs often miss path variable bindings (node/npm), utilize explicit paths.

Edit the cron table: crontab -e

# Run daily at 03:00 AM server time
0 3 * * * cd /opt/services/backer && /usr/bin/node dist/index.js >> /var/log/backer.log 2>&1

---

📋 Example Log Output

Because the service uses explicit JSON logging format, log ingestion is flawless:

{"timestamp":"2026-02-28T14:55:00.000Z","level":"info","message":"Starting external database backup pipeline"}
{"timestamp":"2026-02-28T14:55:00.250Z","level":"info","message":"Database connection established","context":{"host":"127.0.0.1","database":"my_database"}}
{"timestamp":"2026-02-28T14:55:00.410Z","level":"info","message":"Dump stream generation started"}
{"timestamp":"2026-02-28T14:55:00.412Z","level":"info","message":"Encryption is enabled, generating AES-256-CBC cipher"}
{"timestamp":"2026-02-28T14:55:00.650Z","level":"info","message":"Starting backup for table","context":{"table":"users"}}
{"timestamp":"2026-02-28T14:55:01.120Z","level":"info","message":"Upload chunk transferred","context":{"key":"mysql/2026/02/my_database-2026-02-28T14-55-00-112Z.sql.gz.enc","loadedBytes":5242880}}
{"timestamp":"2026-02-28T14:55:03.450Z","level":"info","message":"Upload fully completed","context":{"bucket":"my-backups","key":"mysql/2026/02/my_database-2026-02-28T14-55-00-112Z.sql.gz.enc"}}
{"timestamp":"2026-02-28T14:55:03.452Z","level":"info","message":"Backup pipeline completed successfully"}