Cannot restore MySQL backup | General Questions and Support | Forum Archive

The free forums are no longer in use. It remains available as read-only archive.

Avatar
Lost password?
Advanced Search
Forum Scope


Match



Forum Options



Minimum search word length is 3 characters – maximum search word length is 84 characters
The forums are currently locked and only available for read only access
sp_TopicIcon
Cannot restore MySQL backup
April 1, 2013
11:13 am
Avatar
ner0
Member
Members
Forum Posts: 7
Member Since:
October 5, 2012
sp_UserOfflineSmall Offline

Hello,

Today I realised the hard way that my xcloner MySQL database backups are useless for some reason that I fail to understand.

If I backup the exact same database using phpMyAdmin, the restore process takes 2~3 seconds for the whole database.

When I try to restore using the xCloner backup, it takes on forever and phpMyAdmin is constantly interrupting the import process after 5 minutes. Clearly that there is something wrong with the way my Xcloner SQL backups are generated, but that still doesn't get me closer to understanding how I can fix this. Any tips are welcome.

 

Thanks in advance.

April 1, 2013
12:06 pm
Avatar
Ovidiu Liuta
Admin
Forum Posts: 2484
Member Since:
September 26, 2010
sp_UserOfflineSmall Offline

XCloner backups up the mysql data inside the administrator/backups/database-sql.sql file inside the created backup archive, try and import that and see if it helps.

April 1, 2013
2:45 pm
Avatar
ner0
Member
Members
Forum Posts: 7
Member Since:
October 5, 2012
sp_UserOfflineSmall Offline
Thanks for your quick reply.
Maybe I did not explain my “problem” correctly. I know exactly where XCloner SQL dumps are (exactly where you told). My problem is the time that an XCloner SQL dump takes to complete versus a phpMyAdmin SQL dump (for the same database). In my attempts to recover a database using an XCloner dump I realized that it took 60x more time than a phpMyAdmin dump, and yes this is huge. I'm talking around ~15 seconds to restore from a phpMyAdmin dump and 15+ minutes for an XCloner dump. The restore process from the XCloner's dump is interrupted several times because of timeouts.
I think I have an idea why this happens. It seems to be the way XCloner script the database (compared to phpMyAdmin). Here is an example of what I am saying:
XCLONER SAMPLE DUMP: http://pastebin.com/javFiSSE
…and…
PHPMYADMIN SAMPLE DUMP: http://pastebin.com/ebwxzmv9
XCloner's dump executes 9 queries while phpMyAdmin's dump executes 6 queries. I know this may not seem much for 1 table, 4 rows and 4 lines of data but if the same thing happens for 40 tables, 4000 rows and 40000 lines of data, then I suppose this would have a huge impact on the restoration process. I'm not sure if there is something that can be done about it at the moment but I would like to know what you think of it. Thanks for you time and effort.
April 1, 2013
3:14 pm
Avatar
Ovidiu Liuta
Admin
Forum Posts: 2484
Member Since:
September 26, 2010
sp_UserOfflineSmall Offline

 Yes, i understand now, if you support mysqldump utility on your server, try and activate it and rerun th Backup Process, the mysqldump should be more compressed. Otherwise, you always have the option to restore the mysql backup only by using XCloner, simply upload the file in the administrator/backups folder and start XCloner.php restore process with “Skip files restore” option checked.

April 1, 2013
4:06 pm
Avatar
ner0
Member
Members
Forum Posts: 7
Member Since:
October 5, 2012
sp_UserOfflineSmall Offline

What you said above is the definite solution for my issue!

On one hand, using the XCloner “clone” function allows me to have a real-time notion of the database restore process, I did not use it before but it's a great feature. On the other hand, using mysqldump has created a database dump exactly like the ones phpMyAdmin does. It now takes the same ~15sec. to restore the database through phpMyAdmin.

Everything is working perfectly now, thank you again! Wink

April 1, 2013
4:21 pm
Avatar
ner0
Member
Members
Forum Posts: 7
Member Since:
October 5, 2012
sp_UserOfflineSmall Offline

Sorry but I have one further question. I did not find info on this in any XCloner documentation, wiki or forum.

It is possible to use some switches along mysqldump, but those switches are not from mysqldump itself:

–quote-names

–quick

–single-transaction

–skip-comments

Can you explain to me what each of these switches do? If this info is published elsewhere, please link it here.

 

Thank you!

April 1, 2013
6:11 pm
Avatar
Ovidiu Liuta
Admin
Forum Posts: 2484
Member Since:
September 26, 2010
sp_UserOfflineSmall Offline

Yes, you can use those switches, the mysqldump usage manual is here http://dev.mysql.com…..ldump.html

April 1, 2013
6:51 pm
Avatar
ner0
Member
Members
Forum Posts: 7
Member Since:
October 5, 2012
sp_UserOfflineSmall Offline

I did not find any association between those switches and mysqldump in a quick Google search, that's why I assumed they were customized and asked for it. I will definitely make a donation in a few days. Thank you for your awesome support!

Forum Timezone: America/Chicago
Most Users Ever Online: 867
Currently Online:
Guest(s) 1
Currently Browsing this Page:
1 Guest(s)
Top Posters:
mlguru: 30
Django29: 29
Andy: 21
D: 21
Marcus: 20
Jamie F: 19
Member Stats:
Guest Posters: 737
Members: 9946
Moderators: 2
Admins: 3
Forum Stats:
Groups: 3
Forums: 7
Topics: 2397
Posts: 8236
Newest Members:
Vargas
Moderators: TriP: 0, Steve Burge: 0
Administrators: Ovidiu Liuta: 2484, Victor Drover: 1, Valentin Barbu: 0