MySqlBackup
Class Structure MySqlBackup
with Progress Bar
Changes from V1.3 to V1.3.1 (09 Aug 2012)
Date
" and "Time
" is not handled.
TextReader
and TextWriter
is not closed if exception occur.
iDisposal
. Automatic close all streams in any condition during Export or Import halted.
AddCreateDatabase
(Used in Export process), Default value = false
Changes from V1.2 to V1.3
System.Globalization.NumberFormatInfo
or custom Application's Culture applied. The will cause a SQL syntax error.
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. Changes from V1.1 to V1.2
INSERT
statements are joined. MySqlDataAdapter
with MySqlDataReader
. File.WriteAllLines
with TextWriter > StreamWriter
File.ReadAllLines
with TextReader > StreamReader
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
4. MySqlBackup Class Structure 1.3.1 (09 August 2012)
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. | |
AddCreateDatabase | Gets or Sets a value indicating whether the MySqlBackup should add SQL statement of CREATE DATABASE. Used in export 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 | |
---|---|---|
ProgressChanged |
Occur when a row or a line of data has been processed. Event Argument Properties:
|
|
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; }
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.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.
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:
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.