MYSQL Database Backup / Restore

Using this blog, you can easily create your mysql database backup and you can restore your database also.

Step 1: Go to this link Download MySQL

and download 'mysql-5.1.73-win32'

Step 2: Install it in your system as a server machine and set the password (You can see the pictures given at the bottom of this blog, for help).

Step 3: Download MySql Connector Net 5.1.7 fromhttp://dev.mysql.com/downloads/connector/net/5.1.html and install it.

Step 4: Design the form in .NET. Add the 'Save File Dialog' control and set the name as sfd. Add the 'Open File Dialog' control and set the name opd.

Step 5: Add the 'MySql.Data' version 5.1.7.0 in the Reference

Step 6: Import on Form

Imports System.ComponentModel
Imports System.Text
Imports MySql.Data.MySqlClient
Imports System.IO

Step 7: Code For Backup

Private Sub btnBackup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackup.Click
Dim file As String
sfd.Filter = "SQL Dump File (*.sql)|*.sql|All files (*.*)|*.*"
sfd.FileName = "Database Backup " + DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".sql"
If sfd.ShowDialog = DialogResult.OK Then
file = sfd.FileName
Dim myProcess As New Process()
myProcess.StartInfo.FileName = "cmd.exe"
myProcess.StartInfo.UseShellExecute = False
myProcess.StartInfo.WorkingDirectory = "C:\Program Files\MySQL\MySQL Server 5.1\bin\"
myProcess.StartInfo.RedirectStandardInput = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()
Dim myStreamWriter As StreamWriter = myProcess.StandardInput
Dim mystreamreader As StreamReader = myProcess.StandardOutput
myStreamWriter.WriteLine("mysqldump -u root --password=yourpassword -h 192.168.1.201 ""databasename"" > """ + file + """ ")
myStreamWriter.Close()
myProcess.WaitForExit()
myProcess.Close()
MsgBox("Backup Created Successfully!", MsgBoxStyle.Information, "Backup")
End If
End Sub

Step 8: Code For Restoration

Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
Dim file As String
opd.Filter = "SQL Dump File (*.sql)|*.sql|All files (*.*)|*.*"
If opd.ShowDialog = DialogResult.OK Then
file = opd.FileName
Dim myProcess As New Process()
myProcess.StartInfo.FileName = "cmd.exe"
myProcess.StartInfo.UseShellExecute = False
myProcess.StartInfo.WorkingDirectory = "C:\Program Files\MySQL\MySQL Server 5.1\bin\"
myProcess.StartInfo.RedirectStandardInput = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()
Dim myStreamWriter As StreamWriter = myProcess.StandardInput
Dim mystreamreader As StreamReader = myProcess.StandardOutput
myStreamWriter.WriteLine("mysql -u root --password=yourpassword -h 192.168.1.201 ""databasename"" < """ + file + """ ")
myStreamWriter.Close()
myProcess.WaitForExit()
myProcess.Close()
MsgBox("Database Restoration Successfully!", MsgBoxStyle.Information, "Restore")
End If
End Sub