Tuesday, 24 April 2012

Recovering MySQL Database without using MySQL

This post will tell you how to recover your databases without using MySQL or when you are not able to access MySQL. I had to do this when while experimenting I ruined my Ubuntu. I had to recover data from it. So I used Ubuntu Live Cd to excess the ubuntu ext4 drive to backup the data. From live cd we cannot access MySQL but I had to backup my database. So I just copied my database folder from /var/lib/mysql. After installing Ubuntu and MySQL and other softwares all over again I thought of just replacing database folder with the one which I copied but that didn't worked.

Recently I had used dpkg-reconfigure to solve some password problem. So I tried using that and my database was working just fine after that.
Now How to do this in detail :P
You will find folder for each of your database in /var/lib/mysq. You need root permissions to view files of this folder. You can do this by executing the following command in the terminal:

$ sudo nautilus

Then navigating to /var/lib/mysql. In this folder you will find one folder for each of your databases and other mysql files. I copied whole of the folder and kept at some safe place. Now after reinstalling Ubuntu and Lamp-server, I had to get my databases back. So I just made the same database in mysql so that a folder in /var/lib/mysql gets created. I did this because I thought mysql has other places where it saves information such as current databases and all. So by doing this thing, mysql will do all of its stuff and update all of its settings. Now just stop mysql services using:

$ sudo service mysql stop

After that I copied my database folder from backup mysql folder to the current mysql folder that is in /var/lib/mysql
You will be needing super user privileges for this. 
Don't think that the work is finished mysql will not work after this. You can check this by opening it in phpmyadmin or through mysql console.It will give some error. Now you have to reconfigure mysql package to make it work. Execute the following command in the terminal.

$ dpkg-reconfigure mysql-server-5.4

After executing this command it will reconfigure whole of the mysql server and will ask for passwords and all. Enter your password for mysql and you are done. Mysql service will automatically start after this. Just check your database after this using phpmyadmin or mysql console. In my case it was working just fine and hoping the same from your side.