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
mysqldumpto 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 methodBack 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
db_folderare 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,
ibdata1is a file created to store all your database data when you use innodb as MySQL engine.
Back-UP to SQL FileLet'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.
- Stop your WAMP server and locate the directory where you installed WAMP
- Browse to this directory:
- Back-up the
datadirectory and create a new folder with the same name.
- Copy all the contents of your back-up SQL data to the new data folder.
- 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.