MySQL backup,compress and FTP from WinForms app

Jul 07 2008

MySQL backup,compress and FTP from WinForms app

Posted by admin under .NET

What is this?

From your Windows Forms application, implement two menu commands
a) backup entire MySQL database, compress it and send it with FTP to a ftp server
b) reverse = fetch from FTP server, uncompress, restore to MySQL

Kind of open/save functionality for my database app. Why? A way for me to be more mobile. At night I wanna 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 wanna 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 progress back to a textbox in the GUI to avoid a totally freezed GUI.

Background
As you might know I have set some goals for the infrastructure of my business for this year. Moving from MSSQL 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 we're about to implement here is no rocket science, and can typically be solved with some minutes of scripting, I found it pretty neat to have it all inside my main business app.

The app in sample solution

VS 2008 Express, C# 2.0.
Windows forms app. Two buttons, one for restore and one for backup.

Convention before configuration. I have made it good anough for me. Which means the backups are stored always in
<path to exe>\backups. I only allow for one generation (files are deleted).

All settings are done in app.config file. No override in GUI etc.

This is the name of the database you want to backup/restore. I.e the apps database name. This can of course
differ from box to box if you are using a local database engine.

<add key="BackupFileName" value="testfile"/>
The filename of the backup. Will in this example ve testfile.sql, and depending on compression method testfile.sql.gz or testfile.sql.tar

    <add key="BackupFTPServer" value="ftp.sksajasdas.com">
    <add key="BackupFTPAccount" value="ftpuser"/>
    <add key="BackupFTPPassword" value="ftppass"/>
    <add key="BackupFTPDirectory" value=""/>
No explaination needed. Just FTP info. The last param "BackupFTPDirectory" can be a subdir on the ftp server, say "/mybackups".

<add key="BackupCompressionMethod" value="d:\program\winrar\rar.exe"/>
How to 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. Talk about it later, but in short, either Internal (GZip) or path to
rar.exe is supported right now.

CMD files

In the /backups directory you 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 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 correct username and password of course. The app will call this cmd file for executing the database backup.

About the code

BACKUP DATABASE
The function RunDBBackup is actually really easy. Just about feeding backmysql.cmd the correct parameters. Which sould be a database name and the path to the resulting file.

RESTORE DATABASE
Function RunDBRestore contains no magic. Just start the restmysql.cmd command file with correct params.

COMPRESSION OF FILES
My first idea was to use the internal GZipStream (available in namespace System.IO.Compression). Good enough for me I thought, and no dependencies at all.
Code:
                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 30 MB (ok it's almost 33) only got compressed to over 9 MB, and when I ran Winrar against it I got under 7. 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 a over 200K library depedency into my app either. 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 basically

        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 but same here. As simple as possible. So I took FTPFactory.cs from Jaimon Mathew
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

Now as I said, The whole operation takes a fewq 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 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 the time and reports progress back to 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;
            }
        }

Finally DOWNLOAD

Disclaimers: No guarantees. NO ERROR HANDLING!!!

Works for me and my hope ios that someone could have some use of it as well.
BACKUPEXAMPLE.ZIP

ASPCode.net

Free asp.net and asp scripts

MySQL backup,compress and FTP from WinForms app