topic-35788

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Backup / Restore Mysql *Solved*
Posted: Tue Jun 26, 2018 10:44 AM

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

Code (fw): Select all Collapse

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,

Regards,

Dutch

FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)

Colin Haig

Posts: 310

Joined: Mon Oct 10, 2005 05:10 AM

Re: Backup / Restore Mysql
Posted: Tue Jun 26, 2018 10:12 PM

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 01:28 AM

Mr. Colin

FWH MariaDB library has internal functions for Backup and Restore, which we can execute from within FWH program.

viewtopic.php?f=3t=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

&

Regards

G. N. Rao.

Hyderabad, India

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 01:58 AM

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

nageswaragunupudi wrote: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

Regards,

Dutch

FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 02:09 AM

You please set "max_allowed_packet" size to a high value

Regards

G. N. Rao.

Hyderabad, India

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 02:10 AM

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 (fw): Select all Collapse

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

nageswaragunupudi wrote:
Do not create database "test"

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

Regards,

Dutch

FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 02:13 AM

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.

nageswaragunupudi wrote:You please set "max_allowed_packet" size to a high value

Regards,

Dutch

FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 02:24 AM

dutch wrote: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 (fw): Select all Collapse
cBackupFile := 'c:\backup\test.sql'
oCn:Backup( nil , cBackUpFile , nil, 1000000 )
oCn:SelectDB( 'test' )
oCn:Restore( cBackUpFile )
nageswaragunupudi wrote:
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.

Regards

G. N. Rao.

Hyderabad, India

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 03:13 AM

Dear Mr.Rao,

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

nageswaragunupudi wrote:
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.

Regards,

Dutch

FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 03:30 AM

please see my personal email

Regards

G. N. Rao.

Hyderabad, India

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: Backup / Restore Mysql *Solved*
Posted: Thu Jul 05, 2018 05:15 AM

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

Regards,

Dutch

FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)

FiveTech Support Forums

Continue the discussion