Re: Recover Database after a system crash
Posted by: kuku chipo ()
Date: April 22, 2009 04:41AM

Ok, hope this helps.... I got into a similar situation 2days ago, havent tried this yet but hope it works for you.
Got it from (http://kim.biyn.com/Linux/recover_and_mount_mysql_database_files_after_recovery)

The solution: Mounting a MySQL Live Filesystem Backup

1. Make sure that you have a new MySQL installation you can blow away. If you have already created a new database you need to save, create a dump file so you can import it. DO IT NOW, because we will be deleting your current MySQL installation.
2. Locate the directory where your MySQL data files are located. If you do not know where they are check my.cnf. In our example we are running Suse 9.3, and Suse locates the data files in /var/lib/mysql/
3. Switch to the MySQL directory and see which user and group the MySQL files belong to

kimp4:/var/lib # cd /var/lib/mysql
total 20507
drwxr-xr-x 4 mysql mysql 248 2005-07-03 17:18 .
drwxr-xr-x 40 root root 1064 2005-07-02 14:38 ..
-rw-rw---- 1 mysql mysql 10485760 2005-07-03 17:18 ibdata1
-rw-rw---- 1 mysql mysql 5242880 2005-07-03 17:18 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 2005-07-03 17:18 ib_logfile1
drwx------ 2 mysql mysql 1584 2005-07-03 17:18 mysql
-rw-rw---- 1 mysql mysql 1279 2005-07-03 17:18 mysqld.log
drwx------ 2 mysql mysql 48 2005-07-03 17:18 test
-rw-r--r-- 1 mysql mysql 0 2005-07-03 17:18 update-stamp-4.1

As you can see from our example, the files belong to mysql:mysql.
4. Delete the data files

cd /var/lib/mysql/
rm -r ./*
5. Instruct MySQL to install a new empty DBMS schema then start mysqld

kimp4:/etc # mysql_install_db
Installing all prepared tables
Fill help tables
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h kimp4.biyn.local password 'new-password'
See the manual for more instructions.
NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!
You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
kimp4:/etc # /etc/init.d/mysql start
6. Stop the MySQL daemon again

kimp4:/etc # /etc/init.d/mysql stop
Shutting down service mysql done
7. Copy the files from your backup, overwriting your newly-initialized MySQL installation

cd /var/lib/mysql
kimp4:/var/lib/mysql # cp -R /mnt/bigDisk/june-19/mysql/* .
8. Next, assign recursively ownership to the user:group you took note of above.

cd /var/lib/mysql
kimp4:/var/lib/mysql# chown -R mysql:mysql *
9. Now start the MySQL daemon

kimp4:/var/lib/mysql # /etc/init.d/mysql start
Starting service MySQL done
10. Now you should be able to log into your MySQL environment using your old username/passwords. You should now import any SQL/DDL dump files you may have created in the first step.

See? I TOLD you it was easy - easier than you thought!
Note to Experienced SysAdmins

I do realize I have a few extra steps in here but this is to ensure that you're working with a known quantity. I've done this several times now and know with absolute certainty that this method works. If you're a hardened sysadmin just skip the extra steps. I am trying to make this article so that it will work even for novices on various distributions. Again: I do know that it will work with several of the steps eliminated, but I want to ensure that novices are working with a known-clean environment. Thanks for understanding that many readers looking for MySQL tips are not so experienced, and not sending me nasty emails! ;)