Skip to content

Home

Jump to bottom
adriancs edited this page Oct 19, 2022 · 19 revisions

Last update: Oct 17, 2021

Basic Usage

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:

  • 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.ExportTableStructure = true;
            mb.ExportInfo.ExportRows = false;
            mb.ExportToFile(file);
        }
    }
}

Full List of ExportInfo Options

ExportInfo.GetDocumentHeaders(cmd)

return: List<string>
default value: Demonstrated in test app.
Gets the list of document headers.

ExportInfo.SetDocumentHeaders(List<string>)

Sets the document headers.

ExportInfo.GetDocumentFooters()

return: List<string>
default value: demonstrated in test app.
Gets the document footers.

ExportInfo.SetDocumentFooters(List<string>)

Sets the document headers.

ExportInfo.ExcludeTables - List<string>

default value: empty list
Gets or Sets the tables (black list) that will be excluded for export. The rows of the these tables will not be exported too.

ExportInfo.TablesToBeExportedList - List<string>

default value: empty list
Gets or Sets the list of tables that will be exported. If none, all tables will be exported.

ExportInfo.TablesToBeExportedDic - Dictionary<string, string>

default value: empty dictionary
Gets or Sets the tables that will be exported with custom SELECT defined.
If none or empty, all tables and rows will be exported.
Key = Table's Name. Value = Custom SELECT Statement.
Example 1: SELECT * FROM product WHERE category = 1;
Example 2: SELECT name,description FROM product;

ExportInfo.RecordDumpTime - bool

default value: true
Gets or Sets a value indicates whether the Dump Time should recorded in dump file.

ExportInfo.AddCreateDatabase - bool

default value: false
Gets or Sets a value indicates whether the SQL statement of "CREATE DATABASE" should be added into dump file.

ExportInfo.AddDropDatabase - bool

default value: false
Gets or Sets a value indicates whether the SQL statement of "DROP DATABASE" should be added into dump file.

ExportInfo.ExportTableStructure - bool

default value: true
Gets or Sets a value indicates whether the Table Structure (CREATE TABLE) should be exported.

ExportInfo.AddDropTable - bool

default value: true
Gets or Sets a value indicates whether the SQL statement of "DROP TABLE" should be added into the dump file.

ExportInfo.ResetAutoIncrement - bool

default value: false
Gets or Sets a value indicates whether the value of auto-increment of each table should be reset to 1.

ExportInfo.ExportRows - bool

default value: true
Gets or Sets a value indicates whether the Rows should be exported.

ExportInfo.MaxSqlLength - int

default value: 5 * 1024 * 1024 (5mb)
Gets or Sets the maximum length for combining multiple INSERTs into single sql.
Default value is 5MB.
Only applies if RowsExportMode = "INSERT" or "INSERTIGNORE" or "REPLACE".
This value will be ignored if RowsExportMode = ONDUPLICATEKEYUPDATE or UPDATE.

ExportInfo.ExportProcedures - bool

default value: true
Gets or Sets a value indicates whether the Stored Procedures should be exported.

ExportInfo.ExportFunctions - bool

default value: true
Gets or Sets a value indicates whether the Stored Functions should be exported.

ExportInfo.ExportTriggers - bool

default value: true
Gets or Sets a value indicates whether the Stored Triggers should be exported.

ExportInfo.ExportViews - bool

default value: true
Gets or Sets a value indicates whether the Stored Views should be exported.

ExportInfo.ExportEvents - bool

default value: true
Gets or Sets a value indicates whether the Stored Events should be exported.

ExportInfo.IntervalForProgressReport - int

default value: 100
Gets or Sets a value indicates the interval of time (in miliseconds) to raise the event of ExportProgressChanged.

ExportInfo.ScriptsDelimiter - string

default value: |
Gets or Sets the delimiter used for exporting Procedures, Functions, Events and Triggers.

ExportInfo.ExportRoutinesWithoutDefiner - bool

default value: true
Gets or Sets a value indicates whether the exported Scripts (Procedure, Functions, Events, Triggers, Events) should exclude DEFINER.

ExportInfo.RowsExportMode - enum RowsDataExportMode

default value: Insert
Gets or Sets a enum value indicates how the rows of each table should be exported.
INSERT = The default option. Recommended if exporting to a new database. If the primary key existed, the process will halt;
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. If all fields are primary keys, it will change to INSERT IGNORE;
UPDATE = If the primary key is not existed, skip it and if all the fields are primary key, no rows will be exported.

ExportInfo.WrapWithinTransaction - 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 FALSE if using RowsExportMode = "INSERT" or "INSERTIGNORE" or "REPLACE", else TRUE.

ExportInfo.TextEncoding - System.Text.Encoding

default value: UTF8Encoding(false)
Gets or Sets a value indicates the encoding to be used for exporting the dump. Default = UTF8Coding(false)

ExportInfo.BlobExportMode - enum BlobDataExportMode

default value: BlobDataExportMode.HexString
Gets or Sets a enum value indicates how the BLOB should be exported.
HexString = Hexa Decimal String (default);
BinaryChar = char format.
Note: Export BLOB into Binary Char is not intended for real deploy usage at the moment. Exporting into BinaryChar will raise an exception which attempt to alarm the developers that this function is meant for development and debugging purposes.

ExportInfo.BlobExportModeForBinaryStringAllow - bool

default value: false
If you wish to help to debug, fix or develop the function of exporting BLOB into binary char format (BlobExportMode=BinaryChar), set this value to true.

ExportInfo.GetTotalRowsMode - enum BlobDataExportMode

default value: InformationSchema
Gets or Sets a value indicates the method of how the total rows value is being obtained.
This function is useful if you are developing a progress bar
. InformationSchema = Fast, but approximate value;
SelectCount = Slow but accurate;
Skip = Skip obtaining total rows. Use this option if you are not doing any progress report.

ExportInfo.EnableComment - Boolean

default value: True
Gets or Sets a value indicates whether the comments should be included in the dump content.

ExportInfo.InsertLineBreakBetweenInserts - Boolean

default value: false
Gets or Sets a value indicates whether the multiple combined inserts should be separated into multi lines.

Full List of ImportInfo Options

ImportInfo.IntervalForProgressReport - int

default value: 100
Gets or Sets a value the interval of miliseconds should MySqlBackup report the progress.

ImportInfo.IgnoreSqlError - bool

default value: false
Gets or Sets a value indicates whether SQL errors occurs in import process should be ignored.

ImportInfo.ErrorLogFile - string

default value: string.empty Gets or Sets the file path used to log error messages.

Clone this wiki locally