MySQL: Backup, compress, and FTP from WinForms

• Download source code - 37.3 KB

Introduction

In this article, we will implement two menu commands from our Windows Forms application:

1. backup entire MySQL database, compress it, and FTP it to an FTP server.
2. reverse = fetch from FTP server, uncompress, restore to MySQL. Kind of an open/save functionality for my database app.

Why? A way for me to be more mobile. At night, I want to be able to solve my backup problem easily from within my main business app just by selecting "Backup" or even "Save" in my menu. And the next day, I might be travelling, so from my notebook, I want to be able to open the latest version and keep working. Since it took a few seconds to run it with my 30 MB database, I chose to use a BackgroundWorker thread to do the work and report the progress back to a textbox in the GUI to avoid a totally frozen GUI.

Background

I have set some goals for the infrastructure of my business for this year. Moving from MS SQL Server to MySQL is one of them; trying to find a more lightweight IDE alternative is another - with the main intention of getting more mobile. So, while the tasks I outlined in the beginning that we're about to implement here is no rocket science, and can typically be solved with a few minutes of scripting, I found it pretty neat to have it all inside my main business app.

Using the Code

We need VS2008 Express and C# 2.0. We are developing a Windows Forms app here, with two buttons: one for restore and one for backup. The backups are always stored in <path>\backups. I only allow for one generation (files are deleted). All settings are done in the app.config file. No overriding is done in the GUI etc.

The filename of the backup in this example is testfile.sql, and depending on the compression method, it might be testfile.sql.gz or testfile.sql.tar.

The last parameter "BackupFTPDirectory" can be a subdirectory on the FTP server, say "/mybackups". <!-- 'Internal' (gz) or 'path to winrar\rar.exe'.

How do we compress the .sql file before FTP? As I said, I really didn't feel like waiting for my 30 MB backup file to be downloaded/uploaded so I wanted some compression. Either internal (GZip) or a path to the rar.exe is supported right now. In the /backups directory, you will find two cmd files. Backmysql.cmd:

"C:\xampp\mysql\bin\mysqldump.exe"
--no-create-db
--routines
--host localhost 
--user root
--password=enterpwhere %1 > "%2"

You need to change the path to mysqldump and enter the correct username and password, of course. The app will call this cmd file for executing the database backup.

Restmysql.cmd:

"C:\xampp\mysql\bin\mysql.exe" -u root -penterpwdhere -D %1 < "%2"

You need to change the path to MySQL and enter the correct username and password, of course. The app will call this cmd file for executing the database backup.

Compression of files

My first idea was to use the internal GZipStream (available in the namespace System.IO.Compression). Good enough for me I thought, and no dependencies at all.

sZipped = sLocalDir + "\\" + oParams.FileNameWithoutExt + ".sql.gz";
byte[] buffer = System.IO.File.ReadAllBytes(sLocalDir + "\\" +
    oParams.FileNameWithoutExt + ".sql");
Stream fs = File.Create(sZipped);
GZipStream gZip = new GZipStream(fs, CompressionMode.Compress, true);

gZip.Write(buffer, 0, buffer.Length);
gZip.Close();
fs.Close();

However, my 33 MB only got compressed to over 9 MB, and when I ran WinRar against it, I got under it 7.5 MB. 2.5 MB is a big deal when FTPing, so I had to implement a better compression method. Of course, there is the ISharpCode zip library, but I didn't feel like dragging in an over 200K library dependency into my app. However, since it's my app and my boxes, I can depend on WinRar being installed on all boxes (that's the compression app I have chosen to use). So I whipped up an ugly fix for it.

if (oParams.CompressionMethod == "Internal")
{
    ..old gz
}
else
{
    System.Diagnostics.Process.Start 
d:\program\winrar\rar.exe a filename.sql.rar filename.sql
}

FTP

I know there are a lot of existing FTP libraries out there, free and commercial. I took FTPFactory.cs from Jaimon Mathew's single file, really easy to use.

FTPFactory oFTP = new FTPFactory();
oFTP.setRemoteHost(oParams.FTPServer);
oFTP.setRemoteUser(oParams.FTPAccount);
oFTP.setRemotePass(oParams.FTPPassword);
oFTP.login();
if (oParams.FTPDir.Length > 0)
    oFTP.setRemotePath(oParams.FTPDir);
oFTP.upload(sLocalFile);
oFTP.close();

GUI and BackgroundWorker

As I said, the whole operation takes a few seconds with my 6 MB RAR file. Waiting for it is no big deal since I do it once in the morning and once at night, but a non-freezing GUI and some sort of progress indicator was something I decided to add.

So, in the code, you'll see that the button handlers have code like this:

WorkerParams oParams = new WorkerParams();
oParams.LocalBackupDir = txtDirectory.Text;
oParams.ZipFTP = chFTP.Checked;
oParams.FTPServer = txtFTPServer.Text;
oParams.FTPAccount = txtFTPLogin.Text;
oParams.FTPPassword = txtFTPPassword.Text;
oParams.FTPDir = txtFTPDir.Text;
oParams.DatabaseName = 
    System.Configuration.ConfigurationManager.AppSettings["BackupDatabaseName"];
oParams.CompressionMethod =
    System.Configuration.ConfigurationManager.AppSettings["BackupCompressionMethod"];
oParams.WType = WorkerParams.WorkType.Backup;
oParams.FileNameWithoutExt =
    System.Configuration.ConfigurationManager.AppSettings["BackupFileName"];

BackgroundWorker bw = new BackgroundWorker();
bw.WorkerReportsProgress = true;
bw.WorkerSupportsCancellation = true;
bw.ProgressChanged += new ProgressChangedEventHandler(bw_ProgressChanged);
bw.DoWork += new DoWorkEventHandler(bw_DoWork);
bw.RunWorkerAsync(oParams);

We're creating a worker thread through BackgroundWorker and feeding it a WorkerParams object (see WorkerParams.cs) which contains all the variables.

The work callback looks basically like this:

void bw_DoWork(object sender, DoWorkEventArgs e)
{
    BackgroundWorker oWorker = sender as BackgroundWorker;
    WorkerParams oParams = e.Argument as WorkerParams;

    if (oParams.WType == WorkerParams.WorkType.Backup)
    {
        //Backup
        oWorker.ReportProgress(0, "Starting backup");
        RemoveOldFiles(oParams);
        oWorker.ReportProgress(10, "Old files removed");
        oWorker.ReportProgress(11, "Starting DB Backup");
        RunDBBackup(oParams);
        oWorker.ReportProgress(50, "DB Backup done");
        oWorker.ReportProgress(51, "Starting compression");
        string sLocalFile = RunCompress(oParams);
        oWorker.ReportProgress(70, "Compression done");
        oWorker.ReportProgress(71, "FTP");
        RunSendFTP(oParams, sLocalFile);
        oWorker.ReportProgress(90, "FTP done");
        oWorker.ReportProgress(100, "Done");
    }
}

We run each step one at a time and report the progress back to the GUI thread. Which just appends the text to the multiline "status" textbox.

void bw_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
    string sWhat = e.UserState as String;
    textBox1.Text += sWhat + Environment.NewLine;
    if (e.ProgressPercentage == 100)
    {
        button2.Enabled = true;
        button1.Enabled = true;
    }
}