MySQL is the world’s most popular open source database. With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more.

Oracle drives MySQL innovation, delivering new capabilities to power next generation web, cloud, mobile and embedded applications.

Recently I’ve been working on Point of Sales application with Visual Basic .NET, and as always I use MySQL for the database. Sometimes my client want to backup and restore the database so he didn’t have to input twice on the other PC.

And I’ve been struggle for days to add this feature on the project, so the client can easily backup and restore the database. Because it’s complicated for client to do it from phpmyadmin.

After days searching and trial, finally I find the code to do backup and restore. So I decide to share it to everyone, maybe all of you have meet this kind of situation as I am, so you won’t struggle to googling anymore. So let’s start it.

First of all, what we gonna used here to backup is MySQLDump. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

Process.Start("[MySQL Dump File Location]", "-u [USERNAME] -p [YOUR PASSWORD] [DATABASE THAT YOU WANT TO BACKUP] -r ""[OUTPUT LOCATION INCLUDE .SQL EXTENSION]""")

Code above used for backup the selected database using MySQLDump, and I’m gonna give a example code below.

Process.Start("C:\wamp64\bin\mysql\mysql5.7.14\bin\mysqldump.exe", "-u root skripsi -r """ & BackupPath & "" & DatabaseName & ".sql""")

You can change MySQL Dump File Location based on what are you use, XAMPP or WAMP. Next is a code to Restore the database back to Server.

Dim myProcess As New Process()

myProcess.StartInfo.FileName = "cmd.exe"
myProcess.StartInfo.UseShellExecute = False
myProcess.StartInfo.WorkingDirectory = "[MYSQL BIN PATH LOCATION]"
myProcess.StartInfo.RedirectStandardInput = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()

Dim myStreamerWriter As StreamWriter = myProcess.StandardInput
Dim myStreamerReader As StreamReader = myProcess.StandardOutput

myStreamerWriter.WriteLine("mysql -u [USERNAME] -p [YOUR PASSWORD] [DATABASE NAME] < [BACKUP DATABASE LOCATION]")
myStreamerWriter.Close()
myProcess.WaitForExit()
myProcess.Close()

And the example would be like below.

Dim myProcess As New Process()

myProcess.StartInfo.FileName = "cmd.exe"
myProcess.StartInfo.UseShellExecute = False
myProcess.StartInfo.WorkingDirectory = "C:\wamp64\bin\mysql\mysql5.7.14\bin"
myProcess.StartInfo.RedirectStandardInput = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()

Dim myStreamerWriter As StreamWriter = myProcess.StandardInput
Dim myStreamerReader As StreamReader = myProcess.StandardOutput

myStreamerWriter.WriteLine("mysql -u root skripsi < C:\Backup\Database.sql")
myStreamerWriter.Close()
myProcess.WaitForExit()
myProcess.Close()

You can change the MySQLDump and MySQL Bin Folder depends on what app you used. XAMPP or WAMP have a different path. WAMP MySQLDump can be copied from example above, and dont forget to change the version depends on what version you have. For XAMP MySQLDump can be access from “C:\xampp\mysql\bin\mysqldump.exe“.

For Bin folder still have a same path from example above for WAMP user. And for XAMPP user, you can use this path “C:\xampp\mysql\bin\“.

That’s all you need to backup and restore you database using Visual Basic .NET. For more detail’s you can download the project Here.

Comments

No comments yet

Post Your Comment