Available at:
Contents
- Introduction
- Features, Limitation & Dependencies
- Change Log
- Background
- MySqlBackup.NET 1.5 Class Structure
- Adding MySqlBackup.NET Into Your Project
- Basic Examples
- Examples of Using MySqlBackup.NET in ASP.NET
- About Synchronous And Asynchronous Mode
- Using MySqlBackup.NET with Progress Bar
- Gathering Information After Export/Import Process Completed
- About Importing (Restoring) to Another/New Database
- MySqlBackup.dll V1.1
- Frequently Asked Questions (FAQ)
- Development of the project.
- History
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.NET will be helpful in building a web-based (ASP.NET) backup tool.
2. Features, Limitation & Dependencies
Features
- Export/Import Table's Structures & Rows
- Export/Import Stored Procedures, Functions, Triggers, Events, Views
- Custom Tables and Rows Export.
- Able to apply encryption to the process.
- Export BLOB and save as files.
- Gather SQL Syntax errors during Import process.
- Export/Import will report progress. Enable the usage of progress bar.
- Able to execute in Synchronous or Asynchronous mode.
- Export/Import To/From Zip File.
Limitation
Cannot handle multiple MEDIUMTEXT and MEDIUMBLOB data type. (some where more than 18MB length in single SQL query)
Multiple MEDIUMTEXT and MEDIUMBLOB will make (a single SQL) System.String and MySqlCommand.CommandText long enough to cause System.OutOfMemoryException.
This is not limited by the number of rows. MySqlBackup.NET can backup & restore millions of rows.
However, one thing for sure is, if the length of SQL can be inserted into MySQL database by any .NET application, MySqlBackup.NET is able to do so. Conversely, if you can't insert the SQL (because of the length) by any .NET apps, MySqlBackup.NET too can't.
If the user used in the MySql Connection has super privilege, then MySqlBackup.NET will set the max_allowed_packet to 1GB, which is the maximum length of single query of MySQL.
MySqlBackup.NET is currently not able to backup & restore LONGBLOB and LONGTEXT data type.
It is somehow wondering that:
The computer has 4GB of RAM and there are still 2.5GB free, why the software still raise the exception telling that it is Out Of Memory?
After some findings, 1 of the useful information may describe the problem:
Maybe there are some techniques out there that can tweaks the memory handling which allow a System.String able to hold a length of data up to 1GB.
Or maybe, there is another method to replace this. However, this is still under investigation.
Dependencies
MySqlBackup.NET is built on top the following tools/technologies:
- MySQL dot net Connector/Net (http://www.mysql.com)
- A reference of this DLL must be added into your project in order for MySqlBackup.NET to work.
- Released Precompiled MySqlBackup.DLL requires Connector/Net Version 6.6.4 (MySql.Data.DLL). MySql.Data.DLL is developed by Oracle Corporation and is licensed under GPL License (http://www.gnu.org/licenses/old-licenses/gpl-2.0.html).
- DotNetZip (http://dotnetzip.codeplex.com/)
- This class library enables MySqlBackup.NET compress the Exported SQL Dump File into Zip file.
- It also enables MySqlBackup.NET extract the dump file from a zip file.
- DotNetZip is licensed under Microsoft Public License.
3. Change Log
V1.5.7 (25 Jan 2013)
- Fix Bug: An error will occur when ExportInfo.AsynchronousMode = true and ExportIfo.CalculateTotalRowsFromDatabase = false are set at the same time.
V1.5.6 (14 Dec 2012)
- Fix Bug: If encryption is applied in Export Process, the generated encrypted SQL file is not able to import Stored Procedures, Functions, Triggers, Events and View.
> Discussion: MySqlBackup.dll
- Fix Bug: In some unknown cases, the SHOW CREATE TABLE `tablename` query will return byte array.
> http://www.codeproject.com/Messages/4450086/Small-changes-in-Code.aspx
- Improve 1: During Export, StreamWriter is opened and closed several times when writting to the dump file, which this is considered as not a good practice.
- Improve 2: SQL line in class Database method GetEvents: "SHOW EVENTS WHERE Db LIKE '" + DatabaseName + "';" might potentially cause errors due to case sensitivity.
- New Feature: MySqlBackup.NET is able to export as compressed zip file which results approximately 10 times smaller(file size) than original exported dump file.
New option: ExportInfo.ZipOutputFile.
- MySqlBackup.NET can import a compressed SQL dump file in zip format.
V1.5.5 (11 Dec 2012)
[Full Change Log]
4. 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
5. MySqlBackup.NET Class Structure 1.5.6 beta (14 December 2012)
Constructor
- MySqlBackup()
- MySqlBackup(string)
- MySqlBackup(MySqlConnection)
- MySqlBackup(MySqlCommand)
Public Properties
- Connection - MySqlConnection - Gets or Sets the MySqlConnection that used by this instance.
- DatabaseInfo - Database - Gets the infomations about the connected database.
- Properties, Fields, Methods
- CreateDatabaseSql - string - Gets the SQL Statement of CREATE DATABASE of this database.
- DatabaseName - string - Gets the Name of this Database.
- Tables - Dictionary<string, Table> - Gets all the Tables' information. Key = Table name; Value = Table.
- ServerVersion - string - Gets the MySQL Server Version's number and name.
- ServerVersionNo - string- Gets the MySQL Server Version's number.
- ServerMajorVersion - double - Gets the MSQL Server Major Version's number.
- DefaultDatabaseCharSet - string - Gets the default character set of current database.
- TableNames - string[] - Gets all table's name.
- StoredProcedure - Dictionary<string,string> - Gets or Sets Stored Procedures of current database.
- StoredFunction - Dictionary<string,string> - Gets or Sets Stored Functions of current database.
- StoredTrigger - Dictionary<string,string> - Gets or Sets Stored Triggers of current database;
- StoredEvents - Dictionary<string,string> - Gets or Sets Stored Events of current database.
- StoredView - Dictionary<string,string> - Gets or Sets Stored Views of current database.
- GetTotalRows - Long - Gets the total rows in all tables of current database.
- CalculateTotalRowsProgressChanged - Event - Occur when total rows calculation of 1 table has completed.
- CalculateTotalRowsCompleted - Event - Occur when total rows calculation of all table has completed.
- ExportInfo (ExportInformations) - Gets or Sets the Informations that define behaviour of Export Process. Read more at Class of ExportInformations below.
- Properties, Methods and Fields
- FileName -string - Gets or Sets the full path and file name (dump file) that will be saved to. The output of the export process.
- TableCustomSql - Dictionary<string,string> - Gets or Sets the tables that will be exported and the SQL for selecting the rows of the tables. If the SQL is left blank, the SQL will replace by "SELECT * FROM tablename". Key = tablename, Value = SQL
- TablesToBeExported - string[] - Gets or Sets the tables that will be exported
- RecordDumpTime - bool - Gets or Sets a value indicates whether the Dump Time should recorded in dump file.
- AsynchronousMode - bool - Gets or Sets a value indicates whether the Export process should run in Asynchronous Mode.
- CalculateTotalRowsFromDatabase - bool - Gets or Sets a value indicates whether total rows should be calculated before Export Process starts.
- AutoCloseConnection - bool - Gets or Sets a value indicates whether the MySqlConnection and MySqlCommand used should close and dispose after export process finished.
- EnableEncryption - bool - Gets or Sets a value indicates whether the Exported Dump File should be encrypted.
- EncryptionKey - string - Gets or Sets the key or password used to encrypt the exported dump file.
- SaltSize - int - Gets the length of salt used in encryption.
- AddCreateDatabase - bool - Gets or Sets a value indicates the SQL statement of "CREATE DATABASE" should added into dump file.
- ExportTableStructure - bool - or Sets a value indicates the Table Structure (CREATE TABLE) should be exported.
- ResetAutoIncrement - bool - Gets or Sets a value indicates the value of auto-increment of each table should be reset to 1.
- ExportRows - bool - Gets or Sets a value indicates the Rows should be exported.
- MaxSqlLength - Gets or Sets the Maximum Length allowed for multiple INSERT SQL statements to join. Default value is 1MB.
- ExportStoredProcedures - bool - Gets or Sets a value indicates whether the Stored Procedures should be exported.
- ExportFunctions - bool - Gets or Sets a value indicates whether the Stored Functions should be exported.
- ExportTriggers - bool - Gets or Sets a value indicates whether the Stored Triggers should be exported.
- ExportViews - bool - Gets or Sets a value indicates whether the Stored Views should be exported.
- ExportEvents - bool - Gets or Sets a value indicates whether the Stored Events should be exported.
- CompleteArg - ExportCompleteArg - The event arguments of ExportCompleted.
- ZipOutputFile - Gets or Sets a value indicates whether the exported Sql Dump File should be compressed as Zip file.
- ImportInfo - ImportInformations - Gets or Sets the Informations that define behaviour of Import Process.
- Properties, Methods and Fields
- FileName - string - Gets or Sets the full path and file name (dump file) that will be imported.
- AsynchronousMode - bool - Gets or Sets a value indicates whether the Import process should run in Asynchronous Mode.
- AutoCloseConnection - bool - Gets or Sets a value indicates whether the MySqlConnection and MySqlCommand used should close and dispose after import process finished.
- EnableEncryption - bool - Gets or Sets a value indicates whether the Imported Dump File is encrypted.
- EncryptionKey - string - Gets or Sets the key or password used to decrypt the exported dump file.
- SaltSize - int - Gets the lenght of salt used in encryption.
- SetTargetDatabase(string, string) - Set the target database name that will be imported to and the default character set. If the database if not exists, it will be created.
- SetTargetDatabase(string) - Set the target database name that will be imported to. If the database if not exists, it will be created. The default character set of current connecting MySQL server will be used as default character set for this new target database.
- SetTargetDatabase(string, Enum) - the target database name that will be imported to and the default character set. If the database if not exists, it will be created.
- CreateTargetDatabaseSql - Gets the CREATE DATABASE SQL statement of current database.
- TargetDatabase - string - Gets the name of target database.
- IgnoreSqlError - bool - Gets or Sets a value indicates whether SQL errors occus in import process should be ignored. If true, all errors (exceptions) will be collected in ImportCompleteArg.Errors.
- CompleteArg - ImportCompleteArg (Event argument of ImportCompleted)
Public Methods
- Export() - Execute the export (backup) process.
- Export(ExportInformations) - Execute the export (backup) process. Sets the ExportInformations used by this export process.
- CancelExport() - Cancel the current executing export process.
- Import() - Execute the import process.
- Import(ImportInformations) - Execute the import (restore) process. Sets the ImportInformations about this import process.
- CancelImport() - Cancel the current executing import process.
- DeleteAllRows(bool) - Delete all rows in all tables. Sets a value indicates whether Auto-Increment should reset to 1.
- DeleteAllRows(bool, string[]) - Delete all rows in all tables. Sets a value indicates whether Auto-Increment should reset to 1. Exclude these tables from rows deletion.
- EncryptSqlDumpFile(string, string) - Encrypt a SQL Dump File and save as new file.
- DecryptSqlDumpFile(string, string) - Decrypt a SQL Dump File and save as new file.
- ExportBlobAsFile(string, string, string, string, string) - Export BLOB data type and save as file.
- Dispose() - Release all resources used by this instance.
- Dispose(bool) - Release all resources used by this instance. Determine whether MySqlConnection and MySqlCommand used by this instance should dispose.
Events
- ExportProgressChanged - Occur when a row of data is exported or calculation of total rows of a table is completed.
- Event Arguments:
- CurrentTableName - String
- TotalRowsInCurrentTable - Long
- TotalRowsInAllTables - Long
- CurrentRowInCurrentTable - Long
- CurrentRowInAllTable - Long
- TotalTables - Int
- CurrentTableIndex - Int
- PercentageCompleted - Int
- PercentageGetTotalRowsCompleted - Int
- ExportCompleted - Occur when Export processs is finished.
- Event Arguments:
- TimeStart - DateTime
- TimeEnd - DateTime
- TimeUsed - TimeSpan
- Error - Exception (Error that occur during the process)
- CompletedType - Enum (Completed, Cancelled, Error)
- ImportProgressChanged - Occur when a line in the dump file is imported.
- Event Arguments:
- CurrentByte - Long
- TotalBytes - Long
- Error - Exception (The error information that encounter.)
- CurrentLineNo - Long
- ErrorSql - String (The SQL statement that creates the error)
- PercentageCompleted
- ImportCompleted - Occur when Import process is finished.
- Event Arguments:
- TimeStart - DateTime
- TimeEnd - DateTime
- TimeUsed - TimeSpan
- CurrentLineNo - Long
- HasErrors - Bool
- LastError - Exception (The last error (exception) occur in import process.)
- CompletedType - Enum (Completed, Cancelled, Error)
- Errors - Dictionary<long, Exception> (The collection of errors. Exceptions will be collected if Ignore SQL Error has set to true in Import Info.)
6. Adding MySqlBackup.NET Into Your Project
Before using this library, you must add a reference of MySQL Dot Net Connector
(MySql.Data.DLL). You can get the lastest MySql Dot Net Connector at: [http://dev.mysql.com/downloads/connector/net/].
There are a few easy way to add MySqlBackup.NET in your C# (WinForm, ASP.NET) application.
1st method, (Note: it is available, but not updated to latest version yet) using the Nuget (https://nuget.org/packages/MySqlBackup.NET). At the Package Manager Console of Nuget in Visual Studio, key in the below command, it will automatically download and install MySqlBackup.NET into your project.
PM> Install-Package MySqlBackup.NET
2nd method, add all the CS files into your source code directly. Source code available
at top of this article.
Create a folder and add the CS files:
At top of your cs file, add this line:
using MySql.Data.MySqlClient;
Using in VB.NET,
You can use Nuget to help you. If you want to do it manually, you have to compile the library into a DLL and add it as
reference into your VB.NET project.
7. Basic Examples
Example 1: Backup
void Backup()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ExportInfo.FileName = file;
mb.Export();
}
Example 2: Restore
Note: The source file can be either a SQL Dump file or Compressed Zip file.
void Restore()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ImportInfo.FileName = file;
mb.Import();
}
Example 3: Export and Compress as Zip File
Note: You can use a compressed SQL Dump File in Zip format as source file directly to restore a database.
void Backup()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ExportInfo.FileName = file;
mb.ExportInfo.ZipOutputFile = true;
mb.Export();
}
Example 4: Select tables to backup
void BackupTables()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ExportInfo.FileName = file;
mb.ExportInfo.TablesToBeExported = new string[] { "member", "activity", "season" };
mb.Export();
}
Example 5: Custom columns and rows backup conditions
void BackupTablesCustomSQL()
{
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 constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ExportInfo.FileName = file;
mb.ExportInfo.TableCustomSql = dic;
mb.Export();
}
Example 6: Backup with Encryption
void BackupEncrypt()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ExportInfo.FileName = file;
mb.ExportInfo.EnableEncryption = true;
mb.ExportInfo.EncryptionKey = "my secret password";
mb.Export();
}
Example 7: Resotre with Decryption
void RestoreDecrypt()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ImportInfo.FileName = file;
mb.ImportInfo.EnableEncryption = true;
mb.ImportInfo.EncryptionKey = "my secret password";
mb.Import();
}
Example 8: Encrypt & Decrypt a SQL Dump File
void EncryptDecryptDumpFile()
{
MySqlBackup mb = new MySqlBackup();
string file1 = "C:\\MyDumpFileOld.sql";
string file2 = "C:\\MyDumpFileNew.sql";
string file3 = "C:\\MyDumpFileBackToOri.sql";
string password = "my secret password";
mb.EncryptSqlDumpFile(file1, file2, password);
mb.DecryptSqlDumpFile(file2, file3, password);
}
Example 9: Backup Table Structures without rows and reset auto-increment
to 1
void BackupNoRows()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ExportInfo.FileName = file;
mb.ExportInfo.ExportRows = false;
mb.ExportInfo.ResetAutoIncrement = true;
mb.Export();
}
Example 10: Full List of Settings for Export (Backup)
There are some more settings which affect the output results of Export Process.
All of these settings are configured in ExportInfo. A full list
of settings:
void BackupNoRows()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ExportInfo.FileName = file;
mb.ExportInfo.AddCreateDatabase = true;
mb.ExportInfo.AsynchronousMode = true;
mb.ExportInfo.AutoCloseConnection = true;
mb.ExportInfo.CalculateTotalRowsFromDatabase = true;
mb.ExportInfo.EnableEncryption = true;
mb.ExportInfo.EncryptionKey = "my secret password";
mb.ExportInfo.ExportEvents = true;
mb.ExportInfo.ExportFunctions = true;
mb.ExportInfo.ExportRows = true;
mb.ExportInfo.ExportStoredProcedures = true;
mb.ExportInfo.ExportTableStructure = true;
mb.ExportInfo.ExportTriggers = true;
mb.ExportInfo.ExportViews = true;
mb.ExportInfo.MaxSqlLength = 10000000;
mb.ExportInfo.RecordDumpTime = true;
mb.ExportInfo.ResetAutoIncrement = true;
mb.ExportInfo.TableCustomSql = mb.ExportInfo.TablesToBeExported = mb.ExportInfo.ExportRows = false;
mb.ExportInfo.ResetAutoIncrement = true;
mb.ExportInfo.ZipOutputFile = false;
mb.Export();
}
Example 11: Full List of Settings for Import
void Restore()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ImportInfo.FileName = file;
mb.ImportInfo.AsynchronousMode = true;
mb.ImportInfo.AutoCloseConnection = true;
mb.ImportInfo.EnableEncryption = true;
mb.ImportInfo.EncryptionKey = "my secret password";
mb.ImportInfo.IgnoreSqlError = true;
mb.ImportInfo.SetTargetDatabase("MyNewDatabase", ImportInformations.CharSet.latin1);
mb.Import();
}
Example 12: Export BLOB data and save as files on local drive
private void BackupEncrypt()
{
string folder = "C:\\exportedfiles";
string table = "upload";
string column_Blob = "blobdata";
string column_FileName = "filename";
string column_FileSize = "filesize";
string con = "server=localhost;user=root;pwd=qwerty;database=member;";
MySqlBackup mb = new MySqlBackup(con);
mb.ExportBlobAsFile(folder, table, column_Blob, column_FileName, column_FileSize);
}
Example 13: Get all tables' name from database
private string[] GetTableNames()
{
string con = "server=localhost;user=root;pwd=qwerty;database=test;";
MySqlBackup mb = new MySqlBackup(con);
return mb.DatabaseInfo.TableNames;
}
Example 14: Get Create Table sql statement for specific table
private string GetCreateTable(string tableName)
{
string con = "server=localhost;user=root;pwd=qwerty;database=test;";
MySqlBackup mb = new MySqlBackup(con);
return mb.DatabaseInfo.Tables[tableName].CreateTableSql;
}
8. Examples of Using MySqlBackup.NET in ASP.NET
MySqlBackup.NET need to access a physical file on disk to perform task. We can use Server.MapPath to provide a physical path for MySqlBackup.NET.
Backup MySQL Database
void Backup()
{
string connection = "server=localhost;user=root;pwd=qwerty;database=test;";
string fileOnDisk = HttpContext.Current.Server.MapPath("~/MyDumpFile.sql");
string fileOnWeb = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority)
+ "/MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(connection);
mb.ExportInfo.FileName = fileOnDisk;
mb.Export();
Response.ContentType = "text/plain";
Response.AppendHeader("Content-Disposition", "attachment; filename=MyDumpFile.sql");
Response.TransmitFile(fileOnDisk);
Response.End();
} Restore MySQL Database
void Restore()
{
string connection = "server=localhost;user=root;pwd=qwerty;database=test;";
string fileOnDisk = HttpContext.Current.Server.MapPath("~/MyDumpFile.sql");
string fileOnWeb = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority)
+ "/MyDumpFile.sql";
if (FileUpload1.HasFile)
{
FileUpload1.SaveAs(fileOnDisk);
MySqlBackup mb = new MySqlBackup(connection);
mb.ImportInfo.FileName = fileOnDisk;
mb.Import();
Response.Write("Import Successfully");
}
}
9. About Synchronous And Asynchronous Mode
Switching between Asynchronous and Synchronous Mode is a new feature in V1.5.
By default, all process run in Synchronous Mode (Single Thread).
To execute Export (Backup) process in Asynchronous Mode:
void Backup(string connection, string file)
{
MySqlBackup mb = new MySqlBackup(connection);
mb.ExportInfo.FileName = file;
mb.ExportInfo.AsynchronousMode = true;
mb.Export();
}
Asynchronous Mode prevents WinForm UI to freeze. The animation of Progress Bar continues to move while the export or import process running in background. On the other hand, in Asynchronous Mode, you have the choice to cancel/halt the export or import process and you can't do it in Synchronous Mode.
However, in Web Application or Web Service, there is no differences for the process runs either in Asynchronous or Synchronous Mode. This is because the main thread will still need to be suspended.
If you have a small database, the whole process will be completed in less than 1 second. In such situation, running the process in Asynchronous Mode might not be necessary.
10. Using MySqlBackup.NET with Progress Bar
Progress reporting is a new feature in V1.3. This enable the usage of Progress Bar.
Simple Walk Through
MySqlBackup.NET will raise an Event (ExportProgressChanged or ImportProgressChanged) after a row or a line of data is processed. - The Event carries a bunch of values indicating the current processing point.
- These values are loaded into a temporary storage location.
- Use a Timer (
Timer1) to read the values from temporary storage location and load it into Progress Bar repeatedly.
- Upon the process is finished, cancelled or encounter error, another Event will raised (
ExportCompleted or ImportCompleted) indicating the termination.
- Use another Timer (
Timer2) to stop Timer1.
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 and 2 timers.
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
string CurrentTableName = "";
long TotalRowsInCurrentTable = 0;
long TotalRowsInAllTables = 0;
long CurrentRowInCurrentTable = 0;
long CurrentRowInAllTable = 0;
int TotalTables = 0;
int CurrentTableIndex = 0;
int PercentageComplete = 0;
int PercentageGetTotalRowsCompleted = 0;
MySqlBackup mb;
Timer timerRead;
Timer timerStop;
bool stopTimer1 = false;
public Form1()
{
InitializeComponent();
}
}
}
Build the Backup method.
void Backup()
{
string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ExportInfo.FileName = file;
mb.ExportProgressChanged += new MySqlBackup.exportProgressChange(mb_ExportProgressChanged);
mb.ExportCompleted += new MySqlBackup.exportComplete(mb_ExportCompleted);
timerRead.Start();
mb.Export();
}
void mb_ExportCompleted(object sender, ExportCompleteArg e)
{
throw new NotImplementedException();
}
void mb_ExportProgressChanged(object sender, ExportProgressArg e)
{
throw new NotImplementedException();
}
During event of ExportProgressChanged, load values into temporary storage location:
void mb_ExportProgressChanged(object sender, ExportProgressArg 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;
}
Use a Timer (timerRead) to read the values into Progress Bar.
public Form1()
{
InitializeComponent();
timerRead = new Timer();
timerRead.Interval = 100; timerRead.Tick += new EventHandler(timerRead_Tick);
}
void timerRead_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;
}
You may add another method to stop the Export process just in case you need to cancel it.
public void CancelBackup()
{
mb.CancelExport();
}
Upon the process is finished or terminated, the final event raise. Call the second timer to stop the first timer.
void mb_ExportCompleted(object sender, ExportCompleteArg e)
{
timerStop = new Timer();
timerStop.Interval = 150;
timerStop.Tick += new EventHandler(timerStop_Tick);
timerStop.Start();
}
void timerStop_Tick(object sender, EventArgs e)
{
timerStop.Stop();
timerRead.Stop();
}
We do not stop the first timer immediately. This will allow the first timer able to catch up the last value raised. Or else, the Progress Bar might (might not) showing 99% completed in stead of 100%. Notice that the interval time for 2nd timer is a bit longer than 1st timer. The reason is the same.
11. Gathering Information After Export/Import Process Completed
After the process completed, some useful information is stored in ExportCompleteArg and ImportCompleteArg.
For Export process, ExportCompleteArg is part of event argument of ExportCompleted. However, if you do not raise the event, you still can access the values in the ExportInformation.
Event of ExportCompleted must be applied in order to get notified when it is finished if you run MySqlBackup.NET in Asynchronous Mode.
Example of accessing ExportCompleteArg:
void BackupWithEvent(string connection, string file)
{
MySqlBackup mb = new MySqlBackup(connection);
mb.ExportInfo.FileName = file;
mb.ExportCompleted += new MySqlBackup.exportComplete(mb_ExportCompleted);
mb.Export();
}
void mb_ExportCompleted(object sender, ExportCompleteArg e)
{
ShowMessage(e);
}
void BackupWithNoEvent(string connection, string file)
{
MySqlBackup mb = null;
try
{
mb = new MySqlBackup(connection);
mb.ExportInfo.FileName = file;
mb.Export();
}
catch { }
ShowMessage(mb.ExportInfo.CompleteArg);
}
void ShowMessage(ExportCompleteArg e)
{
StringBuilder sb = new StringBuilder();
sb.Append("Export " + e.CompletedType.ToString() + "\r\n");
sb.Append("Time Start: " + e.TimeStart.ToString() + "\r\n");
sb.Append("Time End: " + e.TimeEnd.ToString() + "\r\n");
sb.Append("Time Used: " + e.TimeUsed.Minutes + " m " + e.TimeUsed.Seconds + " s " + e.TimeUsed.Milliseconds + " ms\r\n\r\n");
if (e.Error != null)
{
sb.Append("Error Message:\r\n\r\n");
sb.Append(e.Error.ToString());
}
else
{
sb.Append("File save at: " + mb.ExportInfo.FileName);
}
MessageBox.Show(sb.ToString());
}
This is same to ImportCompleteArg.
If you have set IgnoreSqlError to true during Import, you can get all the SQL Errors (Exceptions) in
ImportCompleteArg.Errors
or
ImportProgressArg.Error
ImportProgressArg.ErrorSql
12. About Importing (Restoring) to Another/New Database
In V1.5, you can Restore to another database (maybe it is not exists) other than the original database specifies (or not specifies) in the dump file. Here is the example:
void Restore()
{
string constr = "server=localhost;user=root;pwd=qwerty;";
string file = "C:\\MyDumpFile.sql";
MySqlBackup mb = new MySqlBackup(constr);
mb.ImportInfo.FileName = file;
mb.ImportInfo.SetTargetDatabase("MyNewDatabase", ImportInformations.CharSet.latin1);
mb.Import();
}
If your database involve with Unicode characters, you have to carefully select the right Default Database Character Set while setting the Target Database. You should always choose Default Character Set that used by the source database of the dump file. Or else, you will experience encoding problems and unable to use the database properly. In some cases, you might even cannot INSERT unicode characters correctly into the database.
13. MySqlBackup.dll V1.1
There is a guide shows the design of
MySqlBackup.dll version 1.1. You can read it here [
the previous old revision of this article - revision 20, posted on 26 Nov 2011] or 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:
- How to collect
CREATE DATABASE sql statement. - How to collect
CREATE TABLE sql statements for each table. - How to build
INSERT sql statements for each row in each table. - Writes the collected sql statements into SQL Dump File.
- 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.
14. Frequently Asked Questions (FAQ)
1. Cannot connect to website database. An exception Occur: Unable to connect to any of the specified MySQL hosts
By default, MySql at web hosting will only allow connection from "localhost". If you wish to connect to MySql using your computer (remote access), or other IP, you have to allow the MySql at your website to be connected by the specific IP (your IP). If the website is using CPanel, you may refer this guide:
2. MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time value to System.DateTime
This is not a bug. It is an expected behavior. MySQL allow zero date time which means 0 year 0 month and 0 days, but in .NET world's concept, there is no such 0 year, 0 month and 0 day. The minimum possible date in .NET is 01-01-01. (Commonly occur when data type of "DATE" (not DATETIME) used in one of the table).
There are 2 ways to resolve this:
1st, by adding allowzerodatetime in the MySQL connection string (Recommended if you use MySqlBackup)
server=localhost;user=root;pwd=qwerty;database=test;allowzerodatetime=true;
2nd, by adding convertzerodatetime in MySQL connection string.
server=localhost;user=root;pwd=qwerty;database=test;convertzerodatetime=true
3. How do I get notified if there is new release?
You can, by signing up the release notification at http://mysqlbackupnet.codeplex.com/releases
15. Development of this Project
You are welcome to improve / enhance the project.
Please visit the project hosting site:
http://mysqlbackupnet.codeplex.com
16. History
25 Jan 2013 - Release of V1.5.7
... lots of changes ...
19 Sep 2011 - Version 1.0 - Initial release
Submit an article or tip
Post your Blog
News 
Joke 
Rant 
Admin