Backup All MySQL Databases in C# WinForm with 1 Click

• Download MySqlBackupAll_2019-06-25.zip
• Github

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:

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:

show databases;

Example of code block in 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:

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):

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:

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:

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!