Database backup of a mysql database in VB.Net

You can use MySqlBackup.NET, which is an alternative to mysqldump.

Official Website & Documentation > https://github.com/MySqlBackupNET/MySqlBackup.Net

Examples:

Backup a MySql Database

Dim conn As MySqlConnection = New MySqlConnection(constr)
Dim cmd As MySqlCommand = New MySqlCommand
cmd.Connection = conn
conn.Open
Dim mb As MySqlBackup = New MySqlBackup(cmd)
mb.ExportToFile("C:\backup.sql")
conn.Close

Restore a MySql Database

Dim conn As MySqlConnection = New MySqlConnection(constr)
Dim cmd As MySqlCommand = New MySqlCommand
cmd.Connection = conn
conn.Open
Dim mb As MySqlBackup = New MySqlBackup(cmd)
mb.ImportFromFile("C:\backup.sql")
conn.Close

I am one of the author of this project.

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"

Example was in C#. I manually converted it into vb.net and add converting into '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

you could invoke mysqldump, but you may need to be running your VB.NET on the Mysql server.

I found the easiest way was to use the mysqldump.exe which is a standalone application.

mysqldump --host=[HOSTNAME] --user=[USER] --password=[PASSWORD] -R [DATABASE NAME] > [PATH TO BACKUP FILE]

We had issues with backups not saving db functions but the -R switch sorted it so id recommend using it if you use stored procedures or functions in your DB.

to restore the created file use the mysql command instead.

mysql --host=[HOSTNAME] --user=[USER] --password=[PASSWORD] [DATABASE NAME] < [PATH TO BACKUP FILE]

You can read each table's data and write it to a new database.

I would write a stored proc, since MySQL 5 has support for them, that handles all the data "heavy" work. Then just create scheduled task that calls the procedure every "night". For this latter component, I highly recommend Powershell....its awesome.

This is what I use to backup data on mysql. I make a copy of mysqldump.exe and mysql.exe and store it on my LIB_PATH then the following code will backup your data. You can specify your mysqldump.exe directory and assign it to LIB_PATH, provide your login details under the Arguments then specify your output directory, mine is set to BACKUP_DIR and I use the preformatted Now() as my filename. The code is pretty straight forward. Goodluck

    Using myProcess As New Process()
        Dim newfiledb As String = BACKUPDIR_PATH & Format(Now(), "MMM_dd_yyyy@h~mm_tt").ToString & "_local.sql"
        Try
            myProcess.StartInfo.FileName = "mysqldump.exe"
            myProcess.StartInfo.WorkingDirectory = LIB_PATH
            myProcess.StartInfo.Arguments = "--host=localhost --user=username --password=yourpassword yourdatabase -r " & newfiledb
            myProcess.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
            myProcess.Start()
            myProcess.WaitForExit()
            MsgBox("Backup Created ... " & vbNewLine & newfiledb)
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical + vbOKOnly, ex.Message)
        Finally
            myProcess.Close()
        End Try
    End Using