Skip to content

Using mysqldump and mysql.exe Command Line Tool To Backup, Restore, Export, Import MySQL Database

Jump to bottom
adriancs edited this page Jun 27, 2025 · 3 revisions

Using mysqldump and mysql.exe for MySQL Backup and Restore Guide Using mysqldump and mysql.exe

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. This guide is tailored for developers seeking to implement secure and efficient database backup and restore operations, particularly in a C# environment.

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 two 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

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 ""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, you can run mysql.exe directly and use 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 "--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
  • Incorrect Syntax: --defaults-extra-file="C:\path\to\config\my.ini"
  • The --execute=SOURCE {file_sql} command may attempt to read binary files if not properly validated. 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!

Originally adapted from adriancs.com.

Clone this wiki locally