如何在 vb.net 中备份我的 sql 数据库

how to backup my sql database in vb.net

提问by Farcorn

i want the user of my vb application to be able to backup and restore the database (MySQL) onto a storage medium. my problem is that i dont want to specify 'c:\ in the code because i want the application to be able to locate the dumb file whether it is created on drive c or not. below is the code i used but when i installed it on another machine, it had its windows and program files on D:. it turns out that i have to check the drive letter of every machine, change it in the code before i publish the application to allow backup which i dont want to do that. i want it do be universal. thus whether the dump file is on driver C, G or whatever. any help. below is the code i used. Dim cmd As String

我希望我的 vb 应用程序的用户能够将数据库 (MySQL) 备份和恢复到存储介质上。我的问题是我不想在代码中指定 'c:\ ，因为我希望应用程序能够找到哑文件，无论它是否在驱动器 c 上创建。下面是我使用的代码，但是当我将它安装在另一台机器上时，它在 D: 上有它的窗口和程序文件。事实证明，我必须检查每台机器的驱动器号，在发布应用程序之前在代码中更改它以允许备份，而我不想这样做。我希望它是通用的。因此，转储文件是否在驱动程序 C、G 或其他驱动程序上。任何帮助。下面是我使用的代码。将 cmd 调暗为字符串

Private Sub cmdBackup_Click()
    Screen.MousePointer = vbHourglass
    DoEvents

    cmd = Chr(34) & "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump" & Chr(34) & " -uroot -psecretpswd --routines --comments db_name > c:\MyBackup.sql"
    Call execCommand(cmd)

    Screen.MousePointer = vbDefault
    MsgBox "done"
End Sub

回答by John Woo

There is a complied DLL called MySqlBackup.NET. Actually it is an alternative to MySqlDump.

有一个名为MySqlBackup.NET 的已编译DLL 。实际上它是MySqlDump.

Features

特征

• Export/Import Table's Structures & Rows
• Export/Import Stored Procedures, Functions, Triggers, Events, Views
• Custom Tables and Rows Export.
• Able to apply encryption to the process.
• Export BLOB and save as files.
• Gather SQL Syntax errors during Import process.
• Export/Import will report progress. Enable the usage of progress bar.
• Able to execute in Synchronous or Asynchronous mode.
• Export/Import To/From Zip File.

• 导出/导入表的结构和行
• 导出/导入存储过程、函数、触发器、事件、视图
• 自定义表和行导出。
• 能够对过程应用加密。
• 导出 BLOB 并另存为文件。
• 在导入过程中收集 SQL 语法错误。
• 导出/导入将报告进度。启用进度条的使用。
• 能够在同步或异步模式下执行。
• 导出/导入到/从 Zip 文件。

For more info, see the link below,

欲了解更多信息，请参阅下面的链接，

• MySqlBackup.NET - MySQL Backup Solution for C#, VB.NET, ASP.NET

• MySqlBackup.NET - C#、VB.NET、ASP.NET 的 MySQL 备份解决方案

Edited: Code Examples Added

编辑：添加了代码示例

Backup a MySql Database

备份一个 MySql 数据库

Dim con As String = "server=localhost;user=root;pwd=1234;database=test;"
Dim file As String = "C:\backup.sql"
Dim mb As New MySqlBackup(con)
mb.ExportInfo.FileName = file
mb.Export()

Restore a MySql Database

还原一个 MySql 数据库

Dim con As String = "server=localhost;user=root;pwd=1234;database=test;"
Dim file As String = "C:\backup.sql"
Dim mb As New MySqlBackup(con)
mb.ImportInfo.FileName = file
mb.Import()

回答by Steve

Usually this commands are built using parameters external to the application, not hard coding path to MySqlDump, Database Name and path to destination folder.

通常，此命令是使用应用程序外部的参数构建的，而不是 MySqlDump 的硬编码路径、数据库名称和目标文件夹的路径。

Your code should be changed to something like this

你的代码应该改成这样

Private Sub cmdBackup_Click()
    Screen.MousePointer = vbHourglass
    DoEvents
    Dim mySqlDumpCmd = ConfigurationManager.AppSettings("PathToMySqlDump")
    Dim dbName = ConfigurationManager.AppSettings("DatabaseToBackup")
    Dim destPath = ConfigurationManager.AppSettings("DestinationPath")
    cmd = Chr(34) & mySqlDumpCmd & Chr(34) & " -uroot -psecretpswd --routines --comments " +
          dbName & " > " & destPath
    Call execCommand(cmd)
    Screen.MousePointer = vbDefault
    MsgBox "done"
End Sub

and your application.config file contains these values

并且您的 application.config 文件包含这些值

<?xml version="1.0"?>
<configuration>
    <configSections>
    .......

    <appSettings>
    <add key="PathToMySqlDump" value="C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe"/>
    <add key="DatabaseToBackup" value="db_name"/>
    <add key="DestinationPath" value="C:\MyBackup.sql"/>
    </appSettings>
    .......

In this way you read the key information from the config file of your application. If the need arises you can easily change the information used by the command without touching anything in your application

通过这种方式，您可以从应用程序的配置文件中读取关键信息。如果需要，您可以轻松更改命令使用的信息，而无需触及应用程序中的任何内容

回答by GGSoft

Use this code. It works for me.

使用此代码。这个对我有用。

I had such a problem and then found this article

我遇到了这样的问题，然后找到了这篇文章

"http://www.experts-exchange.com/Programming/Languages/.NET/Q_27155602.html"

" http://www.experts-exchange.com/Programming/Languages/.NET/Q_27155602.html"

Example was in C#. I manually converted it into vb.net and add converting into 'utf8'.

示例在 C# 中。我手动将其转换为 vb.net 并添加转换为“utf8”。

   Imports System.Text
   Public Class Form1
       Dim OutputStream As System.IO.StreamWriter
        Sub OnDataReceived1(ByVal Sender As Object, ByVal e As    System.Diagnostics.DataReceivedEventArgs)
          If e.Data IsNot Nothing Then
          Dim text As String = e.Data
          Dim bytes As Byte() = Encoding.Default.GetBytes(text)
          text = Encoding.UTF8.GetString(bytes)
          OutputStream.WriteLine(text)
         End If
    End Sub

    Sub CreateBackup()
        Dim mysqldumpPath As String = "d:\mysqldump.exe"
        Dim host As String = "localhost"
        Dim user As String = "root"
        Dim pswd As String = "Yourpwd"
        Dim dbnm As String = "BaseName"
        Dim cmd As String = String.Format("-h{0} -u{1} -p{2} {3}", host, user, pswd, dbnm)
        Dim filePath As String = "d:\backup\fieName.sql"
        OutputStream = New System.IO.StreamWriter(filePath, False, System.Text.Encoding.UTF8)

        Dim startInfo As System.Diagnostics.ProcessStartInfo = New System.Diagnostics.ProcessStartInfo()
        startInfo.FileName = mysqldumpPath
        startInfo.Arguments = cmd

        startInfo.RedirectStandardError = True
        startInfo.RedirectStandardInput = False
        startInfo.RedirectStandardOutput = True 
        startInfo.UseShellExecute = False
        startInfo.CreateNoWindow = True
        startInfo.ErrorDialog = False

        Dim proc As System.Diagnostics.Process = New    System.Diagnostics.Process()
        proc.StartInfo = startInfo
        AddHandler proc.OutputDataReceived, AddressOf OnDataReceived1
        proc.Start()
        proc.BeginOutputReadLine()
        proc.WaitForExit()

        OutputStream.Flush()
        OutputStream.Close()
        proc.Close()
     End Sub

     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As      System.EventArgs) Handles MyBase.Load
       CreateBackup()
       End Sub
      End Class