(Sharing) Using mysqldump and mysql.exe in C# Process without using external script - Backup and Restore of MySQL databases.

[adriancs2]

First, this is not about mysqlconnector (MIT), but since we're all here, we're most certainly the fan club of C# mysql. Guess, this is one of the best place to share this piece of info. Thank you for allowing this info to be shared in this platform.

This is about using mysqldump and mysql.exe directly in C# to perform backup and restore of MySQL.

Okay, you might be wondering... Wait aren't that info already "Everywhere" in Google? or aren't that can be easily obtained from AI?

and ya that's the same thing that I initially thought. but throughout my journey in solving this problem, it's no. and there is a big gap of missing steps and guides to use these tools (mysqldump and mysql.exe) directly in C#.

After spending a lots of time (few days maybe) diving in using "try-and-error", I finally pieced the puzzle of why mysql.exe (yupe especially mysql.exe) is not able to run directly in C#.

Or maybe I am just the bad google user? or bad AI prompter? no actually I used multiple AI agent and none able to provide the correct answer.

Here's my sharing:

mysqldump is the easiest piece of puzzle. It's straight forward:

mysqldump.exe -u {user} -p{password} {database} --result-file="{output_file}"

This is what you'll find (or AI) provided. But when you try to run this in C#, it will cause exception, the process won't allow you to put the plain password in the command line.

The missing step is... you need to put the password in a mysql config file. Write the following as a text file.

[client]
user=root
password=myrootpassword
host=localhost
port=3306
default-character-set=utf8mb4

and the full basic C# code to run this:

public static async Task Backup()
{
    string user = "root";
    string pwd = "password";
    string host = "localhost";
    int port = 3306;
    string database = "database_name";
    string charset = "utf8mb4";

    string random = DateTime.Now.ToString("ffff");
    string fileMySqlDump = @"C:\mysql\bin\mysqldump.exe";
    string fileConfig = $@"C:\backup\my_temp_{random}.ini";
    string fileSql = @"C:\backup\daily 2025-06-27\backup.sql";

    string configContent = $@"[client]
user={user}
password={pwd}
host={host}
port={port}
default-character-set={charset}";

    File.WriteAllText(fileConfig, configContent);

    string arg = $"--defaults-file=\"{fileConfig}\" --routines --events {database} --result-file=\"{fileSql}\"";

    var processStartInfo = new ProcessStartInfo
    {
        FileName = fileMySqlDump,
        Arguments = arg,
        UseShellExecute = false,
        CreateNoWindow = true,
        WindowStyle = ProcessWindowStyle.Hidden,
        RedirectStandardOutput = true,
        RedirectStandardError = true
    };

    // Schedule automatic deletion of the config file, it was meant for temporary
    // After the process run, the file can be deleted immediately
    _ = Task.Run(() => AutoDeleteFile(fileConfig));

    using (var process = Process.Start(processStartInfo))
    {
        Task<string> outputTask = process.StandardOutput.ReadToEndAsync();
        Task<string> errorTask = process.StandardError.ReadToEndAsync();

        process.WaitForExit();

        // record any process output
        string output = await outputTask;

        // record any process error message
        string errors = await errorTask;

        if (process.ExitCode != 0 || !string.IsNullOrEmpty(errors))
        {
            // potential process error
            throw new Exception($"Process error: [Exit Code:{process.ExitCode}] {errors}");
        }
    }
}

static void AutoDeleteFile(string filePathCnf)
{
    // delay the action for 1 second
    Thread.Sleep(1000);

    try
    {
        File.Delete(filePathCnf);
        return;
    }
    catch { }
}

and that is for mysqldump.

Next, is the most missing steps for running mysql.exe.

So, what we can easily get is:

mysql.exe -u {username} -p{password} --database={target_database} < {sql_file}

If we gonna run this in C#, there are some C# specific missing steps in order for this to run successfully. Okay... let's cut to summary.

First puzzle, just like mysqldump, the password need to write to a config file, it is therefore the command line will look like this:

mysql.exe --defaults-file="C:\mysql\my.ini" --database=show_expresso < "C:\backup.sql"

Next puzzle, the most misleading point here is being the usage of the symbol "<". It will fail, if use it directly on mysql.exe.

"<" does not belong to mysql.exe, it won't understand it.

It is for SHELL (in windows, that refers to CMD). It's a file redirection. It uses the OS (Windows/Linux/Unix/Mac) specific file i/o operation to feed the content of the file into the process as if they are typed in the standard input.

You have to use the SQL command of "SOURCE" to tell mysql.exe to load the SQL import statement directly from a file, so the argument will look like this:

"--execute=SOURCE C:\backup.sql"

or this:

"--execute=SOURCE 'C:\backup.sql'"

both work either way

It uses mysql.exe internal built-in C\C++ file i/o operation to load the text file into it's process directly.

3rd missing puzzle, each block of argument must be wrapped in double quote:

mysql.exe ".... argument 1... " ".... argument 2... " ".... argument 2... "

another rule is, you cannot place additional double quote within a argument block:

This will break the syntax:
"--defaults-file="C:\mysql 8\my.ini""
This is correct:
"--defaults-file=C:\mysql 8\my.ini"
It accepts white space within the argument

So, now, the final basic C# will look like this:

string mysqlexe = @"C:\mysql 8.0\bin\mysql.exe";
string file_sql = @"C:\mysql 8.0\daily backup\2025-06-30 1251.sql";

string arg = $"\"--defaults-extra-file={fileConfig}\" \"--database={database}\" \"--execute=SOURCE {file_sql}\"";

var processStartInfo = new ProcessStartInfo
{
    FileName = mysqlexe,
    Arguments = arg,
    UseShellExecute = false,
    CreateNoWindow = true,
    WindowStyle = ProcessWindowStyle.Hidden,
    RedirectStandardOutput = true,
    RedirectStandardError = true
};

// Schedule automatic deletion of the config file, it was meant for temporary
// After the process run, the file can be deleted immediately
_ = Task.Run(() => AutoDeleteFile(fileConfig));

using (var process = Process.Start(processStartInfo))
{
    Task<string> outputTask = process.StandardOutput.ReadToEndAsync();
    Task<string> errorTask = process.StandardError.ReadToEndAsync();

    process.WaitForExit();

    // record any process output
    string output = await outputTask;

    // record any process error message
    string errors = await errorTask;

    if (process.ExitCode != 0 || !string.IsNullOrEmpty(errors))
    {
        // potential process error
        throw new Exception($"Process error: [Exit Code:{process.ExitCode}] {errors}");
    }
}

Another more traditional way of using mysq.exe to import SQL file, is execute it through CMD. For this work, the primary process becomes CMD. And it accept the file redirection of the symbol "<", which we can finally use it as part of the argument. The following line:

mysql.exe --defaults-extra-file="C:\mysql\my.ini" --database=show_expresso < "C:\backup.sql"

must be wrapped as a single argument that is to be passed to CMD, which means:

cmd.exe /C "....."

cmd.exe /C ".... wrap everything above as single argument..."

Example:
cmd.exe /C ""C:\mysql 8.1\bin\mysql.exe" --defaults-extra-file="C:\mysql\my.ini" --database=show_expresso < "C:\backup.sql""

"/C" >> Yes, here's another missing puzzle, you have to use this switch, to tell the process to run without user interaction.

The basic C# code will look like this:

string mysqlexe = @"C:\mysql 8.0\bin\mysql.exe";

string arg = $"/C \"\"{mysqlexe}\" --defaults-extra-file=\"{fileConfig}\" --database={database} < \"{sql_file}\"\"";

var processStartInfo = new ProcessStartInfo
{
    FileName = "cmd.exe",
    Arguments = arg,
    UseShellExecute = false,
    CreateNoWindow = true,
    WindowStyle = ProcessWindowStyle.Hidden,
    RedirectStandardOutput = true,
    RedirectStandardError = true
};

// Schedule automatic deletion of the config file, it was meant for temporary
// After the process run, the file can be deleted immediately
_ = Task.Run(() => AutoDeleteFile(fileConfig));

using (var process = Process.Start(processStartInfo))
{
    Task<string> outputTask = process.StandardOutput.ReadToEndAsync();
    Task<string> errorTask = process.StandardError.ReadToEndAsync();

    process.WaitForExit();

    // record any process output
    string output = await outputTask;

    // record any process error message
    string errors = await errorTask;

    if (process.ExitCode != 0 || !string.IsNullOrEmpty(errors))
    {
        // potential process error
        throw new Exception($"Process error: [Exit Code:{process.ExitCode}] {errors}");
    }
}

and one more final tips for running mysql.exe in C#, we can also use C# .NET file i/o operation to feed the content into mysql.exe main standard input console. Here's the example:

string mysqlexe = @"C:\mysql 8.0\bin\mysql.exe";

// remove the SOURCE command
string arg = $"\"--defaults-extra-file={fileConfig}\" \"--database={database}\"";

var processStartInfo = new ProcessStartInfo
{
    FileName = mysqlexe,
    Arguments = arg,
    UseShellExecute = false,
    CreateNoWindow = true,
    WindowStyle = ProcessWindowStyle.Hidden,
    
    // Allow input from C# .NET I/O Stream
    RedirectStandardInput = true,
    
    RedirectStandardOutput = true,
    RedirectStandardError = true
};

// Schedule automatic deletion of the config file, it was meant for temporary
// After the process run, the file can be deleted immediately
_ = Task.Run(() => AutoDeleteFile(fileConfig));

using (var process = Process.Start(processStartInfo))
{
    // Start reading output/error asynchronously
    Task<string> outputTask = process.StandardOutput.ReadToEndAsync();
    Task<string> errorTask = process.StandardError.ReadToEndAsync();
    
    // Stream the file content in chunks (memory-efficient)
    using (StreamReader reader = new StreamReader(file_sql))
    {
        char[] buffer = new char[4096]; // 4KB buffer
        int charsRead;
        
        while ((charsRead = reader.Read(buffer, 0, buffer.Length)) > 0)
        {
            process.StandardInput.Write(buffer, 0, charsRead);
        }
        process.StandardInput.Close();
    }
    
    process.WaitForExit();
    
    // Get results from async tasks
    string output = await outputTask;
    string errors = await errorTask;
    
    if (process.ExitCode != 0 || !string.IsNullOrEmpty(errors))
    {
        // potential process error
        throw new Exception($"Process error: [Exit Code:{process.ExitCode}] {errors}");
    }
}

ya... that's all from my sharing. In case of if I missed something, feel free to fill up the gap with your comment below

Happy coding ;)

The info shared in this post is originally posted at my personal blog:
https://adriancs.com/mysql/1741/c-using-mysql-instance-mysql-exe-and-mysqldump-command-line-tool-to-backup-restore-export-import-mysql-database/

Another slightly adapted version is posted at wiki page of MySqlBackup.NET:
https://github.com/MySqlBackupNET/MySqlBackup.Net/wiki/Using-mysqldump-and-mysql.exe-Command-Line-Tool-To-Backup,-Restore,-Export,-Import-MySQL-Database

Alternatively, you can MySqlBackup.NET (https://github.com/MySqlBackupNET/MySqlBackup.Net), a C# open source tool used for backup and restore of MySQL databases. It works on ASP.NET too, you may view the github repository, there's already a working example that uses MySQLBackup.NET in ASP.NET with various strategy including backup/restore of very large database.

Cheers :D

[adriancs2]

I'm wondering how many of you guys actually already knew this, or I'm just late to the party?

[adriancs2]

If you are new to mysqldump and mysql.exe. Sorry that I bypass the introduction. both mysqldump.exe and mysql.exe are built-in tool installed together with MySQL server, that mysqldump.exe is specifically used for exporting a database into a text file with bunch of SQL statements than can be later used for import. ya, mysql.exe is used for importing the sql file, but it's not it's only function, it does more that just importing sql files.