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

By , 9 Apr 2014
Rate this:
Please Sign up or sign in to vote.

Available at:

Contents

  1. Introduction
  2. Features & Dependencies
  3. Change Log
  4. Background
  5. Basic Usage
  6. Example of Using in ASP.NET
  7. Frequently Asked Question
  8. More Guides And Examples
  9. 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.

On the other hand, this tool uses native .NET language to handle all values and parameters, therefore it can handle Unicode/UTF8 character (multi-language) well.

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

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;
using System.Text;
using (MemoryStream ms = new MemoryStream())
{
    using (TextWriter tw = new StreamWriter(ms, new UTF8Encoding(false)))
    {
        using (MySqlConnection conn = new MySqlConnection(connectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                using (MySqlBackup mb = new MySqlBackup(cmd))
                {
                    cmd.Connection = conn;
                    conn.Open();
                    mb.ExportToTextWriter(tw);
                }
            }
        }

        Response.ContentType = "text/plain";
        Response.AppendHeader("Content-Disposition", "attachment; filename=backup.sql");
        Response.BinaryWrite(ms.ToArray());
        Response.End();
    }
}
Sample code for Upload and Import:
byte[] ba = FileUpload1.FileBytes;

using (MemoryStream ms = new MemoryStream(ba))
{
    using (TextReader tr = new StreamReader(ms))
    {
        using (MySqlConnection conn = new MySqlConnection(connectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                using (MySqlBackup mb = new MySqlBackup(cmd))
                {
                    cmd.Connection = conn;
                    conn.Open();
                    mb.ImportFromTextReader(tr);
                }
            }
        }
    }
}
Response.Write("<script type=\"text/javascript\">alert('Import Completed')</script>");

7. Frequently Asked Question

1. Cannot connect to website database. An exception Occur: Unable to connect to any of the specified MySQL hosts

By default, MySql at web hosting will only allow connection from "localhost". If you wish to connect to MySql using your computer (remote access), or other IP, you have to allow the MySql at your website to be connected by the specific IP (your IP). If the website is using CPanel, you may refer this guide:
2. MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time value to System.DateTime

This is not a bug. It is an expected behaviour. MySQL allow zero date time which means 0 year 0 month and 0 days, but in .NET world's concept, there is no such 0 year, 0 month and 0 day. The minimum possible date in .NET is 01-01-01. (Commonly occur when data type of "DATE" (not DATETIME) used in one of the table).

There are 2 ways to resolve this:

1st, by adding allowzerodatetime in the MySQL connection string (Recommended if you use MySqlBackup)
  • Example: server=localhost;user=root;pwd=qwerty;database=test;allowzerodatetime=true;
2nd, by adding convertzerodatetime in MySQL connection string.
  • Example: server=localhost;user=root;pwd=qwerty;database=test;convertzerodatetime=true;

3. Connection Timeout Error - Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The default connection time of MySqlConnection is 15 seconds. You can extend it to a longer time. For example:
  • server=localhost;user=root;pwd=1234;database=test;timeout=120;

Read More: http://dev.mysql.com/doc/refman/5.6/en/connector-net-connection-options.html

4. Error - Packets larger than max_allowed_packet are not allowed..

This means the length of query is larger than the maximum length limit. The default maximum length in single SQL query is 1MB (1024 x 1024 bytes). You can change this in two ways:

1st way: Execute the Query to modify the value. Example:

Set the length limit to 32MB:
SET GLOBAL max_allowed_packet=32*1024*1024;
Set the length limit to 1GB (The largest value allowed in MySQL Server):
SET GLOBAL max_allowed_packet=1024*1024*1024;

Somethings you need to aware:

  • The USER that you used to connect to MySQL Server needs to have the privilege (Administration Rights) to modify any GLOBAL variables.
  • Changes will take effect on New connection, not on Current connection.
  • The changes will reset once the MySQL Server is restarted.
Sample C# codes for modifying max_allowed_packet:
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        cmd.CommandText = "SET GLOBAL max_allowed_packet=32*1024*1024;";
        cmd.ExecuteNonQuery();

        // Close and Reopen the Connection
        conn.Close();
        conn.Open();

        // Start to take effect here...
        // Do something....

        conn.Close();
    }
} 

2nd way: Modify the option file - my.ini

1. Stop MySQL Server.
2. Open MySQL option file on the server - my.ini , under the section of [mysqld], add this line:

max_allowed_packet=32M 

3. Restart MySQL Server.
4. This will change the default value of max_allowed_packet to 32M for all connections.

Read more at MySQL Official Documentation: http://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html


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


9. History

  • 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

About the Author

adriancs
Software Developer
Malaysia Malaysia
Writing program is like another kind of art. Creating virtual life.

Comments and Discussions

 
QuestionMySqlBackup not found PinmemberChristopher Smit24-Feb-14 9:33 
AnswerRe: MySqlBackup not found Pinprofessionaladriancs24-Feb-14 15:50 
GeneralRe: MySqlBackup not found PinmemberChristopher Smit25-Feb-14 7:04 
GeneralRe: MySqlBackup not found Pinprofessionaladriancs26-Feb-14 13:20 
GeneralRe: MySqlBackup not found PinmemberChristopher Smit27-Feb-14 6:30 
QuestionIn place of table structure I get 'System.Byte [];' Pinmembermchldi10-Feb-14 23:17 
AnswerRe: In place of table structure I get 'System.Byte [];' Pinmemberadriancs11-Feb-14 3:09 
SuggestionRe: In place of table structure I get 'System.Byte [];' Pinmembernatoya9-Apr-14 2:35 
GeneralRe: In place of table structure I get 'System.Byte [];' Pinpremiumadriancs10-Apr-14 15:15 
QuestionData Damage PinmemberWinter Bugahod15-Jan-14 20:14 
AnswerRe: Data Damage Pinmemberadriancs15-Jan-14 20:19 
Bugutf-8 and arabic PinmemberAhmed Mohammed8-Jan-14 0:32 
GeneralRe: utf-8 and arabic [modified] Pinmemberadriancs8-Jan-14 3:02 
GeneralRe: utf-8 and arabic PinmemberAhmed Mohammed18-Jan-14 3:34 
QuestionMYSQLBackup net in visual basic PinmemberMember 105036144-Jan-14 10:13 
AnswerRe: MYSQLBackup net in visual basic Pinmemberadriancs4-Jan-14 13:25 
GeneralRe: MYSQLBackup net in visual basic PinmemberMember 105036145-Jan-14 7:02 
GeneralRe: MYSQLBackup net in visual basic PinmemberMember 105036145-Jan-14 7:31 
GeneralRe: MYSQLBackup net in visual basic Pinmemberadriancs5-Jan-14 10:17 
GeneralRe: MYSQLBackup net in visual basic PinmemberMember 105014885-Jan-14 21:44 
GeneralRe: MYSQLBackup net in visual basic Pinmemberadriancs5-Jan-14 22:32 
GeneralRe: MYSQLBackup net in visual basic PinmemberMember 105014886-Jan-14 4:37 
GeneralRe: MYSQLBackup net in visual basic PinmemberMember 105014886-Jan-14 4:44 
GeneralRe: MYSQLBackup net in visual basic [modified] Pinmemberadriancs6-Jan-14 14:37 
GeneralRe: MYSQLBackup net in visual basic PinmemberMember 105014886-Jan-14 21:58 

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 | Mobile
Web04 | 2.8.140415.2 | Last Updated 9 Apr 2014
Article Copyright 2012 by adriancs
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid