Solved

Error reading from backup file mysql

Posted on 2011-09-12
4
772 Views
Last Modified: 2012-05-12
HI:
Stupidly I I entered the wrong delete command and managed to lose 97% of 2million records.
Cleverly I had a backup.
Not so cleverly I get a message 'error reading from backup file' 'unknown object in backup file'
However I have managed to backup AND restore the db since then. The difference is that the offending table was > 1GB (it still reads that even though now tiny).
Does this mean that my old backups are corrupt? I have read that there is  1Gb limit so should I run the script  mysql -u<user> -p<password> < <backupfile> .. and do I have to drop ALL the existing tables first (the other 17 tables in the db are fine)?
Finally - a question:  I had kept all the data now lost in a dbf file.. IS it easier to reimport rather than restore? And if so how will I know future backups are OK?
Thanks!
0
Comment
Question by:ClaytonGlass
  • 3
4 Comments
 

Author Comment

by:ClaytonGlass
Comment Utility
Hi Again.
Change of plan. I am working on the premise that the restore is failing because the table size has gone over 1gb. I should really reduce the table size (I can do some normalisation) but  instead am willing to buy navicat to use instead of mysqladmin.
Does anybody have experience of using navicat; specifically restoring tables over 1gb?
And I do like the idea of navicat allowing import of dbf....

Thanks!
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 250 total points
Comment Utility
Navicat is a good program, but I'm not sure you need it. Sometimes you can get backup errors if the max_packet_size is too small to load blobs in one slurp. Also if you backup using one version of mysql and restore using another. If you use the migration toolkit, it should give you more specific errors (i.e. malformed data).

But start by making your max_packet to 1 gb, and see if it works.

see http://stackoverflow.com/questions/291369/what-would-lead-to-an-unknown-object-in-backup-file-problem-when-restoring-a-ba
0
 

Author Comment

by:ClaytonGlass
Comment Utility
Looking good so far.....
0
 

Author Closing Comment

by:ClaytonGlass
Comment Utility
....And Done!
Thank you very much!
Four years work recovered. Points well earned.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now