Click here to Skip to main content
11,635,123 members (79,327 online)
Click here to Skip to main content

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

, 19 Jul 2015 Public Domain 231.9K 20.7K 314
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
  • Can be used in any .NET Languages.
  • Export/Import to/from MemoryStream
  • Conditional Rows Export (Filter Tables or Rows)
  • Build-In Internal Encryption Function
  • Able Restore to New Non-Existed Database
  • Progress Report is Available for Both Export and Import Task.
  • Able to export rows into different mode. (Insert, Insert Ignore, Replace, On Duplicate Key Update, Update)
  • Can be used directly in ASP.NET or web services.

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.9 (19 July 2015)

  • Improve Performance - Modify GetTotalRows() in MySqlDatabase.cs
  • Modify: The default value of ExportInfo.GetTotalRowsBeforeExport has changed to "TRUE". This is because coding for this section has changed. The time required to get total rows of all tables is only less than 1 second.
  • Fix a typo error in MySqlTable.cs: line 38 - Replace "INSRET" by "INSERT".
  • Modify behaviour of "UPDATE" export mode: If all fields are primary key, no rows will be exported.
  • Update behaviour of "ON DUPLICATE KEY UPDATE" export mode: If all fields are primary key, export behaviour will be changed to "INSERT IGNORE".

V2.0.8 (25 Feb 2015)

  • Fix bug: Parsing error occur when exporting timestamp value. This problem occured after adding the support for exporting time fraction.
  • Modify: Change export behavior for exporting tables that contain only primary keys when using RowsExportMode = OnDuplicateKeyUpdate.

V2.0.7.1 (26 Jan 2015)

  • Minor intellisense updated.
  • All functions are same as V2.0.7

V2.0.7 (25 Jan 2015)

  • New ExportInfo option - RowsExportMode
  • New ExportInfo option - WrapWithinTransaction
  • New Export Destination - Export to String/text
  • Able to reset or remain memory stream position while exporting to memory stream.
  • Added support for exporting Time Fraction (Microseconds).

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.

[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 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 = The default option. Recommended if exporting to new or empty database. If the primary key is existed, the process will halt;
INSERT IGNORE = If the primary key existed, skip it; Useful in finding missing rows.
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, else update 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 FALSE if using RowsExportMode = "INSERT" or "INSERTIGNORE" or "REPLACE", else TRUE.


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. Will only take effect if importing to new non-existed 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

  • 19 July 2015 - Release of V2.0.9
  • 25 Feb 2015 - Release of V2.0.8
  • 26 Jan 2015 - Release of V2.0.7.1
  • 25 Jan 2015 - Release of V2.0.7
  • 17 Nov 2014 - Release of V2.0.6
  • 20 Oct 2014 - Release of V2.0.5
  • ... 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.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
Fabricio Rodrigues10-Apr-13 13:49
memberFabricio Rodrigues10-Apr-13 13:49 
QuestionTablesToBeExported Pin
milos stojmenovic28-Feb-13 7:50
membermilos stojmenovic28-Feb-13 7:50 
AnswerRe: TablesToBeExported Pin
adriancs25-Dec-13 23:10
mvpadriancs25-Dec-13 23:10 
QuestionCompatible with mysqldump? Pin
Findel12-Feb-13 12:17
memberFindel12-Feb-13 12:17 
AnswerRe: Compatible with mysqldump? Pin
adriancs11-May-14 17:36
professionaladriancs11-May-14 17:36 
QuestionProblem with charset Pin
dawc1599511-Feb-13 2:31
memberdawc1599511-Feb-13 2:31 
AnswerRe: Problem with charset Pin
adriancs25-Dec-13 23:11
mvpadriancs25-Dec-13 23:11 
QuestionVb.Net Pin
lutzmann9-Jan-13 1:34
memberlutzmann9-Jan-13 1:34 
AnswerRe: Vb.Net Pin
adriancs12-Jan-13 21:18
mvpadriancs12-Jan-13 21:18 
AnswerRe: Vb.Net Pin
adriancs25-Jan-13 2:49
mvpadriancs25-Jan-13 2:49 
GeneralMy vote of 5 Pin
Mihai MOGA14-Dec-12 5:44
memberMihai MOGA14-Dec-12 5:44 
QuestionSmall changes in Code Pin
LaFoo11-Dec-12 22:33
memberLaFoo11-Dec-12 22:33 
AnswerRe: Small changes in Code Pin
adriancs12-Dec-12 0:04
memberadriancs12-Dec-12 0:04 
GeneralRe: Small changes in Code Pin
LaFoo12-Dec-12 1:37
memberLaFoo12-Dec-12 1:37 
Thanks for your reply.
I get the same results with MySql.Data.DLL versions 6.2.3.0 and the current 6.6.4.0.
Good to know that there might be different behaviours across the DLL versions.
GeneralRe: Small changes in Code Pin
adriancs13-Dec-12 20:15
memberadriancs13-Dec-12 20:15 
BugThere is already an open DataReader associated with this Connection which must be closed first Pin
gillesmaire6-Nov-12 3:47
membergillesmaire6-Nov-12 3:47 
GeneralRe: There is already an open DataReader associated with this Connection which must be closed first Pin
adriancs6-Nov-12 13:42
memberadriancs6-Nov-12 13:42 
GeneralRe: There is already an open DataReader associated with this Connection which must be closed first Pin
gillesmaire7-Nov-12 21:15
membergillesmaire7-Nov-12 21:15 
GeneralRe: There is already an open DataReader associated with this Connection which must be closed first Pin
adriancs6-Nov-12 21:18
memberadriancs6-Nov-12 21:18 
GeneralRe: There is already an open DataReader associated with this Connection which must be closed first Pin
gillesmaire7-Nov-12 21:10
membergillesmaire7-Nov-12 21:10 
GeneralRe: There is already an open DataReader associated with this Connection which must be closed first Pin
adriancs7-Nov-12 22:55
memberadriancs7-Nov-12 22:55 
GeneralRe: There is already an open DataReader associated with this Connection which must be closed first Pin
adriancs30-Dec-12 19:17
memberadriancs30-Dec-12 19:17 
SuggestionInteresting but ... MySql? Pin
BlackMilan24-Oct-12 0:10
memberBlackMilan24-Oct-12 0:10 
GeneralRe: Interesting but ... MySql? Pin
adriancs24-Oct-12 1:18
memberadriancs24-Oct-12 1:18 
GeneralRe: Interesting but ... MySql? Pin
BlackMilan24-Oct-12 3:37
memberBlackMilan24-Oct-12 3:37 
GeneralMy vote of 5 Pin
Jack_32119-Oct-12 3:13
memberJack_32119-Oct-12 3:13 
QuestionNo ExportInfo Pin
DThought17-Oct-12 19:41
memberDThought17-Oct-12 19:41 
AnswerRe: No ExportInfo Pin
adriancs17-Oct-12 19:54
memberadriancs17-Oct-12 19:54 
Questionproblem when exporting procedures and triggers Pin
DESIRE.NTAHIMPERA15-Oct-12 10:43
memberDESIRE.NTAHIMPERA15-Oct-12 10:43 
AnswerRe: problem when exporting procedures and triggers Pin
adriancs15-Oct-12 15:46
memberadriancs15-Oct-12 15:46 
GeneralRe: problem when exporting procedures and triggers Pin
DESIRE.NTAHIMPERA15-Oct-12 21:21
memberDESIRE.NTAHIMPERA15-Oct-12 21:21 
GeneralRe: problem when exporting procedures and triggers Pin
adriancs15-Oct-12 22:38
memberadriancs15-Oct-12 22:38 
GeneralRe: problem when exporting procedures and triggers Pin
DESIRE.NTAHIMPERA16-Oct-12 0:49
memberDESIRE.NTAHIMPERA16-Oct-12 0:49 
GeneralRe: problem when exporting procedures and triggers Pin
adriancs18-Oct-12 17:24
memberadriancs18-Oct-12 17:24 
GeneralMy vote of 5 Pin
Albarhami24-Sep-12 19:38
memberAlbarhami24-Sep-12 19:38 
GeneralMy vote of 5 Pin
Abinash Bishoyi24-Sep-12 14:10
memberAbinash Bishoyi24-Sep-12 14:10 
GeneralClaim you contribution adriancs at ohloh Pin
Abinash Bishoyi24-Sep-12 14:07
memberAbinash Bishoyi24-Sep-12 14:07 
GeneralMy vote of 3 Pin
phoohtoo23-Sep-12 23:58
memberphoohtoo23-Sep-12 23:58 
Questionencrypt Pin
phoohtoo23-Sep-12 23:58
memberphoohtoo23-Sep-12 23:58 
AnswerRe: encrypt Pin
Garth J Lancaster24-Sep-12 0:18
memberGarth J Lancaster24-Sep-12 0:18 
GeneralRe: encrypt Pin
phoohtoo24-Sep-12 0:21
memberphoohtoo24-Sep-12 0:21 
GeneralRe: encrypt Pin
adriancs14-Oct-12 22:22
memberadriancs14-Oct-12 22:22 
GeneralRe: encrypt Pin
phoohtoo16-Oct-12 23:22
memberphoohtoo16-Oct-12 23:22 
GeneralMy vote of 5 Pin
Kanasz Robert19-Sep-12 4:51
mvpKanasz Robert19-Sep-12 4:51 
GeneralMy vote of 5 Pin
Abinash Bishoyi17-Sep-12 6:20
memberAbinash Bishoyi17-Sep-12 6:20 
GeneralMy vote of 5 Pin
soulprovidergr16-Sep-12 23:09
membersoulprovidergr16-Sep-12 23:09 
GeneralMy vote of 4 Pin
Md. Marufuzzaman14-Sep-12 2:02
mentorMd. Marufuzzaman14-Sep-12 2:02 
Questionhelpful Pin
Albarhami13-Sep-12 4:09
memberAlbarhami13-Sep-12 4:09 
GeneralMy vote of 4 Pin
Albarhami13-Sep-12 4:09
memberAlbarhami13-Sep-12 4:09 
GeneralMy vote of 5 Pin
javedsmart11-Sep-12 21:38
memberjavedsmart11-Sep-12 21:38 

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
Web04 | 2.8.150728.1 | Last Updated 19 Jul 2015
Article Copyright 2012 by adriancs
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid