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:
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.
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!
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 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.
