Click here to Skip to main content
Click here to Skip to main content

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

, 21 Nov 2014 Public Domain
Rate this:
Please Sign up or sign in to vote.
A tool to export and import MySQL database in .NET

Available at:

Contents

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

 


 

1. Introduction

This article introduce 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 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
  • Written in C#. Can be used in VB.NET and ASP.NET
  • Export/Import to/from MemoryStream
  • Customize Export/Import Behavior
  • Conditional Rows Export
  • Build-In Internal Encryption Function
  • Able Restore to New Non-Existed Database
  • Progress Report is Available for Both Export and Import Task.

Prerequisite / Dependencies

MySqlBackup.NET is built on top of MySQL dot net Connector/Net (MySql.Data.DLL)

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

3. Change Log

V2.0.6 (17 Nov 2014)

  • Fix bug: Percentage Completed in Progress Report of Import is incorrect.

V2.0.5 (20 Oct 2014)

  • Fix a bug, after 1st export or import, the subsequent new export process is unable to carry out.
  • Add Feature - Able to export/import from/to MemoryStream directly.
  • Add Feature - Able to change document's headers and footers for export process.
  • Add Feature - Able to exclude tables for export process.

V2.0.4 (12 May 2014)

  • Remove "SET GLOBALmax_allowed_packet". It will take effect on new connection, not on current.
  • Clean up. Remove old and inappropriate IntelliSense.
  • Minor update.

V2.0.3 (11 May 2014)

  • Fix bug: In older MySQL server, "SHOW CREATE TABLE" will return byte[] in stead of string.

V2.0.2 (07 Feb 2014)

  • Fix bug: MySqlBackup.NET will decrypt an empty line when encryption is enable and this causes exception.

V2.0.1 (26 Dec 2013)

  • Fix bug: TimeStamp is affected by TimeZone during import.
  • Fix bug: Exported Table Columns is not correct when manually specified in TablesToBeExportedDic or TablesToBeExportedList

V2.0 (24 Dec 2013)

  • Add
    • Able to Export/Import To/From MemoryStream, without saving as physical file.
  • Modify/Enhance
    • Encryption process is simplified. Thus, time required to complete a task is decrease. Internal encryption of V2.0 is not compatible with previous/older version.
    • Code structure is reorganized, bugs are more easier to detect and codes are more easier to maintain. MySqlBackup.NET is more stable than previous version.
  • Drop
    • Drop the internal functionality of Zipping the dump file. This makes MySqlBackup.NET less dependencies. Developers can choose their own solutions for zipping the dump files externally.
    • Drop the single functionality of Exporting BLOB as files.

[Full Change Log]


4. Background

This article is assumed that you have already familiar with MySQL dot net connector (MySql.Data.dll) with minimum knowledge that you are able to perform the four basic operation 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]


5. Basic Usage

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. 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:

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:

  • Import to new (non-exist) 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 false 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 exclude DEFINER.
ExcludeTables 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.

New feature in v2.0.5

GetDocumentHeaders(MySqlCommand) List<string> see demo app Gets the list of document headers. New feature in v2.0.5
SetDocumentHeaders(List<string>) List<string> see demo app

Sets the document headers.

New feature in v2.0.5

GetDocumentFooters() List<string> see demo app

Gets the document footers.

New feature in v2.0.5

SetDocumentFooters(List<string>) List<string> see demo app

Sets the document footers.

New feature in v2.0.5


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.
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.

 

 


6. Example of Using in ASP.NET

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

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:

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>"));

7. More Guides And Examples

More guides and examples available at the project site's documentation:
https://mysqlbackupnet.codeplex.com/documentation[^]

Below are some of the guides


8. History

  • 17 Nov 2014 - Release of V2.0.6
  • 20 Oct 2014 - Release of V2.0.5
  • 12 May 2014 - Release of V2.0.4
  • 11 May 2014 - Release of V2.0.3
  • 07 Feb 2014 - Release of V2.0.2
  • 26 Dec 2013 - Release of V2.0.1
  • 25 Jan 2013 - Release of V1.5.7
  • ... lots of changes ...
  • 19 Sep 2011 - Version 1.0 - Initial release

License

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

Share

About the Author

adriancs
Software Developer
Malaysia Malaysia
Writing programs is an art.

Comments and Discussions

 
QuestionUpdate vs Insert into PinmemberPatrice Dargenton10hrs 25mins ago 
AnswerRe: Update vs Insert into [modified] Pinprofessionaladriancs7hrs 59mins ago 
QuestionExport problem Pinmemberdonkey4427-May-14 4:45 
AnswerRe: Export problem Pinprofessionaladriancs27-May-14 19:47 
GeneralRe: Export problem Pinmemberdonkey4428-May-14 3:38 
GeneralRe: Export problem Pinprofessionaladriancs28-May-14 19:43 
GeneralRe: Export problem Pinprofessionaladriancs28-May-14 19:52 
QuestionMySqlBackup not found PinmemberChristopher Smit24-Feb-14 10:33 
AnswerRe: MySqlBackup not found Pinprofessionaladriancs24-Feb-14 16:50 
GeneralRe: MySqlBackup not found PinmemberChristopher Smit25-Feb-14 8:04 
GeneralRe: MySqlBackup not found Pinprofessionaladriancs26-Feb-14 14:20 
GeneralRe: MySqlBackup not found PinmemberChristopher Smit27-Feb-14 7:30 
GeneralRe: MySqlBackup not found Pinprofessionaladriancs4-Oct-14 0:09 
QuestionIn place of table structure I get 'System.Byte [];' Pinmembermchldi11-Feb-14 0:17 
AnswerRe: In place of table structure I get 'System.Byte [];' Pinmemberadriancs11-Feb-14 4:09 
SuggestionRe: In place of table structure I get 'System.Byte [];' Pinmembernatoya9-Apr-14 3:35 
GeneralRe: In place of table structure I get 'System.Byte [];' Pinprofessionaladriancs11-May-14 18:26 
AnswerRe: In place of table structure I get 'System.Byte [];' Pinprofessionaladriancs11-May-14 4:17 
AnswerRe: In place of table structure I get 'System.Byte [];' Pinprofessionaladriancs11-May-14 18:26 
QuestionData Damage PinmemberWinter Bugahod15-Jan-14 21:14 
AnswerRe: Data Damage Pinmemberadriancs15-Jan-14 21:19 
Bugutf-8 and arabic PinmemberAhmed Mohammed8-Jan-14 1:32 
GeneralRe: utf-8 and arabic [modified] Pinmemberadriancs8-Jan-14 4:02 
GeneralRe: utf-8 and arabic PinmemberAhmed Mohammed18-Jan-14 4:34 
QuestionMYSQLBackup net in visual basic PinmemberMember 105036144-Jan-14 11:13 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 21 Nov 2014
Article Copyright 2012 by adriancs
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid