Click here to Skip to main content
15,867,277 members
Articles / Desktop Programming / Windows Forms
Tip/Trick

Backup All MySQL Databases in C# WinForm with 1 Click

Rate me:
Please Sign up or sign in to vote.
4.60/5 (4 votes)
17 Sep 2018CPOL1 min read 14.1K   1.1K   9  
A tool to backup & restore all MySQL databases in one click

Introduction

I have written a tool that will backup all MySQL databases in a single click. Done in C# WinForm, but the source code can easily be integrated into other platforms (such as ASP.NET, Mono, etc.).

Backup

This tool is mainly using MySqlBackup.NET (github, CodeProject) as core library to perform the backup/restore work.

A typical code block to backup one specific pre-defined database will be something like this:

C#
string connstr = "server=localhost;user=root;pwd=1234;database=db1;sslmode=none;convertdatetime=true;";
string backupfile = "C:\\backup.sql";

using (MySqlConnection conn = new MySqlConnection(connstr))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            conn.Open();
            cmd.Connection = conn;

            mb.ExportToFile(backupfile);

            conn.Close();
        }
    }
}

In order to backup all databases, we'll need to get a list of databases and loop through them one by one to perform the backup work.

This SQL command will get the list:

SQL
show databases;

Example of code block in C#:

C#
string connstr = "server=localhost;user=root;pwd=1234;sslmode=none;convertdatetime=true;";

using (MySqlConnection conn = new MySqlConnection(connstr))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            conn.Open();
            cmd.Connection = conn;

            cmd.CommandText = "show databases;";
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataTable dtDbList = new DataTable();
            da.Fill(dtDbList);

            conn.Close();
        }
    }
}

Next, we loop through the databases one by one to perform the backup work:

C#
cmd.CommandText = "show databases;";
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dtDbList = new DataTable();
da.Fill(dtDbList);

string defaultFolder = "C:\\backup_folder";

foreach(DataRow dr in dtDbList.Rows)
{
    string dbname = dr[0] + "";
    
    // skip mysql default system tables
    switch (dbname)
    {
        case "sys":
        case "performance_schema":
        case "mysql":
        case "information_schema":
            continue;
    }
    
    string dumpFile = System.IO.Path.Combine(defaultFolder, dbname) + ".sql";

    cmd.CommandText = "use `" + dbname + "`;";
    cmd.ExecuteNonQuery();

    mb.ExportToFile(dumpFile);
}

Restore

For the restore task, loop through all the backup files and perform the restore one by one.

Code for getting a list of backup files (dump files):

C#
string defaultBackupFolder = "C:\\backup_folder";
string[] files = System.IO.Directory.GetFiles(defaultBackupFolder);

foreach (string file in files)
{
    if (file.ToLower().EndsWith(".sql"))
    {
        // Perform backup
    }
}

Begin restoration of database:

C#
foreach (string file in files)
{
    if (file.ToLower().EndsWith(".sql"))
    {
        string dbName = System.IO.Path.GetFileNameWithoutExtension(file);

        cmd.CommandText = "create database if not exists `" + dbName + "`";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "use `" + dbName + "`";
        cmd.ExecuteNonQuery();

        mb.ImportFromFile(file);
    }
}

The full code block will be something like this:

C#
string connstr = "server=localhost;user=root;pwd=1234;sslmode=none;convertdatetime=true;";

using (MySqlConnection conn = new MySqlConnection(connstr))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            conn.Open();
            cmd.Connection = conn;

            string defaultBackupFolder = "C:\\backup_folder";
            string[] files = System.IO.Directory.GetFiles(defaultBackupFolder);

            foreach (string file in files)
            {
                if (file.ToLower().EndsWith(".sql"))
                {
                    string dbName = System.IO.Path.GetFileNameWithoutExtension(file);

                    cmd.CommandText = "create database if not exists `" + dbName + "`";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "use `" + dbName + "`";
                    cmd.ExecuteNonQuery();

                    mb.ImportFromFile(file);
                }
            }

            conn.Close();
        }
    }
}

The sample project that I have included in this page is presented in WinForm. If the backup or restore process takes a long time, the program will be frozen and has a high possibility to have the Time Out (more than 60 seconds) error.

One of the possible solutions is by running the process using BackgroundWorker so that the process will be executed on a separate thread.

Thanks for reading and happy coding!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Other Other
Programming is an art.

Comments and Discussions

 
-- There are no messages in this forum --