MySqlDump and MySqlBackup.NET are both used to backup/restore MySQL database. Both can be used in C# (.NET) projects. This is a study specially made for the open source project MySqlBackup.NET.
Date: Sep 24, 2021
This benchmark test was carried out in the following setup/environment/parameters:
- OS: Windows 10 Pro x64 (version 21H1, build: 19043.1237)
- CPU: Intel Core i5-3570 CPU @ 3.40GHz
- RAM: 12GB DDR3
- Hard Disk: Samsung SSD Evo 860 (550 MB/sec read, 520 MB/sec write)
- MySQL Community Server v5.7.26, installed by using MySql Installer 5.7.26
- Default Character Set=utf8 (This will affect the file size generated by
MySqlDump), as latin1 is normally has smaller size than utf8. On the other hand, MySqlBackup.NET uses UTF8 as text encoding for writing the dump content. - MySql.EXE v5.7.26 (Import)
- MySqlDump v8.0.26 (Export)
- MySqlBackup 2.3.5, with mysql.data.dll v8.0.26 (start from 2.3.5, MySqlCommand is replacing MySqlScript, resulting faster import execution)
- max_allowed_packet = 999M
- MySql server and backup/restore dump file saving location are both executed on the same hard disk.
- MySqlDump (Export) and MySql.exe (Import) are executed through MySqlWorkBench v8.0.26
- MySqlBackup is executed through a Console App (.NET Framework 4.8)
- Total database size: 3.50 GB (3,762,407,157 bytes)
- Total rows: 15,350,000 Rows (15 millions)
There are some other elements that have a great impact on the processing speed:
- The hard disk type and model
- The distance between MySql Server and the Software Application (that runs the backup/restore).
Here is the benchmark result:
Process Tools Time
------- --------- ------
Backup 1 MySqlDump 2m 36s
Backup 2 MySqlDump 2m 33s
Backup 3 MySqlDump 2m 35s
File Size:
4.66 GB (5,008,487,275 bytes)
Backup 4 MySqlBackup.NET 7m 48s
Backup 5 MySqlBackup.NET 7m 46s
Backup 6 MySqlBackup.NET 7m 50s
File Size:
4.59 GB (4,931,743,894 bytes)
Restore 1 MySql.exe 8m 42s
Restore 2 MySql.exe 8m 23s
Restore 3 MySql.exe 8m 57s
Restore 4 MySqlBackup.NET 9m 44s
Restore 5 MySqlBackup.NET 9m 39s
Restore 6 MySqlBackup.NET 9m 39s
Here is the CREATE TABLE statement:
CREATE TABLE `tableA` (
`int` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`varchar` VARCHAR(45),
`text` TEXT,
`datetime` DATETIME,
`date` DATE,
`time` TIME,
`decimal` DECIMAL(10,5),
`tinyint` TINYINT UNSIGNED,
`timestamp` TIMESTAMP,
`char36` CHAR(36),
`binary16` BInary(16),
`float` FLOAT,
`double` DOUBLE,
`blob` BLOB,
PRIMARY KEY (`int`)
)
ENGINE = InnoDB;Here’s the INSERT statement:
INSERT INTO `tableA` (`varchar`,`text`,`datetime`,`date`,`time`,`decimal`,
`tinyint`,`timestamp`,`char36`,`binary16`,`float`,`double`,`blob`,`bool`)
VALUES('GtEva4ijqGoLnFvffBO3dPK1cLT9dWpQ56pzrt4vAkJr0','10UQ0F9MDuUM95KOcrED4GoyndiCQcWmILLh2h0uIQtm6',
'2021-09-24 20:40:12','2021-09-24','20:40:12',3487.2398,1,CURRENT_TIMESTAMP,
'00000000000000000000000000000000',0x00000000000000000000000000000000,243.234,456.456,
0x00000000000000000000000000000000,1);The code for executing Backup (MySqlBackup.NET) in Console App:
List<TimeSpan> lstDate = new List<TimeSpan>();
for (int i = 0; i < 3; i++)
{
int round = 1 + i;
Console.WriteLine($"round {round} started...");
DateTime datestart = DateTime.Now;
string constr = $"server=127.0.0.1;user=root;pwd=1234;database=test1;sslmode=none;default command timeout=90000000;";
using (MySqlConnection conn = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand())
{
conn.Open();
cmd.Connection = conn;
string filepath = System.IO.Path.Combine(Environment.CurrentDirectory, $"{round}.sql");
datestart = DateTime.Now;
cmd.CommandTimeout = 9000;
using (MySqlBackup mb = new MySqlBackup(cmd))
{
mb.ExportInfo.GetTotalRowsMode = GetTotalRowsMethod.Skip;
mb.ExportToFile(filepath);
conn.Close();
}
}
}
DateTime dateend = DateTime.Now;
var ts = dateend - datestart;
lstDate.Add(ts);
}
StringBuilder sb = new StringBuilder();
foreach (var ts in lstDate)
{
string s = $"{ts.Hours}h {ts.Minutes}m {ts.Seconds}s {ts.Milliseconds}ms";
Console.WriteLine($"{ts.Hours}h {ts.Minutes}m {ts.Seconds}s {ts.Milliseconds}ms");
sb.AppendLine(s);
}
System.IO.File.WriteAllText("report_backup.txt", sb.ToString());Here’s the code for executing Restore (MySqlBackup.NET) in Console App:
List<TimeSpan> lstDate = new List<TimeSpan>();
for (int i = 0; i < 3; i++)
{
Console.WriteLine($"round {1 + i} started...");
DateTime datestart = DateTime.Now;
string dbname = "test" + (5 + i);
string constr = $"server=127.0.0.1;user=root;pwd=1234;sslmode=none;";
using (MySqlConnection conn = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand())
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = $"drop database if exists `{dbname}`;";
cmd.ExecuteNonQuery();
cmd.CommandText = $"create database `{dbname}`;";
cmd.ExecuteNonQuery();
cmd.CommandText = $"use `{dbname}`;";
cmd.ExecuteNonQuery();
datestart = DateTime.Now;
using (MySqlBackup mb = new MySqlBackup(cmd))
{
mb.ExportInfo.GetTotalRowsMode = GetTotalRowsMethod.Skip;
mb.ImportFromFile(@"D:\dumps\mysql_data1.sql");
conn.Close();
}
}
}
DateTime dateend = DateTime.Now;
var ts = dateend - datestart;
lstDate.Add(ts);
}
StringBuilder sb = new StringBuilder();
foreach (var ts in lstDate)
{
string s = $"{ts.Hours}h {ts.Minutes}m {ts.Seconds}s {ts.Milliseconds}ms";
Console.WriteLine($"{ts.Hours}h {ts.Minutes}m {ts.Seconds}s {ts.Milliseconds}ms");
sb.AppendLine(s);
}
System.IO.File.WriteAllText("report.txt", sb.ToString());This article is also published at the official project wiki page.