Skip to content

Home

Jump to bottom
Adrian Voo edited this page Sep 17, 2018 · 19 revisions

This guide assumes that you have already 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

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 be modified the export and import behavior. These options are defined in

  • MySqlBackup.ExportInfo
  • MySqlBackup.ImportInfo

Example of customized 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 customized 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 Descriptions
TablesToBeExportedList
Data Type: List<string>
Default Value: Empty		 Only defined tables in the List will be exported.
TablesToBeExportedDic
Data Type: Dictionary <string,string>
Default Value: 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
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the Dump Time should recorded in dump file.
EnableEncryption
Data Type: bool
Default Value: false 		Gets or Sets a value indicates whether the Exported Dump File should be encrypted.
EncryptionPassword
Data Type: string
Default Value: Empty		 Sets the password used to encrypt the exported dump file.
AddCreateDatabase
Data Type: bool
Default Value: false 		Gets or Sets a value indicates whether the SQL statement of "CREATE DATABASE" should added into dump file.
ExportTableStructure
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the Table Structure (CREATE TABLE) should be exported.
ResetAutoIncrement
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the value of auto-increment of each table should be reset to 1.
ExportRows
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the Rows should be exported.
MaxSqlLength
Data Type: int
Default Value: 5x1024x1024 = 5MB		 Gets or Sets the maximum length for combining multiple INSERTs into single sql.
ExportProcedures
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the Stored Procedures should be exported.
ExportFunctions
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the Stored Functions should be exported.
ExportTriggers
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the Stored Triggers should be exported.
ExportViews
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the Stored Views should be exported.
ExportEvents
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the Stored Events should be exported.
IntervalForProgressReport
Data Type: int
Default Value: 50		 Gets or Sets a value indicates the interval of time (in miliseconds) to raise the event of ExportProgressChanged.
GetTotalRowsBeforeExport
Data Type: bool
Default Value: 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
Data Type: string
Default Value: | 		Gets or Sets the delimiter used for exporting Procedures, Functions, Events and Triggers.
ExportRoutinesWithoutDefiner
Data Type: bool
Default Value: true 		Gets or Sets a value indicates whether the exported Scripts (Procedure, Functions, Events, Triggers, Events) should include DEFINER.
ExcludeTables (v2.0.5)
Data Type: List<string>
Default Value: 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)
Data Type: List<string>
Default Value: see demo app		 Gets the list of document headers.
SetDocumentHeaders(List) (v2.0.5)
Data Type: List<string>
Default Value: see demo app 		Sets the document headers.
GetDocumentFooters() (v2.0.5)
Data Type: List<string>
Default Value: see demo app		 Gets the document footers.
SetDocumentFooters(List) (v2.0.5)
Data Type: List<string>
Default Value: see demo app 		Sets the document footers.
RowsExportMode (v2.0.7)
Data Type: Enum
Default Value: 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)
Data Type: bool
Default Value: 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 Descriptions
EnableEncryption
Data Type: bool
Default Value: false 		Gets or Sets a value indicates whether the Imported Dump File is encrypted.
EncryptionPassword
Data Type: string
Default Value: Empty		 Sets the password used to decrypt the exported dump file.
IntervalForProgressReport
Data Type: int
Default Value: 100		 Gets or Sets a value indicates the interval of time (in miliseconds) to raise the event of ExportProgressChanged.
TargetDatabase
Data Type: string
Default Value: Empty		 Gets or Sets the name of target database. If the database is not existed, it will be created.
DatabaseDefaultCharSet
Data Type: string
Default Value: Empty		 Gets or Sets the default character set of the target database. This will only take effect if creating new TargetDatabase.
IgnoreSqlError
Data Type: bool
Default Value: false 		Gets or Sets a value indicates whether SQL errors occurs in import process should be ignored.
ErrorLogFile
Data Type: string
Default Value: Empty		 Gets or Sets the file path used to log error messages.
Clone this wiki locally