c#如何对mysql数据库进行备份和恢复 现在做一个winform的东东,被迫用mysql数据库。现在想对mysql数据库实现备份与恢复,在C#(winform)中如何实现?有什么好的方法?用sql脚本可以不?对Mysql不熟啊。希望大家多多指教啊。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 mysql的bin目录下面有个mysqldump.exe,是个命令行工具,可以备份恢复mysql数据库。你需要备份mysql数据库的话,可以使用mysqldump命令编写备份数据库批处理文件,然后程序里面在调用批处理文件。或者在操作系统的任务中调用该批处理文件,这个可以定时备份。 首先,bin下需要两个exe文件 mysql.exe,mysqldump.exe这个是备份的一段代码,测试过,是正确的 private void btnExeute_Click(object sender, EventArgs e) { if (txtPath.Text.Equals(String.Empty)) { MessageBox.Show("请选择一个路径保存备份文件。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); btnPath.Focus(); return; } if (!System.IO.File.Exists(BaseCommon.Const.APPLICATION_PATH + "\\mysqldump.exe")) { MessageBox.Show("无法完成备份,请检查 mysqldump.exe 是否存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); return; } btnPath.Enabled = false; chkGzip.Enabled = false; btnHelp.Enabled = false; btnExeute.Enabled = false; btnCancel.Enabled = false; progressBar1.Visible = true; Refresh(); Process proc = new Process(); proc.StartInfo.FileName = "cmd.exe"; // 启动命令行程序 proc.StartInfo.UseShellExecute = false; // 不使用Shell来执行,用程序来执行 proc.StartInfo.RedirectStandardError = true; // 重定向标准输入输出 proc.StartInfo.RedirectStandardInput = true; proc.StartInfo.RedirectStandardOutput = true; proc.StartInfo.CreateNoWindow = true; // 执行时不创建新窗口 proc.Start(); String ChcharacterSet = new BaseCommon.CommonDataBase().getDataSet("show create database " + BaseCommon.Const.DB_NAME).Tables[0].Rows[0][1].ToString(); ChcharacterSet = ChcharacterSet.Split(' ')[ChcharacterSet.Split(' ').Length - 2]; if (ChcharacterSet == "utf8_bin") { ChcharacterSet = "utf8"; } String str = "\"" + BaseCommon.Const.APPLICATION_PATH + "\\mysqldump.exe\" -h " + BaseCommon.Const.DB_SERVER + " -u" + BaseCommon.Const.DB_USER_NAME + " -p" + BaseCommon.Const.DB_USER_PWD + " --hex-blob --default-character-set " + ChcharacterSet + " -R --opt --add-drop-table -f " + BaseCommon.Const.DB_NAME + ">\"" + txtPath.Text + "\""; if (chkGzip.Checked) { if (!System.IO.File.Exists(BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe")) { MessageBox.Show("无法完成数据库备份,请检查 gzip.exe 是否存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); return; } str = "\"" + BaseCommon.Const.APPLICATION_PATH + "\\mysqldump.exe\" -h " + BaseCommon.Const.DB_SERVER + " -u" + BaseCommon.Const.DB_USER_NAME + " -p" + BaseCommon.Const.DB_USER_PWD + " --hex-blob --default-character-set " + ChcharacterSet + " -R --opt --add-drop-table -f " + BaseCommon.Const.DB_NAME + " | \"" + BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe\">\"" + txtPath.Text + "\""; } Refresh(); proc.StandardInput.WriteLine(str); try { String BackUpResult = proc.StandardOutput.ReadLine(); BackUpResult = proc.StandardOutput.ReadLine(); BackUpResult = proc.StandardOutput.ReadLine(); BackUpResult = proc.StandardOutput.ReadLine(); BackUpResult = proc.StandardOutput.ReadLine(); if (BackUpResult.Equals(String.Empty)) { btnPath.Enabled = true; chkGzip.Enabled = true; btnHelp.Enabled = true; btnExeute.Enabled = true; btnCancel.Enabled = true; progressBar1.Visible = false; MessageBox.Show("已成功完成数据库备份。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); Close(); } else { MessageBox.Show(BackUpResult, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); } } catch(Exception ex) { MessageBox.Show(ex.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); } } 这个是还原的代码; private void btnExeute_Click(object sender, EventArgs e) { if (txtSource.Text.Equals(String.Empty)) { MessageBox.Show("请选择要还原的源文件。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); txtSource.Focus(); return; } if (!System.IO.File.Exists(BaseCommon.Const.APPLICATION_PATH + "\\mysql.exe")) { MessageBox.Show("无法完成数据库恢复,请检查 mysql.exe 是否存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); return; } btnPath.Enabled = false; btnExeute.Enabled = false; btnCancel.Enabled = false; progressBar1.Visible = true; String DBSource = txtSource.Text; Process proc = new Process(); proc.StartInfo.FileName = "cmd.exe"; // 启动命令行程序 proc.StartInfo.UseShellExecute = false; // 不使用Shell来执行,用程序来执行 proc.StartInfo.RedirectStandardError = true; // 重定向标准输入输出 proc.StartInfo.RedirectStandardInput = true; proc.StartInfo.RedirectStandardOutput = true; proc.StartInfo.CreateNoWindow = true; // 执行时不创建新窗口 proc.Start(); //判断是不是压缩文件 if (txtSource.Text.Substring(txtSource.Text.LastIndexOf('.'), txtSource.Text.Length - txtSource.Text.LastIndexOf('.')).Equals(".gz")) { if (!System.IO.File.Exists(BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe")) { MessageBox.Show("无法完成数据库恢复,请检查 gzip.exe 是否存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); return; } proc.StandardInput.WriteLine("\"" + BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe\" -l \"" + txtSource.Text + "\""); DBSource = proc.StandardOutput.ReadLine(); DBSource = proc.StandardOutput.ReadLine(); DBSource = proc.StandardOutput.ReadLine(); DBSource = proc.StandardOutput.ReadLine(); DBSource = proc.StandardOutput.ReadLine(); DBSource = proc.StandardOutput.ReadLine(); DBSource = DBSource.Substring(DBSource.LastIndexOf('%') + 1).Trim(); proc.StandardInput.WriteLine("\"" + BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe\" -k -d \"" + txtSource.Text + "\""); } String[] BackupInfo = BackupInfo = GetBackupInfo(DBSource); CreateDB(BackupInfo[1], BackupInfo[0]); proc.StandardInput.WriteLine("\"" + BaseCommon.Const.APPLICATION_PATH + "\\mysql.exe\" -h " + BaseCommon.Const.DB_SERVER + " -u" + BaseCommon.Const.DB_USER_NAME + " -p" + BaseCommon.Const.DB_USER_PWD + " --default-character-set=" + BackupInfo[1] + " " + BackupInfo[0] + " <\"" + DBSource + "\""); try { String BackUpResult = proc.StandardOutput.ReadLine(); BackUpResult = proc.StandardOutput.ReadLine(); BackUpResult = proc.StandardOutput.ReadLine(); BackUpResult = proc.StandardOutput.ReadLine(); btnPath.Enabled = true; btnHelp.Enabled = true; btnExeute.Enabled = true; btnCancel.Enabled = true; progressBar1.Visible = false; MessageBox.Show("已成功完成数据库恢复。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); if (txtSource.Text.Substring(txtSource.Text.LastIndexOf('.'), txtSource.Text.Length - txtSource.Text.LastIndexOf('.')).Equals(".gz")) { try { System.IO.File.Delete(DBSource); } catch { } } Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); } } 非常感谢onlylovefly(上善若水) ,贴这多代码,不胜感激。辛苦,辛苦。 帮忙看个小问题! 关于枚举类型有空格和特殊字符的问题 C#写的ASP.NET集成采集系统开源项目 将一个datagridview选中的数据加入到另一个datagridview中啊 vs2005 很奇怪的问题,键盘不管用了 表的问题 由于目标机器积极拒绝,无法连接127.0.0.1:8888 求通过用户名和密码的通用SQL SERVER连接字符串(ASP。NET和C#) 用.Net 开发的应用程序是否在发布时的问题? 怎么用c#调用外部摄像头 我是一个刚学习.net的新手,希望各位大侠们推荐一本适合新手学习的书籍 C#下怎么用Nodes.Item?
你需要备份mysql数据库的话,可以使用mysqldump命令编写备份数据库批处理文件,然后程序里面在调用批处理文件。或者在操作系统的任务中调用该批处理文件,这个可以定时备份。
{
if (txtPath.Text.Equals(String.Empty))
{
MessageBox.Show("请选择一个路径保存备份文件。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
btnPath.Focus();
return;
} if (!System.IO.File.Exists(BaseCommon.Const.APPLICATION_PATH + "\\mysqldump.exe"))
{
MessageBox.Show("无法完成备份,请检查 mysqldump.exe 是否存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
return;
} btnPath.Enabled = false;
chkGzip.Enabled = false;
btnHelp.Enabled = false;
btnExeute.Enabled = false;
btnCancel.Enabled = false; progressBar1.Visible = true;
Refresh(); Process proc = new Process();
proc.StartInfo.FileName = "cmd.exe"; // 启动命令行程序
proc.StartInfo.UseShellExecute = false; // 不使用Shell来执行,用程序来执行
proc.StartInfo.RedirectStandardError = true; // 重定向标准输入输出
proc.StartInfo.RedirectStandardInput = true;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.CreateNoWindow = true; // 执行时不创建新窗口
proc.Start(); String ChcharacterSet = new BaseCommon.CommonDataBase().getDataSet("show create database " + BaseCommon.Const.DB_NAME).Tables[0].Rows[0][1].ToString();
ChcharacterSet = ChcharacterSet.Split(' ')[ChcharacterSet.Split(' ').Length - 2];
if (ChcharacterSet == "utf8_bin")
{
ChcharacterSet = "utf8";
}
String str = "\"" + BaseCommon.Const.APPLICATION_PATH + "\\mysqldump.exe\" -h " + BaseCommon.Const.DB_SERVER + " -u" + BaseCommon.Const.DB_USER_NAME + " -p" + BaseCommon.Const.DB_USER_PWD + " --hex-blob --default-character-set " + ChcharacterSet + " -R --opt --add-drop-table -f " + BaseCommon.Const.DB_NAME + ">\"" + txtPath.Text + "\"";
if (chkGzip.Checked)
{ if (!System.IO.File.Exists(BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe"))
{
MessageBox.Show("无法完成数据库备份,请检查 gzip.exe 是否存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
return;
}
str = "\"" + BaseCommon.Const.APPLICATION_PATH + "\\mysqldump.exe\" -h " + BaseCommon.Const.DB_SERVER + " -u" + BaseCommon.Const.DB_USER_NAME + " -p" + BaseCommon.Const.DB_USER_PWD + " --hex-blob --default-character-set " + ChcharacterSet + " -R --opt --add-drop-table -f " + BaseCommon.Const.DB_NAME + " | \"" + BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe\">\"" + txtPath.Text + "\"";
}
Refresh();
proc.StandardInput.WriteLine(str);
try
{
String BackUpResult = proc.StandardOutput.ReadLine();
BackUpResult = proc.StandardOutput.ReadLine();
BackUpResult = proc.StandardOutput.ReadLine();
BackUpResult = proc.StandardOutput.ReadLine();
BackUpResult = proc.StandardOutput.ReadLine();
if (BackUpResult.Equals(String.Empty))
{
btnPath.Enabled = true;
chkGzip.Enabled = true;
btnHelp.Enabled = true;
btnExeute.Enabled = true;
btnCancel.Enabled = true;
progressBar1.Visible = false; MessageBox.Show("已成功完成数据库备份。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
Close();
}
else
{
MessageBox.Show(BackUpResult, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
} }
private void btnExeute_Click(object sender, EventArgs e)
{
if (txtSource.Text.Equals(String.Empty))
{
MessageBox.Show("请选择要还原的源文件。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
txtSource.Focus();
return;
} if (!System.IO.File.Exists(BaseCommon.Const.APPLICATION_PATH + "\\mysql.exe"))
{
MessageBox.Show("无法完成数据库恢复,请检查 mysql.exe 是否存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
return;
} btnPath.Enabled = false;
btnExeute.Enabled = false;
btnCancel.Enabled = false; progressBar1.Visible = true; String DBSource = txtSource.Text; Process proc = new Process();
proc.StartInfo.FileName = "cmd.exe"; // 启动命令行程序
proc.StartInfo.UseShellExecute = false; // 不使用Shell来执行,用程序来执行
proc.StartInfo.RedirectStandardError = true; // 重定向标准输入输出
proc.StartInfo.RedirectStandardInput = true;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.CreateNoWindow = true; // 执行时不创建新窗口
proc.Start(); //判断是不是压缩文件
if (txtSource.Text.Substring(txtSource.Text.LastIndexOf('.'), txtSource.Text.Length - txtSource.Text.LastIndexOf('.')).Equals(".gz"))
{
if (!System.IO.File.Exists(BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe"))
{
MessageBox.Show("无法完成数据库恢复,请检查 gzip.exe 是否存在。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
return;
}
proc.StandardInput.WriteLine("\"" + BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe\" -l \"" + txtSource.Text + "\"");
DBSource = proc.StandardOutput.ReadLine();
DBSource = proc.StandardOutput.ReadLine();
DBSource = proc.StandardOutput.ReadLine();
DBSource = proc.StandardOutput.ReadLine();
DBSource = proc.StandardOutput.ReadLine();
DBSource = proc.StandardOutput.ReadLine();
DBSource = DBSource.Substring(DBSource.LastIndexOf('%') + 1).Trim();
proc.StandardInput.WriteLine("\"" + BaseCommon.Const.APPLICATION_PATH + "\\gzip.exe\" -k -d \"" + txtSource.Text + "\""); }
String[] BackupInfo = BackupInfo = GetBackupInfo(DBSource);
CreateDB(BackupInfo[1], BackupInfo[0]); proc.StandardInput.WriteLine("\"" + BaseCommon.Const.APPLICATION_PATH + "\\mysql.exe\" -h " + BaseCommon.Const.DB_SERVER + " -u" + BaseCommon.Const.DB_USER_NAME + " -p" + BaseCommon.Const.DB_USER_PWD + " --default-character-set=" + BackupInfo[1] + " " + BackupInfo[0] + " <\"" + DBSource + "\"");
try
{
String BackUpResult = proc.StandardOutput.ReadLine();
BackUpResult = proc.StandardOutput.ReadLine();
BackUpResult = proc.StandardOutput.ReadLine();
BackUpResult = proc.StandardOutput.ReadLine();
btnPath.Enabled = true;
btnHelp.Enabled = true;
btnExeute.Enabled = true;
btnCancel.Enabled = true;
progressBar1.Visible = false; MessageBox.Show("已成功完成数据库恢复。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
if (txtSource.Text.Substring(txtSource.Text.LastIndexOf('.'), txtSource.Text.Length - txtSource.Text.LastIndexOf('.')).Equals(".gz"))
{
try
{
System.IO.File.Delete(DBSource);
}
catch { }
}
Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
}