Contents

This article introduces a tool (DLL) that can backup/restore MySQL database in C# or VB.NET and some sample codes on how to use it. It is an alternative to MySqlDump.

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.

The most common way to backup a MySQL Database is by using MySqlDump.exe and MySQL Workbench.

MySQL Workbench is good for developers, but, when it 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 Workbench as a backup tool is not a suitable solution for client or end-user.

On the other hand, MySqlDump.exe cannot be used for Web applications. As most web hosting providers forbid that, MySqlBackup.NET will be helpful in building a web-based (ASP.NET/Web-Services) backup tool.

This article assumes that you are already familiar with MySQL dot net connector (MySql.Data.dll) with minimum knowledge that you are able to perform the four basic operations, SELECT, INSERT, UPDATE,and DELETE. In case you are not, you can read the walk-through and explanation on Connecting C# to MySQL at: [http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL].

Gets or Sets an enum value indicates how the rows of each table should be exported

INSERT = The default option. Recommended if exporting to a new database. If the primary key existed, the process will halt.

INSERT IGNORE = If the primary key existed, skip it

REPLACE = If the primary key existed, delete the row and insert new data

OnDuplicateKeyUpdate = If the primary key existed, update the row. If all fields are primary keys, it will change to INSERT IGNORE.

UPDATE = If the primary key does not exist, skip it and if all the fields are primary key, no rows will be exported.

ExportInfo.WrapWithinTransaction - bool

default value: false

Gets or Sets a value indicates whether the rows dump should be wrapped with transaction.

Recommended to set this value to FALSE if using RowsExportMode = "INSERT" or "INSERTIGNORE" or "REPLACE", else TRUE.

ExportInfo.TextEncoding - System.Text.Encoding

default value: UTF8Encoding(false)

Gets or Sets a value indicates the encoding to be used for exporting the dump.

ExportInfo.BlobExportMode - enum BlobDataExportMode

default value: BlobDataExportMode.HexString

Gets or Sets an enum value indicates how the BLOB should be exported.

BinaryChar = char format

Note: Export BLOB into Binary Char is not intended for real deploy usage at the moment. Exporting into BinaryChar will raise an exception which attempts to alarm the developers that this function is meant for development and debugging purposes. Read more: https://github.com/MySqlBackupNET/MySqlBackup.Net/issues/47

ExportInfo.BlobExportModeForBinaryStringAllow - bool

default value: false

If you wish to help to debug, fix or develop the function of exporting BLOB into binary char format (BlobExportMode=BinaryChar), set this value to true

ExportInfo.GetTotalRowsMode - enum GetTotalRowsMethod

default value: InformationSchema

Gets or Sets a value indicates the method of how the total rows value is being obtained

This function is useful if you are developing a progress bar

InformationSchema = Fast, but approximate value

SelectCount = Slow but accurate

Skip = Skip obtaining total rows. Use this option if you are not doing any progress report.

Full List of ImportInfo Options

ImportInfo.IntervalForProgressReport - int

default value: 100

Gets or Sets a value indicates the interval of time (in milliseconds) to raise the event of ExportProgressChanged

ImportInfo.IgnoreSqlError - bool

default value: false

Gets or Sets a value indicates whether SQL errors occurs in import process should be ignored

maybe you use an old MySQL.Data.dll in your reference. In had the same problem and after update the reference to the MySQL.Data.dll located in the same binary directory as the MySqlBackup.dll, the problem was solved...

Thanks for your reply.
My Test database is created with charset utf8 and collation utf8_general_ci.
Tables in the database are created with the same charset and collation.
Input data is unicode. The backup and restore functions are working with
MySqlBackupTestApp 1.7.0.0 tool (MySql.Data.dll v6.9.7.0, MySqlBackup.dll v2.0.9.2)
I don't understand what is causing the error.
Any help welcome.

Sometimes, different version of MySQL server and configurations will result different export and import behavior. Therefore, if the same problem still appear at your database, try again at another different server, for example: www.db4free.net[^].

Hi adriancs,
Thank you very much for your support.
I backup Test database with MySqlBackupTestApp tool.
I re-checked restore Test dump file by SQLyog Ultimate tool (my local computer).
Result is normal but restore database with MySqlBackupTestApp tool is not.
Thus, the problem is an error in the restore database function.
MySQL version is 5.1.

Yes, I did try both export and import before posting the previous.
In fact, I myself do use this library in lots of application involving unicode characters.
and my database is also version 5.1.
I'm currently unable to identify the cause of your problem.
Please upload a sample project that will produce the error.
Please upload the project at here:MySqlBackup.NET - Issues[^]

I found an issue during backup from table having text field. It creates regular backup file and converts any cr nad new line chars into \r and \n, but leaving ASCI NUL 0x00 as it is.
Then I have to convert this char to \0 to have correct restore file.

Is this a bug, or ASCII NULL have a special treatment for some reason?
If you need any other details on this issue, I can send.

No useful information were provided by making me or others almost impossible to identified the problem. Your act seem to be trying to refuse to provide the least information as possible.
It looks like an attempt of trying to make a false report, play around and wasting other's time.
Please, don't ever do this.

Find something else to do.

like ... write a book, do some studies, etc...

Anyway, this library has been used in lots of commercial software in a production environment.
If you just post a sentence like that, you will have a big chance for being ignored in future.

Try something harder, like... try to find out any possible bugs. This is much appreciated.
If you do, you are really doing a great job.

MySql.Data.MySqlClient.MySqlException (0x80004005): Packets larger than max_allowed_packet are not allowed.
at MySql.Data.MySqlClient.MySqlStream.SendPacket(MySqlPacket packet)
at MySql.Data.MySqlClient.NativeDriver.ExecutePacket(MySqlPacket packetToExecute)
at MySql.Data.MySqlClient.NativeDriver.SendQuery(MySqlPacket queryPacket)
at MySql.Data.MySqlClient.Driver.SendQuery(MySqlPacket p)
at MySql.Data.MySqlClient.Statement.ExecuteNext()
at MySql.Data.MySqlClient.PreparableStatement.ExecuteNext()
at MySql.Data.MySqlClient.Statement.Execute()
at MySql.Data.MySqlClient.PreparableStatement.Execute()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at MySql.Data.MySqlClient.MySqlScript.Execute()
at MySql.Data.MySqlClient.MySqlBackup.Import_AppendLineAndExecute(String line) in ...\MySqlBackup\MySqlBackup.cs:line 1209
at MySql.Data.MySqlClient.MySqlBackup.Import_ProcessLine(String line) in ...\MySqlBackup\MySqlBackup.cs:line 1116
at MySql.Data.MySqlClient.MySqlBackup.Import_Start() in ...\MySqlBackup\MySqlBackup.cs:line 1010

i think you want client to resolve this!! isnt it ..

max_allowed_packet on my MySql server

max_allowed_packet=1,048,576

I am saying this because, you think you are smarter than other...
stop thinking that and kindly check your code for bugs
or
prepare export code in such a way that does not create issue while importing

an exported Sql becomes useless when not able to import for any reason.

Solution to this is

read max_allowed_packet variable from database in your code and
use that while exporting if user does not specify other size

which mean, if true, automatic retrieve value of max_allow_packet from server and apply it.
And this ExportInfo.UseServerMaxAllowedPacket will be overrided to false if the user manually specify the value of:

I've been working on my own sql dump utility independently and ran into a snag. So I've been scouring the web for code that converts a binary field into a string. My goal is to do it the way that tools like SqlYog does it. When I found this project of yours I hoped it might contain some code that does it but I see that you are taking an approach similar to what I was initially doing which is to generate a hex string for the binary. This of course works great for short binary fields but for longer ones it's not space efficient since each byte in the binary field will be output as two hex string chars. Alternately, SqlYog exports the binary field as a string which can be much more space efficient. So for example if a binary field holds 0x00000000007D3582 SqlYog will export this as this string '\0\0\0\0\0}5‚' For my sqldump tool I'd like to take this same approach. Any idea how once could do that in C#? PS: I know this question isn't about your MySqlBackup.Net project exactly, but I thought you might find it interesting given we are working on similar projects. Also the answer would be useful for your project too.