Backup dan Restore Database MySQL dari Aplikasi

Backup dan Restore Database MySQL dari Aplikasi

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

2	mysqldump -uUSER_NAME -pUSER_PASSWORD --routines NAMA_DATABASE > LOKASI_FILE_BACKUP.SQL

3

4

' restore

5	mysql -uUSER_NAME -pUSER_PASSWORD NAMA_DATABASE < LOKASI_FILE_BACKUP.SQL

parameter –routines ditambahkan jika database mempunyai fungsi/trigger
Sekarang tinggal kita siapkan kode berikut :

01	Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

02	Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

03	Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

04

05	Private Const SYNCHRONIZE       As Long = &H100000

06	Private Const INFINITE          As Long = &HFFFF

07

08	Private Sub execCommand(ByVal cmd As String)

09	Dim result  As Long

10	Dim lPid    As Long

11	Dim lHnd    As Long

12	Dim lRet    As Long

13

14	cmd = "cmd /c " & cmd

15	result = Shell(cmd, vbHide)

16

17	lPid = result

18	If lPid <> 0 Then

19	lHnd = OpenProcess(SYNCHRONIZE, 0, lPid)

20	If lHnd <> 0 Then

21	lRet = WaitForSingleObject(lHnd, INFINITE)

22	CloseHandle (lHnd)

23

End If

24

End If

25

End Sub

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

01	Dim cmd As String

02

03	Private Sub cmdBackup_Click()

04	Screen.MousePointer = vbHourglass

05

DoEvents

06

07	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"

08	Call execCommand(cmd)

09

10	Screen.MousePointer = vbDefault

11	MsgBox "done"

12

End Sub

13

14	Private Sub cmdRestore_Click()

15	Screen.MousePointer = vbHourglass

16

DoEvents

17

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

19	Call execCommand(cmd)

20

21	Screen.MousePointer = vbDefault

22	MsgBox "done"

23

End Sub

kode diatas dengan asumsi direktori bin MySQL ada di folder “C:\Program Files\MySQL\MySQL Server 5.1\bin”.