Backing up Database in MySQL using C#

Question

I created a Winforms in order to backup my Database. Then When I run my program it gives an Win32Exception was unhandled. "The system cannot find the file specified" Although the file is already existed and resulted to that exception.

Here is my code regarding my problem

using System.Diagnostics;

private void btnProceed_Click(object sender, EventArgs e)
{
            path = @"D:\MySQL\MySQL Server 5.5\bin\mysqldump.exe -u " + txtBoxDBUsername.Text + @" -p " + txtBoxDBName.Text + @" > D:\C#\Client\Salesmate - EMC\SalesMate\Backup\" + maskeTxtBoxDBFile.Text + @"";
            Process p = new Process();
            p.StartInfo.FileName = path;
            p.Start();
}

Solution 1

You can use MySqlBackup.NET as alternative to MySqlDump
Documentation:
http://www.codeproject.com/Articles/256466/MySqlBackup-NET-MySQL-Backup-Solution-for-Csharp-V
https://github.com/MySqlBackupNET/MySqlBackup.Net

Sample codes:

Backup a MySQL database

using MySql.Data.MySqlClient; 

then the code,

private void Backup()
{
    string constring = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\backup.sql";
    using (MySqlConnection conn = new MySqlConnection(constring))
    {
        using (MySqlCommand cmd = new MySqlCommand())
        {
            using (MySqlBackup mb = new MySqlBackup(cmd))
            {
                cmd.Connection = conn;
                conn.Open();
                mb.ExportToFile(file);
                conn.Close();
            }
        }
    }
}

Restore a MySQL database

private void Restore()
{
    string constring = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\backup.sql";
    using (MySqlConnection conn = new MySqlConnection(constring))
    {
        using (MySqlCommand cmd = new MySqlCommand())
        {
            using (MySqlBackup mb = new MySqlBackup(cmd))
            {
                cmd.Connection = conn;
                conn.Open();
                mb.ImportFromFile(file);
                conn.Close();
            }
        }
    }
}

Update:
I am one of the author of this library.

Solution 2

I have try that code but problem is occure in before run. exeption is -An unhandled exception of type 'System.IO.FileLoadException' occurred in System.Windows.Forms.dll

Additional information: Could not load file or assembly 'MySql.Data, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

Comments

[Andrew Barber]

Thanks for posting your answer! Please be sure to read the FAQ on Self-Promotion carefully. Also note that it is required that you post a disclaimer every time you link to your own site/product.

[Andrew Barber]

By disclaimer, I mean about the fact that you are posting about your own product/website; what you added at the bottom in the 'Update'.

[Smith]

how can it be made to backup at regular intervals, say once everyday

[mjb]

@Smith Schedule task

[mjb]

The DLL of "MySql.Data.DLL" is not shipped with your program assemblies. You have to mark the DLL's property in your project as "Copy Local"

[Hassan Rahman]

If you have large database, mysqlcommand will give you timeout exception. You can increase it's timeout limit by MySqlCommand cmd = new MySqlCommand(); cmd.CommandTimeout = 600;

[mjb]

@HardikVinzava Yes, it is possible. But you have to modify all your tables to include "update_time". Then you have to cache the "last_backup_time" in a text file in your application folder. Then you will export all rows that have the "update_time" older than the "last_backup_time". Apply the option of MySqlBackup.BackupInfo.ExportRowsMode = Replace

[Hardik Vinzava]

Hi @mjb Thanks for your reply. Actually I don't have any control on adding new columns (as it's a third-party system) on the existing system. Do you have any other suggestions? Or Can we use this dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/… into your code?

[mjb]

@HardikVinzava I'm unsure how to do this at the momoent.

[JoeyB]

Why are you closing the connection manually? By using the "using statement" the connection is automatically closed.

[A X]

Can you make a version of this library that doesn't use the Oracle GPL client? As a result your library is also GPL!

[mjb]

@Abr I will try to have a look on this