c# , simple MySQL backup solution

[Rathamon]

I'm coding an app in c# that uses a MySQL database as a backend and it needs a simple backup feature

the backup will be done to a USB memory stick (probably 256MB or so)

I need a simple and very preferably free way to do this

I tried calling mysql dump from inside c# with the following code

 private void backupButtonj_Click(object sender, System.EventArgs e) {
 	 System.Diagnostics.Process proc = new System.Diagnostics.Process();
 	 proc.EnableRaisingEvents=false;
 	 proc.StartInfo.FileName="mysqldump";
 	 proc.StartInfo.Arguments="supplydirect > I:\\backup\\backup.txt";
 	 proc.Start();
 	 proc.WaitForExit();
  }

but for some reason it just flashes up the command window and doesnt do the backup

but doing the same thing from the DOS prompt works...

[Glowstick]

I think you need to define the full path to the executable.

[Rathamon]

nah , that aint it , I added it to the path variable (in windows environment)

that exact command works if I goto the command promt and type it :s

[Andareed]

It's probably your output redirection. Including ">" as a param generally fails. You should check proc has options to specify some output redirection.

[Rathamon]

yea , you were right thanks , here's the final code if anyone cares

 private void backupButtonj_Click(object sender, System.EventArgs e) {
    try {
   	 DateTime backupTime = DateTime.Now;
   	 int year = backupTime.Year;
   	 int month = backupTime.Month;
   	 int day = backupTime.Day;
   	 int hour = backupTime.Hour;
   	 int minute = backupTime.Minute;
   	 int second = backupTime.Second;
   	 int ms = backupTime.Millisecond;

   	 String tmestr = backupTime.ToString();
   	 tmestr = "I:\\"+year+"-"+month+"-"+day+"-"+hour+"-"+minute+"-"+second+"-"+ms+".txt";
   	 StreamWriter file = new StreamWriter(tmestr);
   	 ProcessStartInfo proc = new ProcessStartInfo(); 
   	 proc.FileName = "mysqldump";
   	 proc.RedirectStandardInput = false;
   	 proc.RedirectStandardOutput = true;
   	 proc.Arguments = "supplydirect";
   	 proc.UseShellExecute = false; 
   	 Process p = Process.Start(proc);
   	 string res;
   	 res = p.StandardOutput.ReadToEnd();
   	 file.WriteLine(res);
   	 p.WaitForExit(); 
   	 file.Close();
    }

    catch (IOException ex) {
   	 MessageBox.Show("Disk full or other IO error , unable to backup!");
    }
  }

[shrestha]

While searching for backup mysql data from C#, I found this one good solution, but it did not solve my problem completely.

The problem is that, I am just getting following text in backup file instead of whole database.

-- MySQL dump 10.10

--

-- Host: localhost Database: smartdb

-- ------------------------------------------------------

-- Server version 5.0.22-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

I have modified the code as below.

try

{

DateTime backupTime = DateTime.Now;

int year = backupTime.Year;

int month = backupTime.Month;

int day = backupTime.Day;

int hour = backupTime.Hour;

int minute = backupTime.Minute;

int second = backupTime.Second;

int ms = backupTime.Millisecond;

String tmestr = backupTime.ToString();

tmestr = "C:\\"+year+"-"+month+"-"+day+"-"+hour+"-"+minute+".sql";

StreamWriter file = new StreamWriter(tmestr);

ProcessStartInfo proc = new ProcessStartInfo();

string cmd = string.Format(@"-u{0} -p{1} -h{2} {3} > {4};", "root", "password", "localhost", "dbfile", "backup.sql");

proc.FileName = "mysqldump";

proc.RedirectStandardInput = false;

proc.RedirectStandardOutput = true;

proc.Arguments = cmd;//"-u root -p smartdb > testdb.sql";

proc.UseShellExecute = false;

Process p = Process.Start(proc);

string res;

res = p.StandardOutput.ReadToEnd();

file.WriteLine(res);

p.WaitForExit();

file.Close();

}

catch (IOException ex)

{

MessageBox.Show("Disk full or other IO error , unable to backup!");

}

THANKS FOR YOUR HELP.

SHRESTHA

[Shamim Rezaie]

The problem is about this line:

string cmd = string.Format(@"-u{0} -p{1} -h{2} {3} > {4};", "root", "password", "localhost", "dbfile", "backup.sql");

You should not include piping options in the string. also the string should not contain any semicolon ( ; ) character.

Just rewrite the line as:

string cmd = string.Format(@"-u{0} -p{1} -h{2} {3}", "root", "password", "localhost", "dbfile");

and the everything will be OK.

[kazuyabo]

Hi! How to restore the back-up file?

[kazuyabo]

Oh sorry I just figure it out.

;)

[bakus]

Hi! How to restore the back-up file?

Sorry I didn't figure it out!

So can tell me anybody how to restore it?

Thank you a lot.

[anmae]

Hello,

the Backup of the Database works fine like this

System.Diagnostics.Process myProcess = new System.Diagnostics.Process();
myProcess.StartInfo.FileName = Path.Combine(ApplicationConfig.ApplicationDataPath, "db\\bin\\mysqldump.exe");
myProcess.StartInfo.UseShellExecute = false;
myProcess.StartInfo.CreateNoWindow = true;
myProcess.StartInfo.RedirectStandardOutput = true;
myProcess.StartInfo.Arguments = "-h localhost --user=" + dbUser + " --password=" + dbPwd + " --databases db1 db2 --result-file " + OutputFile;
myProcess.Start();
string output = myProcess.StandardOutput.ReadToEnd();

if the output ios empty - no error occured.

But the question is: how does the restore work? I cant find a parameter like --result-file for the restore direction.

Are there other ways to do this?

Thanks