Skip to content

Getting Started Guide: From Basics to Advanced

Jump to bottom
adriancs edited this page Jul 4, 2025 · 1 revision

MySqlBackup.NET v2.6 User Guide

Last Updated: July 2025

Welcome to MySqlBackup.NET v2.6! We're excited to help you master MySQL database backup and restoration with this powerful, easy-to-use library. Whether you're just starting out or you're a seasoned developer, this guide will walk you through everything you need to know, from basic operations to advanced customization.

Getting Started

What You'll Need

Before we dive in, let's make sure you have the basics covered. This guide assumes you're comfortable with:

  • Basic C# programming
  • MySQL .NET connectors (MySqlConnector.NET (MIT), MySQL .NET Connector by Oracle, or dotConnect for MySQL by Devart)
  • Standard SQL operations like SELECT, INSERT, UPDATE, and DELETE

Setting Up Your Project

Start by adding the appropriate using statement to your file:

// For MySqlConnector.NET (MIT)
using MySqlConnector;

// For MySQL .NET Connector by Oracle
using MySql.Data.MySqlClient;

// For dotConnect for MySQL by Devart
using Devart.Data.MySql;

Your First Database Export

Let's start with something simple and satisfying - exporting your entire database to a file:

string constr = "server=localhost;user=root;pwd=1234;database=test1;convertzerodatetime=true;";
string filePath = @"C:\backup.sql";

using (var conn = new MySqlConnection(constr))
using (var cmd = conn.CreateCommand())
using (var mb = new MySqlBackup(cmd))
{
    conn.Open();
    mb.ExportToFile(filePath);
}

That's it! You've just created a complete backup of your database. The backup file contains everything: table structures, data, stored procedures, functions, views, triggers, and events.

Your First Database Import

Restoring from a backup is just as straightforward:

string constr = "server=localhost;user=root;pwd=1234;database=test1;convertzerodatetime=true;";
string filePath = @"C:\backup.sql";

using (var conn = new MySqlConnection(constr))
using (var cmd = conn.CreateCommand())
using (var mb = new MySqlBackup(cmd))
{
    conn.Open();
    mb.ImportFromFile(filePath);
}

Advanced Export Options

Now let's explore the powerful customization options available through the ExportInfo property. All options are listed alphabetically for easy reference:

Complete ExportInfo Reference

Property Type Default Description
AddCreateDatabase bool false Includes CREATE DATABASE statement in the export
AddDropDatabase bool false Includes DROP DATABASE statement in the export
AddDropTable bool true Includes DROP TABLE statements before CREATE TABLE
EnableComment bool true Adds descriptive comments to the export file
EnableLockTablesWrite bool false Uses LOCK TABLES WRITE during export for consistency
EnableParallelProcessing bool true Enables multi-threaded processing for better performance
ExcludeRowsForTables List<string> Empty Tables to exclude from row export (structure only)
ExcludeTables List<string> Empty Tables to completely exclude from export
ExportEvents bool true Exports scheduled events
ExportFunctions bool true Exports stored functions
ExportProcedures bool true Exports stored procedures
ExportRoutinesWithoutDefiner bool true Removes DEFINER clause from routines for portability
ExportRows bool true Exports table row data
ExportTableStructure bool true Exports table schemas (CREATE TABLE statements)
ExportTriggers bool true Exports database triggers
ExportViews bool true Exports database views
GetTotalRowsMode GetTotalRowsMethod SelectCount How to calculate total rows: InformationSchema (fast, but estimation only), SelectCount (accurate, but slow), or Skip
InsertLineBreakBetweenInserts bool false Adds line breaks between multiple INSERT statements
IntervalForProgressReport int 100 Progress update interval in milliseconds
MaxSqlLength int 16MB Maximum size for combined INSERT statements
RecordDumpTime bool true Records export timestamp in the file
ResetAutoIncrement bool false Resets auto-increment values to 1
RowsExportMode RowsDataExportMode Insert How to handle row exports: Insert, InsertIgnore, Replace, OnDuplicateKeyUpdate, or Update
ScriptsDelimiter string `" "`
SetTimeZoneUTC bool true Sets timezone to UTC for consistent timestamps
TableColumnValueAdjustments Dictionary<string, Dictionary<string, Func<object, object>>> null Custom value transformations per table/column
TablesToBeExportedDic Dictionary<string, string> null Specific tables with custom SELECT statements
TablesToBeExportedList List<string> null Simple list of tables to export
TextEncoding Encoding UTF8 Text encoding for the export file
WrapWithinTransaction bool false Wraps row exports in transactions

Advanced Import Options

The ImportInfo property offers these customization options:

Complete ImportInfo Reference

Property Type Default Description
ErrorLogFile string "" Path to file for logging import errors
IgnoreSqlError bool false Continue import process even when SQL errors occur
IntervalForProgressReport int 100 Progress update interval in milliseconds
TextEncoding Encoding UTF8 Text encoding for reading the import file

Practical Examples

Example 1: Structure-Only Export

Perfect for creating database schemas without data:

using (var conn = new MySqlConnection(constr))
using (var cmd = conn.CreateCommand())
using (var mb = new MySqlBackup(cmd))
{
    conn.Open();
    
    // Configure for structure-only export
    mb.ExportInfo.ExportTableStructure = true;
    mb.ExportInfo.ExportRows = false;
    mb.ExportInfo.ExportProcedures = true;
    mb.ExportInfo.ExportFunctions = true;
    mb.ExportInfo.ExportViews = true;
    mb.ExportInfo.ExportTriggers = true;
    
    mb.ExportToFile(@"C:\backups\schema_only.sql");
}

Example 2: Data-Only Export

When you only need the data without structure:

using (var conn = new MySqlConnection(constr))
using (var cmd = conn.CreateCommand())
using (var mb = new MySqlBackup(cmd))
{
    conn.Open();
    
    // Configure for data-only export
    mb.ExportInfo.ExportTableStructure = false;
    mb.ExportInfo.ExportRows = true;
    mb.ExportInfo.ExportProcedures = false;
    mb.ExportInfo.ExportFunctions = false;
    mb.ExportInfo.ExportViews = false;
    mb.ExportInfo.ExportTriggers = false;
    mb.ExportInfo.AddDropTable = false;
    
    mb.ExportToFile(@"C:\backups\data_only.sql");
}

Example 3: Selective Table Export

Export only specific tables with custom queries. Read more: Conditional Rows Export

using (var conn = new MySqlConnection(constr))
using (var cmd = conn.CreateCommand())
using (var mb = new MySqlBackup(cmd))
{
    conn.Open();
    
    // Define custom table exports
    mb.ExportInfo.TablesToBeExportedDic = new Dictionary<string, string>
    {
        ["users"] = "SELECT id, username, email FROM users WHERE active = 1",
        ["orders"] = "SELECT * FROM orders WHERE order_date >= '2024-01-01'",
        ["products"] = "SELECT * FROM products"
    };
    
    mb.ExportToFile(@"C:\backups\selective_export.sql");
}

Example 4: Safe Import with Error Handling

Handle potential import issues gracefully:

using (var conn = new MySqlConnection(constr))
using (var cmd = conn.CreateCommand())
using (var mb = new MySqlBackup(cmd))
{
    conn.Open();
    
    // Configure import settings
    mb.ImportInfo.IgnoreSqlError = true;
    mb.ImportInfo.ErrorLogFile = @"C:\logs\import_errors.log";
    
    try
    {
        mb.ImportFromFile(@"C:\backups\database.sql");
        Console.WriteLine("Import completed successfully!");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Import failed: {ex.Message}");
        Console.WriteLine("Check the error log for details.");
    }
}

Example 5: Progress Tracking

Monitor your backup progress in real-time. Read more: Using Progress Report

using (var conn = new MySqlConnection(constr))
using (var cmd = conn.CreateCommand())
using (var mb = new MySqlBackup(cmd))
{
    // Subscribe to progress events
    mb.ExportProgressChanged += (sender, e) =>
    {
        int percentage = (int)((double)e.CurrentRowIndexInAllTables / e.TotalRowsInAllTables * 100);
        Console.WriteLine($"Exporting {e.CurrentTableName}: {percentage}% complete");
    };
    
    mb.ExportCompleted += (sender, e) =>
    {
        Console.WriteLine($"Export completed in {e.TimeUsed.TotalSeconds:F2} seconds");
    };
    
    conn.Open();
    mb.ExportToFile(@"C:\backups\tracked_backup.sql");
}

Example 6: Advanced Column Value Adjustments

Transform data during export using the new v2.6 feature. Read more: Adjust Column Value

using (var conn = new MySqlConnection(constr))
using (var cmd = conn.CreateCommand())
using (var mb = new MySqlBackup(cmd))
{
    conn.Open();
    
    // Add value adjustments for sensitive data
    mb.ExportInfo.AddTableColumnValueAdjustment("users", "password", 
        value => "***REDACTED***");
    
    mb.ExportInfo.AddTableColumnValueAdjustment("users", "email", 
        value => value?.ToString()?.Replace("@", "@example."));
    
    mb.ExportInfo.AddTableColumnValueAdjustment("orders", "total_amount", 
        value => value is decimal amount ? Math.Round(amount, 2) : value);
    
    mb.ExportToFile(@"C:\backups\sanitized_backup.sql");
}

Export Methods Overview

MySqlBackup.NET v2.6 provides multiple export destinations:

// Export to file
mb.ExportToFile(@"C:\backup.sql");

// Export to string
string sqlContent = mb.ExportToString();

// Export to stream
using (var fileStream = new FileStream(@"C:\backup.sql", FileMode.Create))
{
    mb.ExportToStream(fileStream);
}

// Export to TextWriter
using (var writer = new StreamWriter(@"C:\backup.sql"))
{
    mb.ExportToTextWriter(writer);
}

// Export to MemoryStream
using (var memoryStream = new MemoryStream())
{
    mb.ExportToMemoryStream(memoryStream);
    // Process the memory stream as needed
}

Import Methods Overview

Similarly, you can import from various sources:

// Import from file
mb.ImportFromFile(@"C:\backup.sql");

// Import from string
string sqlContent = File.ReadAllText(@"C:\backup.sql");
mb.ImportFromString(sqlContent);

// Import from stream
using (var fileStream = new FileStream(@"C:\backup.sql", FileMode.Open))
{
    mb.ImportFromStream(fileStream);
}

// Import from TextReader
using (var reader = new StreamReader(@"C:\backup.sql"))
{
    mb.ImportFromTextReader(reader);
}

// Import from MemoryStream
using (var memoryStream = new MemoryStream(sqlBytes))
{
    mb.ImportFromMemoryStream(memoryStream);
}

What's New in v2.6

  • Enhanced Parallel Processing: Improved multi-threaded performance
  • Better Error Handling: More robust error recovery and logging
  • Column Value Adjustments: Transform data during export with custom functions
  • Improved Memory Management: Better handling of large datasets
  • Enhanced Security: Better handling of identifiers and SQL injection prevention

Getting Help

Need more assistance? Here are your options:

  1. Documentation: Visit our GitHub Wiki for detailed guides
  2. Issues: Report bugs or request features on our GitHub Issues page
  3. Examples: Check out the sample projects in our repository

Conclusion

MySqlBackup.NET v2.6 makes database backup and restoration straightforward and reliable. From simple one-line exports to complex multi-table operations with custom transformations, you now have all the tools you need to handle your MySQL backup requirements professionally.

Whether you're backing up a small application database or managing enterprise-level data operations, MySqlBackup.NET v2.6 provides the flexibility, performance, and reliability you need.

Happy coding, and may your backups always be successful! 🚀

For the most up-to-date information and advanced topics, please visit our official GitHub repository.

Clone this wiki locally