Export Each Table As Separate SQL Backup File

Obtain the list of tables from the database, then iterate over each table (or perform a loop on each table) to export it to a SQL file.

SQL statement for getting the list of tables:

SHOW TABLES;

In C#:

string databaseName = "test";
string constr = $"server=localhost;user=root;pwd=pwd;database={databaseName};convertzerodatetime=true;";

DataTable dt = new DataTable();

using (MySqlConnection conn = new MySqlConnection(constr))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        conn.Open();
        cmd.Connection = conn;

        cmd.CommandText = "show tables";

        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        da.Fill(dt);
    }
}

foreach (DataRow dr in dt.Rows)
{
    Console.WriteLine(dr[0] + "");
}

The above SQL statement is sufficient IF your database does not contain any VIEWS.

A VIEW is a method to present a virtual table based on the result-set of an SQL statement. You may ask ChatGPT/Gemini for more details about VIEWS in the MySQL context.

If your database does contain VIEWS, then you need to exclude them in the backup process.

Use this SQL statement instead:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 
TABLE_SCHEMA = 'databaseName' AND TABLE_TYPE = 'BASE TABLE';

Here's a complete example that demonstrates the idea:

using System;
using System.Data;
using MySqlConnector;
using System.IO;

namespace ConsoleApp_MySqlBackup
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string timenow = DateTime.Now.ToString("yyyy-MM-dd_HHmm");

            string backupFolder = $@"D:\mysql backup\{timenow}";

            Directory.CreateDirectory(backupFolder);

            string databaseName = "test";
            string constr = $"server=localhost;user=root;pwd=password;database={databaseName};convertzerodatetime=true;";

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

                        DataTable dtTables = new DataTable();

                        string sqlShowAllTables = $"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{databaseName}' AND TABLE_TYPE = 'BASE TABLE';";

                        cmd.CommandText = sqlShowAllTables;

                        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                        da.Fill(dtTables);

                        foreach (DataRow dr in dtTables.Rows)
                        {
                            string tableName = dr["TABLE_NAME"] + "";

                            string sqlFile = $@"{backupFolder}\{tableName}.sql";

                            string sqlSelect = $"SELECT * FROM `{tableName}`;";

                            mb.ExportInfo.TablesToBeExportedDic.Clear();
                            mb.ExportInfo.TablesToBeExportedDic[tableName] = sqlSelect;
                            mb.ExportToFile(sqlFile);
                        }
                    }
                }
            }
        }
    }
}