This project has moved. For the latest updates, please go here.

Basic Usage (Updated 25 Jan 2015)


MySqlBackup.NET is built on top of MySQL .NET Connector.
This guide assumes that you are familiar with MySQL Dot Net Connector with the minimum knowledge of performing SELECT, INSERT, UPDATE, DELETE by using MySql.Data.DLL.

Add this using statement before coding with MySqlBackup.NET:
using MySql.Data.MySqlClient;

Simple Export Example
string constring = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();
            mb.ExportToFile(file);
            conn.Close();
        }
    }
}

Simple Import Example
string constring = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();
            mb.ImportFromFile(file);
            conn.Close();
        }
    }
}

Above examples will export and import a MySQL database with default options.

Customize Export and Import Behavior


There are some options that can modify the export and import behavior. These options are defined in
  • MySqlBackup.ExportInfo
  • MySqlBackup.ImportInfo
Example of customize export behavior: You want to:
  • Create new database
  • Only export table's structures
  • Don't export rows of data
Sample Codes:
string constring = "server=localhost;user=root;pwd=1234;database=test1;";
string file = "Y:\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();
            mb.ExportInfo.AddCreateDatabase = true;
            mb.ExportInfo.ExportTableStructure = true;
            mb.ExportInfo.ExportRows = false;
            mb.ExportToFile(file);
        }
    }
}

Example of customize import behavior: You want to:
  • Import to new (non-existed) database
  • Set default character of new database to utf8
Sample Codes:
string constring = "server=localhost;user=root;pwd=1234;database=test1;";
string file = "Y:\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = conn;
            conn.Open();
            mb.ImportInfo.TargetDatabase = "test2";
            mb.ImportInfo.DatabaseDefaultCharSet = "utf8";
            mb.ImportFromFile(file);
        }
    }
}

Full List of ExportInfo Options

Options Data Type Default Value Descriptions
TablesToBeExportedList List<string> Empty Only defined tables in the List will be exported.
TablesToBeExportedDic Dictionary <string,string> Empty Only defined tables in the Dictionary will be exported. Key refers to table's name, Value will be SELECT statement. The SELECT statement is used to filter rows. Conditioning rows export. Example: SELECT * FROM tableA WHERE membershipid = 1
RecordDumpTime bool true Gets or Sets a value indicates whether the Dump Time should recorded in dump file.
EnableEncryption bool false Gets or Sets a value indicates whether the Exported Dump File should be encrypted.
EncryptionPassword string Empty Sets the password used to encrypt the exported dump file.
AddCreateDatabase bool false Gets or Sets a value indicates whether the SQL statement of "CREATE DATABASE" should added into dump file.
ExportTableStructure bool true Gets or Sets a value indicates whether the Table Structure (CREATE TABLE) should be exported.
ResetAutoIncrement bool true Gets or Sets a value indicates whether the value of auto-increment of each table should be reset to 1.
ExportRows bool true Gets or Sets a value indicates whether the Rows should be exported.
MaxSqlLength int 5x1024x1024 = 5MB Gets or Sets the maximum length for combining multiple INSERTs into single sql.
ExportProcedures bool true Gets or Sets a value indicates whether the Stored Procedures should be exported.
ExportFunctions bool true Gets or Sets a value indicates whether the Stored Functions should be exported.
ExportTriggers bool true Gets or Sets a value indicates whether the Stored Triggers should be exported.
ExportViews bool true Gets or Sets a value indicates whether the Stored Views should be exported.
ExportEvents bool true Gets or Sets a value indicates whether the Stored Events should be exported.
IntervalForProgressReport int 50 Gets or Sets a value indicates the interval of time (in miliseconds) to raise the event of ExportProgressChanged.
GetTotalRowsBeforeExport bool true (modified in v2.0.9) Gets or Sets a value indicates whether the totals of rows should be counted before export process commence.
ScriptsDelimiter string | Gets or Sets the delimiter used for exporting Procedures, Functions, Events and Triggers.
ExportRoutinesWithoutDefiner bool true Gets or Sets a value indicates whether the exported Scripts (Procedure, Functions, Events, Triggers, Events) should include DEFINER.
ExcludeTables (v2.0.5) List<string> Empty Gets or Sets the tables (black list) that will be excluded for export. The rows of the these tables will not be exported too.
GetDocumentHeaders(MySqlCommand) (v2.0.5) List<string> see demo app Gets the list of document headers.
SetDocumentHeaders(List<string>) (v2.0.5) List<string> see demo app_ Sets the document headers.
GetDocumentFooters() (v2.0.5) List<string> see demo app Gets the document footers.
SetDocumentFooters(List<string>) (v2.0.5) List<string> see demo app Sets the document footers.
RowsExportMode (v2.0.7) Enum Insert Gets or Sets a enum value indicates how the rows of each table should be exported. INSERT = Terminate the process if duplicate key existed; INSERT IGNORE = If the primary key existed, skip it; REPLACE = If the primary key existed, delete the row and insert new data; OnDuplicateKeyUpdate = If the primary key existed, update the row; UPDATE = If the primary key is not existed, skip it.
WrapWithinTransaction (v2.0.7) bool false Gets or Sets a value indicates whether the rows dump should be wrapped with transaction. Recommended to set this value to TRUE if using RowsExportMode = "INSERT" or "INSERTIGNORE" or "REPLACE", else FALSE.


Full List of ImportInfo Options

Options Data Type Default Value Descriptions
EnableEncryption bool false Gets or Sets a value indicates whether the Imported Dump File is encrypted.
EncryptionPassword string Empty Sets the password used to decrypt the exported dump file.
IntervalForProgressReport int 100 Gets or Sets a value indicates the interval of time (in miliseconds) to raise the event of ExportProgressChanged.
TargetDatabase string Empty Gets or Sets the name of target database. If the database is not existed, it will be created.
DatabaseDefaultCharSet string Empty Gets or Sets the default character set of the target database. This will only take effect if creating new TargetDatabase.
IgnoreSqlError bool false Gets or Sets a value indicates whether SQL errors occurs in import process should be ignored.
ErrorLogFile string Empty Gets or Sets the file path used to log error messages.

Last edited Jul 19, 2015 at 1:30 PM by adriancs, version 13