This project has moved. For the latest updates, please go here.

Documentation for MySqlBackup.NET 1.4

Contents  

  1. Introduction 
  2. Change Log 
  3. Background 
  4. MySqlBackup Class Structure
  5. Examples of Using MySqlBackup 
  6. Using MySqlBackup with Progress Bar  
  7. How is MySqlBackup.dll made?

1. Introduction 

This article introduce a tool (DLL) that can backup/restore MySQL database in .NET Programming Language 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.

This tool is develop in C# but useable in VB.NET.

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

MySQL Administrator 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 Administrator as a backup tool is not a suitable solution for client or end-user.

On the other hand, MySqlDump is another tool that can customize within code to meet specific situation. However, MySqlDump has compatible problems while handling with Unicode characters, for example Korean, Japanese, Chinese and Russian characters. The data will corrupt during the encoding between MySQL database and MySqlDump. Besides, MySqlDump cannot be used for Web applications. As most providers forbid that, MySqlBackup will be helpful in building a web-based (ASP.NET) backup tool.  

This tool (MySqlBackup.dll) can helps developer to build the big button "Backup" for end-user (so everything done automatically) and solves the Unicode encoding error that might cause by MySqlDump.


2. Change Log   

 

Changes from V1.3.1 to V1.4 (13 Aug 2012)

 

  • (New) Able to export Stored Procedures, Functions, Events, Triggers, Views
  • (Change) Implementation of IDisposal is dropped.
  • (New) New Settings for Export - ExportStoredProcedures, ExportFunctions, ExportViews, ExportTriggers, ExportEvents . 
  • (New) New Settings for Import - IgnoreSqlErrors. 
  • (Change) ProgressChanged is split and replaced by ImportProgressChanged and ExportProgressChanged.
  • (New) Error informations is collected during import if IgnoreSqlErrors is set to true. 

Full change log: [Change Log]

 


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

 


4. MySqlBackup Class Structure 1.4 beta (13 August 2012) 

Constructors

Name Description
mtd.png MySqlBackup() Initializes a new instance of the MySqlBackup class.
mtd.png MySqlBackup(string) Initializes a new instance of the MySqlBackup class and MySqlConnection string.
mtd.png MySqlBackup(MySqlConnection) Initializes a new instance of the MySqlBackup class and MySqlConnection.
mtd.png MySqlBackup(MySqlCommand) Initializes a new instance of the MySqlBackup class and MySqlCommand.

Properties

  Name Description
prop.png DatabaseInfo 

Gets some basic information about current connected database.  

Available Fields:

 

  • string - CreateDatabaseSql (The CREATE statement of database.) 
  • string - DatabaseName 
  • Dictionary<string, Table> - Tables (Basic informations of tables.) 
  • string - ServerVersion (The version of MySQL database.) 
  • string[] - AllTableNames (The names of table in database.) 
  • long - TotalRows (Total rows in whole database. This is calculated during Export process with   CalculateTotalRowsFromDatabase set to true.) 
  • Dictionary <string, string> - StoredProcedure
  • Dictionary <string, string> - StoredFunction
  • Dictionary <string, string> - StoredTrigger 
  • Dictionary <string, string> - StoredEvents 
  • Dictionary <string, string> - StoredView  

Method 

 

  •  GetTotalRows() - Return the Total Rows from all tables. 

 

 

prop.png DatabaseName Gets the name of the database in current active connection.  
prop.png ConnectionString Gets or sets the MySQL Connection string used by this instance of the MySqlBackup.
prop.png Command Gets or sets the MySqlCommand used by this instance of the MySqlBackup.
prop.png Connection Gets or sets the MySqlConnection used by this instance of the MySqlBackup.
prop.png TablesToBeExported Gets or sets the tables that will be exported(backup). Used in Export process.
prop.png TableCustomSql Gets or Sets the Dictionary which defines the Tables that will be exported (backup) and the conditions (SELECT sql statement) that filter the selection of rows of data during the export (backup) process.
prop.png ExportRows Gets or Sets a value indicating whether the Rows in database should be exported (backup). Used in Export process only.
prop.png ExportTableStructure Gets or Sets a value indicating whether the Table's Structure should be exported (backup) by adding "DROP TABLE" and "CREATE TABLE" SQL statement in SQL Dump File. Used in Export process.
prop.png EnableEncryption Gets or Sets a value indicating whether the data will be encrypted (for export) or decrypted (for import). Used in both Export and Import process.
prop.png EncryptionKey Sets a string that will be used as key for encryption or decryption. Used in both Export and Import process.
prop.png ResetAutoIncrement Gets or Sets a value indicating whether all the tables should reset Auto Increment to 1. Used in Export process.
prop.png AutoCloseConnection Gets or Sets a value indicating whether the MySqlConnection should close after every process called from MySalBackup has completed. Used in both export and import process. 
prop.png CalculateTotalRowsFromDatabase Gets or Sets a value indicating whether MySqlBackup should calculate total rows before Export process. Used in Export process.
prop.png ExportStoredProcedures (1.4)  Gets or Sets a value indicating whether the Stored Procedures should be exported during Export process. Used in Export process. 
prop.png ExportFunctions (1.4)  Gets or Sets a value indicating whether the Stored Functions should be exported during Export process. Used in Export process. 
prop.png ExportViews  (1.4)     Gets or Sets a value indicating whether the Stored Views should be exported during Export process. Used in Export process. 
prop.png ExportTriggers  (1.4)     Gets or Sets a value indicating whether the Stored Triggers should be exported during Export process. Used in Export process. 
prop.png ExportEvents  (1.4)     Gets or Sets a value indicating whether the Stored Events should be exported during Export process. Used in Export process. 
prop.png IgnoreSqlErrors  (1.4)     Gets or Sets a value indicating whether the Import process should ignore any error that occur and continue to process. Used in Import process. 

Methods

  Name Description
mtd.png Export(string) Execute the backup process and save data into specific SQL Dump File.
mtd.png Export(string, string[]) Execute the backup process, backup specific tables and save data into specific SQL Dump File.
mtd.png Export(string, Dictionary<string,string>) Execute the backup process, backup specific tables, rows and columns and save data into specific SQL Dump file.
mtd.png Import(string) Execute the restore process and retrieve data from specific SQL Dump File.
mtd.png CancelExport() Cancel and stop the export(backup) process.
mtd.png CancelImport() Cancel and stop the import(restore) process.
mtd.png EncryptSqlDumpFile(string, string) Encrypt a SQL Dump File and the encrypted data into new File.
mtd.png DecryptSqlDumpFile(string, string) Decrypt an encrypted SQL Dump File and save the data into new SQL Dump File.
mtd.png ExportBlobAsFile(string, string, string, string, string) Export the BLOB value stored in MySQL database and save it as files at specific location. 

Events 

  Name Description
evt.png ExportProgressChanged (1.4) 

Occur when a row of data has been processed during Export.

Event Argument:

 

  • string - CurrentTableName
  • long - TotalRowsInCurrentTable
  • long - TotalRowsInAllTables
  • long - CurrentRowInCurrentTable
  • long - CurrentRowInAllTable
  • int - TotalTables
  • int - CurrentTableIndex 
  • long - CurrentByte
  • long - TotalBytes
  • int - PercentageCompleted
  • int - PercentageGetTotalRowsCompleted 

 

evt.png ImportProgressChanged (1.4) 

Occur when a line of data has been processed during Import.

Event Arguments: 

 

  • long - CurrentByte 
  • long - TotalBytes 
  • Exception - ImportErrors 
  • long - ErrorLineNo 
  • string - ErrorSql 
  • int - PercentageCompleted 

 

evt.png ProgressCompleted

Occur when the Export or Import process is completed, an exception is caught or the process is cancelled.

Event Argument Field & Properties:

 

  • DateTime - TimeStart
  • DateTime - TimeEnd  
  • enum - CompletedType (Completed, Cancelled, Error)
  • enum - ProcessType (Export, Import) 
  • Exception - ExceptionInfo 
  • Dictionary<long, Exception> - Exceptions 
  • TimeSpan - TimeUsed  

 

 


5. Examples of Using MySqlBackup 

Note: MySql.Data.dll (MySQL ADO.NET Connector) version 6.5.4.0 (which is the latest at this time) is used to compile this tool. If you are using other version of MySql.Data.dll, you have to manually recompile MySqlBackup.dll with your version of MySql.Data.dll. 

Below describes 2 common method to add this programming tool.

Method 1: Add MySqlBackup.dll as Reference. 

Download the source code of MySqlBackup.dll at the top of this article. Locate MySqlBackup.dll inside the zip file. Add it as reference into your project.

  

  

Method 2: Add all souce code files into your application.

Download the source code and add all the cs files directly into your projects. 

 

 

Examples: 

Add this line at the top of your class/application.  

using MySql.Data.MySqlClient;  

Below are the examples of using the tool (MySqlBackup.dll).

Example 1: Backup (Export) MySQL database.  

private void Backup()
{
    string file = "C:\\backup.sql";
    string conn = "server=localhost;user=root;pwd=1234;database=test;";
    MySqlBackup mb = new MySqlBackup(conn);
    mb.ProgressCompleted += new MySqlBackup.progressComplete(mb_ProgressCompleted);
    mb.Export(file);
}

void mb_ProgressCompleted(object sender, MySqlBackupCompleteArg e)
{
    string msg = e.CompletedType.ToString();
    if (e.ExceptionInfo != null)
        msg += e.ExceptionInfo.ToString();
    MessageBox.Show(msg);
}  

Example 2: Restore (Import) MySQL database  

public void Restore()
{
    string file = "C:\\backup.sql";
    string conn = "server=localhost;user=root;pwd=1234;database=test;";
    MySqlBackup mb = new MySqlBackup(conn);
    mb.ProgressCompleted += new MySqlBackup.progressComplete(mb_ProgressCompleted);
    mb.Import(file);
}

void mb_ProgressCompleted(object sender, MySqlBackupCompleteArg e)
{
    string msg = e.CompletedType.ToString();
    if (e.ExceptionInfo != null)
        msg += e.ExceptionInfo.ToString();
    MessageBox.Show(msg);
} 

Example 3: Select tables to backup

private void BackupCustomTable()
{
    string[] tables = new string[3];
    tables[0] = "member";
    tables[1] = "payment";
    tables[2] = "activity";
    string sqlDumpFile = "C:\\backup.sql";
    string con = "server=localhost;user=root;pwd=1234;database=member;"; 
    mb.ProgressCompleted += new MySqlBackup.progressComplete(mb_ProgressCompleted);
    MySqlBackup mb = new MySqlBackup(con);
    mb.Export(sqlDumpFile, tables);
}

void mb_ProgressCompleted(object sender, MySqlBackupCompleteArg e)
{
    string msg = e.CompletedType.ToString();
    if (e.ExceptionInfo != null)
        msg += e.ExceptionInfo.ToString();
    MessageBox.Show(msg);
} 

Example 4: Custom columns and rows backup conditions

private void BackupCustomColumnsRows()
{
    Dictionary<string, string> dic = new Dictionary<string, string>();
    dic.Add("member", "SELECT * FROM `member` WHERE `membertype` = 1;");
    dic.Add("payment", "SELECT `id`,`total` FROM `payment`;");
    dic.Add("activity", "SELECT * FROM `activity` a INNER JOIN `season` s ON a.`seasonid` = s.`id` WHERE s.`name` = 'Spring';");
    string sqlDumpFile = "C:\\backup.sql";
    string con = "server=localhost;user=root;pwd=1234;database=member;";
    MySqlBackup mb = new MySqlBackup(con);
    mb.ProgressCompleted += new MySqlBackup.progressComplete(mb_ProgressCompleted);
    mb.Export(sqlDumpFile, dic);
} 

void mb_ProgressCompleted(object sender, MySqlBackupCompleteArg e)
{
    string msg = e.CompletedType.ToString();
    if (e.ExceptionInfo != null)
        msg += e.ExceptionInfo.ToString();
    MessageBox.Show(msg);
} 

Example 5: Backup with Encryption

private void BackupEncrypt()
{
    string sqlDumpFile = "C:\\backup.sql";
    string con = "server=localhost;user=root;pwd=1234;database=member;";
    MySqlBackup mb = new MySqlBackup(con);
    mb.EnableEncryption = true;
    mb.EncryptionKey = "qwerty";
    mb.ProgressCompleted += new MySqlBackup.progressComplete(mb_ProgressCompleted);
    mb.Export(sqlDumpFile);
}

void mb_ProgressCompleted(object sender, MySqlBackupCompleteArg e)
{
    string msg = e.CompletedType.ToString();
    if (e.ExceptionInfo != null)
        msg += e.ExceptionInfo.ToString();
    MessageBox.Show(msg);
} 

Example 6: Restore with Decryption

private void RestoreDecrypt()
{
    string sqlDumpFile = "C:\\backup.sql";
    string con = "server=localhost;user=root;pwd=1234;database=member;";
    MySqlBackup mb = new MySqlBackup(con);
    mb.EnableEncryption = true;
    mb.EncryptionKey = "qwerty";
    mb.ProgressCompleted += new MySqlBackup.progressComplete(mb_ProgressCompleted);
    mb.Import(sqlDumpFile);
}

void mb_ProgressCompleted(object sender, MySqlBackupCompleteArg e)
{
    string msg = e.CompletedType.ToString();
    if (e.ExceptionInfo != null)
        msg += e.ExceptionInfo.ToString();
    MessageBox.Show(msg);
} 

Example 7: Encrypt the Dump File

private void EncryptDumpFile()
{
    string oldDumpFile = "C:\\backup.sql";
    string newDumpFile = "C:\\backup_new.sql";
    MySqlBackup mb = new MySqlBackup();
    mb.EnableEncryption = true;
    mb.EncryptionKey = "qwerty";
    mb.EncryptSqlDumpFile(oldDumpFile, newDumpFile);
}

Example 8: Decrypt the Dump File.

private void DecryptDumpFile()
{
    string oldDumpFile = "C:\\backup.sql";
    string newDumpFile = "C:\\backup_new.sql";
    MySqlBackup mb = new MySqlBackup();
    mb.EnableEncryption = true;
    mb.EncryptionKey = "qwerty";
    mb.DecryptSqlDumpFile(oldDumpFile, newDumpFile);
}

Example 9: Backup Table Structures without rows and reset auto-increment to 1.

private void BackupEncrypt()
{
    string sqlDumpFile = "C:\\backup.sql";
    string con = "server=localhost;user=root;pwd=1234;database=member;";
    MySqlBackup mb = new MySqlBackup(con);
    mb.ExportTableStructure = true;
    mb.ExportRows = false;
    mb.ResetAutoIncrement = true;
    mb.ProgressCompleted += new MySqlBackup.progressComplete(mb_ProgressCompleted);
    mb.Export(sqlDumpFile);
}

void mb_ProgressCompleted(object sender, MySqlBackupCompleteArg e)
{
    string msg = e.CompletedType.ToString();
    if (e.ExceptionInfo != null)
        msg += e.ExceptionInfo.ToString();
    MessageBox.Show(msg);
} 

Example 10: Export BLOB data and save as files on local drive.

private void BackupEncrypt()
{
    string folder = "C:\\uploadfiles";
    string table = "upload";
    string column_Blob = "blobdata";
    string column_FileName = "filename";
    string column_FileSize = "filesize";
    string con = "server=localhost;user=root;pwd=1234;database=member;";
    MySqlBackup mb = new MySqlBackup(con);
    mb.ExportBlobAsFile(folder, table, column_Blob, column_FileName, column_FileSize);
    MessageBox.Show("Done.");
}

Example 11: Get all tables' name from database.

private string[] GetTableNames()
{
    string con = "server=localhost;user=root;pwd=1234;database=member;";
    MySqlBackup mb = new MySqlBackup(con);
    return mb.DatabaseInfo.AllTableNames;
}

Example 12: Get Create Table sql statement for specific table.

private string GetCreateTable(string tableName)
{
    string con = "server=localhost;user=root;pwd=1234;database=member;";
    MySqlBackup mb = new MySqlBackup(con);
    return mb.DatabaseInfo.Tables[tableName].CreateTableSql;
}

Example 13: Gather Error Message during Import (New)

private void Restore()
{
    string conn = "server=localhost;user=root;pwd=qwerty;database=test;";
    MySqlBackup mb = new MySqlBackup(conn);
    mb.IgnoreSqlErrors = true;
    mb.ImportProgressChanged += new MySqlBackup.importProgressChange(mb_ImportProgressChanged);
    mb.ProgressCompleted += new MySqlBackup.progressComplete(mb_ProgressCompleted);
}

void mb_ProgressCompleted(object sender, MySqlBackupCompleteArg e)
{
    MessageBox.Show(e.ProcessType.ToString() + " " + e.CompletedType.ToString());
}

void mb_ImportProgressChanged(object sender, ImportProgressArg e)
{
    StringBuilder sb = new StringBuilder();
    if (e.ImportErrors != null)
    {
        sb.Append("Line " + e.ErrorLineNo + ": " + e.ImportErrors.Message);
    }
    MessageBox.Show(sb.ToString(), "Error occur during Import");
}

Note:

Settings that used by Export() 

 

  • AutoCloseConnection 
  • TablesToBeExported
  • TableCustomSql
  • AddCreateDatabase 
  • ExportRows
  • ExportTableStructure 
  • EnableEncryption
  • EncryptionKey
  • ResetAutoIncrement
  • CalculateTotalRowsFromDatabase
  • ExportFunctions
  • ExportStoredProcedures
  • ExportViews
  • ExportTriggers
  • ExportEvents 

 

Settings that used by Import() 

 

  • AutoCloseConnection  
  • EnableEncryption
  • EncryptionKey 
  • IgnoreSqlErrors 

 


 

6. Using MySqlBackup with Progress Bar

Progress reporting is a new feature of MySqlBackup in release version of 1.3. This enable the usage of Progress Bar.  

Simple walk-through: 

  1. MySqlBackup will raise the an event (ProgressChanged) after a row or line of data processed.
  2. The event carries a bunch of values indicating the current processing point.
  3. These values will load into a temporary storage location. 
  4. Use a timer to read values from the temporary storage location and load the values into Progress bar. 
  5. After MySqlBackup is finished processing, it will raise another event (ProgressCompleted) to stop the timer
  6. ProgressCompleted will also be raised if the process (Export or Import) is cancelled or it catches an exception. 

Detail walk-through:  

This guide is shown in WinForm.  

Start by adding this line at the top of the form class. 

using MySql.Data.MySqlClient; 

This is what we have initially with a new WinForm. 

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    }
}

Declare some fields for storing the progress value, an instance of MySqlBackup, a timer and a boolean value (used to stop the timer).

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        // Temporary Storage Location
        string CurrentTableName = "";
        long TotalRowsInCurrentTable = 0;
        long TotalRowsInAllTables = 0;
        long CurrentRowInCurrentTable = 0;
        long CurrentRowInAllTable = 0;
        int TotalTables = 0;
        int CurrentTableIndex = 0;
        long CurrentByte = 0;
        long TotalBytes = 0;
        int PercentageComplete = 0;
        int PercentageGetTotalRowsCompleted = 0;

        MySqlBackup mb;

        Timer timerExport = new Timer();
        Timer timerImport = new Timer();

        bool stopTimer1 = false;

        public Form1()
        {
            InitializeComponent();
        }
    }
}

Build the Backup method. 

private void Backup()
{
    string file = "C:\\backup.sql";
    string connection = "server=localhost;user=root;pwd=1234;database=test;";

    mb = new MySqlBackup(connection);
    mb.ProgressCompleted += new MySqlBackup.progressComplete(mb_ProgressCompleted);
    mb.ExportProgressChanged += new MySqlBackup.exportProgressChange(mb_ExportProgressChanged);

    stopTimer1 = false;

    mb.CalculateTotalRowsFromDatabase = true;

    timerExport.Start();
    mb.Export(file);
}

void mb_ExportProgressChanged(object sender, ExportProgressArg e)
{
    throw new NotImplementedException();
}

void mb_ProgressCompleted(object sender, MySqlBackupCompleteArg e)
{
    stopTimer1 = true;
    string msg = e.CompletedType.ToString();
    if (e.ExceptionInfo != null)
        msg += e.ExceptionInfo.ToString();
    MessageBox.Show(msg);
}

During the ProgressChanged event,  load the event's argument values into Temporary Storage Location. 

void mb_ExportProgressChanged(object sender, MySqlBackupProgresstArg e)
{
    PercentageGetTotalRowsCompleted = e.PercentageGetTotalRowsCompleted;
    PercentageComplete = e.PercentageCompleted;
    CurrentTableName = e.CurrentTableName;
    TotalRowsInCurrentTable = e.TotalRowsInCurrentTable;
    TotalRowsInAllTables = e.TotalRowsInAllTables;
    CurrentRowInCurrentTable = e.CurrentRowInCurrentTable;
    CurrentRowInAllTable = e.CurrentRowInAllTable;
    TotalTables = e.TotalTables;
    CurrentTableIndex = e.CurrentTableIndex;
}

Then, use and call timerExport to load values from the Temporary Storage Location into Progress Bar.

public Form1()
{
    InitializeComponent();

    timerExport.Interval = 100; // Refresh Progress Bar 10 times in 1 second
    timerExport.Tick += new EventHandler(timerExport_Tick);
}

void timerExport_Tick(object sender, EventArgs e)
{
    progressBar_PercentComplete.Maximum = 100;
    progressBar_PercentComplete.Value = PercentageComplete;

    progressBar_Table.Maximum = TotalTables * 10;
    progressBar_Table.Value = CurrentTableIndex * 10;

    progressBar_RowsDB.Maximum = (int)TotalRowsInAllTables;
    progressBar_RowsDB.Value = (int)CurrentRowInAllTable;

    progressBar_RowsTable.Maximum = (int)TotalRowsInCurrentTable;
    progressBar_RowsTable.Value = (int)CurrentRowInCurrentTable;

    if (stopTimer1)
        timerExport.Stop();
}

You may add another method to stop the Export process just in case you need to cancel it. 

public void CancelBackup()
{
    stopTimer1 = true;
    mb.CancelExport();
} 

A more complete demo project is available for download at the top of this article. 

 


7. How is MySqlBackup.dll made?

There is a guide shows the design of MySqlBackup.dll version 1.1. You can read it here [The Basic Ideas].  The above guide describes the methods and design used in MySqlBackup.dll version 1.1. Version 1.2 has a large changes. However, the basic concept is almost the same. The guide includes:

  1. How to collect CREATE DATABASE sql statement.
  2. How to collect CREATE TABLE sql statements for each table. 
  3. How to build INSERT sql statements for each row in each table.
  4. Writes the collected sql statements into SQL Dump File.
  5. Reads the SQL Dump File and execute the sql statements.

Although the guide is for the older version (V1.1), but the guide contains some valuable information on backing up MySQL database manually.

Last edited Aug 13, 2012 at 9:14 AM by adriancs, version 1