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?

I have the same issue: multiple files with extensions FRM, MYD and MYI. I also have files ib_logfile0, ib_logfile1 and ibdata1. I cannot access a running server or create a dump. I tried running a new MySQL server and use the files, but I didn't suceed... Does anyone have a clear how-to? — flo5783, Mar 05 '19 at 19:40

score 145 · Accepted Answer · edited Jul 25 '15 at 20:24

145

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.

edited Jul 25 '15 at 20:24

Benoit Duffez

11,839
12
77
125

answered Jan 27 '09 at 19:09

Vincent

22,366
18
58
61

69

this is true only for MyISAM tables. InnoDB stores its tables and indexes in a single tablespace *, which by default consist of the 3 files ibdata1, ib_logfile0, and ib_logfile1. for restoring a database, you would also need those files. * per-table tablespaces are possible, but not default – ax. Aug 21 '09 at 18:18
23

He says he have .frm .myi and .myd files. I then assumed it was MyISAM tables. – Vincent Aug 24 '09 at 16:31
@ax is it possible to get data from InnoDB without the ibdata? I have a months old backup, and only the current ib_logfile* and .frm files. – eglasius Sep 03 '10 at 21:16
@ax you added info to make this question more general, I did copied the frm etc files, but I didn't copy the ibdata1 etc, which made me search this question - so I am really thankful to you for mentioning that – Oriesok Vlassky Jul 24 '12 at 08:05
7

Valuable answer, but I had to go one step further for things to work fine: since I had to log in as root to restore the files, the `mysql` user running the `mysqld` process couldn't access them. Doing a `chmod -R mysql:mysql .` on the mysql data directory is quick and easy, but figuring out, before that, why all my resoterd DBs seemed to have no tables took a bit longer. – Edurne Pascual Sep 26 '12 at 15:41
I have the same problem as the previous comment. Mysql shows the tables of the pasted database when I run `show tables;` but it returns `ERROR 1146 (42S02): Table 'mydb.myrnd_categories' doesn't exist` error when I run `select` query. – orezvani Sep 03 '13 at 11:39
10

herenvardo I think you meant chown not chmod – Oliver M Grech Jan 13 '16 at 12:10
1

You can do it while the server is running (at least if those tables do not already exist). The new tables will appear almost immediately (again, noting that it only works with MyISAM, and you have to have the correct permissions on the files) – andrew lorien Jun 28 '16 at 08:13
3

To recap some of the important notes, you may have to `chown` the copied files like so: `sudo chown -R mysql:mysql /var/lib/mysql` – Jacksonkr Dec 20 '16 at 18:30
1

as described in the answer posted by @Filip Ekberg, you should restore permissions. In my case after I restored databases from mysql v5.5 to a mysql v5.7 instance, running `mysqlcheck --all-databases -uUSERNAME -pPASSWORD -r` seemed to convert fields from the old tables to new v 5.7 format, plus displaying several warnings due to various inconsistencies. – Alex P. May 24 '17 at 13:10
"You should do it while the server is not running" - what are the possible dangers if the server *is* running? – Mawg says reinstate Monica Jan 10 '18 at 09:58

score 70 · Answer 2 · edited Apr 19 '18 at 08:43

70

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/

edited Apr 19 '18 at 08:43

George Dimitriadis

1,681
1
18
27

answered Apr 03 '17 at 02:46

biolinh

2,175
1
24
23

4

+1 for detailed step by step instructions. But I edited some statements to truly reflect what they should be. – Peter Jul 24 '17 at 15:42
1

Got a #1932 Error, unable to access data. The databases appear, but the data is not accessible. Running XAMPP 7.2.12 on MAC OS X Mojave. – Steve1754a Dec 09 '18 at 16:16
@Steve1754a the error can from many reasons 1. Do you move a database from Mac OS to Mac OS? 2. Please check the permission of those files/ data/ table – biolinh Dec 19 '18 at 04:17
Answers: 1. Yes; 2. I did. I had to undertake a complete rebuild of my DB. Could not figure out a solution. Moral to the story: always create a backup of the DB. I wish there were an easy way to do that automatically in XAMPP. – Steve1754a Dec 20 '18 at 12:02
I oriented myself at the other file permissions from my fresh mysql install so I changed the files in the database folder to 640 (`sudo chmod -R 640 /var/lib/mysql/database1`) and the folder itself to 750 (`sudo chmod 750 /var/lib/mysql/database1`). – mhellmeier Mar 28 '19 at 15:27
After doing this, I get the error: `ERROR! The server quit without updating PID file` – Nick Manning Aug 26 '19 at 04:59
Hi @NickManning, Which step u get the error? can u start mýsql server? PID is only relate to process of program not db. – biolinh Sep 01 '19 at 07:36
This worked beautifully for me but does anyone have any tips on restoring users and user privileges? – qotsa42 Jan 09 '20 at 06:11
I get `Job for mysql.service failed` when I restart the server. – crash springfield Apr 16 '20 at 22:45
1

Actually copying (zip / unzip) the whole `var/lib/mysql` folder worked better for me. And for simplicity used: `sudo chown -R mysql:mysql /var/lib/mysql` and `sudo chmod -R 760 /var/lib/mysql/` – User Rebo Oct 29 '20 at 19:24

score 12 · Answer 3 · edited Nov 08 '19 at 04:57

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.

method also works on ubuntu/debian with ibdata1, ib_logfile0, and ib_logfile1 files and database folder. user won't be copied, but then you could add a new user to the database and dump it with that. — bokorben, Jul 26 '16 at 23:31

score 10 · Answer 4 · edited Jan 24 '17 at 23:00

10

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

edited Jan 24 '17 at 23:00

Bruno Ribeiro

1,280
16
21

answered Jun 29 '14 at 12:41

Joel Davey

2,363
1
21
20

2

this isn't really an answer to the question but was very helpful. – ryantuck Jan 11 '16 at 20:27
actualy you should not chown everything to mysql, the mysql database folder should retain root group – Galvani Aug 27 '16 at 17:43

score 2 · Answer 5 · answered Aug 20 '20 at 12:06

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!

score 2 · Answer 6 · edited Oct 29 '12 at 03:37

2

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.

edited Oct 29 '12 at 03:37

andrewsi

10,807
132
35
51

answered Mar 01 '12 at 11:19

sergey.olifirenko

71
2

score 1 · Answer 7 · answered Jan 27 '09 at 19:05

1

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.

answered Jan 27 '09 at 19:05

Filip Ekberg

36,033
20
126
183

I did it, but I it does not recognizes the tables. As I said in my comment on previous answer. – orezvani Sep 03 '13 at 11:42

score 0 · Answer 8 · answered Jan 27 '09 at 19:07

0

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.

answered Jan 27 '09 at 19:07

Spikolynn

4,067
2
37
44

score 0 · Answer 9 · answered Feb 08 '19 at 15:19

0

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

answered Feb 08 '19 at 15:19

SirJohnFranklin

57
4

this is not an issue of being able to start/restart mysql server. – Oluwatumbi Dec 18 '19 at 10:25

score 0 · Answer 10 · answered Mar 07 '21 at 00:29

0

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.

answered Mar 07 '21 at 00:29

Matteus Barbosa

2,409
20
21

score 0 · Answer 11 · answered Feb 27 '22 at 00:50

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.

Restoring MySQL database from physical files

11 Answers11

Linked

Related