Topic: HDD crash MySQL db retrieval

[rico]

Hello,

I've run into a major issue. Last November my house was spiked by 230 volts. My neighbors tree overgrew and grounded my main electrical feed into another cable causing it to arc. I lost my TV's, appliances, and 4 computers that were on surge protectors (basically anything in the first 3 plugs nearest to the switch were hit). My SME server seemed to survive as it was connected to a battery back up and a surge protector (so I thought) Anyway, I did not realize one of my drives had gone south, so I continued with normal operations.

Fast forward to Jan 29 2009 7 AM.  My inbox is flooded with errors from the SME server and I can no longer access email since 3 AM.
I go to the server and the monitor is just replicating hdd block errors. I had to go to work, so I left it alone. From work, I was able to remote console via ssh, but I could not back up to anything, as I did not have an external drive connected at home. When I got home I made a huge mistake by trying to do a 7.4 Sme upgrade. The installation failed and disallowed any remote console access. I then tried to fix the bad blocks via the Adaptec SCSI controller software. The scan ran perfect for the first 99% and on the last 1% I ran into all sorts of problems. I tried to relocate the blocks for 5 hours, I would confirm "yes" and "enter" for about 5 hours, until I was bored to death and could no longer stay awake. I gave up and tried to reboot the server in hopes that some of the relocated blocks would at least get the OS up and running. No luck!

Jan 30 I find a program called Stellar Phoenix Linux Data Recovery for windows. I install a SCSI controller in my 2003 server and scan the drive, the scan retrieves mpg, jpg, gif, etc. and makes an img file. Ultimately it wasn't what I needed, as I wanted to retrieve directory structures.

In the meantime I rebuild the server with another hdd.

I then find and use product called explore2FS.exe for Windows.
I was able to view the drive in a directory structure and export files to my Windows Server.

I retrieve all the websites and get them up and running, minus the most important, running Vbulletin which is dependent on MySQL.
I searched for the MySQL db without any luck. I did although find table files in var/lib/mysql and was able to retrieve them

My assumption of not finding or being able to read the main MySQL db is permission issues.

Here is where I am stuck.

I am contemplating mounting the bad hdd onto a second SCSI controller in the rebuilt SME server as I do not know what the server will do at start up.

What next?

HELP!!!!!!





   

 

 

[cactus]

If you run into problems next time, certainly do not upgrade. I also think you should not have messed with other tools and contacted the forums here right away. I also think you seriously need to consider backing up data as you now have learned what effort it takes and stress it causes when you do not do so.

If you have no dump file you most likely are stuck and have no way of restoring as these files might be inconsistent and changes are that the files are corrupt as well as you are not supposed to hotcopy them, but make a dump (or use the binary logs).

If you have a SME Server test box you might try stopping the MySQL service on that box. After that you can copy the files from one database (but not the MySQL database itself) to begin with to the /var/lib/mysql/ folder, set ownership to mysql:mysql and restart your MySQL server and hope you can access the database.

First thing you might need to do is run a mysqlcheck to see if your tables are corrupt and need a repair. If that works you might try the trick for the next database as well. All databases are stored as a folder (with the name of the database) with files representing the tables in that folder.

Once again changes are very minimal this will work and you should certainly not try this on your production system.

[rico]

Update

My friend just sent me a link to http://www.sysresccd.org/Main_Page

I'm going to disconnect my Windows HDD's, install SME on another drive, daisy chain the bad hdd with the fresh SME HDD and attempt to boot to this CD to try to retrieve the db.

Rico

[cactus]

Update

My friend just sent me a link to http://www.sysresccd.org/Main_Page

I'm going to disconnect my Windows HDD's, install SME on another drive, daisy chain the bad hdd with the fresh SME HDD and attempt to boot to this CD to try to retrieve the db.

Rico

That does not make sense to me as you already retrieved the database files. You only need a way to restore them, which I outlined in my previous post. No matter how you retrieve the MySQL table files you still have to cross your fingers for them to work on your system as this is not the way to save and restore MySQL data.

[rico]

First off.... I'm new to MySQL, my co-admin set it up originally, who for the life of me I can not get a hold of. So everything I should have done, was not done, because I didn't know any better.

Second, your instructions are foreign to me as I do not know tit for tat.

Third, For the life of me I can not GRANT access to my user. I ran into this issue 2 years ago, and for the life of me I do not remember how we got it done.

(example not actual user or pass)
#GRANT ALL ON *.* TO 'user2'@'localhost' IDENTIFIED BY 'pass1'

When I go to phpmyadmin it tells me grant NO

I've used SME for 10 years now, and I'm beginning to think of using another solution, just because of the complications with MySQL,lack of PHP5 and MySQL5,  and that 75 character root password.

Prove me wrong, help me, don't scold me!

[cactus]

First off.... I'm new to MySQL, my co-admin set it up originally, who for the life of me I can not get a hold of. So everything I should have done, was not done, because I didn't know any better.

Second, your instructions are foreign to me as I do not know tit for tat.

Then we perhaps need to write them out...

Third, For the life of me I can not GRANT access to my user. I ran into this issue 2 years ago, and for the life of me I do not remember how we got it done.

(example not actual user or pass)
#GRANT ALL ON *.* TO 'user2'@'localhost' IDENTIFIED BY 'pass1'

When I go to phpmyadmin it tells me grant NO

Not sure what you are doing wrong there, but have a look here: http://wiki.contribs.org/MySQL

I've used SME for 10 years now, and I'm beginning to think of using another solution, just because of the complications with MySQL

I think MySQL works perfectly on SME Server.

lack of PHP5 and MySQL5

I can imagine that.

and that 75 character root password.

Which you do not need at all, so why are you bothered by that? If you login as root user to the SME Server command line you should be able to access MySQL simply by typing

Code: [Select]

mysql.
If you are using the root password because you think your applications need access to the database you are doing very dangerous things as the root user is a super user (for MySQL as well in this case) and has far more rights than any application should have. If you create the proper user, with the proper privileges on the proper database/tables things should work out.

If you already have 10 years of experience with SME Server I do not understand why you have not posted you issues in the forums/bugtracker as I think above problems could have been easily resolved.

Prove me wrong, help me, don't scold me!

I will if you let me:

Please follow these steps and if something does not work (as expected) stop. Post back here and do not wander of on your own in the forest of SME Server and MySQL, you might not see the forrest for the trees.

1. Setup a test box with SME Server, I am serious do not try this on your production machine as it might brake stuff.
2. Log in as root
3. Verify that you can login as root user into mysql without any password or username with the following command:

Code: [Select]

mysqlAbove command should get you on the mysql prompt.
4. Type

Code: [Select]

quit to return to the SME Server shell.

5. Now it is time to somehow retrieve the data form your backup disk and place it somewhere on the MySQL server, I suggest somewhere in a separate folder in /tmp, I assume you use /tmp/restore for a minute.

6. To stop mysql type

Code: [Select]

service mysqld stop7. Verify that MySQL is not available anymore by issuing the following command:

Code: [Select]

mysqlAbove should return something like this:

Code: [Select]

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)8. Now select one database that is not on the system, not being the MySQL database from /tmp/restore and copy the folder to /var/lib/mysql/
9. Check that it is there:

Code: [Select]

ls -la /var/lib/mysqlWhich should look more or less like this:

Code: [Select]

total 20
drwxr-xr-x   5 mysql mysql 4096 Feb  1 18:56 .
drwxr-xr-x  20 root  root  4096 Feb  1 16:56 ..
drwx------   2 mysql mysql 4096 Feb  1 16:58 horde
drwx------   2 mysql mysql 4096 Feb  1 18:40 mysql
drwx------   2 mysql mysql 4096 Feb  1 16:58 test
You might have some extra databases there as well as the one you restored.
10. Changes are very likely that ownership is not mysql mysql like above, wo we need to fix that by doing:

Code: [Select]

chown -R mysql:mysql /var/lib/mysql/database where you should replace database with the name of the database you restored.
11. Now we should be ready to restart mysql:

Code: [Select]

service mysqld startAbove should output something like this:

Code: [Select]

Starting mysqld:                                           [  OK  ]12. If MySQL started succesfully we can try and login to mysql again:

Code: [Select]

mysql13. Try and see if you can see the database you restored by issuing the following command on the MySQL prompt:

Code: [Select]

SHOW DATABASES;It should list the same databases as you have seen in step 9.
14. Quit MySQL again by typing:

Code: [Select]

quit15. Now we need to check the integrity of the databases and tables you just restored:

Code: [Select]

mysqlcheck --all-databasesIf all is well this will print out a list of all databases and tables with the result of the check, hope for the best all should have OK behind them if not please post the output in this thread.

If restore shows all tables OK you can make a dump file of the restored database which you can install on your production box:
16. Make a dump of the restired database like this

Code: [Select]

mysqldump --opt --database databasename > /tmp/database.sql17. Somehow transfer the /tmp/database.sql file to your production box.
18. Login on your production server as root.
19. Restore the database with the following command:

Code: [Select]

mysql < /path/to/database.sql20. Login to mysql

Code: [Select]

mysql21. Check that the restored database is available:

Code: [Select]

SHOW DATABASES;
Once again, if you encounter any error in this process, please stop and post back here.

[rico]

Not sure what you are doing wrong there, but have a look here: http://wiki.contribs.org/MySQL

I followed those directions like 500 times yesterday

In vbulletin the forums/includes/config.php file asks for the user name and password to db 'forums'

I keep getting an access denied error and read only error.

Therefore I was trying to give the user name grant access.

Anyway, yesterday I raided the first drive in the original state before I made any changes. I then took the 2nd drive out. So in essence the first drive was the test box.

Also while (not not knowing what I was doing) I did actually get some part of phpmyadmin to work. I exported the tables to the default location but I have no clue where that was at and now i can't see them.

At this point I'd like too wipe out the 1st hdd. Install the 2nd drive as the master, raid the 1st drive as #2, and start all over.
Again leaving the newly raided drive as a backup. As my other sites are not MySQL dependent, and I have them backed up, I can use this box as a test box.

What do you think?

[Stefano]

What do you think?

I think you should follow Cactus' guide, step by step and stop if something is wrong.

IMO, if you wish to restore your data, you should read and do what you are told to do..
if you prefer go on your way, you will be alone..

my 2c
good luck

ciao
Stefano

[cactus]

What do you think?

What do you think would be the reason I wrote such an extensive step-by-step instruction?

I will if you let me:

I hope I do not need to repeat myself, you ignorance and lack of following advice is eating up my patience with you.

Prove me wrong, help me, don't scold me!

I tried but it seems you are not willing to follow up my advice.

If you choose to do other things or try other solutions fine by me, but don't come here complaining that SME Server does not work or that you are not helped, I have tried, the ball is in your court.

If you pick it up I am fine helping you out, but if you desire to mess up things even more than do not come to me for help.

[rico]

Oh no! I didn't mean to imply I was going to do something else.
I'm sorry, if I gave you the wrong impression.

I plan on doing exactly what you told me to do. I just meant preparations before I follow your instructions. So I got something to go back to in case I screw something up. (Back up)

Thanks for your help. I'll give you an update as soon as I finish




 

[rico]

OK it took me a while to figure out how to see the mysqlcheck output, it was going by too fast.

Had to use the following mysqlcheck forum > check.txt

Then copied check.txt to ftp folder and opened it up from laptop.

Anywho,

2 were not OK

forum.vb_cpsession
note     : The storage engine for the table doesn't support check

forum.vb_session
note     : The storage engine for the table doesn't support check

The rest OK

waiting for instructions as instructed.

[rico]

YOUR INSTRUCTIONS WORKED PROBLEM SOLVED!!!!!!

THANK YOU SO MUCH  FOR YOUR TIME AND PATIENCE!!!!!
(Sorry for yelling but I'm happy and grateful)

Explore2fs.exe is a great tool http://www.chrysocome.net/explore2fs
MSQL Commands http://www.bios.niu.edu/johns/bioinform/mysql_commands.htm

[cactus]

YOUR INSTRUCTIONS WORKED PROBLEM SOLVED!!!!!!

THANK YOU SO MUCH  FOR YOUR TIME AND PATIENCE!!!!!
(Sorry for yelling but I'm happy and grateful)

Good to know that you were able to solve your problems by following my instructions. I suggest you spend you positive energy on finding a proper backup solution so you do not need these procedures in the future.
Here are some suggestions: http://wiki.contribs.org/Category:Backup