Restoring MySQL database from physical files

Solution 1:

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.

Solution 2:

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/

Solution 3:

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.

1. Make another new mysql database server same version from the recovered files.

2. Stop the mysql server

3. copy the recovered folder and paste in the (C:\ProgramData\MySQL\MySQL Server 5.5\data) mysql database is stored.

4. 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.

5. start the mysql server and check if you have successfully recovered the database files.

6. 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.

Solution 4:

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