Using Encryption and Compression

Overview

MySqlBackup.NET provides powerful capabilities for creating secure database backups with optional compression and encryption. This guide demonstrates how to implement robust backup and restore functionality using MemoryStream operations, which offer better memory management and flexibility compared to direct file operations.

Core Components

1. Database Export with MemoryStream

The foundation of our backup system uses MemoryStream to handle data in memory before applying compression and encryption:

public MemoryStream ExportDatabaseToMemoryStream(string connectionString, bool exportRows = true)
{
    MemoryStream ms = new MemoryStream();
    
    try
    {
        using (var conn = new MySqlConnection(connectionString))
        using (var cmd = conn.CreateCommand())
        using (var mb = new MySqlBackup(cmd))
        {
            conn.Open();
            mb.ExportInfo.ExportRows = exportRows;
            mb.ExportToStream(ms);
        }
        
        ms.Position = 0; // Reset position for subsequent operations
        return ms;
    }
    catch
    {
        ms?.Dispose();
        throw;
    }
}

2. Compression Implementation

Compression significantly reduces backup file size (typically 50-70% smaller). We use the DeflateStream for optimal compression:

public static class CompressorHelper
{
    public static byte[] Compress(byte[] data)
    {
        using (var output = new MemoryStream())
        {
            using (var deflateStream = new DeflateStream(output, CompressionLevel.Optimal))
            {
                deflateStream.Write(data, 0, data.Length);
            }
            return output.ToArray();
        }
    }

    public static byte[] Decompress(byte[] compressedData)
    {
        using (var input = new MemoryStream(compressedData))
        using (var output = new MemoryStream())
        {
            using (var deflateStream = new DeflateStream(input, CompressionMode.Decompress))
            {
                deflateStream.CopyTo(output);
            }
            return output.ToArray();
        }
    }
}

3. Enhanced Encryption with AES

For secure backups, we implement AES encryption with proper key derivation:

public static class AesHelper
{
    private const int SaltSize = 16;
    private const int KeySize = 32; // 256 bits
    private const int IvSize = 16;  // 128 bits
    private const int Iterations = 100000; // OWASP recommended minimum

    public static byte[] Encrypt(byte[] data, byte[] password)
    {
        // Generate random salt for each encryption
        byte[] salt = new byte[SaltSize];
        RandomNumberGenerator.Fill(salt);

        using (var pdb = new Rfc2898DeriveBytes(password, salt, Iterations, HashAlgorithmName.SHA256))
        {
            byte[] key = pdb.GetBytes(KeySize);
            byte[] iv = pdb.GetBytes(IvSize);

            byte[] encryptedData = PerformAesOperation(data, key, iv, true);

            // Prepend salt to encrypted data for storage
            byte[] result = new byte[salt.Length + encryptedData.Length];
            Buffer.BlockCopy(salt, 0, result, 0, salt.Length);
            Buffer.BlockCopy(encryptedData, 0, result, salt.Length, encryptedData.Length);

            return result;
        }
    }

    public static byte[] Decrypt(byte[] encryptedDataWithSalt, byte[] password)
    {
        if (encryptedDataWithSalt.Length < SaltSize)
            throw new ArgumentException("Invalid encrypted data format");

        // Extract salt from the beginning
        byte[] salt = new byte[SaltSize];
        byte[] encryptedData = new byte[encryptedDataWithSalt.Length - SaltSize];
        
        Buffer.BlockCopy(encryptedDataWithSalt, 0, salt, 0, SaltSize);
        Buffer.BlockCopy(encryptedDataWithSalt, SaltSize, encryptedData, 0, encryptedData.Length);

        using (var pdb = new Rfc2898DeriveBytes(password, salt, Iterations, HashAlgorithmName.SHA256))
        {
            byte[] key = pdb.GetBytes(KeySize);
            byte[] iv = pdb.GetBytes(IvSize);

            return PerformAesOperation(encryptedData, key, iv, false);
        }
    }

    private static byte[] PerformAesOperation(byte[] data, byte[] key, byte[] iv, bool encrypt)
    {
        using (var aes = Aes.Create())
        {
            aes.Key = key;
            aes.IV = iv;
            aes.Mode = CipherMode.CBC;
            aes.Padding = PaddingMode.PKCS7;

            using (var memoryStream = new MemoryStream())
            using (var cryptoStream = new CryptoStream(memoryStream,
                encrypt ? aes.CreateEncryptor() : aes.CreateDecryptor(),
                CryptoStreamMode.Write))
            {
                cryptoStream.Write(data, 0, data.Length);
                cryptoStream.FlushFinalBlock();
                return memoryStream.ToArray();
            }
        }
    }
}

Complete Backup Implementation

Here's a comprehensive backup method that combines all components:

public void CreateBackup(string connectionString, bool compress = true, bool encrypt = true, string password = null)
{
    MemoryStream ms = null;
    
    try
    {
        // Step 1: Export database to MemoryStream
        ms = ExportDatabaseToMemoryStream(connectionString);
        
        // Step 2: Apply compression if requested
        if (compress)
        {
            byte[] compressedData = CompressorHelper.Compress(ms.ToArray());
            ms.Dispose(); // Important: dispose before reassigning
            ms = new MemoryStream(compressedData);
        }
        
        // Step 3: Apply encryption if requested
        if (encrypt)
        {
            if (string.IsNullOrWhiteSpace(password))
                throw new ArgumentException("Password is required for encryption");
                
            byte[] passwordBytes = Encoding.UTF8.GetBytes(password);
            byte[] encryptedData = AesHelper.Encrypt(ms.ToArray(), passwordBytes);
            ms.Dispose(); // Important: dispose before reassigning
            ms = new MemoryStream(encryptedData);
        }
        
        // Step 4: Generate appropriate filename
        string filename = GenerateBackupFilename(compress, encrypt);
        
        // Step 5: Save to file or send as response
        SaveBackupToFile(ms, filename);
        // OR for web applications: SendAsDownload(ms, filename);
    }
    finally
    {
        ms?.Dispose(); // Always dispose of MemoryStream
    }
}

private string GenerateBackupFilename(bool compressed, bool encrypted)
{
    string suffix = "";
    if (compressed) suffix += "-compressed";
    if (encrypted) suffix += "-encrypted";
    
    string extension = encrypted ? ".bin" : (compressed ? ".gz" : ".sql");
    return $"backup{suffix}-{DateTime.Now:yyyy-MM-dd_HHmmss}{extension}";
}

Complete Restore Implementation

The restore process reverses the backup operations:

public void RestoreDatabase(string connectionString, byte[] backupData, bool wasCompressed, bool wasEncrypted, string password = null)
{
    MemoryStream ms = new MemoryStream(backupData);
    
    try
    {
        // Step 1: Decrypt if necessary
        if (wasEncrypted)
        {
            if (string.IsNullOrWhiteSpace(password))
                throw new ArgumentException("Password is required for decryption");
                
            byte[] passwordBytes = Encoding.UTF8.GetBytes(password);
            byte[] decryptedData = AesHelper.Decrypt(ms.ToArray(), passwordBytes);
            ms.Dispose();
            ms = new MemoryStream(decryptedData);
        }
        
        // Step 2: Decompress if necessary
        if (wasCompressed)
        {
            byte[] decompressedData = CompressorHelper.Decompress(ms.ToArray());
            ms.Dispose();
            ms = new MemoryStream(decompressedData);
        }
        
        // Step 3: Import to database
        ms.Position = 0;
        using (var conn = new MySqlConnection(connectionString))
        using (var cmd = conn.CreateCommand())
        using (var mb = new MySqlBackup(cmd))
        {
            conn.Open();
            mb.ImportFromStream(ms);
        }
    }
    finally
    {
        ms?.Dispose();
    }
}

Best Practices and Important Notes

Memory Management

Always dispose of MemoryStream objects properly to prevent memory leaks. The pattern shown above ensures proper cleanup even when exceptions occur.

Security Considerations

• Use strong passwords for encryption
• Consider using more advanced key derivation functions like PBKDF2 for production environments
• Store passwords securely and never hardcode them in your application

Performance Optimization

• For large databases, consider processing data in chunks rather than loading everything into memory
• Monitor memory usage during backup operations
• Consider implementing progress reporting for long-running operations

Error Handling

Implement comprehensive error handling to gracefully manage various failure scenarios:

try
{
    // Backup operations
}
catch (MySqlException ex)
{
    // Handle database-specific errors
    LogError($"Database error during backup: {ex.Message}");
    throw new BackupException("Database backup failed", ex);
}
catch (CryptographicException ex)
{
    // Handle encryption/decryption errors
    LogError($"Encryption error: {ex.Message}");
    throw new BackupException("Encryption operation failed", ex);
}
catch (Exception ex)
{
    // Handle general errors
    LogError($"Unexpected error: {ex.Message}");
    throw;
}

Web Application Integration

For web applications, you can provide backup downloads directly to users:

public void SendBackupAsDownload(MemoryStream backupStream, string filename)
{
    backupStream.Position = 0;
    
    Response.Clear();
    Response.ContentType = "application/octet-stream";
    Response.Headers.Add("Content-Length", backupStream.Length.ToString());
    Response.Headers.Add("Content-Disposition", $"attachment; filename=\"{filename}\"");
    
    backupStream.CopyTo(Response.OutputStream);
    Response.Flush();
    Response.End();
}

Conclusion

This provides a robust, secure, and efficient method for database backup and restore operations. The combination of compression and encryption ensures that your backups are both space-efficient and secure, while proper memory management prevents resource leaks in production environments.

Remember to test your backup and restore procedures thoroughly in a development environment before deploying to production, and always verify that restored data maintains its integrity.