MySQL DB Backup - HMGMySQL

Utilities like DBU, Make, IDE written in HMG/ used to create HMG based applications

Moderator: Rathinagiri

Post Reply
User avatar
Rathinagiri
Posts: 5243
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 162 times
Been thanked: 161 times
Contact:

MySQL DB Backup - HMGMySQL

Post by Rathinagiri » Mon Jul 30, 2018 7:19 am

If you are using HMGMySQL library to use with MariaDB/MySQL, the following code can be used to make a dump backup of whole database

Code: Select all

#include <hmg.ch>

function backup_db( cFname )
   local aTable := {}
   local aTables := {}
   local tables := {}
   local aData := {}
   local aRow := {}
   local cLB := chr( 10 ) + chr( 13 )
   local nHandle := 0
   local cTime := time()
   local cTempFolder := GetCurrentFolder() + '\' + 'backup_' + dtos( date() ) + substr( cTime, 1, 2 )  + substr( cTime, 4, 2 ) + substr( cTime, 7, 2 )
   local aFiles := {}
   default cFName := 'backup.sql'
   tables := sql( oDB,"show tables")
   for i := 1 to len(tables)
      aadd(aTables,alltrim(tables[i,1]))
   next i
   i := 0
   tottables := len(aTables)
   if MakeDir( cTempFolder ) <> 0
      msgstop( 'Backup Directory could not be created!' )
      return nil
   endif   
   nHandle := fcreate( cTempFolder + '\' + cFName )
   aadd( aFiles, cTempFolder + '\' + cFName )
   if nHandle < 0
      msgstop( 'Backup File Could not be created!' )
      return nil
   endif
   for i := 1 to tottables
      WAIT WINDOW "Processing Table - " + aTables[ i ] + '( ' + alltrim( str( i ) ) + '/' + alltrim( str( tottables ) ) + ' )'   NOWAIT
      aTable := sql( oDB, 'show create table ' + aTables[ i ] )
      cCreateCommand := aTable[ 1, 2 ]
      fwrite( nHandle, alltrim( cCreateCommand ) + ';' + cLB )
      // try to dumpt data to file
      cTempFileName := cTempFolder + '\' + alltrim( aTables[ i ] ) + '.txt'
      aadd( aFiles, cTempFileName )
      cTempFileName := strtran( cTempFileName, '\', '/' )
      miscsql( oDB, "select * from " + aTables[ i ] + " into outfile '" + cTempFileName + "'" )
      if .not. file( cTempFileName )
         msgstop( 'Dump file not created!' )
         return nil
      endif
   next i
   wait clear
   fClose( nHandle )
   cZip := cTempFolder + '.zip'
   hb_zipFile( cZip, aFiles )
//   compress files aFiles to cZip
   if file( cZip )
      for i := 1 to hmg_len( aFiles )
         ferase( aFiles[ i ] )
      next i
      dirremove( cTempFolder )
   endif  
   msginfo( 'Backup Complete - ' + cZip + ' File Created' )   
return nil
Restore utility can be created with the code as below:

Code: Select all

#include <hmg.ch>

Function Main
   define window main at 0, 0 width 260 height 200 main
      define label hostlabel
         row 10
         col 10
         width 100
         value 'Host'
      end label
      define textbox host
         row 10
         col 110
         width 120
         value 'localhost'
      end textbox
      define label userlabel
         row 40
         col 10
         width 100
         value 'UserName'
      end label
      define textbox user
         row 40
         col 110
         width 120
         value 'root'
      end textbox
      define label passlabel
         row 70
         col 10
         width 100
         value 'Password'
      end label
      define textbox pass
         row 70
         col 110
         width 120
         password .t.
      end textbox
      define label newdblabel
         row 100
         col 10
         width 100
         value 'New DB'
      end label
      define textbox newdb
         row 100
         col 110
         width 120
      end textbox
      define button restore
         row 130
         col 60
         caption 'Restore'
         action restoredb()
      end button   
   end window
   main.center
   main.activate
Return


function restoredb( cZip )
   local aFiles := {}
   local cSQLFile := ''
   local cLB := chr( 10 ) + chr( 13 )
   local cTime := time()
   default cZip := ''
   
   if hmg_len( alltrim( cZip ) ) == 0
      cZip := GetFile( , 'Select the File to Restore' )
   endif
   if .not. file( cZip )   
      msgstop( cZip + ' File Not Found!' )
      return nil
   endif
   cCurFolder := GetCurrentFolder()
   cTempFolder := GetCurrentFolder() + '\' + '_RestoreTemp' + dtos( date() ) + substr( cTime, 1, 2 )  + substr( cTime, 4, 2 ) + substr( cTime, 7, 2 )
   if MakeDir( cTempFolder ) <> 0
      msgstop( 'Restore Directory could not be created!' )
      return nil
   endif   
   SetCurrentFolder( cTempFolder )
   WAIT WINDOW 'Processing the zip file...' nowait
   Uncompress File cZip
   aDirectory := Directory()
   for i := 1 to hmg_len( aDirectory )
      if HB_UTF8RAT( '.SQL', hmg_upper( aDirectory[ i, 1 ] ) ) > 0
         cSQLFile := aDirectory[ i, 1 ]
      else   
         aadd( aFiles, aDirectory[ i, 1 ] )
      endif
   next i
   if hmg_len( aFiles )== 0
      msgstop( 'Could not find any file!' )
      return nil
   endif
   if hmg_len( cSQLFile ) == 0
      msgstop( 'SQL File Not found!' )
      return nil
   endif
   cUser := main.user.value
   cHost := main.host.value
   cPass := main.pass.value
   cNewDB := main.newdb.value
   oDB := connect2db( cHost, cUser, cPass, cNewDB )
   if oDB == nil
      msgstop( 'Database could not be connected' )
      return nil
   endif      
   tables := sql( oDB, "show tables" )
   if hmg_len( tables ) > 0
      msgstop( 'Database Should be new and empty!' )
      return nil
   endif
   WAIT WINDOW 'Creating Tables...' nowait
   aCreate := HB_ATOKENS( MEMOREAD( cSQLFile ),   cLB ) 
   for i := 1 to hmg_len( aCreate )
      if hmg_len( alltrim( aCreate[ i ] ) ) > 0
         lSuccess := .t.
         lSuccess := miscsql( oDB, alltrim( aCreate[ i ] ) )
         if .not. lSuccess
            msgstop( 'Table Creation Error!' )
            return nil
         endif
      endif   
   next i
   tottables := hmg_len( aFiles )
   for i := 1 to tottables
      cFileName := aFiles[ i ]
      cTable := substr( cFileName, 1, hmg_len( cFileName ) - 4 )
      WAIT WINDOW "Processing Table - " + cTable + '( ' + alltrim( str( i ) ) + '/' + alltrim( str( tottables ) ) + ' )'   NOWAIT
      cFileNameWithPath := cTempFolder + '\' + cFileName
      cTempFileName := strtran( cFileNameWithPath, '\', '/' )
      lSuccess := miscsql( oDB, "load data infile '" + cTempFileName + "' into table " + cTable )
      if .not. lSuccess 
         msgstop( cFileName + ' could not be restored!' )
         return nil
      endif
   next i
   wait clear
   msginfo( 'Database restored successfully!' )
   ferase( cSQLFile )
   for i := 1 to hmg_len( aFiles )
      ferase( aFiles[ i ] )
   next i
   SetCurrentFolder( cCurFolder )
   dirremove( cTempFolder )
   return nil
 
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

ecabrerizo
Posts: 28
Joined: Mon Mar 26, 2012 10:33 am
Location: España

Post by ecabrerizo » Mon Jul 30, 2018 5:54 pm

Thanks !!!!
Best Regards

User avatar
koke
Posts: 70
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB
Has thanked: 15 times
Been thanked: 24 times

Post by koke » Mon Jul 30, 2018 6:18 pm

Thanks !!
,___,
[O.o]
/)__)
-”–”-
KoKe

ASESORMIX
Posts: 77
Joined: Thu Oct 25, 2012 8:08 pm
Location: Bqto, Venezuela
Been thanked: 6 times

Post by ASESORMIX » Tue Jul 31, 2018 11:59 am

Saludos.
Muchas gracias.

Post Reply