Vb.Net Example Code To Backup Mysql Database Using Mysqldump Command In Vb Using Visual Studio 2015

In this post, we will see how to create a simple VB.NET application to backup MySQL database using the same mysqldump command by firing command prompt from the VB application. For this example, I am using Visual Studio 2015 Community edition.

In some examples on the internet, you would also find the use of mysql connectors for Visual Studio. I have also used it and it works. But since in this post, I am directly going to fire the command prompt from the VB application just like we execute mysqldump command directly in command prompt, there is no need to use the mysql connector for backup purposes. It will however be required if your application is going to fetch data display data etc from a MySQL database in the VB application.

The form designed is:

Ignoring the other labels, the important form elements used in code are:

• Textbox 1  - To display messages here
• Button 1 - View the backups folder
• Button 2 - Help. Some text for information and about the tool
• Button 3 - Exit Application
• Label 5 - Timer. Display Count Down time in seconds

 Let's start designing:

• Open Visual Studio and create a new VB Project
• And design the form as in the screenshot above. (I have not changed the names of controls in the form. I left it to defaults. If you do change, do so accordingly in the code as well.) 

The code is as given below.

Imports System.IO

Public Class Form1
    Dim dbname As String

    'Set time to close for timer
    Private timeleft As Integer = 500 'in seconds

    'Change the backup directory here if required.
    Dim bkpath As String = "C:\MYSQLBACKUPS\"

    'Working directory of my sql
    Dim mysqldirectory As String

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'hide timer labels
        Label5.Visible = False
        Label6.Visible = False

        'Display Welcome Message in the form's textbox
        TextBox1.Text = "Messages:"

        TextBox1.Text += Environment.NewLine
        TextBox1.Text += Environment.NewLine & DateAndTime.Now()
        TextBox1.Text += Environment.NewLine & "Welcome!" & Environment.NewLine & "I am going to backup the local MySQL database..."


        'Define the variables
        Dim dbfullpath As String
        Dim DbFile As String
        'change the DB name here ------------------------
        dbname = "wpdemo"

        'Change the MySQL Working directory here if required. Find the right directory and replace here. 
'example: it could be mysqldirectory = "C:\Program Files\MySQL\MySQL Server 5.5\bin\"
 
        mysqldirectory = "C:\xampp\mysql\bin\"       


        'check if the mysqldirectory exists
        Try
            If Not Directory.Exists(mysqldirectory) Then
                TextBox1.Text += Environment.NewLine
                TextBox1.Text += Environment.NewLine & DateAndTime.Now()
                TextBox1.Text += Environment.NewLine & "Could not find MySQL directory at " & mysqldirectory
                TextBox1.Text += Environment.NewLine & "I am unable to continue."
               
                'program will auto exit as per the timer specified. No need to include application exit here
            Else

                'If MySQL installation directory is found, continue
                'Change the DB backup file name if required here
                DbFile = "MYSQL_BACKUP_" + DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".sql"

                'Append date and time to MySQL backup file
                dbfullpath = bkpath & DbFile

                'Check if C:\MYSQLBACKUPS folder exists. and if not then create

                Try
                    If Not Directory.Exists(bkpath) Then

                        TextBox1.Text += Environment.NewLine
                        TextBox1.Text += Environment.NewLine & DateAndTime.Now()
                        TextBox1.Text += Environment.NewLine & "I could not find """ + bkpath + """ folder. Creating Folder."

                        Directory.CreateDirectory(bkpath)

                        TextBox1.Text += Environment.NewLine
                        TextBox1.Text += Environment.NewLine & DateAndTime.Now()
                        TextBox1.Text += Environment.NewLine & "Created folder. The backups will be stored at  """ + bkpath + """ "

                    Else
                        TextBox1.Text += Environment.NewLine
                        TextBox1.Text += Environment.NewLine & DateAndTime.Now()
                        TextBox1.Text += Environment.NewLine & " """ + bkpath + """ folder already exists. Backups will be stored at """ + bkpath + """ "

                    End If


                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

                'Start a new process to run Command Prompt

                Dim BackupProcess As New Process

                BackupProcess.StartInfo.FileName = "cmd.exe"
                BackupProcess.StartInfo.UseShellExecute = False
                BackupProcess.StartInfo.WorkingDirectory = mysqldirectory
                BackupProcess.StartInfo.RedirectStandardInput = True
                BackupProcess.StartInfo.RedirectStandardOutput = True


                BackupProcess.Start()

                Dim BackupStream As StreamWriter = BackupProcess.StandardInput
                Dim myStreamreader As StreamReader = BackupProcess.StandardOutput

                TextBox1.Text += Environment.NewLine
                TextBox1.Text += Environment.NewLine & DateAndTime.Now()
                TextBox1.Text += Environment.NewLine & "Attempting to take backup and save to """ + bkpath + """ folder."

                Try
                  BackupStream.WriteLine("mysqldump --routines -u root -proot " & dbname & " > """ + dbfullpath + """")

                    TextBox1.Text += Environment.NewLine
                    TextBox1.Text += Environment.NewLine & DateAndTime.Now()
                    TextBox1.Text += Environment.NewLine & "Backup Created Successfully. Click ""View Backups"" to view."

                Catch ex As Exception
                    MsgBox("Some error occured. MySQL may not be installed or DB not found. Contact Admin.")

                End Try

                BackupStream.Close()
                BackupProcess.WaitForExit()
                BackupProcess.Close()

            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


        'show timer labels
        Label5.Visible = True
        Label6.Visible = True

        'Enable Timer
        Timer1.Enabled = True
        Timer1.Start()


    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Process.Start("explorer.exe", bkpath)
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        TextBox1.Text = " About This Tool "
        TextBox1.Text += Environment.NewLine
        TextBox1.Text += "--------------------------"
        TextBox1.Text += Environment.NewLine
        TextBox1.Text += "PURPOSE OF THIS TOOL"
        TextBox1.Text += Environment.NewLine
        TextBox1.Text += "This tool takes a backup of the MySQL database and saves it to " & bkpath & ". So that the backups can be used to restore in the event of system crash or data loss."
        TextBox1.Text += "Thank you for using this tool!"
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Application.Exit()
    End Sub



    
'Decrement timer by 1 seconds till it reaches 0 to fire close application 
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick

        If timeleft > 0 Then
            timeleft -= 1
            Label5.Text = timeleft
        Else
            Timer1.Stop()
            Label5.Text = "Closing..."
            Application.Exit()
        End If
    End Sub

End Class

The running jadwal looks like:

• User then clicks on the "View backups" button to view the backed up files in the directory.
• The jadwal exits automatically when the countdown reaches zero (0).
• Since the jadwal exists automatically, this jadwal can also be fired from Windows Task Scheduler to run at periodic intervals without any user intervention.

NOTE:  If the MySQL database is a part of XAMPP / WAMP etc, MySQL should be running. Check the XAMPP / WAMP control panel.

PS: This is an example code shared and you are advised to review the code on your own. Use it at your own risk.

Sumber http://techubber.blogspot.com/

Share this post