0

To dump database from command line, all I need to do is:

mysqldump -uroot --password=  myDb --routines> "C:\s.sql"

So all I would try programmatically is this, which is the direct interpretation of it I suppose:

ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = dumpUtilityPath;
psi.RedirectStandardInput = false;
psi.RedirectStandardOutput = true;
psi.UseShellExecute = false;
psi.CreateNoWindow = true;

psi.Arguments = "-uroot --password=  myDb --routines> \"C:\\s.sql\"";

Process process = Process.Start(psi);
process.WaitForExit();
process.Close();

Which doesn't work at all. Instead I have to go for this which one can find all over the net, which works too.

ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = dumpUtilityPath;
psi.RedirectStandardInput = false;
psi.RedirectStandardOutput = true;
psi.UseShellExecute = false;
psi.CreateNoWindow = true;

psi.Arguments = string.Format("-R -u{0} --password={1} -h{2} {3} --routines", "root", "", "localhost", "myDb");

Process process = Process.Start(psi);
string output = process.StandardOutput.ReadToEnd();
process.WaitForExit();
process.Close();

using (StreamWriter writer = new StreamWriter("C:\\s.sql"))
{
    writer.WriteLine(output);
    writer.Close();
}

  1. Why is that I need to use stream writer to get the database in an sql file which I can do otherwise directly from commands in command prompt?

  2. What is the role of -R in the second block?

nawfal
  • 70,104
  • 56
  • 326
  • 368

3 Answers3

2

  1. You can not redirect stdout using ">" in arguments because that is a feature of the command prompt.

  2. -R includes the stored procedures and functions in the dump. See http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_routines for more information.

Bruno Silva
  • 3,077
  • 18
  • 20
1

What you are doing in the command line version is using the shell to pipe the standard output to a file (the > command, followed by a file name, is a shorthand way of saying "take all of the standard output of this program and write it to this file"). To do the same thing from C#, you need to hand the standard output yourself and write it to a file.

The -R in the second example seems duplicative. According to this page, it is the same as --routines. Have you tried it without?

Chris Shain
  • 50,833
  • 6
  • 93
  • 125
  • Chris, thanks. I tried without it and it still worked. Can I ask another question, is the order of various parameters (like -u, -p, -h etc) important while dumping? I ask because I get the database dumped along with stored procedures even when I use -R in the start and also when I use just --routines in the end. So order doesnt matter? I can always test. Just asking – nawfal Mar 03 '12 at 17:13
  • By convention, order never matters for command line parameters that have a leading indicator (like "--something" or "-something"). It does matter for un-labeled parameters, for example `cp a b` copies `a` to `b`, whereas `cp b a` copies `b` to `a`. – Chris Shain Mar 03 '12 at 17:16
0

I thought I would include what the Arguments could look like programmatically, in our case we also wanted to dump the events of the DB to a file.

psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", "someUser", "xxxxxx", "localhost", dbName, "--routines","--events");
JohnL
  • 373
  • 1
  • 5
  • 15