Restoring MySQL database from physical files

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?

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.

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/

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.

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

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.

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.

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!

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.

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

The icon remained orange with empty error log, until I accidentally discovered I had to replace/update names in my.cnf file from the old directory name wamp64 to wamp in the new PC.

From the answer of Biolinh

After doing the detailed procedures, I got the following error:

mysqlcheck: Got error: 2013: Lost connection to server during query when executing 'REPAIR TABLE ... '

Then I also copied the /var/lib/mysql/mysql directory from the backup and ran the command:

mysql_secure_installation

After that all works fine.