Click here to Skip to main content
12,815,616 members (40,530 online)
Click here to Skip to main content
Articles » Database » Database » MySQL » Revisions
Add your own
alternative version

Tagged as

Stats

356.4K views
26.8K downloads
329 bookmarked
Posted 14 Aug 2012

Making Your Own MySQL Backup and Restore Tools in C# (Supports Unicode, UTF8)

, 26 Nov 2011 Apache
Rate this:
Please Sign up or sign in to vote.
Unicode, UTF8 character backup and restore of MySQL database in C#
This is an old version of the currently published article.
screenshot.jpg

Introduction

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.

Therefore, I have decided to figure out a way to make a MySQL Backup Tools.

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. Besides, we will have better control on the output result.

Important Release Note

This version is currently only capable for small size of MySQL Database. It is unable to handle large number of rows in a table at the moment. It's currently under development. A more stable and better version of this Software(code) will be released in the near future.

Background

The method used in this article is actually easy. The basic concept of backup method described here is similar to MySqlDump, we create SQL statements to store the structure of the current database. While restoring, we just simply execute all the SQL statements.

Before Start

You need to download and install MySQL .NET Connector and add reference of MySql.Data into your C# projects. This is described in details here: Connect C# to MySQL

Follows the steps in that article, until you added the Using command line:

using MySql.Data.MySqlClient; 

Making the Backup File

Part 1: Construct the Database CREATE Statement

For example, we have a database called "people".

We use this statement to display the CREATE of this database:

SHOW CREATE DATABASE `people`;  

In C#:

string sql = "SHOW CREATE DATABASE `people`;";
MySqlConnection conn = new MySqlConnection_
	("server=localhost;user=root;password=qwerty;database=people;");
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
da.Fill(dt);
string output = dt.Rows[0][0] + "";
MessageBox.Show(output);  

Below is the result of the above commands. This will be the CREATE SQL statement for creating a database exactly the same of the current database.

CREATE DATABASE `people` /*!40100 DEFAULT CHARACTER SET utf8 */ 

However, CREATE DATABASE will cause error if there already existed a database called `people`. We need to replace CREATE DATABASE with CREATE DATABASE IF NOT EXISTS.

string sql = "SHOW CREATE TABLE `people`;";
MySqlConnection conn = new MySqlConnection_
	("server=localhost;user=root;password=qwerty;database=people;");
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
da.Fill(dt);
string output = (dt.Rows[0][0] + "").Replace_
	("CREATE DATABASE", "CREATE DATABASE IF NOT EXISTS ") + ";");;
MessageBox.Show(output);  

This is the result:

CREATE DATABASE IF NOT EXISTS `people` /*!40100 DEFAULT CHARACTER SET utf8 */; 

For some situations, you might need to add a DROP DATABASE SQL statement before a CREATE. Add USE to state that all commands will take effect within database of `people`.

DROP DATABASE IF EXISTS `people`;
CREATE DATABASE IF NOT EXISTS `people` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `people`;  

Now, we have collected 3 SQL commands.

Part 2: Construct the Tables CREATE Commands

First, collect all tables' name in database `people`.

string sql = "SHOW TABLES;";
MySqlConnection conn = new MySqlConnection_
	("server=localhost;user=root;password=qwerty;database=people;");
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
    MessageBox.Show(dr[0] + "");
} 

Now, DataTable dt contains a list of currently available tables in database.

Collecting CREATE statement of tables:

foreach (DataRow dr in dt.Rows)
{
    string sql = "SHOW CREATE TABLE `" + dr[0] + "`;";
    MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
    DataTable dtNames = new DataTable();
    da.Fill(dtNames);
    MessageBox.Show(dtNames.Rows[0][1] + "");
} 

Result (This is just an example, the result will vary on different structure of table):

CREATE TABLE `people`.`personal` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) COMMENT 'Member\'s Name',
  `datejoin` DATETIME,
  `status` SMALLINT UNSIGNED DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB; 

Please note that, this CREATE statement is split into several rows. If we browse the statement in runtime debug mode, we'll find that the CREATE statement is actually in one line, but there is a special escape character "\n" appear in between. "\n" means "New Line" (split the current point into new line). It'll look like this:

CREATE TABLE `people`.`personal` (\n `id` INTEGER UNSIGNED........... 

This "\n" needs to be replaced to avoid the whole statement to be split. Besides, CREATE TABLE SQL statement needs to be replaced as well with CREATE TABLE IF NOT EXISTS.

string sql = "SHOW TABLES;";
MySqlConnection conn = new MySqlConnection_
	("server=localhost;user=root;password=qwerty;database=people;");
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
    sql = "SHOW CREATE TABLE `" + dr[0] + "`;";
    MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
    DataTable dtNames = new DataTable();
    da.Fill(dtNames);
    string output = dtNames.Rows[0][1] + "";
    output = output.Replace("\n", string.Empty);
    output = output.Replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS") + ";");
    MessageBox.Show(output);
} 

Result:

CREATE TABLE IF NOT EXISTS `people`.`personal` ( `id` INTEGER UNSIGNED...........

In some situations, you might need to add DROP TABLE before CREATE TABLE.

DROP TABLE IF EXISTS `people`.`personal`;
CREATE TABLE IF NOT EXISTS  `people`.`personal` ( `id` INTEGER........

Till here, we have collected the CREATE statement for creating Tables.

Part 3: Construct the INSERT Statement for all Data

In C#:

string sql = "SELECT * FROM `personal`;"
string mycon = "server=localhost;user=root;password=qwerty;database=people;";
MySqlConnection conn = new MySqlConnection(mycon);
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataTable dtRows = new DataTable();
da.Fill(dtRows);

All data is loaded from database into C# DataTable of dtRows.

Collecting INSERT statement of each row:

for (int i = 0; i < dtRows.Rows.Count; i++)
{
    StringBuilder sb = new StringBuilder();
    sb.AppendFormat("INSERT INTO `people` VALUE(");
    for (int j = 0; j < dtRows.Columns.Count; j++)
    {
        if (dtRows.Rows[i][j] == DBNull.Value) // NULL value
            sb.AppendFormat("NULL");
        else
            sb.AppendFormat("'" + dtRows.Rows[i][j] + "'");
        if (j + 1 != dtRows.Columns.Count)
            sb.AppendFormat(",");
    }
    sb.AppendFormat(");");
    MessageBox.Show(sb.ToString());
}

Result:

INSERT INTO `people` VALUE('1','James','19/09/2011 Monday 8:00:12 AM',NULL);
INSERT INTO `people` VALUE('2',NULL,'19/09/2011 Monday 11:00:40 AM','1');
INSERT INTO `people` VALUE('3','Thomas',NULL,'1');

Next, we need to do some modification on the DateTime Value Type of Data. C# default datetime format is affected by System Regional and Language Settings and it is not compatible with MySQL datetime format. Thus, we need to convert it into something acceptable by MySQL.

for (int i = 0; i < dtRows.Rows.Count; i++)
{
	StringBuilder sb = new StringBuilder();
	sb.AppendFormat("INSERT INTO `people` VALUE(");
	for (int j = 0; j < dtRows.Columns.Count; j++)
	{
		string datatype = dtRows.Columns[j].DataType.ToString();

		string text = "";

		DateTime dtime = DateTime.Now;
		if (datatype == "System.DateTime")
		{
			if (DateTime.TryParse(dtRows.Rows[i][j] + "", out dtime))
			{
				text = "'" + Convert.ToDateTime
					(dtRows.Rows[i][j]).ToString
					("yyyy-MM-dd HH:mm:ss") + "'";
			}
			else
				text = "null";
		}
		else if (dtRows.Rows[i][j] == DBNull.Value) // NULL value
			text = "null";
		else
			text = "'" + dtRows.Rows[i][j] + "'";

		sb.AppendFormat(text);

		if (j + 1 != dtRows.Columns.Count)
			sb.AppendFormat(",");
	} 
	sb.AppendFormat(");");
	MessageBox.Show(sb.ToString());
}

Result:

INSERT INTO `people` VALUE(1,James,'2011-09-19 08:00:12',1);
INSERT INTO `people` VALUE(2,Cathrine,'2011-09-19 11:00:40',1);
INSERT INTO `people` VALUE(3,Thomas,'2011-09-20 12:23:10',1);

Next, we need to solve the special escape character that appears between MySQL and C#.

Example:

Process BackupBackupBackupRestore Restore
PlaceMySQL C#TextfileC# MySQL
string\a\\a\\\\a\\a\a
DescriptionOriginal string store in MySQL databaseAnother "\" is automatically added by C# when the string passes into C#We manually add another "\\" into the string and write it into textfile.C# convert "\\\\" into "\\" after reading from textfile.MySQL will finally convert "\\" into "\"
string'a'a\\'a\'a'a
DescriptionOriginal string store in MySQL databaseNothing is changed when passing the string from MySQL to C#We manually add another "\\" into the string and write it into textfile.C# convert "\\" into "\" after reading from textfile.MySQL replace "\'" with "'"

So, there are 2 special characters we need to take care of in order to pass information correctly during database restore process.

Simply replace "\\" with "\\\\" and ' with \\'.

for (int i = 0; i < dtRows.Rows.Count; i++)
{
	StringBuilder sb = new StringBuilder();
	sb.AppendFormat("INSERT INTO `people` VALUE(");
	for (int j = 0; j < dtRows.Columns.Count; j++)
	{
		string datatype = dtRows.Columns[j].DataType.ToString();

		string text = "";

		DateTime dtime = DateTime.Now;
		if (datatype == "System.DateTime")
		{
			if (DateTime.TryParse(dtRows.Rows[i][j] + "", out dtime))
			{
				text = "'" + Convert.ToDateTime
					(dtRows.Rows[i][j]).ToString
					("yyyy-MM-dd HH:mm:ss") + "'";
			}
			else
				text = "null";
		}
		else if (dtRows.Rows[i][j] == DBNull.Value) // NULL value
			text = "null";
		else
		{
			text = dtRows.Rows[i][j] + "";

			// Escape special character for MySQL commands
			text = text.Replace("\\", "\\\\");
			text = text.Replace("'", "\\'");
			text = "'" + text + "'";
		}

		sb.AppendFormat(text);

		if (j + 1 != dtRows.Columns.Count)
		sb.AppendFormat(",");
		
	}
	sb.AppendFormat(");");
	MessageBox.Show(sb.ToString());
} 

Part 4: Write All SQL Statements into textfile

Lastly, we write all the SQL statements that are collected into textfile.

We use a list to store all SQL statements.

List<string> SqlList = new List<string>();
SqlList.Add(sql);

All the SQL statements have to be added into the list in the correct sequence. For example, you won't CREATE a table, and then you DROP it right? We immediately add the SQL statement by the time it is created. After we added all the SQL statements, it's time to write it into textfile.

At the top of your project, add:

Using System.IO;

C#,

string[] sa = null;
sa = new string[SqlList.Count];
for (int i = 0; i < SqlList.Count; i++)
{
	sa[i] = SQLs[i];
}
File.WriteAllLines("C:\\backup.sql", sa, Encoding.UTF8);

Done.

Part 5: Restore

Open the file, read it and execute all SQLs one by one.

string myCon = "server=localhost;user=root;password=qwerty;database=people;port=3306";
MySqlConnection conn = new MySqlConnection(myCon);
string[] sqls = File.ReadAllLines("C:\\backup.sql", Encoding.UTF8);

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
conn.Open();

foreach (string s in sqls)
{
	cmd.CommandText = s;
	cmd.ExecuteNonQuery();
}
conn.Close();

Done.


Using the Code/Class

I have made a class based on this method. The download is available at the top of this article. After download, find two classes inside the zip file.

MySQLBackupRestore.cs and XCrypt.cs (Special thanks to XCrypt project which enables the encryption function)

XCrypt Project Site: XCrypt - encryption & decryption class wrapper

Add the two classes into your project. Add this command at the top of your project:

Using MySql.Data.MySqlClient; 

To Backup

MySqlBackupRestore mb = new MySqlBackupRestore();
mb.myServer = "localhost";
mb.myUser = "root";
mb.myPassword = "qwerty";
mb.myDatabase = "people";
mb.myPort = "3306";
mb.DropAndRecreateDatabase = true;
mb.DropAndRecreateTable = true;
mb.Construct_SQL_In_One_Line_From_Same_Table = true;
mb.EncryptBackupFile = true;;
mb.EncryptionKey = "asdf";
mb.Backup("C:\\backup.sql");

To Restore

MySqlBackupRestore mb = new MySqlBackupRestore();
mb.myServer = "localhost";
mb.myUser = "root";
mb.myPassword = "qwerty";
mb.myPort = "3306";
mb.EncryptBackupFile = true; ;
mb.EncryptionKey = "asdf";
mb.Restore("C:\\backup.sql");

You may find a sample application in the download too.


Joining this Project

This project is hosted at a online source control solution. The project is open, anyone is welcome to join this project, improve the code, making this a more stable version.

Project site: http://mysqlbackuprestore.codeplex.com

This page will be updated from there.


History

24 Sep 2011

  • Article: Download of application (backup tool) added at the top of article

20 Sep 2011 - Version 1.1

  • License changed to Apache License, Version 2.0 (Version 1.0 is GNU General Public License V3)
  • Application: Able to choose default folder
  • Application: Improve encryption of auto save parameters file

19 Sep 2011 - Version 1.0

  • Initial release

License

This article, along with any associated source code and files, is licensed under The Apache License, Version 2.0

Share

About the Author

adriancs
Software Developer
Malaysia Malaysia
Writing programs is an art.

You may also be interested in...

Pro
Pro

Comments and Discussions


Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
 
QuestionError the system Can't Find the Reference Specified Pin
Samoual Shingrai5-Jul-16 7:12
memberSamoual Shingrai5-Jul-16 7:12 
AnswerRe: Error the system Can't Find the Reference Specified Pin
adriancs6-Jul-16 2:07
professionaladriancs6-Jul-16 2:07 
QuestionExport Sizes Differ Pin
Member 1232267512-Feb-16 0:10
memberMember 1232267512-Feb-16 0:10 
AnswerRe: Export Sizes Differ Pin
adriancs23-Feb-16 23:07
professionaladriancs23-Feb-16 23:07 
QuestionVB.NET error Pin
GirorgioC20-Jan-16 6:26
memberGirorgioC20-Jan-16 6:26 
AnswerRe: VB.NET error Pin
Member 951947518-Feb-16 1:42
memberMember 951947518-Feb-16 1:42 
QuestionBackup lost Unicode data and can not Restore the binary data Pin
Nguyen Van Bay6-Jan-16 0:04
memberNguyen Van Bay6-Jan-16 0:04 
AnswerRe: Backup lost Unicode data and can not Restore the binary data Pin
adriancs6-Jan-16 15:41
professionaladriancs6-Jan-16 15:41 
GeneralRe: Backup lost Unicode data and can not Restore the binary data Pin
Nguyen Van Bay6-Jan-16 18:36
memberNguyen Van Bay6-Jan-16 18:36 
GeneralRe: Backup lost Unicode data and can not Restore the binary data Pin
adriancs6-Jan-16 22:06
professionaladriancs6-Jan-16 22:06 
GeneralRe: Backup lost Unicode data and can not Restore the binary data Pin
Nguyen Van Bay7-Jan-16 1:35
memberNguyen Van Bay7-Jan-16 1:35 
GeneralRe: Backup lost Unicode data and can not Restore the binary data Pin
Nguyen Van Bay7-Jan-16 15:50
memberNguyen Van Bay7-Jan-16 15:50 
GeneralnRe: Backup lost Unicode data and can not Restore the binary data Pin
adriancs8-Jan-16 23:13
professionaladriancs8-Jan-16 23:13 
GeneralRe: nRe: Backup lost Unicode data and can not Restore the binary data Pin
Nguyen Van Bay10-Jan-16 20:01
memberNguyen Van Bay10-Jan-16 20:01 
GeneralRe: nRe: Backup lost Unicode data and can not Restore the binary data Pin
adriancs11-Jan-16 21:35
professionaladriancs11-Jan-16 21:35 
GeneralRe: nRe: Backup lost Unicode data and can not Restore the binary data Pin
nvbay24-Jan-17 0:47
membernvbay24-Jan-17 0:47 
QuestionNot converting ASCII NULL to ESC SEQ \0 Pin
eZov30-Oct-15 2:54
membereZov30-Oct-15 2:54 
AnswerRe: Not converting ASCII NULL to ESC SEQ \0 Pin
adriancs13-Dec-15 2:51
mvpadriancs13-Dec-15 2:51 
QuestionImport Does not work Pin
mailparikshit@yahoo.com26-Oct-15 20:29
membermailparikshit@yahoo.com26-Oct-15 20:29 
AnswerRe: Import Does not work Pin
adriancs26-Oct-15 21:42
mvpadriancs26-Oct-15 21:42 
GeneralRe: Import Does not work Pin
mailparikshit@yahoo.com27-Oct-15 5:02
membermailparikshit@yahoo.com27-Oct-15 5:02 
GeneralRe: Import Does not work Pin
mailparikshit@yahoo.com27-Oct-15 5:38
membermailparikshit@yahoo.com27-Oct-15 5:38 
GeneralRe: Import Does not work Pin
adriancs27-Oct-15 16:42
mvpadriancs27-Oct-15 16:42 
GeneralRe: Import Does not work Pin
mailparikshit@yahoo.com28-Oct-15 23:41
membermailparikshit@yahoo.com28-Oct-15 23:41 
GeneralRe: Import Does not work Pin
mailparikshit@yahoo.com29-Oct-15 0:08
membermailparikshit@yahoo.com29-Oct-15 0:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170308.1 | Last Updated 26 Nov 2011
Article Copyright 2012 by adriancs
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid