Click here to Skip to main content
15,867,141 members
Articles / Web Development / ASP.NET

MySqlBackup.NET - MySQL Backup Solution for C#, VB.NET, ASP.NET

Rate me:
Please Sign up or sign in to vote.
4.86/5 (129 votes)
24 Sep 2021Public Domain8 min read 846.3K   38.1K   374   347
A tool to export and import MySQL database in .NET

Available at:

Contents

  1. Introduction
  2. Features & Dependencies
  3. Background
  4. Basic Usage
  5. Example of Using in ASP.NET
  6. More Guides And Examples
  7. History

1. Introduction

This article introduces a tool (DLL) that can backup/restore MySQL database in C# or VB.NET and some sample codes on how to use it. It is an alternative to MySqlDump.

Another benefits of making this tool is, we don't have to rely on two small programs - MySqlDump.exe and MySql.exe to perform the backup and restore task. We will have better control on the output result.

The most common way to backup a MySQL Database is by using MySqlDump.exe and MySQL Workbench.

MySQL Workbench is good for developers, but, when it comes to client or end-user, the recommended way is to get every parameter preset and all they need to know is press the big button "Backup" and everything is done. Using MySQL Workbench as a backup tool is not a suitable solution for client or end-user.

On the other hand, MySqlDump.exe cannot be used for Web applications. As most web hosting providers forbid that, MySqlBackup.NET will be helpful in building a web-based (ASP.NET/Web-Services) backup tool.

2. Features & Dependencies

Features

  • Backup and Restore of MySQL Database
  • Can be used in any .NET Language
  • Export/Import to/from MemoryStream
  • Conditional Rows Export (Filter Tables or Rows)
  • Progress Report is Available for Both Export and Import Task
  • Able to export rows into different modes (Insert, Insert Ignore, Replace, On Duplicate Key Update, Update)
  • Can be used directly in ASP.NET or web services

Prerequisite / Dependencies

MySqlBackup.NET relies on the following component to work.

Option 1: MySql.Data (Connector/NET)

  • MySQL dot net Connector/Net (MySql.Data.DLL)
  • A reference of this DLL must be added into your project in order for to compile or work with MySqlBackup.NET.
  • MySql.Data.DLL is developed by Oracle Corporation, licensed under GPL License (http://www.gnu.org/licenses/old-licenses/gpl-2.0.html).
  • MySql.Data.DLL

Option 2: Devart Express (dotConnect)

  • Devart dotConnect for MySQL Express
  • A reference of this DLL must be added into your project in order for to compile or work with MySqlBackup.NET.
  • For license agreement, please read: https://www.devart.com/dotconnect/mysql/licensing-faq.html
  • Devart.Data.DLL
  • Devart.Data.MySql.DLL

Option 3: MySqlConnector (MIT)

  • MySqlConnector: High Performance MySQL Library for .NET
  • A reference of this DLL must be added into your project in order for to compile or work with MySqlBackup.NET.
  • Project URL: https://github.com/mysql-net/MySqlConnector
  • Licensed under MIT
  • MySqlConnector.DLL

3. Background

This article assumes that you are already familiar with MySQL dot net connector (MySql.Data.dll) with minimum knowledge that you are able to perform the four basic operations, SELECT, INSERT, UPDATE, and DELETE. In case you are not, you can read the walk-through and explanation on Connecting C# to MySQL at: [http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL].

4. Basic Usage

Add this using statement before coding with MySqlBackup.NET:

C#
using MySql.Data.MySqlClient; 

Simple Export Example

C#
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

C#
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();
        }
    }
}

The above examples will export and import a MySQL database with default options. 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:

  • Create new database
  • Only export table's structures
  • Don't export rows of data

Sample Codes:

C#
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);
        }
    }
}

Full List of ExportInfo Options

VB.NET
ExportInfo.GetDocumentHeaders(cmd)
  • return: List<string>
  • default value: Demonstrated in test app
  • Gets the list of document headers
VB.NET
ExportInfo.SetDocumentHeaders(List<string>)
  • Sets the document headers
VB.NET
ExportInfo.GetDocumentFooters()
  • return: List<string>
  • default value: demonstrated in test app
  • Gets the document footers
VB.NET
ExportInfo.SetDocumentFooters(List<string>)
  • Sets the document headers
VB.NET
ExportInfo.ExcludeTables - List<string>
  • default value: empty list
  • Gets or Sets the tables (black list) that will be excluded for export. The rows of these tables will not be exported too.
VB.NET
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.
VB.NET
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;
VB.NET
ExportInfo.RecordDumpTime - bool
  • default value: true
  • Gets or Sets a value indicates whether the Dump Time should be recorded in dump file
VB.NET
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.
VB.NET
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
VB.NET
ExportInfo.ExportTableStructure - bool
  • default value: true
  • Gets or Sets a value indicates whether the Table Structure (CREATE TABLE) should be exported.
VB.NET
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
VB.NET
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
VB.NET
ExportInfo.ExportRows - bool
  • default value: true
  • Gets or Sets a value indicates whether the Rows should be exported.
VB.NET
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
VB.NET
ExportInfo.ExportProcedures - bool
  • default value: true
  • Gets or Sets a value indicates whether the Stored Procedures should be exported
VB.NET
ExportInfo.ExportFunctions - bool
  • default value: true
  • Gets or Sets a value indicates whether the Stored Functions should be exported
VB.NET
ExportInfo.ExportTriggers - bool
  • default value: true
  • Gets or Sets a value indicates whether the Stored Triggers should be exported
VB.NET
ExportInfo.ExportViews - bool
  • default value: true
  • Gets or Sets a value indicates whether the Stored Views should be exported
VB.NET
ExportInfo.ExportEvents - bool
  • default value: true
  • Gets or Sets a value indicates whether the Stored Events should be exported
VB.NET
ExportInfo.IntervalForProgressReport - int
  • default value: 100
  • Gets or Sets a value indicates the interval of time (in miliseconds) to raise the event of ExportProgressChanged
VB.NET
ExportInfo.ScriptsDelimiter - string
  • default value: |
  • Gets or Sets the delimiter used for exporting Procedures, Functions, Events and Triggers
VB.NET
ExportInfo.ExportRoutinesWithoutDefiner - bool
  • default value: true
  • Gets or Sets a value indicates whether the exported Scripts (Procedure, Functions, Events, Triggers, Events) should exclude DEFINER
VB.NET
ExportInfo.RowsExportMode - enum RowsDataExportMode
  • default value: Insert
  • Gets or Sets an 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 does not exist, skip it and if all the fields are primary key, no rows will be exported.
VB.NET
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.
VB.NET
ExportInfo.TextEncoding - System.Text.Encoding
  • default value: UTF8Encoding(false)
  • Gets or Sets a value indicates the encoding to be used for exporting the dump. 
VB.NET
ExportInfo.BlobExportMode - enum BlobDataExportMode
  • default value: BlobDataExportMode.HexString
  • Gets or Sets an enum value indicates how the BLOB should be exported.
  • 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 attempts to alarm the developers that this function is meant for development and debugging purposes. Read more: https://github.com/MySqlBackupNET/MySqlBackup.Net/issues/47
VB.NET
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
VB.NET
ExportInfo.GetTotalRowsMode - enum GetTotalRowsMethod
  • 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.
C#
ExportInfo.EnableComment - Boolean
  • default value: True
  • Gets or Sets a value indicates whether the comments should be included in the dump content.

Full List of ImportInfo Options

VB.NET
ImportInfo.IntervalForProgressReport - int
  • default value: 100
  • Gets or Sets a value indicates the interval of time (in milliseconds) to raise the event of ExportProgressChanged
VB.NET
ImportInfo.IgnoreSqlError - bool
  • default value: false
  • Gets or Sets a value indicates whether SQL errors occurs in import process should be ignored
VB.NET
ImportInfo.ErrorLogFile - string
  • default value: string.empty
  • Gets or Sets the file path used to log error messages

5. Example of Using in ASP.NET

Sample code for Export. The below codes will export the content into MemoryStream, then transmit it directly for download.

C#
using System.IO;

string connstr = "server=localhost;user=root;pwd=1234;database=test;";
MemoryStream ms = new MemoryStream();
using (MySqlConnection conn = new MySqlConnection(connstr))
{
    MySqlCommand cmd = new MySqlCommand();
    MySqlBackup mb = new MySqlBackup(cmd);
    cmd.Connection = conn;
    conn.Open();
    mb.ExportToMemoryStream(ms);
}
Response.ContentType = "text/plain";
Response.AppendHeader("Content-Disposition", "attachment; filename=backup.sql");
Response.BinaryWrite(ms.ToArray());
Response.End();

Sample code for Upload and Import:

C#
string connstr = "server=localhost;user=root;pwd=1234;database=test;";
byte[] ba = FileUpload1.FileBytes;
MemoryStream ms = new MemoryStream(ba);
using (MySqlConnection conn = new MySqlConnection(connstr))
{
    MySqlCommand cmd = new MySqlCommand();
    MySqlBackup mb = new MySqlBackup(cmd);
    cmd.Connection = conn;
    conn.Open();
    mb.ExportToMemoryStream(ms);
}
Header.Controls.Add(new LiteralControl
     ("<script type=\"text/javascript\">alert('ok');</script>"));

6. More Guides And Examples

More guides and examples are available at the project site's documentation:
https://github.com/MySqlBackupNET/MySqlBackup.Net

Below are some of the guides.

7. History

License

This article, along with any associated source code and files, is licensed under A Public Domain dedication


Written By
Software Developer
Other Other
Programming is an art.

Comments and Discussions

 
QuestionTimeout expired - export error Pin
Golden Basim11-Jul-23 23:04
professionalGolden Basim11-Jul-23 23:04 
QuestionHow to set utf8mb4 for ExportInfo.TextEncoding Pin
Member 1059714115-Oct-22 7:46
Member 1059714115-Oct-22 7:46 
AnswerRe: How to set utf8mb4 for ExportInfo.TextEncoding Pin
adriancs9-Nov-22 23:08
mvaadriancs9-Nov-22 23:08 
QuestionNet does not recognize utf8mb3 Pin
HectorRodriguez8-Aug-22 12:50
HectorRodriguez8-Aug-22 12:50 
AnswerRe: Net does not recognize utf8mb3 Pin
adriancs30-Aug-22 17:23
mvaadriancs30-Aug-22 17:23 
GeneralRe: Net does not recognize utf8mb3 Pin
HectorRodriguez31-Aug-22 4:34
HectorRodriguez31-Aug-22 4:34 
GeneralRe: Net does not recognize utf8mb3 Pin
HectorRodriguez31-Aug-22 5:00
HectorRodriguez31-Aug-22 5:00 
GeneralRe: Net does not recognize utf8mb3 Pin
adriancs1-Sep-22 0:03
mvaadriancs1-Sep-22 0:03 
GeneralRe: Net does not recognize utf8mb3 Pin
adriancs1-Sep-22 0:17
mvaadriancs1-Sep-22 0:17 
GeneralRe: Net does not recognize utf8mb3 Pin
adriancs1-Sep-22 0:22
mvaadriancs1-Sep-22 0:22 
GeneralRe: Net does not recognize utf8mb3 Pin
HectorRodriguez1-Sep-22 3:00
HectorRodriguez1-Sep-22 3:00 
QuestionRemote Backup (save file) Pin
Member 139704628-Apr-22 8:46
Member 139704628-Apr-22 8:46 
QuestionSSL Connection error Pin
Salam Y. ELIAS26-Oct-21 1:29
professionalSalam Y. ELIAS26-Oct-21 1:29 
AnswerRe: SSL Connection error Pin
adriancs27-Oct-21 4:17
mvaadriancs27-Oct-21 4:17 
QuestionExportInfo.MaxSqlLength limitation Pin
Member 109498544-Oct-21 14:27
Member 109498544-Oct-21 14:27 
AnswerRe: ExportInfo.MaxSqlLength limitation Pin
adriancs5-Oct-21 1:56
mvaadriancs5-Oct-21 1:56 
Questionhow to Speeding up Export and import? Pin
Golden Basim9-Jan-21 7:57
professionalGolden Basim9-Jan-21 7:57 
AnswerRe: how to Speeding up Export and import? Pin
adriancs5-Oct-21 2:02
mvaadriancs5-Oct-21 2:02 
QuestionExport temporary columns Pin
Member 1329498016-Jun-20 20:25
Member 1329498016-Jun-20 20:25 
Questionthere are any method to know the database version before importing it ? Pin
Golden Basim28-Apr-20 4:11
professionalGolden Basim28-Apr-20 4:11 
AnswerRe: there are any method to know the database version before importing it ? Pin
adriancs9-Jun-20 1:20
mvaadriancs9-Jun-20 1:20 
AnswerRe: there are any method to know the database version before importing it ? Pin
adriancs5-Oct-21 1:58
mvaadriancs5-Oct-21 1:58 
QuestionEntry point was not found. Pin
ByWorth1-Apr-20 2:22
ByWorth1-Apr-20 2:22 
QuestionBackup only selected tables Pin
Member 1436045610-Feb-20 3:07
Member 1436045610-Feb-20 3:07 
AnswerRe: Backup only selected tables Pin
Member 1436045617-Feb-20 3:13
Member 1436045617-Feb-20 3:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.