Back-up and Restoring Database Files When MySQL-Server Stops Running

It has been a week since I encountered a weird issue on my Ubuntu machine. All of sudden, my MySQL server stops running after I restarted it and whatever I type it won't even start.

Worst case, I don't even have the latest SQL file of the database. I also can't use mysqldump to retrieve the recent SQL since the server is down.

There I was, left with no other solution but to try re-installing the SQL-server and that means I need to remove all my previous MySQL data which means I need to look for a way to back-up them.

So what could be done to retrieve those information without extracting it to a SQL file?

Back-up the .FRM files first!

NOTE: Make sure that you have root access for this method

Back up the directory /var/lib/mysql. I actually transferred it to another machine to be sure.

This folder may have the following files:

.
..
ibdata1
ib_logfileX
db_folder1
db_folder2
db_folder3

Where X is a number starting from 0, and db_folder are folders for every database you have. This folders have the .FRM files that you'll be needing.

FRM (FoRMat) files are used by MySQL for formatting. This is used to define the table format used on MySQL. The files you may see inside the directory that was stated previously may have the same filename as the tables you have used and this files are created every time a table is created in MySQL.

On the other hand, ibdata1 is a file created to store all your database data when you use innodb as MySQL engine.

Back-UP to SQL File

Let's just say that you have already back-up your MySQL folder and you want to transfer it to another system. Well, I think it's better to convert this to SQL files rather than keeping it as .FRM files.

In my case I used a local WAMP application to convert it back to SQL. This are the steps:

The steps assumed that you have already installed a WAMP server locally. Example also uses a Windows machine.

1. Stop your WAMP server and locate the directory where you installed WAMP
2. Browse to this directory: [WAMP_DIR]/bin/mysql/mysql[VERSION HERE]
3. Back-up the data directory and create a new folder with the same name.
4. Copy all the contents of your back-up SQL data to the new data folder.
5. Run the server and the SQL server and now you can see the old database. With this you can perform the usual mysqldump back-up or you can access the phpmyadmin that comes with it.