Backup dan Restore Database MySQL dari Aplikasi

10.55 | Labels: Programming

Untuk proses backup kita cukup menggunakan tool bawaan MySQL yaitu mysqldump dan untuk restorenya menggunakan mysql.

Adapun perintah dasar untuk melakukan backup dan restore adalah sebagai berikut :

1' backup
2mysqldump -uUSER_NAME -pUSER_PASSWORD --routines NAMA_DATABASE > LOKASI_FILE_BACKUP.SQL
3 
4' restore
5mysql -uUSER_NAME -pUSER_PASSWORD NAMA_DATABASE < LOKASI_FILE_BACKUP.SQL

parameter –routines ditambahkan jika database mempunyai fungsi/trigger

Sekarang tinggal kita siapkan kode berikut :

01Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
02Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
03Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

05Private Const SYNCHRONIZE       As Long = &H100000
06Private Const INFINITE          As Long = &HFFFF

08Private Sub execCommand(ByVal cmd As String)
  Dim result  As Long
  Dim lPid    As Long
  Dim lHnd    As Long
  Dim lRet    As Long

  cmd = "cmd /c " & cmd
  result = Shell(cmd, vbHide)

  lPid = result
  If lPid <> 0 Then
      lHnd = OpenProcess(SYNCHRONIZE, 0, lPid)
      If lHnd <> 0 Then
          lRet = WaitForSingleObject(lHnd, INFINITE)
          CloseHandle (lHnd)
      End If
  End If
25End Sub

kemudian tinggal panggil di masing-masing proses (backup dan restore)

01Dim cmd As String

03Private Sub cmdBackup_Click()
  Screen.MousePointer = vbHourglass
  DoEvents

  cmd = Chr(34) & "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump" & Chr(34) & " -uroot -prahasia --routines --comments nama_database > c:\nama_database.sql"
  Call execCommand(cmd)

  Screen.MousePointer = vbDefault
  MsgBox "done"
12End Sub

14Private Sub cmdRestore_Click()
  Screen.MousePointer = vbHourglass
  DoEvents

  cmd = Chr(34) & "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" & Chr(34) & " -uroot -prahasia --comments nama_database < c:\nama_database.sql"
  Call execCommand(cmd)

  Screen.MousePointer = vbDefault
  MsgBox "done"
23End Sub