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) 

Changes from V1.2 to V1.3

Changes from V1.1 to V1.2

 


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

Settings that used by Import() 

 

 


 

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:

Event arguments of ProgressChanged that used in Import: 

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.