Imports
MySql.Data.MySqlClient
--------------------------------------------------------------
Private Sub BackupMySql()
Dim localDir As String = "path to where to save file"
Dim strDate As String = Date.Now.ToShortDateString 'Prepend file with date for dated backups
Dim fileName As String = strDate.Replace("/", "-") & "_" & ftphost & ".sql"
Dim saveFile As String = localDir & fileName
Dim DBServer As String = "mysql host"
Dim DBServerPort As String = "mysql port"
Dim Database As String = "database name"
Dim DBUser As String = "mysql username"
Dim DBPass As String = "mysql password"
Dim MyConString As String = "SERVER=" & DBServer & ";DATABASE=" & Database & ";UID=" & DBUser & ";PASSWORD=" & DBPass
Dim connection As New MySqlConnection(MyConString)
Dim tablesCommand As MySqlCommand = connection.CreateCommand()
Dim rowsCommand As MySqlCommand = connection.CreateCommand()
Dim tablesReader As MySqlDataReader
Dim rowsReader As MySqlDataReader
tablesCommand.CommandText =
"SHOW TABLES FROM " & Database
connection.Open()
tablesReader = tablesCommand.ExecuteReader()
Dim query As String = ""
Dim tablesArray As New ArrayList()
While tablesReader.Read()
tablesArray.Add(tablesReader.GetValue(0).ToString())
End While
tablesReader.Close()
For i = 0 To tablesArray.Count - 1
query +=
"DROP TABLE IF EXISTS `" & tablesArray(i) & "`;" & vbCrLf '& Database & "."
query += vbCrLf &
"CREATE TABLE `" & tablesArray(i) & "` (" & vbCrLf
rowsCommand.CommandText =
"DESCRIBE " & tablesArray(i)
rowsReader = rowsCommand.ExecuteReader()
Dim temp As String = ""
While rowsReader.Read()
query +=
"`" & rowsReader.GetString("Field") & "` " & rowsReader.GetString("Type")
If Not rowsReader.GetString("Null") = "YES" Then
query +=
" NOT NULL"
End If
If IsDBNull(rowsReader.Item("Default")) = False Then
query +=
" DEFAULT '" & rowsReader.GetString("Default") & "'"
End If
If Not rowsReader.GetString("Extra") = Nothing Then
query +=
" " & rowsReader.GetString("Extra").ToUpper()
End If
If rowsReader.GetString("Key") = "PRI" Then
temp =
"primary key(" & rowsReader.GetString("Field") & ")"
End If
query +=
"," & vbCrLf
End While
query += temp & vbCrLf &
");" & vbCrLf & vbCrLf
rowsReader.Close()
rowsCommand.CommandText =
"SELECT * FROM " & tablesArray(i)
rowsReader = rowsCommand.ExecuteReader()
While rowsReader.Read()
query +=
"INSERT INTO `" & tablesArray(i) & "` ("
Dim count As Integer = rowsReader.FieldCount - 1
Dim keys(count) As String
Dim values(count) As String
For n = 0 To count
keys(n) = rowsReader.GetName(n)
values(n) = rowsReader.Item(n)
Next
query += Join(keys,
", ") & ")" & vbCrLf & "VALUES ('" & Join(values, "', '") & "');" & vbCrLf
End While
rowsReader.Close()
query += vbCrLf & vbCrLf
Next
connection.Close()
connection.Dispose()
If File.Exists(saveFile) Then
File.Delete(saveFile)
End If
Dim objWriter As New System.IO.StreamWriter(saveFile)
objWriter.Write(query)
objWriter.Close()
sumber :