|
Home > Archive > MySQL ODBC Connector > April 2006 > Undelete rows with .MYD-File?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Undelete rows with .MYD-File?
|
|
|
|
| mysql@karsites.net 2006-04-03, 9:29 am |
| If those three files were backed up some where before you
dropped the table all you need to do is to copy them back
into the data dir, and things should be ok again.
Keith
In theory, theory and practice are the same;
in practice they are not.
On Mon, 3 Apr 2006, Nico Schefer wrote:
> To: mysql@lists.mysql.com
> From: Nico Schefer <schefer@webdessert.ch>
> Subject: Undelete rows with .MYD-File?
>
> Hi!
>
> Today i've dropped a MySQL-table and realized seconds later that i've
> dropped the wrong one.. I've saved the .MYD-File and tried to recover it
> with a tool (MySQLRecovery 1.5), but it just recovered the structure and
> not the data (about 620 rows).
> I've searched now all the day and not found anything. Maybe someone knows
> how to bring the data back or has got any hint for me?
>
> Binary log is not enabled unfortunately...
>
> http: //www.swissmade.com/mysql/shop_item.MYD
> http: //www.swissmade.com/mysql/shop_item.MYI
> http: //www.swissmade.com/mysql/shop_item.frm
>
> Thanks a lot!
>
> Nico
>
> --
> Nico Schefer
> schefer@webdessert.ch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| mysql@karsites.net 2006-04-03, 9:29 am |
|
I have downloaded the table files and this is what I get:
mysql> use swissmade;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_swissmade |
+---------------------+
| shop_item |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from shop_item \G
Empty set (0.00 sec)
Do you have any copies of the table files that you have not
run the recovery program on?
Keith
In theory, theory and practice are the same;
in practice they are not.
On Mon, 3 Apr 2006, Nico Schefer wrote:
> To: mysql@lists.mysql.com
> From: Nico Schefer <schefer@webdessert.ch>
> Subject: Undelete rows with .MYD-File?
>
> Hi!
>
> Today i've dropped a MySQL-table and realized seconds later that i've
> dropped the wrong one.. I've saved the .MYD-File and tried to recover it
> with a tool (MySQLRecovery 1.5), but it just recovered the structure and
> not the data (about 620 rows).
> I've searched now all the day and not found anything. Maybe someone knows
> how to bring the data back or has got any hint for me?
>
> Binary log is not enabled unfortunately...
>
> http: //www.swissmade.com/mysql/shop_item.MYD
> http: //www.swissmade.com/mysql/shop_item.MYI
> http: //www.swissmade.com/mysql/shop_item.frm
>
> Thanks a lot!
>
> Nico
>
> --
> Nico Schefer
> schefer@webdessert.ch
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...ites
.net
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| mysql@karsites.net 2006-04-03, 9:29 am |
|
IIRC there may be a hidden field in each table row that
mysql uses to mark that row as deleted.
I'm not sure if you can use some mysql utility program such
as myisamchk to undelete the rows.
This may be possible.
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Mon, 3 Apr 2006 mysql@karsites.net wrote:
> To: mysql@lists.mysql.com
> From: mysql@karsites.net
> Subject: Re: Undelete rows with .MYD-File?
>
>
> I have downloaded the table files and this is what I get:
>
> mysql> use swissmade;
> Database changed
> mysql> show tables;
> +---------------------+
> | Tables_in_swissmade |
> +---------------------+
> | shop_item |
> +---------------------+
> 1 row in set (0.00 sec)
>
> mysql> select * from shop_item \G
> Empty set (0.00 sec)
>
> Do you have any copies of the table files that you have not
> run the recovery program on?
>
> Keith
>
> In theory, theory and practice are the same;
> in practice they are not.
>
>
> On Mon, 3 Apr 2006, Nico Schefer wrote:
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...ites
.net
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| mysql@karsites.net 2006-04-03, 1:29 pm |
|
karsites:/var/swissmade # ./myisamchk -dvv shop_item.MYI
MyISAM file: shop_item.MYI
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2006-03-31 13:59:48
Status: open,changed
Auto increment key: 1
Last value: 673
Data records: 0
Deleted blocks: 675
Datafile parts: 675
Deleted data: 33760
Datafile pointer (bytes): 4
Keyfile pointer (bytes): 4
Datafile length: 33760
Keyfile length: 8192
Max datafile length: 4294967294
Max keyfile length: 4398046510079
Recordlength: 295
It looks like someone has packed this table with myisampack,
which means it's read only. Did you use myisampack on the
table before it got dropped Nico?
Also, did mysql die when the table was in use?
I have tries to run myisamchk -r shop_item.MYI but this sets
the *.MYD file to zero.
I don't know if it is possible to recover data from a packed
table. AFAIK the packing process is one way, and you may
need the original non-compressed table to get your data.
Regards
Keith
On Mon, 3 Apr 2006, Nico Schefer wrote:
> To: mysql@karsites.net
> From: Nico Schefer <schefer@webdessert.ch>
> Subject: Re: Undelete rows with .MYD-File?
>
> Hi
>
> Keith, thanks for your proposal with myisamchk.
>
> If i'm using the myisamchk, it finds the deleted rows, but i have not
> found a way to restore them and i can't find nothing in the manual..
>
> Checking MyISAM file: shop_item.MYI
> Data records: 0 Deleted blocks: 675
> myisamchk.exe: warning: 1 client is using or hasn't closed the table
> properly
> - check file-size
> - check record delete-chain
> - check key delete-chain
> - check index reference
> - check data record references index: 1
> - check record links
> MyISAM-table 'shop_item.MYI' is usable but should be fixed
>
> Does anybody know how to restore the data this way?
>
> Thanks a lot, Nico
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| mysql@karsites.net 2006-04-03, 8:25 pm |
|
As my server does not get alot of traffic I tend to shutdown
mysql, and do an OS copy of the complete /var/lib/mysql
directory to another partition on another drive. Then
restart mysql again.
This may not be a feasable option on a busy server.
Obviously each person has their way of doing backups. I do
need to study the manual myself on all available backup
options.
Regards
Keith
On Mon, 3 Apr 2006, Nico Schefer wrote:
> To: mysql@karsites.net
> From: Nico Schefer <schefer@webdessert.ch>
> Subject: Re: Undelete rows with .MYD-File?
>
> Hi Keith
>
>
> Thanks a lot for looking at the problem. As far as i know the table has
> not been compressed, and i've not used myisampack. I'v tried to run
> myisamchk -e -r as well, but is has trunctated my MYD-File as well.
> I think i have to live with it, i begun to recunstruct the data by hand
> wich took me the whole day now and i'm not finished by far.. but well,
> i'll certainly do some backups now ;-)
>
> Thanks and greetings, Nico
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|