Skip to content

C# ‐ Executing mysqldump and mysql.exe Directly in System.Diagnostics.Process Without External Script

Jump to bottom
adriancs edited this page Jul 22, 2025 · 9 revisions

C# - Executing mysqldump and mysql.exe Directly in System.Diagnostics.Process Without External Script

This is the world's most comprehensive C#-specific guide on using mysqldump.exe and mysql.exe directly, before AI even know how do this. Most probably still don't know by now, at the time of writing this. Note: Grok has already added this info into it's knowledge base.

Welcome to this comprehensive guide on using MySQL's built-in command-line tools, mysqldump and mysql.exe, for backing up and restoring MySQL databases in C# System.Diagnostics.Process.

Backup / Export Using mysqldump

The mysqldump.exe tool is MySQL's built-in utility for exporting or backing up databases. Below are the key syntaxes and examples for using it effectively.

Basic Usage

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

Example:

mysqldump.exe -u root -pmyrootpassword shop_expresso --result-file="C:\output.sql"

Advanced Usage with Specific Options

For more control, you can include additional parameters like host, port, character set, routines, and events.

mysqldump.exe -u {user} -p{password} -h {host} -P {port} --default-character-set={charset}
              --routines --events {database} --result-file="{output_file}"

Example:

mysqldump.exe -u root -pmyrootpassword -h localhost -P 3306 --default-character-set=utf8mb4
              --routines --events shop_expresso --result-file="C:\output.sql"

Using a Config File for Security

To avoid exposing passwords in command-line arguments (especially in C# applications), use a configuration file. Create a text file (e.g., my.ini, my.cnf, or any .txt file) with the following content:

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

Save it to a temporary location, such as:

  • C:\my.ini
  • C:\any_path\to_folder\my.cnf
  • C:\mysql\backup\daily_2025-06-07\my.txt

Then, execute mysqldump with the config file:

mysqldump.exe --defaults-file="{config_file}" --routines --events {database} --result-file="{output_file}"

or

mysqldump.exe --defaults-extra-file="{config_file}" --routines --events {database} --result-file="{output_file}"

Example:

mysqldump.exe --defaults-file="C:\my.ini" --routines --events shop_expresso --result-file="C:\output.sql"
mysqldump.exe --defaults-extra-file="C:\my.ini" --routines --events shop_expresso --result-file="C:\output.sql"

C# Implementation for mysqldump

Below is a C# code snippet to execute mysqldump securely using a config file:

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 temporary config file
    _ = Task.Run(() => AutoDeleteFile(fileConfig));

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

        process.WaitForExit();

        string output = await outputTask;
        string errors = await errorTask;

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

static void AutoDeleteFile(string filePathCnf)
{
    Thread.Sleep(1000); // Delay for 1 second
    try
    {
        File.Delete(filePathCnf);
    }
    catch { }
}

Restore / Import Using mysql.exe

The mysql.exe command-line tool can restore (or import) a database in 3 primary ways:

  1. Using CMD shell with file redirection (<).
  2. Running mysql.exe directly with the SOURCE command.
  3. Running mysql.exe directly without SOURCE command by using C# StreamReader

Method 1: Using CMD Shell with File Redirection

This method uses the shell's < operator to feed the SQL file content to mysql.exe.

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

Example:

mysql.exe -u root -pmyrootpassword --database=shop_expresso < "C:\backup.sql"

Advanced Usage

Include additional options for more control:

mysql.exe -u {username} -p{password} -h {host} -P {port} --default-character-set={charset}
          --database={target_database} < {sql_file}

Example:

mysql.exe -u root -pmypassword -h localhost -P 3306 --default-character-set=utf8mb4
          --database=shop_expresso < "C:\backup.sql"

Using a Config File

Above examples exposing password in the arguments, which is not allowed in C#. It will cause error. To avoid exposing passwords, use a config file (as shown in the backup section):

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

or

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

Since the < operator is a shell feature (not part of mysql.exe), you must run mysql.exe through cmd.exe. The entire command, including mysql.exe and its arguments, is passed as a single argument to cmd.exe using the /C flag:

cmd.exe /C "....wrap everything as single argument including double quote...."

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

C# Implementation

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 temporary config file
_ = Task.Run(() => AutoDeleteFile(fileConfig));

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

    process.WaitForExit();

    string output = await outputTask;
    string errors = await errorTask;

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

Method 2: Running mysql.exe Directly with SOURCE Command

Instead of using CMD shell redirection, we can run mysql.exe directly as the main program. In this case, the symbol of < file redirection cannot be used. mysql.exe will not understand this < symbol. Instead, it uses the SOURCE command to load the SQL file. This approach relies on mysql.exe's internal file I/O operations. Each argument passed to mysql.exe must be individually wrapped in double quotes, but sub-arguments (e.g., file paths) should not include additional quotes.

mysql.exe "... argument 1..." ".. argument 2.." ".. argument 3.." ".. argument 4.."

Note: within each block of argument, white space is allowed

mysql.exe "--defaults-extra-file={fileConfig}" "--database={database}" "--execute=SOURCE {file_sql}"

Example:

mysql.exe "--defaults-extra-file=C:\my daily backup\my.ini" "--database=shop_expresso" 
          "--execute=SOURCE C:\mysql\backup\daily backup 2025-06-27\backup.sql"

Important Notes:

Correct Syntax   : --defaults-extra-file=C:\path to config\my.ini   << white space allowed
Incorrect Syntax : --defaults-extra-file="C:\path to config\my.ini"

Wrap with single quote in file path:

No wrapping
--execute=SOURCE {file_sql} << command may attempt to read binary files if not properly validated.

Wrap with single quote
--execute=SOURCE '{file_sql}' << Using quotes around the file path (e.g., `'file_sql'`) may restrict binary file reading

but both approaches work for text files.

C# Implementation

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

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 temporary config file
_ = Task.Run(() => AutoDeleteFile(fileConfig));

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

    process.WaitForExit();

    string output = await outputTask;
    string errors = await errorTask;

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

Method 3: Executing mysql.exe Directly Without SOURCE Command

An alternative approach is to use C#’s StreamReader to read the SQL file and feed its content directly to mysql.exe’s standard input. This method avoids the SOURCE command and is memory-efficient for large files.

C# Implementation

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

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

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

// Schedule automatic deletion of the temporary config file
_ = 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();

    string output = await outputTask;
    string errors = await errorTask;

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

Conclusion

This guide covers the essentials of using mysqldump and mysql.exe for MySQL database backup and restore operations, with a focus on secure and efficient C# implementations. Key points include:

  • Using configuration files to avoid exposing sensitive credentials.
  • Multiple methods for restoring databases, including CMD shell redirection, the SOURCE command, and direct streaming via C#.
  • Memory-efficient handling of large SQL files using buffered streaming.

For additional tools, explore MySqlBackup.NET for a C# open-source alternative. Thank you for reading, and happy coding!

Clone this wiki locally