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

Documentation for V1.3

Contents 

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

 

1. Change Log  

Changes from V1.3 to V1.3.1 (09 Aug 2012) 

  • (Fix Bugs) MySQL data type of "Date" and "Time" is not handled. 
  • TextReader and TextWriter is not closed if exception occur. 
  • (New) Implement iDisposal. Automatic close all streams in any condition during Export or Import halted.
  • (New) New option: AddCreateDatabase (Used in Export process), Default value = false

Changes from V1.2 to V1.3

  • (Fix bug) The symbol "." (dot) might not be used as default decimal point separator when custom System.Globalization.NumberFormatInfo or custom Application's Culture applied. The will cause a SQL syntax error. 
  • (New) Export and Import process are now run asynchronously.
  • (New) Export and Import process can be cancelled.
  • (New) Export and Import process will report progress. Enable the usage of progress bar.
  • (New) max_allowed_packet will be set to 1GB (Maximum length allowed by MySQL in single query) during Export or Import process if the MySQL user used in the connection has the super privilege to do so. This is useful when exporting or importing large BLOB or TEXT data type.
  • (New) Has option to reset Auto-Increment.
  • (New) Has option to close MySqlConnection automatically after each process finished.
  • (New) Able to export BLOB data and save as files on local drive.  
  • (Change) XCrypt project (an Encryption Library) is dropped from MySqlBackup. Encryption is not compatible between v1.2 and v1.3    

Changes from V1.1 to V1.2

  • Comments are added into SQL Dump File.
  • All SQL statements are split into lines.
  • Multiple INSERT statements are joined.
  • Able to export(backup) BLOB data type.
  • Date Time conversion bug fixed.
  • SQL Dump File encoding change from UTF8 to UTF8 without BOM.
  • SQL statements are divided into paragraphs, more neat and organized. 
  • Perform MySQL server variable checking before export/import.
  • Replace MySqlDataAdapter with MySqlDataReader.
  • Replace File.WriteAllLines  with TextWriter > StreamWriter 
  • Replace File.ReadAllLines with TextReader > StreamReader 
  • Able to export (backup) selected tables, columns and rows.
  • Able to encrypt and decrypt SQL Dump File.
  • Encryption is enhanced by adding salt. 

 


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

 


3. MySqlBackup Class Structure

4. MySqlBackup Class Structure 1.3.1 (09 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.) 

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 AddCreateDatabase  Gets or Sets a value indicating whether the MySqlBackup  should add SQL statement of CREATE DATABASE. Used in export 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 ProgressChanged

Occur when a row or a line of data has been processed.

Event Argument Properties:

 

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

 

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 
  • TimeSpan - TimeUsed 

 

 


4. 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;
}

Note:

Settings that used by Export() 

  • AddCreateDatabase
  • AutoCloseConnection
  • TablesToBeExported
  • TableCustomSql
  • ExportRows
  • ExportTableStructure 
  • EnableEncryption
  • EncryptionKey
  • ResetAutoIncrement
  • CalculateTotalRowsFromDatabase

Settings that used by Import() 

  • AutoCloseConnection  
  • EnableEncryption
  • EncryptionKey 

 

 


 

5. 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.ProgressChanged += new MySqlBackup.progressChange(mb_ProgressChanged);

    stopTimer1 = false;

    mb.CalculateTotalRowsFromDatabase = true;

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

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

void mb_ProgressChanged(object sender, MySqlBackupProgresstArg e)
{
    throw new NotImplementedException();
}

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

void mb_ProgressChanged(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;
    CurrentByte = e.CurrentByte;
    TotalBytes = e.TotalBytes;
}

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

ProgressChanged applies to Export and Import process. 

Not all the ProgressChanged event arguments value apply the same in both Export and Import.

Event arguments of ProgressChanged that used in Export:

  • PercentageGetTotalRowsCompleted 
  • PercentageCompleted 
  • CurrentTableName 
  • TotalRowsInCurrentTable 
  • TotalRowsInAllTables 
  • CurrentRowInCurrentTable 
  • CurrentRowInAllTable 
  • TotalTables 
  • CurrentTableIndex 

Event arguments of ProgressChanged that used in Import: 

  • CurrentByte 
  • TotalBytes  

A more complete demo project is available for download at [Download] section.

 


6. 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 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 8:18 AM by adriancs, version 6