Conditional Rows Export for Each Table

Conditional Rows Export

MySqlBackup.NET v2.6 provides comprehensive control over which tables and rows are exported during the database backup process. This feature allows you to create selective backups by applying various filtering strategies, optimizing both performance and storage requirements.

Overview

The library offers multiple approaches to control export behavior:

1. Table-level exclusion - Completely exclude specific tables from export
2. Row-level exclusion - Export table structure but exclude all row data
3. Custom SELECT queries - Define precise data export criteria per table
4. Column-specific exports - Export only selected columns from tables

Configuration Properties

ExcludeTables

Completely excludes specified tables from the export process. Both table structure and data are omitted.

ExportInfo.ExcludeTables.Add("temporary_table");
ExportInfo.ExcludeTables.Add("cache_data");

ExcludeRowsForTables

Exports table structure but excludes all row data for specified tables. This is useful for maintaining schema integrity while omitting sensitive or unnecessary data.

ExportInfo.ExcludeRowsForTables.Add("user_sessions");
ExportInfo.ExcludeRowsForTables.Add("audit_logs");

TablesToBeExportedDic

Provides fine-grained control over data export through custom SELECT statements. This dictionary maps table names to their corresponding SELECT queries.

var exportTables = new Dictionary<string, string>();

// Export all rows from tableA
exportTables["tableA"] = "SELECT * FROM `tableA`;";

// Export no rows from tableB (structure only)
exportTables["tableB"] = "SELECT * FROM `tableB` WHERE 1 = 2;";

// Export filtered rows based on business logic
exportTables["tableC"] = "SELECT * FROM `tableC` WHERE membershipid = 1;";

// Export specific columns only
exportTables["tableD"] = "SELECT id, name, address, tel FROM `tableD`;";

// Export rows within date range
exportTables["tableE"] = "SELECT * FROM `tableE` WHERE dateregister >= '2014-01-01 00:00:00' AND dateregister <= '2014-12-31 23:59:59';";

ExportInfo.TablesToBeExportedDic = exportTables;

Export Behavior Logic

The library processes tables according to the following priority:

1. Complete Exclusion: Tables listed in ExcludeTables are entirely skipped
2. Structure Export: All remaining tables have their structure exported (if ExportTableStructure is enabled)
3. Row Export Decision:
   • If table is in ExcludeRowsForTables, skip row export
   • If table has custom SELECT in TablesToBeExportedDic, use that query
   • Otherwise, export all rows with SELECT * FROM table

Implementation Examples

Basic Conditional Export

void ConditionalExport()
{
    var exportTables = new Dictionary<string, string>();
    
    // Complete data export
    exportTables["products"] = "SELECT * FROM `products`;";
    
    // No data export (structure only)
    exportTables["temp_calculations"] = "SELECT * FROM `temp_calculations` WHERE 1 = 2;";
    
    // Filtered export
    exportTables["orders"] = "SELECT * FROM `orders` WHERE order_date >= '2024-01-01';";
    
    // Selective columns
    exportTables["customers"] = "SELECT customer_id, name, email FROM `customers`;";

    using (var conn = new MySqlConnection(connectionString))
    using (var cmd = conn.CreateCommand())
    using (var mb = new MySqlBackup(cmd))
    {
        conn.Open();
        mb.ExportInfo.TablesToBeExportedDic = exportTables;
        mb.ExportToFile("selective_backup.sql");
    }
}

Advanced Filtering Strategies

void AdvancedConditionalExport()
{
    var exportTables = new Dictionary<string, string>();
    
    // Privacy-compliant user data export
    exportTables["users"] = @"
        SELECT user_id, username, email, created_date 
        FROM `users` 
        WHERE account_status = 'active' 
        AND created_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)";
    
    // Audit trail with retention policy
    exportTables["audit_logs"] = @"
        SELECT log_id, user_id, action, timestamp 
        FROM `audit_logs` 
        WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 90 DAY)";
    
    // Reference data only
    exportTables["configurations"] = @"
        SELECT config_key, config_value 
        FROM `configurations` 
        WHERE is_system_config = 0";

    using (var conn = new MySqlConnection(connectionString))
    using (var cmd = conn.CreateCommand())
    using (var mb = new MySqlBackup(cmd))
    {
        conn.Open();
        
        // Additional exclusions
        mb.ExportInfo.ExcludeTables.Add("cache_entries");
        mb.ExportInfo.ExcludeRowsForTables.Add("session_data");
        
        mb.ExportInfo.TablesToBeExportedDic = exportTables;
        mb.ExportToFile("compliance_backup.sql");
    }
}

Performance Considerations

Parallel Processing

MySqlBackup.NET v2.6 introduces parallel processing capabilities that can significantly improve export performance:

// Enable parallel processing for large datasets
mb.ExportInfo.EnableParallelProcessing = true;

Query Optimization

When using custom SELECT statements, consider the following optimization strategies:

• Indexed Columns: Ensure WHERE clauses utilize indexed columns
• Date Ranges: Use appropriate date filtering to limit dataset size
• Column Selection: Export only necessary columns to reduce I/O
• Batch Processing: For very large tables, consider date-based partitioning

Use Cases

1. Development Environment Setup

Export production schema with minimal test data:

exportTables["users"] = "SELECT * FROM `users` LIMIT 100;";
exportTables["products"] = "SELECT * FROM `products` WHERE featured = 1;";
exportTables["orders"] = "SELECT * FROM `orders` WHERE 1 = 2;"; // Structure only

2. Data Migration

Export specific business units or geographical regions:

exportTables["customers"] = "SELECT * FROM `customers` WHERE region = 'North America';";
exportTables["orders"] = "SELECT * FROM `orders` WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = 'North America');";

3. Compliance and Privacy

Export data while maintaining regulatory compliance:

// Exclude sensitive tables entirely
mb.ExportInfo.ExcludeTables.Add("payment_details");
mb.ExportInfo.ExcludeTables.Add("personal_identifiers");

// Export anonymized data
exportTables["user_analytics"] = "SELECT user_id, session_duration, page_views FROM `user_analytics` WHERE consent_given = 1;";

4. Archival Strategy

Export historical data with retention policies:

exportTables["transactions"] = "SELECT * FROM `transactions` WHERE transaction_date >= DATE_SUB(NOW(), INTERVAL 7 YEAR);";
exportTables["user_activities"] = "SELECT * FROM `user_activities` WHERE activity_date >= DATE_SUB(NOW(), INTERVAL 2 YEAR);";

Best Practices

1. Test Queries: Validate custom SELECT statements before production use
2. Monitor Performance: Use appropriate indexes and LIMIT clauses for large datasets
3. Documentation: Maintain clear documentation of export criteria and business logic
4. Regular Review: Periodically review and update export criteria based on changing requirements
5. Backup Validation: Verify that exported data meets intended criteria through sampling or automated tests

Compatibility Notes

• Custom SELECT statements must be valid MySQL syntax
• Table and column names are automatically escaped using backticks
• Generated columns are automatically excluded from INSERT statements
• Foreign key relationships are preserved through dependency ordering

This conditional export functionality provides the flexibility needed for various backup scenarios while maintaining data integrity and performance optimization.