Solved

Error reading from backup file mysql

Posted on 2011-09-12
4
787 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
ID: 36528405
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
ID: 36528961
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
ID: 36529286
Looking good so far.....
0
 

Author Closing Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

840 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