Backup and Restore MySQL database (.sql) using C# .NET

Links

GitHub Repository — https://github.com/joemoceri/database-toolkit

Backup

In order to backup a MySQL database we first need to know the name of the database on the server and the path where to save it locally. Consider the following method and appsettings.json.

We need to use mysqldump, a command line utility for MySQL to create a database backup. In order to do this using C# .NET, we need to use the Process class and set the FileName to the property MySqlDumpPath set in appsettings.json.

startInfo.FileName = options.Value.MySqlDumpPath;

We pass along the defaults file so there’s no need to use the -u or -p switches for usernames and passwords. You can’t use < and > when running processes using the Process class, fortunately mysqldump supports a -r switch which we can use instead of >.

startInfo.Arguments = $@"--defaults-file=""{options.Value.MySqlDefaultsFilePath}"" {databaseName} -r {localDatabasePath}";

In order for this to work, you need to make sure that you update your defaults file. On Windows the path to my.ini is the following.

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Open this file and make sure under [mysqldump] you place user and password with the credentials you’d like to use when connecting.

The final mysqldump command will look like this.

C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" sakila -r C:\backups\sakila.sql

After, we specify a few flags regarding the process so it doesn’t create a command window and to not use shell execute. We call Start first, then WaitForExit so we don’t terminate the process early and Close it after.

startInfo.CreateNoWindow = true;
startInfo.UseShellExecute = false;
process.StartInfo = startInfo;
process.Start();
process.WaitForExit();
process.Close();

It will create a .sql file at the location specified in localDatabasePath.

Restore

In order to restore a MySQL database, we need the name of the database on the server and the local path to the database we’re restoring. Consider the following method, using the same appsettings.json from above.

This method ran into the same issue above regarding the use of < in the process arguments. To circumvent this, we need to use a .bat file and place our mysql script there.

First we set the FileName of the process to the .bat file ‘mysql-restore.bat’.

Inside the file, we need to use variables, so our command looks like this.

Here we can omit the -u and -p flags for the same reasons above. Make sure to update your defaults file [mysql] group with your user and password.

The first and third argument should be quoted to handle paths that contain spaces, the second argument is the name of the database and should not be quoted.

startInfo.Arguments = $@"""{options.Value.MySqlDefaultsFilePath}"" {databaseName} ""{localDatabasePath}""";

The final restore command will look like this.

mysql --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" sakila < "C:\backups\sakila.sql"

After, we run the same methods as above to Start and wait for the process to exit.

startInfo.CreateNoWindow = true;
startInfo.UseShellExecute = false;
process.StartInfo = startInfo;
process.Start();
process.WaitForExit();
process.Close();

Once the command finishes running the database will be restored on the server.