dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
16
share rss forum feed


Maxo
Your tax dollars at work.
Premium,VIP
join:2002-11-04
Tallahassee, FL

1 edit
reply to Maxo

Re: Data recovery help

I'm pretty pumped as all of the mysql files are now readable and appear to be in tact. It will be a while before I have the opportunity to actually try restoring those files and seeing if they work.
If this works I'm shipping some beers out your way leibold See Profile.



leibold
Premium,MVM
join:2002-07-09
Sunnyvale, CA
kudos:10
Reviews:
·SONIC.NET

said by Maxo:

If this works I'm shipping some beers out your way leibold See Profile.

Better not, I don't drink

The file with inode number 90322 got truncated during the fsck. To identify which file this was, you can use the find command after mounting the filesystem. E.g.:

mount -r /dev/loop0 /mnt
find /mnt -inum 90322 -print

It is also possible to determine which files have been corrupted due to the defects in the sdcard by checking the list of badblocks that dd_rescue reported. This is a bit more involved and you can find some related information here . Note1: you don't need to run any programs to find bad blocks since you already have the bad block list (running badblocks on the copy wouldn't find any). Note2: in addition to the calculation regarding block sizes you also need to subtract the offset of the 2nd partition.
--
Got some spare cpu cycles ? Join Team Helix or Team Starfire!


Maxo
Your tax dollars at work.
Premium,VIP
join:2002-11-04
Tallahassee, FL

I got my server back up and running and setup mysql with my October backup.
From my recovery image I pulled out /var/lib/mysql and put it on the server but mysql won't start up. So I put back in the database from my restore and tried adding in just /var/lib/mysql/gnucash, a schema that I added a week before the crash.
With that in place I can see it from "show schemas" and when I run "show tables" it lists all of the gnucash tables. However accessing the tables doesn't go so well.

mysql> use gnucash;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_gnucash |
+-------------------+
| accounts |
| billterms |
| books |
| budget_amounts |
| budgets |
| commodities |
| customers |
| employees |
| entries |
| gnclock |
| invoices |
| jobs |
| lots |
| orders |
| prices |
| recurrences |
| schedxactions |
| slots |
| splits |
| taxtable_entries |
| taxtables |
| transactions |
| vendors |
| versions |
+-------------------+
24 rows in set (0.00 sec)

mysql> select * from accounts;
ERROR 1146 (42S02): Table 'gnucash.accounts' doesn't exist
mysql> select * from transactions;
ERROR 1146 (42S02): Table 'gnucash.transactions' doesn't exist
mysql> select * from prices;
ERROR 1146 (42S02): Table 'gnucash.prices' doesn't exist
mysql> select * from vendors;
ERROR 1033 (HY000): Incorrect information in file: './gnucash/vendors.frm'
mysql>

At this point do you think it best to move the problem over to »Web Developers ?
--
"Padre, nobody said war was fun now bowl!" - Sherman T Potter

»maxolasersquad.com/

»maxolasersquad.blogspot.com

»www.facebook.com/maxolasersquad


Maxo
Your tax dollars at work.
Premium,VIP
join:2002-11-04
Tallahassee, FL

Another bit of information that may be useful.

root@butter:/var/lib/mysql/gnucash# ls -l
total 634
-rw-rw---- 1 mysql mysql 33550 Feb 10 09:42 accounts.frm
-rw-rw---- 1 mysql mysql 9184 Jan 29 20:10 accounts.MYD
-rw-rw---- 1 mysql mysql 5120 Jan 29 20:10 accounts.MYI
-rw-rw---- 1 mysql mysql 25372 Feb 10 09:42 billterms.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 billterms.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 billterms.MYI
-rw-rw---- 1 mysql mysql 8674 Feb 10 09:42 books.frm
-rw-rw---- 1 mysql mysql 104 Jan 29 20:10 books.MYD
-rw-rw---- 1 mysql mysql 2048 Jan 29 20:10 books.MYI
-rw-rw---- 1 mysql mysql 8776 Feb 10 09:42 budget_amounts.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 budget_amounts.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 budget_amounts.MYI
-rw-rw---- 1 mysql mysql 20966 Feb 10 09:42 budgets.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 budgets.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 budgets.MYI
-rw-rw---- 1 mysql mysql 45736 Feb 10 09:42 commodities.frm
-rw-rw---- 1 mysql mysql 84 Jan 29 20:10 commodities.MYD
-rw-rw---- 1 mysql mysql 2048 Jan 29 20:10 commodities.MYI
-rw-rw---- 1 mysql mysql 58990 Feb 10 09:42 customers.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 customers.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 customers.MYI
-rw-rw---- 1 mysql mysql 61 Feb 10 09:42 db.opt
-rw-rw---- 1 mysql mysql 50314 Feb 10 09:42 employees.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 employees.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 employees.MYI
-rw-rw---- 1 mysql mysql 38538 Feb 10 09:42 entries.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 entries.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 entries.MYI
-rw-rw---- 1 mysql mysql 8596 Feb 10 09:42 gnclock.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 gnclock.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 gnclock.MYI
-rw-rw---- 1 mysql mysql 25626 Feb 10 09:42 invoices.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 invoices.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 invoices.MYI
-rw-rw---- 1 mysql mysql 25158 Feb 10 09:42 jobs.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 jobs.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 jobs.MYI
-rw-rw---- 1 mysql mysql 8646 Feb 10 09:42 lots.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 lots.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 lots.MYI
-rw-rw---- 1 mysql mysql 25248 Feb 10 09:42 orders.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 orders.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 orders.MYI
-rw-rw---- 1 mysql mysql 21124 Feb 10 09:42 prices.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 prices.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 prices.MYI
-rw-rw---- 1 mysql mysql 12876 Feb 10 09:42 recurrences.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 recurrences.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 recurrences.MYI
-rw-rw---- 1 mysql mysql 13206 Feb 10 09:42 schedxactions.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 schedxactions.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 schedxactions.MYI
-rw-rw---- 1 mysql mysql 33596 Feb 10 09:42 slots.frm
-rw-rw---- 1 mysql mysql 732 Jan 29 20:10 slots.MYD
-rw-rw---- 1 mysql mysql 3072 Jan 29 20:10 slots.MYI
-rw-rw---- 1 mysql mysql 21314 Feb 10 09:42 splits.frm
-rw-rw---- 1 mysql mysql 296 Jan 29 20:10 splits.MYD
-rw-rw---- 1 mysql mysql 4096 Jan 29 20:10 splits.MYI
-rw-rw---- 1 mysql mysql 8748 Feb 10 09:42 taxtable_entries.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 taxtable_entries.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 taxtable_entries.MYI
-rw-rw---- 1 mysql mysql 8702 Feb 10 09:42 taxtables.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 taxtables.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 taxtables.MYI
-rw-rw---- 1 mysql mysql 21050 Feb 10 09:42 transactions.frm
-rw-rw---- 1 mysql mysql 96 Jan 29 20:10 transactions.MYD
-rw-rw---- 1 mysql mysql 3072 Jan 29 20:10 transactions.MYI
-rw-rw---- 1 mysql mysql 50164 Feb 10 09:42 vendors.frm
-rw-rw---- 1 mysql mysql 0 Jan 29 20:10 vendors.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 29 20:10 vendors.MYI
-rw-rw---- 1 mysql mysql 8620 Feb 10 09:42 versions.frm
-rw-rw---- 1 mysql mysql 504 Jan 29 20:10 versions.MYD
-rw-rw---- 1 mysql mysql 2048 Jan 29 20:10 versions.MYI


--
"Padre, nobody said war was fun now bowl!" - Sherman T Potter

»maxolasersquad.com/

»maxolasersquad.blogspot.com

»www.facebook.com/maxolasersquad

pablo
MVM
join:2003-06-23
kudos:1
reply to Maxo

Hi,

At this point, you'll want to scour the web/Internet on how to scrape data from a corrupted MySQL schema.

There may be some MySQL utilities which can be used to attempt a repair of the data files too.

Cheers,
-pablo
--
openSUSE 12.2/KDE 4.x
ISP: TekSavvy Bonded DSL; backhauled via a 6KM wireless link
Assorted goodies: »pablo.blog.blueoakdb.com



Maxo
Your tax dollars at work.
Premium,VIP
join:2002-11-04
Tallahassee, FL

1 recommendation

said by pablo:

Hi,

At this point, you'll want to scour the web/Internet on how to scrape data from a corrupted MySQL schema.

There may be some MySQL utilities which can be used to attempt a repair of the data files too.

Cheers,
-pablo

I've done some looking around. I move from trying to repair the gnucash database, which is completely disposable, to working on is4c_op, which is an important schema. I was able to repair all tables except for the member_payments table.
Most of the tables where just fine and didn't need any repair.
Some table where fixed by running "repair table use_frm;"
Some tables where fixable by copying .frm from the good restore and plopping it and then running the repair command.
I've got one more important schema to try to repair, is4c_log.
--
"Padre, nobody said war was fun now bowl!" - Sherman T Potter

»maxolasersquad.com/

»maxolasersquad.blogspot.com

»www.facebook.com/maxolasersquad


Maxo
Your tax dollars at work.
Premium,VIP
join:2002-11-04
Tallahassee, FL

I'm happy to report that I appear to have been able to successfully recover all tables by piecing together the binary files from the restored disk image and the mysqldump export from last October.
I have two more steps before I can move on. I need to make the server live and I need to develop a proper backup mechanism so that I'm not in this rut again the next time I run into problems.
I'd really like to thank everyone that stopped by and gave suggestions. I only do this work as a volunteer and my main knowledge is in coding, not server administration. I would not have been able to get a full recovery without your great advice.
--
"Padre, nobody said war was fun now bowl!" - Sherman T Potter

»maxolasersquad.com/

»maxolasersquad.blogspot.com

»www.facebook.com/maxolasersquad



leibold
Premium,MVM
join:2002-07-09
Sunnyvale, CA
kudos:10
Reviews:
·SONIC.NET

3 edits

1 recommendation

A nice piece of set-it-up-and-forget-it remote backup software is rsnapshot.

Since you are using mysql databases I would combine a local mysqldump (easier to recover from then binary database files) with rsnapshot for frequent full system backups. Don't let "full system backup" scare you, with rsnapshot only the first backup will take a lot of time since all files will need to be transferred from the database server to your backup destination. All subsequent backups only transmit new or changed data. Another nice feature of rsnapshot is that it heavily uses hard-links. This makes each snapshot a full and complete copy of the backed up server while keeping total disk utilization for all snapshots very small (size of 1 full backup plus the sum of all new and changed files).

For this suggestion:
1.) run rsyncd on the database server (locked down to only be accessible from the backup server, run as root, no chroot to allow backup of files with restricted access, read-only mode for added security since that is all that is needed for backup purposes), ideally start it automatically on boot (e.g. init.d).
2.) periodically run mysqldump on the database server (e.g. from cron so that nobody forgets it). It is fine if it always backs up to the same destination on the local disk.
3.) run rsnapshot on the backup server to backup the entire database server (use daily, weekly and monthly cron jobs as desired).Add 'exclude' directives to rsnapshot.conf for virtual filesystems (/dev, /proc, /sys) or anything you really don't want to back up. I also added '--bwlimit=...' to the rsync arguments inside rsnapshot.conf which reduces the impact of backups taken on always up production servers.

Edit: before I get a lot of flak for the "forget-it" part, I specially mean the low maintenance aspect of rsnapshot once it is configured. It doesn't hurt to periodically check rsnapshot.log to see that everything is going smooth just like many other logfiles ought to be periodically reviewed (before warnings turn into fatal errors).

Edit: some additional details added in italic.
--
Got some spare cpu cycles ? Join Team Helix or Team Starfire!