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

• Download application - 181.7 KB
• Download source code - 211.5 KB

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

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:

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