Database Snapshots Service

A command-line utility for creating, managing, and restoring database snapshots for MySQL. It provides features for full and incremental backups, historical binlog event reading, and point-in-time recovery.

Features

• Full and Incremental Snapshots: Create full database backups or smaller, incremental snapshots that capture changes since the last backup.
• Historical Binlog Reading: Read and process historical binlog events from MySQL for auditing and analysis.
• Point-in-Time Recovery: Restore the database to a specific state using a combination of full snapshots and incremental event data.
• File-Based Event Store: All database events are captured and stored locally in a file-based event store for auditing and recovery.
• Optimized Performance: Includes optimizations for file handling, such as streaming for large files and parallel processing where appropriate.
• Security: Supports optional AES-256-CBC encryption for snapshot files to protect sensitive data.
• Compression: Supports optional LZ4 compression to reduce the storage footprint of snapshots.
• Rich CLI: A comprehensive command-line interface for interacting with the service.

Prerequisites

• .NET 6.0 or later
• MySQL 5.7+ or MariaDB 10.2+ with binlog enabled
• mysqldump and mysqlbinlog utilities installed
• Appropriate MySQL user permissions for:
  • SELECT, SHOW, RELOAD, LOCK TABLES, REPLICATION CLIENT (for binlog reading)
  • SELECT, SHOW, RELOAD, LOCK TABLES (for snapshots)

Installation

1. Clone the repository:

   git clone <repository-url>
   cd DatabaseSnapshotsService
   

2. Build the project:

   dotnet build
   

3. Create a configuration file:

   cp config.example.json config.json
   

4. Edit config.json with your database settings (see Configuration section below).

Project Structure

DatabaseSnapshotsService/
├── Program.cs                 # Main CLI entry point
├── DatabaseSnapshots.cs       # Snapshot management logic
├── Models/
│   ├── Configuration.cs       # Configuration classes
│   ├── ConfigurationValidation.cs # Configuration validation
│   ├── DataModels.cs          # Data transfer objects
│   └── InputValidation.cs     # Input validation logic
├── Services/
│   ├── BinlogReader.cs        # MySQL binlog reading service
│   ├── EncryptionService.cs   # File encryption/decryption
│   ├── EventStore.cs          # Event storage service
│   ├── OptimizedFileService.cs # Optimized file operations
│   ├── RecoveryService.cs     # Database recovery service
│   └── SnapshotService.cs     # Snapshot creation and management
└── README.md                  # This file

Configuration

The service is configured via a config.json file in the root directory. Here's a complete example with all available options:

{
  "connectionString": "Server=localhost;Database=trading;Uid=root;Pwd=password;",
  "binlogReader": {
    "host": "localhost",
    "port": 3306,
    "username": "binlog_reader",
    "password": "secure_password",
    "serverId": 999,
    "startPosition": 4,
    "heartbeatInterval": 30
  },
  "snapshotStorage": {
    "path": "./snapshots",
    "compression": true,
    "retentionDays": 30,
    "maxFileSize": 104857600,
    "dumpOptimizations": {
      "singleTransaction": true,
      "includeRoutines": true,
      "includeTriggers": true,
      "includeEvents": true,
      "extendedInsert": true,
      "completeInsert": true,
      "hexBlob": true,
      "netBufferLength": 16384,
      "maxAllowedPacket": "1G",
      "excludeTables": [],
      "includeTables": [],
      "quick": true,
      "orderByPrimary": true,
      "flushLogs": true,
      "masterData": 2,
      "compact": false,
      "noAutocommit": false,
      "lockTables": false
    }
  },
  "eventStore": {
    "path": "./events",
    "maxFileSize": 52428800,
    "retentionDays": 90,
    "batchSize": 1000,
    "flushInterval": 5
  },
  "security": {
    "encryption": false,
    "encryptionKey": null
  }
}

Configuration Options

Root Level

• connectionString (string, required): MySQL connection string for the target database.

binlogReader

• host (string, default: "localhost"): MySQL server hostname
• port (int, default: 3306): MySQL server port
• username (string, default: "binlog_reader"): Username for binlog reader
• password (string, default: "secure_password"): Password for binlog reader
• serverId (int, default: 999): Server ID for binlog reader (must be unique)
• startPosition (long, default: 4): Starting position in binlog
• heartbeatInterval (int, default: 30): Interval in seconds to send heartbeats

snapshotStorage

• path (string, default: "./snapshots"): Directory where snapshots are stored
• compression (bool, default: true): Enable LZ4 compression for snapshots
• retentionDays (int, default: 30): Number of days to retain snapshots
• maxFileSize (long, default: 100MB): Maximum file size for snapshots in bytes

snapshotStorage.dumpOptimizations

• singleTransaction (bool, default: true): Use --single-transaction for consistent backups
• includeRoutines (bool, default: true): Include stored procedures and functions
• includeTriggers (bool, default: true): Include triggers
• includeEvents (bool, default: true): Include events
• extendedInsert (bool, default: true): Use extended INSERT syntax
• completeInsert (bool, default: true): Use complete INSERT syntax
• hexBlob (bool, default: true): Dump binary columns in hexadecimal
• netBufferLength (int, default: 16384): TCP/IP buffer length
• maxAllowedPacket (string, default: "1G"): Maximum allowed packet size
• excludeTables (string[], default: []): Tables to exclude from backup
• includeTables (string[], default: []): Tables to include in backup
• quick (bool, default: true): Use --quick option for faster dumps
• orderByPrimary (bool, default: true): Order by primary key
• flushLogs (bool, default: true): Flush logs before dump
• masterData (int, default: 2): --master-data option value
• compact (bool, default: false): Use compact dump format
• noAutocommit (bool, default: false): Disable autocommit
• lockTables (bool, default: false): Lock all tables before dump

eventStore

• path (string, default: "./events"): Directory where events are stored
• maxFileSize (long, default: 50MB): Maximum file size for event files
• retentionDays (int, default: 90): Number of days to retain events
• batchSize (int, default: 1000): Number of events to batch before writing
• flushInterval (int, default: 5): Interval in seconds to flush events

security

• encryption (bool, default: false): Enable AES-256-CBC encryption
• encryptionKey (string, optional): Base64-encoded encryption key (required if encryption is enabled)

Usage

The service is used via the command line.

Snapshots

Manage database snapshots.

• Create a full snapshot:

  dotnet run -- snapshot -c create -n "MyFullSnapshot" --type "full"
  

• Create an incremental snapshot:

  dotnet run -- snapshot -c create -n "MyIncrementalSnapshot" --type "incremental"
  

• List all snapshots:

  dotnet run -- snapshot -c list
  

• Show details for a specific snapshot:

  dotnet run -- snapshot -c show -n <SNAPSHOT_ID>
  

• Delete a snapshot:

  dotnet run -- snapshot -c delete -n <SNAPSHOT_ID>
  

Recovery

Manage recovery points.

• Create a recovery point:

  dotnet run -- recovery -c create-point -n "MyRecoveryPoint"
  

• List all recovery points:

  dotnet run -- recovery -c list-points
  

Restore

Restore the database from a snapshot.

• Restore from a snapshot:

  dotnet run -- restore --from-snapshot <SNAPSHOT_ID>
  

• Perform a dry run of a restore:

  dotnet run -- restore --from-snapshot <SNAPSHOT_ID> --dry-run
  

Events

Query events from incremental snapshots.

• Query events from a snapshot:

  dotnet run -- events -s <SNAPSHOT_ID> --table "my_table" -l 50
  

• Filter events by operation type:

  dotnet run -- events -s <SNAPSHOT_ID> --operation "insert"
  

Binlog

Read historical binlog events.

• Read historical binlog events:

  dotnet run -- binlog
  

Configuration

Display the current configuration.

• Show current configuration:

  dotnet run -- config
  

Troubleshooting

Common Issues

1. Connection Failed: Ensure MySQL is running and the connection string is correct
2. Permission Denied: Verify the MySQL user has the required permissions
3. Binlog Not Found: Ensure binlog is enabled in MySQL configuration
4. Encryption Errors: Check that the encryption key is valid Base64 and at least 32 characters
5. File System Errors: Ensure the service has write permissions to the configured paths

Logs

The service outputs colored console messages:

• Green: Success messages
• Yellow: Warnings and informational messages
• Red: Error messages
• White: General output
• Gray: Debug information

Performance Tips

• Use quick and orderByPrimary options for faster dumps
• Enable compression for large databases
• Adjust batchSize and flushInterval for optimal event store performance

Security Considerations

• Store encryption keys securely and never commit them to version control
• Use strong, unique encryption keys (generate with EncryptionService.GenerateEncryptionKey())
• Ensure proper file system permissions for snapshot and event storage
• Consider network security for remote MySQL connections
• Regularly rotate encryption keys and update passwords

License

This project is licensed under the Strice License. See LICENSE for more details.