Restore the mysql database from frm files

Question

I have dumped all my tables everyweek to got the backup  But later I understand that it is only storing the  frm file of the table  It is not showing  MYD and  MYI files of a table  So I have only my  frm file of the database with me and also mydatabase is innodb  So can I get my database with data in the database

User · Answer

I just copy pasted the database folders to data folder in MySQL  i e  If you have a database called alto then find the folder alto in your MySQL -  Data folder in your backup and copy the entire alto folder and past it to newly installed MySQL -  data folder  restart the MySQL and this works perfect

User · Answer

After much trial and error I was able to get this working based on user359187 answer and this blog post   To get my old  frm and  ibd transferred to a new MySQL database after copying the files over and assigning MySQL ownership  the key for me was to then log into MySQL and connect to the new database then let MySQL do the work by importing the tablespace   mysql gt  connect test  mysql gt  ALTER TABLE t1 IMPORT TABLESPACE    This will import the data using the copied  frm and  ibd files   I had to run the Alter command for each table separately but this worked and I was able to recover the tables and data

User · Answer

Just might be useful for someone   I could only recover frm files after a disaster  at least I could get the table structure from FRM files by doing the following   1- create some dummy tables with at least one column and SAME NAME with frm files in a new mysql database   2-stop mysql service  3- copy and paste the old frm files to newly created table s frm files  it should ask you if you want to overwrite or not for each  replace all   4-start mysql service  and you have your table structure     regards  anybudy

User · Answer

Copy all file and replace to  var lib mysql    after that you must change owner of files to mysql  this is so important if mariadb service restart has been faild     chown -R mysql mysql  var lib mysql     and      chmod -R 700  var lib mysql

User · Answer

I answered this question here  as well  https   dba stackexchange com a 42932 24122  I recently experienced this same issue  I m on a Mac and so I used MAMP in order to restore the Database to a point where I could export it in a MySQL dump   You can read the full blog post about it here  http   www quora com Jordan-Ryan Web-Dev How-to-Recover-innoDB-MySQL-files-using-MAMP-on-a-Mac  You must have   -ibdata1   -ib logfile0   -ib logfile1   - FRM files from your mysql database folder   -Fresh installation of MAMP   MAMP Pro that you are willing to destroy  if need be    SSH into your web server  dev  production  no difference  and browse to your mysql folder  mine was at  var lib mysql for a Plesk installation on Linux  Compress the mysql folder Download an archive of mysql folder which should contain all mySQL databases  whether MyISAM or innoDB  you can scp this file  or move this to a downloadable directory  if need be  Install MAMP  Mac  Apache  MySQL  PHP  Browse to  Applications MAMP db mysql  Backup  Applications MAMP db mysql to a zip archive  just in case  Copy in all folders and files included in the archive of the mysql folder from the production server  mt Plesk environment in my case  EXCEPT DO NOT OVERWRITE   - Applications MAMP db mysql mysql   - Applications MAMP db mysql mysql upgrade info  - Applications MAMP db mysql performance schema And voila  you now should be able to access the databases from phpMyAdmin  what a relief    But we re not done  you now need to perform a mysqldump in order to restore these files to your production environment  and the phpmyadmin interface times out for large databases  Follow the steps here    http   nickhardeman com 308 export-import-large-database-using-mamp-with-terminal    Copied below for reference  Note that on a default MAMP installation  the password is  root    How to run mysqldump for MAMP using Terminal  EXPORT DATABASE FROM MAMP 1   Step One  Open a new terminal window  Step Two  Navigate to the MAMP install by entering the following line in terminal     cd  applications MAMP library bin Hit the enter key  Step Three  Write the dump command       mysqldump -u  USERNAME  -p  DATA BASENAME     PATH TO FILE  Hit the enter key  Example     mysqldump -u root -p wp database  gt   Applications MAMP htdocs symposium10 wp wp db onezero sql   Quick tip  to navigate to a folder quickly you can drag the folder into the terminal window and it will write the location of the folder  It was a great day when someone showed me this   Step Four  This line of text should appear after you hit enter Enter password  So guess what  type your password  keep in mind that the letters will not appear  but they are there Hit the enter key  Step Five  Check the location of where you stored your file  if it is there  SUCCESS Now you can import the database  which will be outlined next   Now that you have an export of your mysql database you can import it on the production environment

User · Answer

Yes this is possible  It is not enough you just copy the  frm files to the to the databse folder but you also need to copy the ib logfiles and ibdata file into your data folder  I have just copy the  frm files and copy those files and just restart the server and my database is restored   After copying the above files execute the following command -      sudo chown -R mysql mysql  var lib mysql   The above command will change the file owner under mysql and it s folder to MySql user  Which is important for mysql to read the  frm and ibdata files

User · Answer

Yes  It is possible  Long approach but you can get all the data s using just  frm files  Of course you need other files in the mysql data directory    My Problem  One day my harddisk crashed and got the booting blue screen error  I try connecting with multiple machine and it didn t work  Since it is a booting error i was concered about the files  and i tryed with the secondary harddisk and try to recover the folders and files  I also backed up the full xampp folder c  xampp just in case   Because I had no back of the recent databases i got really worried how to retrieve the database  we have lot of clients project management and personal doc in the database   None of the method listed on the stackoverflow comment works  at-least for me   It took me 2 full days googling for the answer to get the data s from the  frm files  Came across multiple approaches from many people but everything was frustration and getting some error or another when implementing  If most of them get it working  based on their comment  then what am i missing    Because i was so desperate I even reinstall windows which result in loosing all my softwares and tried again  But still the same error  THANKS to Dustin Davis  i found the solution in his blog and i managed to get it working exactly the same way he did  Let me give the credit to this guy  Dustin Davis  https   dustindavis me restoring-mysql-innodb-files-on-windows    You could jump from here to his blog and try his method  pretty clear and easy to follow   But there are something i discovered when trying his approach which he hasn t explained in his blog and i will try my best to explain how i did and what you need to look for   Follow this exactly  IMPORTANT  Make sure you to install the same version of XAMPP  You cannot copy paste from older XAMPP to a new version  This will result in   config or   tracking errors   How to check your XAMPP version   Go to your xampp folder  your backed up xampp   Open the readme en txt file  which is in the root directory of the xampp  You should see the version on top         ApacheFriends XAMPP Version X X XX          Files require to restore  xampp old folder  mysql data   ibdata1 ib logfile0 ib logfile1  lt databasename gt    frm  lt databasename gt    ibd   Step 1   After installed the same version of xampp  Do not start the apache or myql   Step 2   Go to the mysql data folder and replace the ibdata1  ib logfile0  and ib logfile1 Now copy paste your database folder from your old xampp backup to the newly installed xampp folder c  xampp mysql data  that contain  frm and  ibd files  If you are not sure try with one database    Step 3   Go to c  xampp mysql bin and look for my cn  Open the my cn file and look for  skip-innodb and under that look for the line that says innodb log file size 5M change it to 170M  innodb log file size 170M  This is your log file size and if you are not sure just set it to 170   Step 4   Now open the file mysql start bat  Windows Batch file  that is in the c  xampp  directory  Add    innodb force recovery 6 after the     --console        mysql bin mysqld --defaults-file mysql bin my ini --standalone --console    innodb force recovery 6 if errorlevel 1 goto error goto finish    Step 5   Now Start your Apache and Mysql  Go to your phpmyadmin and check for your database and its tables  if you do not get any errors you are on the right track  Stop the Apache and Mysql and copy paste the rest of the databases

User · Answer

Before starting you should stop the WAMP services  or at least restart the services when prompted to start them   On the old server instance navigate to the MySQL data folder by default this should look something similar to C  wamp bin mysql mysql5 1 53 data  where mysql5 1 53 will be the version number of the previously installed MySQL database   Inside this folder you should see a few files and folders  The folders are the actual MySQL databases  and contain a bunch of  frm files which we will require  You should recognise the folder names as the database names  These folder and all their contents can be copied directly to your MySQL data folder  you can neglect the default databases mysql  performance schema  test   If you started the server now you will see the databases are picked up  however the databases will contain none of the tables which were copied across  In order for the contents of the database to be picked up  back in the data folder you should see a file ibdata1  this is the data file for tables  copy this directly into the data folder  you should already have a file in your new data folder called    ibdata1  so you may wish to rename this to ibdata1 bak before copying across the ibdata1 from the old MySQL data folder   Once this has been done Restart all the WAMP services  You can use PhpMyAdmin to check if your databases have been successfully restored

User · Answer

create a new database with same name  copy the  frm  ibd files into xampp mysql data  databasename    you will need ibdata file as well which is found inside  xampp mysql data  copy the previous ibdata1 file  paste in the paste the file and replace it with the existing ibdata file   caution  you may loose the contents of the database which are newly created in the new ibdata file

User · Answer

I made use of mysqlfrm which is a great tool which generates table creation sql code from  frm files  I was getting this nasty table not found error although tables were being listed  Thus I used this tool to regenerate the tables  In ubuntu you need to install this as   sudo apt install mysql-utilities   then    mysqlfrm --diagnostic mysql db name   gt  db name sql   Create a new database and then you can use    mysql -u username -p  lt  db name sql   However  this will give you the tables but not the data  In my case this was enough

[mysql] Restore the mysql database from .frm files

Examples related to mysql

Examples related to mysqldump