The Basic Idea


This is the initial idea for writing MySqlBackup.NET. It is the ancestor of this project. Everything start from here.

MySqlBackup.NET export the database into SQL dump format file, which is the standard export format for most SQL backup solutions.

A typical SQL Dump File has the following structure:
<Part 1:>
- Tool's name and version
- MySQL server's version

<Part 2:>
- create database statement
- use database statement

<Part 3:>
- MySQL Server's Variables Initialization

<Part 4:>
- create table

<Part 5:>
- INSERTs

<Part 6:>
- Create Procedure, Function, Triggers, Events, Views

<Part 7:>
- MySQL Server's Variables, Reset to original values.

Example of a SQL Dump Content:

-- MySqlBackup.NET 2.0.1
-- Dump Time: 2013-12-31 10:24:11
-- --------------------------------------
-- Server version 5.6.15-log MySQL Community Server (GPL)

-- 
-- Create schema test
-- 

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

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- 
-- Definition of profile
-- 

DROP TABLE IF EXISTS `profile`;
CREATE TABLE IF NOT EXISTS `profile` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `address` varchar(300) DEFAULT NULL,
  `tel` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table profile
-- 

/*!40000 ALTER TABLE `profile` DISABLE KEYS */;
INSERT INTO `profile`(`id`,`name`,`address`,`tel`) VALUES
(1,'Good Girl','1600 Amphitheatre Parkway, Mountain View, CA 94043','1 650-253-0000'),
(2,'Mike Grew Solve','One Microsoft Way, Redmond, WA 98052-7329','1-425-882-8080'),
(3,'Yupe Who','701 First Ave, Sunnyvale, CA 94089','(408) 349-3300'),
(4,'Phase Broke','1 Hacker Way (at Bayfront Expy.), Menlo Park, CA 94025','(650) 308-7300');
/*!40000 ALTER TABLE `profile` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2013-12-31 10:24:11
-- Total time: 0:0:0:0:85 (d:h:m:s:ms)

For Import process, simply execute all the SQLs line by line.

Route for Creating a Dump File:

  • Part 1 : Construct the database CREATE statements
  • Part 2 : Construct the Tables CREATE statements
  • Part 3 : Construct the INSERT statement for all data.
  • Part 4 : Write All SQL statements into textfile.
  • Part 5 : Restore.
  • Part 6: MySQL Server's Variables Initialization

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 is 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 */;

Add USE to state that all command will take effect within database of `people`.

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

Now, we have collected 2 SQL commands.

Part 2 : Construct the Tables CREATE Commands

First, collects 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 current available table 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] + "");
}

Example result:

CREATE TABLE `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; 

CREATE TABLE statement will cause error if the table is existed. Therefore, it needs to be replaced as 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 `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; 

Part 3 : Construct the INSERT statement for all data.

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] + "" == "")
			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 appear between MySQL and C#.
Example:

Process Backup Backup Backup Restore Restore
Place MySQL C# Text File C# MySQL
Example 1 (string value) \a \\a \\\\a \\a \a
Description Original string store in MySQL database Another "\" is automatically added by C# when the string passes into C# Manually add another "\\" into the string and write it into text file. C# convert "\\\\" into "\\" after reading from text file. MySQL will finally convert "\\" into "\"
Example 2 (string value) 'a 'a \\'a \'a 'a
Description Original string store in MySQL database Nothing is changed when passing the string from MySQL to C# Manually add another "\\" into the string and write it into text file. C# convert "\\" into "\" after reading from text file. MySQL replace "\'" with "'"


So, there are 2 special character we need to take care 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] + "" == "")
			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 text file.

Lastly, we write all the SQL statements that collected into text file.

using System.IO;
using System.Text;

using (TextWriter tw = new StreamWriter("C:\\backp.sql", new UTF8Encoding(false))
{
    bool done = false;
    while (!done)
    {
        // Processing and get SQL one by one
        string sql = ...........
        tw.WriteLine(sql);
    }
}

Part 5: Restore

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

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

StringBuilder sb = new StringBuilder();

using (TextReader tr = new StreamReader("C:\\backup.sql"))
{
    string sql = "";
    while (sql != null)
    {
        sql = tr.ReadLine();

        if (sql == null)
            break;

        if (sql.Length == 0 || sql.StartWith("--"))
            continue;

        sb.Append(sql);

        if (sql.EndsWith(";");
        {
            cmd.CommandText = sb.ToString();
            cmd.ExecuteNonQuery();
            sb = new StringBuilder();
        }
    }
}

Part 6: MySQL Server's Variables Initialization

These are MySQL Server's Variables Initialization

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

and resetting them to original values:

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

You can see some of the SQL is wrapped within the symbol of /*!

Example:

/*!40101  ... <some SQL statement> ...   */;
  1. It's a conditional comment that can be interpreted by MySQL
  2. The code 40100 means only these versions of MySQL>=4.1.0 (4.01.00) will interpret the conditional comment.
  3. The ! is here to force MySQL to parse the code between the /* ... */
  4. Read more about In-Line Comment at: http://dev.mysql.com/doc/refman/5.7/en/comments.html

These MySQL Server's Variables Initialization SQL statements executed to ensure the content will not be modified or interrupted during Import.

Above codes demonstrate the very basic idea on how MySqlBackup.NET works.

Last edited Dec 31, 2013 at 2:18 AM by adriancs, version 4

Comments

No comments yet.