Backup and Restore MySQL Database

[afdoal]

ATTENTION:
this thread just for newbie like me

for the backup mysql database from VB6, we simply use the MySQL tool MySQLDump and for restore using the mysql.

this a basic commands to backup and restore mysql database:

' backup
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
or 
mysqldump -uUSER_NAME -pUSER_PASSWORD --routines DB_NAME > dumpfilename.sql

' restore
mysql -uUSER_NAME -pUSER_PASSWORD DB_NAME < dumpfilename.sql

--routines : use this parameter if u database have a function/trigger

HOW to implement it in VB6?
OK, prepare the following code :

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
 
Private Const SYNCHRONIZE       As Long = &H100000
Private Const INFINITE          As Long = &HFFFF
 
Private 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
End Sub

then, use this code for backup or restore the MySQL Database

Dim cmd As String
 
Private Sub cmdBackup_Click()
    Screen.MousePointer = vbHourglass
    DoEvents
 
    cmd = Chr(34) & "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump" & Chr(34) & " -uroot -psecretpswd --routines --comments db_name > c:\MyBackup.sql"
    Call execCommand(cmd)
 
    Screen.MousePointer = vbDefault
    MsgBox "done"
End Sub
 
Private Sub cmdRestore_Click()
    Screen.MousePointer = vbHourglass
    DoEvents
 
    cmd = Chr(34) & "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" & Chr(34) & " -uroot -psecretpswd --comments db_name < c:\MyBackup.sql"
    Call execCommand(cmd)
 
    Screen.MousePointer = vbDefault
    MsgBox "done"
End Sub

I hope this article helps you