Page 1 of 1
Backup / Restore Mysql *Solved*
Posted: Tue Jun 26, 2018 10:44 am
by 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
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,
Re: Backup / Restore Mysql
Posted: Tue Jun 26, 2018 10:12 pm
by 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
Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 1:28 am
by nageswaragunupudi
Mr. Colin
FWH MariaDB library has internal functions for Backup and Restore, which we can execute from within FWH program.
http://forums.fivetechsupport.com/viewt ... =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
Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 1:58 am
by 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
nageswaragunupudi wrote:Mr. Colin
FWH MariaDB library has internal functions for Backup and Restore, which we can execute from within FWH program.
http://forums.fivetechsupport.com/viewt ... =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
Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 2:09 am
by nageswaragunupudi
You please set "max_allowed_packet" size to a high value
Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 2:10 am
by 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
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
Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 2:13 am
by 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.
nageswaragunupudi wrote:You please set "max_allowed_packet" size to a high value
Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 2:24 am
by nageswaragunupudi
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: Select all
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.
Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 3:13 am
by dutch
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.
Re: Backup / Restore Mysql
Posted: Wed Jun 27, 2018 3:30 am
by nageswaragunupudi
please see my personal email
Re: Backup / Restore Mysql *Solved*
Posted: Thu Jul 05, 2018 5:15 am
by 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