Backup / Restore Mysql *Solved*

[dutch]

I would like to backup current data and restore to another Database ("test"). I use below code but it doesn't successful.

Code: Select all  Expand view

local nSecs, aTables, cBackUpFile := 'c:\easyfo\backup\test.sql'
nSecs := seconds()
 oCon:SelectDB( 'data' )
oCn:Backup( nil , cBackUpFile , nil, 1000000 )  // upto 1000000 records (4th parameter need or not)

? 'Backup : '+cBackUpFile + " created in ", Seconds() - nSecs, "Seconds"

nSecs := seconds()

 oCon:SelectDB( 'test' )

 oCn:Restore( cBackUpFile )  // Can I use 4th parameter to specific Database or need to change current database by use ::SelectDB()

 ? 'Restore : '+cBackUpFile + " created in ", Seconds() - nSecs, "Seconds"
 

What did I do wrong?

Thanks in advance,

[Colin Haig]

Hi Dutch

You need to use MySql to backup and restore

backup mysqldump -u username -p database > backup.sql

restore mysql -u username -p database < backup.sql

This is a sample from doing it from a shell or command line - you are prompted for the password.

Regards

Colin

[nageswaragunupudi]

Mr. Colin

FWH MariaDB library has internal functions for Backup and Restore, which we can execute from within FWH program.
viewtopic.php?f=3&t=32791


Mr. Dutch
You want to backup database "data" and later restore it with a different name "test".

Step.1:
oCn:BackUp( "data", cBackUpFile ) --> cBackUpfile (if succeeds)

Do not create database "test"

Step.2
oCn:Restore( cBackUpFile, nil, nil, "test" ) --> lSuccess

[dutch]

Dear Mr.Rao,

I can backup to "test" with auto create database but the "fodata" is not fully backup. Some tables doesn't backup or it create only table structure.
I doubt that all files that do not backup or create only structure, it sizes ( more than 10,000 records ).
1. How do I know the backup data is fully backup complete?
2. I try many time, it got the same result.

How do I check, what is the incomplete process?

Thanks in advance,
Dutch

Mr. Colin

FWH MariaDB library has internal functions for Backup and Restore, which we can execute from within FWH program.
viewtopic.php?f=3&t=32791


Mr. Dutch
You want to backup database "data" and later restore it with a different name "test".

Step.1:
oCn:BackUp( "data", cBackUpFile ) --> cBackUpfile (if succeeds)

Do not create database "test"

Step.2
oCn:Restore( cBackUpFile, nil, nil, "test" ) --> lSuccess

[nageswaragunupudi]

You please set "max_allowed_packet" size to a high value

[dutch]

Dear Mr.Rao,

If I use below procedure and I have created 'test' db before run it, it got problem.
Restore will be replace to 'fodata' Db instead of 'test' Db. It means replace to original backup data source.

Code: Select all  Expand view

cBackupFile := 'c:\backup\test.sql'
oCn:Backup( nil , cBackUpFile , nil, 1000000 )
oCn:SelectDB( 'test' )
oCn:Restore( cBackUpFile )

Do not create database "test"

Step.2
oCn:Restore( cBackUpFile, nil, nil, "test" ) --> lSuccess

[dutch]

Dear Mr.Rao,

I set "oCn:max_allowed_packet_MB( 50 )" after connect to server.
I set in MySql server variable as the same size 50Mb also.

You please set "max_allowed_packet" size to a high value

[nageswaragunupudi]

Dear Mr.Rao,

If I use below procedure and I have created 'test' db before run it, it got problem.
Restore will be replace to 'fodata' Db instead of 'test' Db. It means replace to original backup data source.

Code: Select all  Expand view

cBackupFile := 'c:\backup\test.sql'
oCn:Backup( nil , cBackUpFile , nil, 1000000 )
oCn:SelectDB( 'test' )
oCn:Restore( cBackUpFile )

Do not create database "test"

Step.2
oCn:Restore( cBackUpFile, nil, nil, "test" ) --> lSuccess

That is the expected and documented behavior.
If you want to restore it with a different database name, you have to give the new database name in the 4th parameter. Otherwise, it will restore to the same database of which the backup is taken.

[dutch]

Dear Mr.Rao,

I've got it. But I still face the incompleted set of backup database problem. How can I check it?

That is the expected and documented behavior.
If you want to restore it with a different database name, you have to give the new database name in the 4th parameter. Otherwise, it will restore to the same database of which the backup is taken.

[nageswaragunupudi]

please see my personal email

[dutch]

Dear All,

First of all, Thank you so much Mr.Rao for great support.

The backup issue is occurred by my mistake. I've use the old version of LIBMYSQL.DLL. It made the incomplete backup (I guess, incompatible fully FWH feature), that why it did not backup the table which have more than 10,000 records.

After I change LIBMYSQL.DLL (FWH version). The Backup and Restore are working well and really fast.

I do backup database (139 tables / 355 Mb) and speed as below
FWH 1711
localhost = 12 seconds (SSD harddisk)
cloud server = 72 seconds (VM)

I use Navicat to test dump sql in the same database
Navicat
localhost = 17 seconds (SSD harddisk)
cloud server = 101 seconds (VM)

FWH is faster than Navicat around 1.4 time (my test)

Thanks once again, Mr.Rao.

Regards,
Dutch