FWH 16.06: MariaDb/MySql Backup and Restore (Updated 16.08)

[nageswaragunupudi]

Backup and Restore speeds:

We made a test to benchmark speeds for a million record table on localhost. The test program is listed below.

We are familiar with customer.dbf in fwh\samples folder. First the data of the dbf is inserted into "custbig" table 2000 times, making it a million records table. Then this table is backedup, dropped, restored, opened and browsed.

Code: Select all  Expand view  RUN

function BigTable()

   local cSourceDBF  := "c:\fwh\samples\customer.dbf"
   local cBackUpFile := "c:\tests\custbig.sql"
   local cTable      := "custbig"
   local aData, cList, cInsertSQL
   local oRs, n, nSecs

   oCn:lShowErrors   := .t.

   if ! oCn:TableExists( cTable )

      // Create Table Structure
      oCn:ImportFromDBF( cSourceDBF, cTable, nil, 0 )
      // Prepare insert sql
      USE ( cSourceDBF ) NEW ALIAS SRC SHARED VIA "DBFCDX"
      cList    := nil
      aData    := SRC->( FW_DbfToArray( @cList ) )
      CLOSE SRC
      cInsertSQL  := oCn:InsertSQL( cTable, cList, aData, .f., .f. )

      ? "Creating Big talble. Please wait"
      nSecs    := SECONDS()
      for n := 1 to 2000
         oCn:Execute( cInsertSQL )
      next
      ? "Table CustBig with million records created in", ;
        Seconds() - nSecs, "Seconds" // --> 138.89 Secs = 2 and half minutes

   endif

   ? "Start Backup. Please wait."
   nSecs    := Seconds()
   oCn:BackUp( { cTable }, cBackUpFile, nil, 10000 )  // 10000 records per sql
   ? cBackUpFile + " created in ", Seconds() - nSecs, "Seconds" // --> 4.4 secs

   ? "Start Restore. Please Wait"
   oCn:DropTable( cTable )
   nSecs    := Seconds()
   oCn:Restore( cBackUpFile )
   ? "Restored one million records in", Seconds() - nSecs, "Seconds"
   // --> 47.88 secs

   ? "Opening Table for Browse. Please wait"
   oRs   := oCn:RowSet( cTable )
   XBROWSER oRs SHOW SLNUM TITLE cTable + " Read in " + cValToChar( oRs:nReadSecs ) + " Seconds"
   // 5 secs

return nil
 

Results:
1) Creation of table : 2 and half minutes
2) Backup: Less than 5 seconds.
3) Restore: 48 seconds.
4) Reading into Rowset: 5 seconds.

These tests are done on localhost. On remote connections the speeds will be less. Actual speeds may also vary due to differences in hardware and memory.

Important Note: To improve speeds we used large buffers. It is necessary that the max_allowed_packet_size is set to adequately large size.

We shall be glad to get a feedback on comparative time taken by other tools to backup and restore the same table.

[Armando]

Mr. Rao:

Thanks for the benchmark, it's very impresive.

I'll be glad and very gratifully if you can make an other benchmark
using tables with longblob and longtext fields and with images or
PDF files inside a longblob field.

I want to update my fwh but it's very important to know the restore speed.

Thanks so much

With best regards

[nageswaragunupudi]

tables with longblob and longtext fields and with images or
PDF files inside a longblob field.

As we said in our earlier post, these would be a little slower because we are backing up and restoring one record per one sql statement. While writing the code, we just wanted to play safe on memory issues. We may increase the speeds later but initially we thought safety comes first. But even in these cases we won't be disappointed.

Let me think how to make a test using the files available in fwh folder, so that we can all test the same sample.

Only case where I am not confident is where binary data is stored in TEXT fields.

[Armando]

Mr. Rao:

Ok, thanks for your answer, and don't worry it's not urgent.

With best regards

[nageswaragunupudi]

Mr Armando

So far our tests were confined to fwh\samples\wwonders.dbf with 9 jpeg images and some tables created with fwh pngs and bmps. Obviously they are not large tables, We shall soon do test with a large table and report the results here. You may please wait.

[vilian]

Mr Rao,

I have tried and everything is fine.
In the tests I did the backup/restore of a database with more than 150mb of size, where I have some tables with TEXT/BINARY fields.
Indexes and triggers were successful restored too.

[nageswaragunupudi]

Mr Vilian

Thanks for your testing and also for your support during development.

[Armando]

Mr. Rao:

Don't worry, I'll be patient.

Regards

Vilan:

Você pode compartilhar o tempo de recuperação

Obrigado

[vilian]

Armando,

About time to restore there are two situations:

1) For tables without TEXT/BINARY fields it's a little fatest than mysql.exe restore;
2) For tables with TEXT/BINARY fields it´s almost twice slower. But It´s something that Mr Rao is working to improve.

I used to work with tdolphin for a long time, but I believe that FWH MYSQL implementation already works better than it, with more resources and has the best integration with xbrowse that I have ever seen!

Mr Rao is doing a great job !!

[nageswaragunupudi]

We did this test for LONGBLOB and LONGTEXT fields. Source code is provided in this post, which uses files available in fwh\samples folder and can be tested by any.

Code: Select all  Expand view  RUN

function BlobTable()

   local cSourceDBF  := "c:\fwh\samples\wwonders.dbf"
   local cBackUpFile := "c:\tests\wwonders.sql"
   local cTable      := "wwonders"
   local aData, cList, cInsertSQL
   local oRs, n, nSecs

   oCn:lShowErrors   := .t.
   ? "Start"

   if oCn:TableExists( cTable )
      oCn:DropTable( cTable )
   endif
   if oCn:TableExists( "wtemp" )
      oCn:DropTable( "wtemp" )
   endif

   ? "Create from DBF"

   nSecs := SECONDS()
   aData    := { "OLGA1_JPG", MemoRead( "c:\fwh\bitmaps\olga1.jpg" ), MemoRead( "c:\fwh\source\classes\window.prg" ) }
   oCn:ImportFromDBF( cSourceDBF, cTable )
   oCn:Insert( cTable, "NAME,IMAGE,DETAILS", aData )
   oCn:ImportFromDBF( cSourceDBF, "wtemp" )
   oCn:Insert( "wtemp", "NAME,IMAGE,DETAILS", aData )
   oCn:SetAutoCommit( .f. )
   for n := 1 to 3
      oCn:Execute( "INSERT INTO WTEMP    (NAME,IMAGE,DETAILS) SELECT NAME,IMAGE,DETAILS FROM WWONDERS" )
      SysRefresh()
      oCn:Execute( "INSERT INTO WWONDERS (NAME,IMAGE,DETAILS) SELECT NAME,IMAGE,DETAILS FROM WTEMP" )
      SysRefresh()
   next
   for n := 1 to 30
      oCn:Execute( "INSERT INTO WWONDERS (NAME,IMAGE,DETAILS) SELECT NAME,IMAGE,DETAILS FROM WTEMP" )
      SysRefresh()
   next
   oCn:SetAutoCommit( .t. )

   ? SECONDS() - nSecs   // 183.79 secs
   ? oCn:QueryResult( "SELECT COUNT(*) FROM WWONDERS" )  // 4110 records

   // BLOB size 492,522,672 bytes
   // TEXT      43,313,235

   ? "Start Backup. Please wait."
   nSecs    := Seconds()
   oCn:BackUp( { cTable }, cBackUpFile )
   ? cBackUpFile + " created in ", Seconds() - nSecs, "Seconds" // --> 20 secs

   oCn:BackupIndex( cBackupFile, .t. )

   ? "Start Restore. Please Wait"
   oCn:DropTable( cTable )

   nSecs    := Seconds()
   oCn:Restore( cBackUpFile )
   ? "Restored in", Seconds() - nSecs, "Seconds"
   // 130 Secs ( 2 mins 10 secs )

   ? "Opening Table for Browse. Please wait"
   oRs   := oCn:RowSet( cTable )
   XBROWSER oRs SHOW SLNUM TITLE cTable + " Read in " + cValToChar( oRs:nReadSecs ) + " Seconds"
   // 15 secs

return nil
 

First we created a table "wwonders" wit fields ID (AI), NAME( VarChar ), IMAGE ( LONGBLOB ) and DETAILS( LONGTEXT ) with 4110 records.

Total Size of LONGBLOB field contents : 0.493 GB
Total Size of LONGTEXT field contents : 0.043 GB
Total size 0.540 GB

Backup file size 1.070 GB created in 20 seconds.
Restored in 2 minutes and 10 seconds.

[devtuxtla]

Hi mr. Nages


Is it possible to implement an encryption method to the backup result?

Regards

[nageswaragunupudi]

Possible.
So far we did not implement.
If that is what many users want we can implement.

[devtuxtla]

Thanks...

Any idea for a quick implementation?

Regards

[nageswaragunupudi]

We will confirm in a few days if we can include it in next release.

[nageswaragunupudi]

FWH 17.03

Encrypted Backup files


Now it is possible to encrypt the backup files, with a key specified by the user. When encrypted, the backed up data is not in human readable form thereby protecting sensitive data from being accessed by unauthorized persons. It may be noted that the backup data is not encrypted after creation of the file, but encrypted data is written to the file. So even if the backup process is interrupted half way, the already created file is not human readable. The difference in performance is not perceptible.

For this purpose, the user has to first set an encryption key and then run the usual backup functions and finally clear the encryption key.

Again for restoring, the same key should be set and then call the restore function.

Setting and clearing the encryption key:
function MYSQL_CRYPTKEY( [cKey] ) --> cOldKey

This function always returns the key already set. "" indicates no key is set.

Calling the function with string of 6 or more bytes as parameter, sets the string as encryption key. (Note: The key has 32 bytes limit and any excess bytes are ignored)

Calling the function with any other parameter ( eg. nil, "", or any non character type ) clears the key.

Example:

Code: Select all  Expand view  RUN

MYSQL_CRYPTKEY( "My Encryption Key" )
oCn:Backup( { <aTables> }, <cFile> )
MYSQL_CRYPTKEY( nil ) // clear the key
 

Restoring

Code: Select all  Expand view  RUN

MYSQL_CRYPTKEY( "My Encryption Key" )
oCn:Restore( <cFile> )
MYSQL_CRYPTKEY( nil ) // clear the key
 

Attempt to restore without the same key fails.