MySqlBackup
Class Structure MySqlBackup
with Progress Bar
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.
Changes from V1.3.1 to V1.4 (13 Aug 2012)
IDisposal
is dropped.
ProgressChanged
is split and replaced by
ImportProgressChanged
and ExportProgressChanged
.
IgnoreSqlErrors
is set to true. Full change log: [Change Log]
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
. 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] DELETE
Constructors
Name | Description | |
---|---|---|
MySqlBackup() | Initializes a new instance of the MySqlBackup class. | |
MySqlBackup(string) | Initializes a new instance of the MySqlBackup class and MySqlConnection string. | |
MySqlBackup(MySqlConnection) | Initializes a new instance of the MySqlBackup class and MySqlConnection. | |
MySqlBackup(MySqlCommand) | Initializes a new instance of the MySqlBackup class and MySqlCommand. |
Properties
Name | Description | |
---|---|---|
DatabaseInfo |
Gets some basic information about current connected database. Available Fields:
Method
|
|
DatabaseName | Gets the name of the database in current active connection. | |
ConnectionString | Gets or sets the MySQL Connection string used by this instance of the MySqlBackup. | |
Command | Gets or sets the MySqlCommand used by this instance of the MySqlBackup. | |
Connection | Gets or sets the MySqlConnection used by this instance of the MySqlBackup. | |
TablesToBeExported | Gets or sets the tables that will be exported(backup). Used in Export process. | |
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. |
|
ExportRows | Gets or Sets a value indicating whether the Rows in database should be exported (backup). Used in Export process only. | |
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. |
|
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. | |
EncryptionKey | Sets a string that will be used as key for encryption or decryption. Used in both Export and Import process. | |
ResetAutoIncrement | Gets or Sets a value indicating whether all the tables should reset Auto Increment to 1. Used in Export process. | |
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. | |
CalculateTotalRowsFromDatabase | Gets or Sets a value indicating whether MySqlBackup should calculate total rows before Export process. Used in Export process. | |
ExportStoredProcedures (1.4) | Gets or Sets a value indicating whether the Stored Procedures should be exported during Export process. Used in Export process. | |
ExportFunctions (1.4) | Gets or Sets a value indicating whether the Stored Functions should be exported during Export process. Used in Export process. | |
ExportViews (1.4) | Gets or Sets a value indicating whether the Stored Views should be exported during Export process. Used in Export process. | |
ExportTriggers (1.4) | Gets or Sets a value indicating whether the Stored Triggers should be exported during Export process. Used in Export process. | |
ExportEvents (1.4) | Gets or Sets a value indicating whether the Stored Events should be exported during Export process. Used in Export process. | |
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 | |
---|---|---|
|
Export(string) | Execute the backup process and save data into specific SQL Dump File. |
|
Export(string, string[]) | Execute the backup process, backup specific tables and save data into specific SQL Dump File. |
|
Export(string, Dictionary<string,string>) | Execute the backup process, backup specific tables, rows and columns and save data into specific SQL Dump file. |
|
Import(string) | Execute the restore process and retrieve data from specific SQL Dump File. |
|
CancelExport() | Cancel and stop the export(backup) process. |
|
CancelImport() | Cancel and stop the import(restore) process. |
|
EncryptSqlDumpFile(string, string) | Encrypt a SQL Dump File and the encrypted data into new File. |
|
DecryptSqlDumpFile(string, string) | Decrypt an encrypted SQL Dump File and save the data into new SQL Dump File. |
|
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 | |
---|---|---|
ExportProgressChanged (1.4) |
Occur when a row of data has been processed during Export. Event Argument:
|
|
ImportProgressChanged (1.4) |
Occur when a line of data has been processed during Import. Event Arguments:
|
|
ProgressCompleted |
Occur when the Export or Import process is completed, an exception is caught or the process is cancelled. Event Argument Field & Properties:
|
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.
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()
Settings that used by Import()
Progress reporting is a new feature of MySqlBackup in release version of 1.3. This enable the usage of Progress Bar.
ProgressChanged
) after a row or line of data processed.
timer
to read values from the temporary storage location and load the values into Progress bar.
ProgressCompleted
) to stop the
timer
. ProgressCompleted
will also be raised if the process (Export or Import) is cancelled or it catches an exception.
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.
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:
CREATE DATABASE
sql statement. CREATE TABLE
sql statements for each table.
INSERT
sql statements for each row in each table.
Although the guide is for the older version (V1.1), but the guide contains some valuable information on backing up MySQL database manually.