Restoring MySQL database from physical files

Question

Is it possible to restore a MySQL database from the physical database files  I have a directory that has the following file types   client frm client MYD client MYI    but for about 20 more tables   I usually use mysqldump or a similar tool to get everything in 1 SQL file so what is the way to deal with these types of files

User · Accepted Answer

A MySQL MyISAM table is the combination of three files:

The FRM file is the table definition.
The MYD file is where the actual data is stored.
The MYI file is where the indexes created on the table are stored.

You should be able to restore by copying them in your database folder (In linux, the default location is /var/lib/mysql/)

You should do it while the server is not running.

User · Answer

With MySql 5 1  Win7   To recreate DBs  InnoDbs  I ve replaced all contents of following dirs  my ini params    datadir  C  ProgramData MySQL MySQL Server 5 1 Data   innodb data home dir  C  MySQL Datafiles     After that I started MySql Service and all works fine

User · Answer

In my case  simply removing the tc log in  var lib mysql was enough to start mariadb mysql again

User · Answer

Yes it is  Just add them to your database-folder   depending on the OS   and run a command such as  MySQL Fix Permissions   This re-stored the database  See too it that the correct permissions are set on the files aswell

User · Answer

Yes it is  Just add them to your database-folder   depending on the OS   and run a command such as  MySQL Fix Permissions   This re-stored the database  See too it that the correct permissions are set on the files aswell

User · Answer

In my case  simply removing the tc log in  var lib mysql was enough to start mariadb mysql again

User · Answer

I once copied these files to the database storage folder for a mysql database which was working  started the db and waited for it to  repair  the files  then extracted them with mysqldump

User · Answer

Yes it is  Just add them to your database-folder   depending on the OS   and run a command such as  MySQL Fix Permissions   This re-stored the database  See too it that the correct permissions are set on the files aswell

User · Answer

If you are restoring the folder don t forget to chown the files to mysql mysql chown -R mysql mysql  var lib mysql-data otherwise you will get errors when trying to drop a database or add new column etc    and restart MySQL  service mysql restart

User · Answer

From the answer of  Vicent  I already restore MySQL database as below   Step 1  Shutdown Mysql server  Step 2  Copy database in your database folder  in linux  the default location is  var lib mysql   Keep same name of the database  and same name of database in mysql mode   sudo cp -rf    mnt ubuntu 426 var lib mysql database1  var lib mysql    Step 3   Change own and change mode the folder   sudo chown -R mysql mysql  var lib mysql database1 sudo chmod -R 660  var lib mysql database1 sudo chown  mysql mysql  var lib mysql database1  sudo chmod 700  var lib mysql database1   Step 4  Copy ibdata1 in your database folder  sudo cp  mnt ubuntu 426 var lib mysql ibdata1  var lib mysql   sudo chown mysql mysql  var lib mysql ibdata1   Step 5  copy  ib logfile0 and  ib logfile1 files in your database folder   sudo cp  mnt ubuntu 426 var lib mysql ib logfile0  var lib mysql   sudo cp  mnt ubuntu 426 var lib mysql ib logfile1  var lib mysql    Remember change own and change root of those files   sudo chown -R mysql mysql  var lib mysql ib logfile0  sudo chown -R mysql mysql  var lib mysql ib logfile1   or  sudo chown -R mysql mysql  var lib mysql   Step 6  Optional   My site has configuration to store files in a specific location  then I copy those to corresponding location  exactly   Step 7  Start your Mysql server  Everything come back and enjoy it   That is it   See more info at  https   biolinh wordpress com 2017 04 01 restoring-mysql-database-from-physical-files-debianubuntu

User · Answer

From the answer of  Vicent  I already restore MySQL database as below   Step 1  Shutdown Mysql server  Step 2  Copy database in your database folder  in linux  the default location is  var lib mysql   Keep same name of the database  and same name of database in mysql mode   sudo cp -rf    mnt ubuntu 426 var lib mysql database1  var lib mysql    Step 3   Change own and change mode the folder   sudo chown -R mysql mysql  var lib mysql database1 sudo chmod -R 660  var lib mysql database1 sudo chown  mysql mysql  var lib mysql database1  sudo chmod 700  var lib mysql database1   Step 4  Copy ibdata1 in your database folder  sudo cp  mnt ubuntu 426 var lib mysql ibdata1  var lib mysql   sudo chown mysql mysql  var lib mysql ibdata1   Step 5  copy  ib logfile0 and  ib logfile1 files in your database folder   sudo cp  mnt ubuntu 426 var lib mysql ib logfile0  var lib mysql   sudo cp  mnt ubuntu 426 var lib mysql ib logfile1  var lib mysql    Remember change own and change root of those files   sudo chown -R mysql mysql  var lib mysql ib logfile0  sudo chown -R mysql mysql  var lib mysql ib logfile1   or  sudo chown -R mysql mysql  var lib mysql   Step 6  Optional   My site has configuration to store files in a specific location  then I copy those to corresponding location  exactly   Step 7  Start your Mysql server  Everything come back and enjoy it   That is it   See more info at  https   biolinh wordpress com 2017 04 01 restoring-mysql-database-from-physical-files-debianubuntu

User · Answer

With MySql 5 1  Win7   To recreate DBs  InnoDbs  I ve replaced all contents of following dirs  my ini params    datadir  C  ProgramData MySQL MySQL Server 5 1 Data   innodb data home dir  C  MySQL Datafiles     After that I started MySql Service and all works fine

User · Answer

I once copied these files to the database storage folder for a mysql database which was working  started the db and waited for it to  repair  the files  then extracted them with mysqldump

User · Answer

Yes it is  Just add them to your database-folder   depending on the OS   and run a command such as  MySQL Fix Permissions   This re-stored the database  See too it that the correct permissions are set on the files aswell

User · Answer

If you are restoring the folder don t forget to chown the files to mysql mysql chown -R mysql mysql  var lib mysql-data otherwise you will get errors when trying to drop a database or add new column etc    and restart MySQL  service mysql restart

User · Answer

I have the same problem but was not able to successfully recover the database  based on the instructions above    I was only able to recover mysql database folders from my Ubuntu OS  My problem is how to recover my database with those unreadable mysql data folders  So I switched back to win7 OS for development environment    NOTE I have an existing database server running in win7 and I only need few database files to retrieve from the recovered files  To successfully recover the database files from Ubuntu OS I need to freshly install mysql database server  same version from Ubuntu OS in my win7 OS  to recover everything in that old database server    Make another new mysql database server same version from the recovered files  Stop the mysql server copy the recovered folder and paste in the  C  ProgramData MySQL MySQL Server 5 5 data  mysql database is stored  copy the ibdata1 file located in linux mysql installed folder and paste it in  C  ProgramData MySQL MySQL Server 5 5 data   Just overwrite    the existing or make backup before replacing  start the mysql server and check if you have successfully recovered the database files  To use the recovered database in my currently used mysql server simply export the recovered database and import it my existing mysql server    Hope these will help  because nothing else worked for me

User · Answer

I once copied these files to the database storage folder for a mysql database which was working  started the db and waited for it to  repair  the files  then extracted them with mysqldump

User · Answer

I have the same problem but was not able to successfully recover the database  based on the instructions above    I was only able to recover mysql database folders from my Ubuntu OS  My problem is how to recover my database with those unreadable mysql data folders  So I switched back to win7 OS for development environment    NOTE I have an existing database server running in win7 and I only need few database files to retrieve from the recovered files  To successfully recover the database files from Ubuntu OS I need to freshly install mysql database server  same version from Ubuntu OS in my win7 OS  to recover everything in that old database server    Make another new mysql database server same version from the recovered files  Stop the mysql server copy the recovered folder and paste in the  C  ProgramData MySQL MySQL Server 5 5 data  mysql database is stored  copy the ibdata1 file located in linux mysql installed folder and paste it in  C  ProgramData MySQL MySQL Server 5 5 data   Just overwrite    the existing or make backup before replacing  start the mysql server and check if you have successfully recovered the database files  To use the recovered database in my currently used mysql server simply export the recovered database and import it my existing mysql server    Hope these will help  because nothing else worked for me

User · Answer

I once copied these files to the database storage folder for a mysql database which was working  started the db and waited for it to  repair  the files  then extracted them with mysqldump

User · Answer

I ran into this trying to revive an accidentally deleted Docker Container  oraclelinux s MySQL  from a luckily-not-removed docker volume that had the DB data in physical files  So  all I wanted to do was to turn the data from physical files into a  sql importable file to recreate the container with the DB and the data  I tried biolin s solution  but ran into some  InnoDB  Multiple files found for the same tablespace ID errors  after restart   I realized that doing open hurt surgery on certain folders files there is quite trickey  The solution that worked for me was temporarily changing the datadir  in my cnf to the available folder and restarting the MySQL server   It did the job perfectly

User · Answer

I ran into this trying to revive an accidentally deleted Docker Container  oraclelinux s MySQL  from a luckily-not-removed docker volume that had the DB data in physical files  So  all I wanted to do was to turn the data from physical files into a  sql importable file to recreate the container with the DB and the data  I tried biolin s solution  but ran into some  InnoDB  Multiple files found for the same tablespace ID errors  after restart   I realized that doing open hurt surgery on certain folders files there is quite trickey  The solution that worked for me was temporarily changing the datadir  in my cnf to the available folder and restarting the MySQL server   It did the job perfectly

[mysql] Restoring MySQL database from physical files

Examples related to mysql

Examples related to backup

Examples related to restore