Where does MySQL store database files on Windows and what are the names of the files?

Question

I accidentally formatted my hard drive and re-installed Windows and forgot to backup an important database I had in my MySQL server. I'm trying to salvage files now using some software, but I don't know what to look for.

What is the path that the files are stored in, and what are the files named (what naming convention, or file extension should I look for)?

I believe my server was using MyISAM, but not 100% sure.

not sure but I always backup C:\wamp\bin\mysql\mysql5.5.24\data this folder before formating — Ravikumar Sharma, Oct 18 '12 at 05:28
I would have just exported my tables to .sql if I remembered, but since I haven't used the database in a long time, I completely forgot about it — Brad, Oct 18 '12 at 05:35

Udan · Accepted Answer · 2017-04-12T15:33:54.947

107

You can check my.ini file to see where the data folder is located.

Usually there is a folder {mysqlDirectory}/data

MySQL data storage:

Commands.frm
Commands.myd
Commands.myi

The *.frm files contain the table definitions. Your *.myi files are MyISAM index files. Your *.myd files contain the table data.

Edit/Update. Because of the interest shown in the question here is more info which is found also in the comments.

In Windows 8.1, the MySQL databases are stored (by default) here: C:\ProgramData\MySQL\MySQL Server 5.6\data

The folder C:\ProgramData is a hidden folder, so you must type it into Windows Explorer address to get there. In that data folder, the databases are named /{database_name_folder}/{database_tables_and_files}.

For instance,

C:\ProgramData\MySQL\MySQL Server 5.6\data\mydatabase\mytable.frm
C:\ProgramData\MySQL\MySQL Server 5.6\data\mydatabase\mytable.ibd

Thank @marty-mcgee for this content

edited Apr 12 '17 at 15:33

answered Oct 18 '12 at 05:27

Udan

5,429
2
28
34

12

this (.myd) is only for MyISAM engine. For InnoDB you need to see "ibdata1" file placed on the "{mysqlDirectory}/". – Raheel Hasan Mar 13 '13 at 10:25
can I copy this data/mydatabase folder from another PC to mine? For some reason the sql import (using WAMP) is INCREDIBLY SLOW, but not on the other PC. I am thinking of copying its data folder – dominicbri7 Jun 11 '13 at 16:59
why don't you use mysql_dump? – Udan Jun 18 '13 at 06:27
20

In Windows 8.1, the MySQL databases are stored (by default) here: `C:\ProgramData\MySQL\MySQL Server 5.6\data` -- this folder `C:\ProgramData` is a hidden folder, so you must type it into Windows Explorer address to get there. In that data folder, the databases are named `/{database_name_folder}/{database_tables_and_files}`. For instance, `C:\ProgramData\MySQL\MySQL Server 5.6\data\mydatabase\mytable.frm` and `C:\ProgramData\MySQL\MySQL Server 5.6\data\mydatabase\mytable.ibd` – Marty McGee Dec 11 '13 at 18:55
1

Marty-McGee's comment above should be answer for people using InnoDB. I almost missed his solution since it was in a comment. – HPWD Apr 12 '17 at 02:44
can anyone help me how to open that database folder in mysql workbench – Aman Chaudhary Jul 16 '21 at 12:03

score 42 · Answer 2 · edited Aug 28 '21 at 07:30

In Windows 7, the MySQL database is stored at

C:\ProgramData\MySQL\MySQL Server 5.6\data

Note: this is a hidden folder. And my example is for MySQL Server version 5.6; change the folder name based on your version if different.

It comes in handy to know this location because sometimes the MySQL Workbench fails to drop schemas (or import databases). This is mostly due to the presence of files in the db folders that for some reason could not be removed in an earlier process by the Workbench. Remove the files using Windows Explorer and try again (dropping, importing), your problem should be solved.

score 19 · Answer 3 · answered Feb 12 '15 at 04:05

I have a default my-default.ini file in the root and there is one server configuration:

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

So that does not tell me the path.

The best way is to connect to the database and run this query:

SHOW VARIABLES WHERE Variable_Name LIKE "%dir" ;

Here's the result of that:

basedir                     C:\Program Files (x86)\MySQL\MySQL Server 5.6\
character_sets_dir          C:\Program Files (x86)\MySQL\MySQL Server 5.6\share\charsets\

datadir                     C:\ProgramData\MySQL\MySQL Server 5.6\Data\
innodb_data_home_dir    
innodb_log_group_home_dir   .\
lc_messages_dir             C:\Program Files (x86)\MySQL\MySQL Server 5.6\share\

plugin_dir                  C:\Program Files (x86)\MySQL\MySQL Server 5.6\lib\plugin\

slave_load_tmpdir           C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp
tmpdir                      C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp

If you want to see all the parameters configured for the database execute this:

SHOW VARIABLES;

The storage_engine variable will tell you if you're using InnoDb or MyISAM.

No, that will tell you the default. You need to use `SHOW CREATE TABLE` to check a specific table. And different tables can use different Engines. — Rick James, Nov 21 '16 at 00:39

Dongdong · Answer 4 · 2018-09-07T16:10:34.897

11

MYSQL 8.0:

Search my.ini in disk, we will find this folder:

C:\ProgramData\MySQL\MySQL Server 8.0
It's ProgramData, not Program file

Data is in sub-folder: \Data.

Each database owns a folder, each table is file, each index is 1+ files.

Here is a sample database sakila:

edited Sep 07 '18 at 16:10

answered Sep 07 '18 at 14:21

Dongdong

2,208
19
28

can you help me how to open the sakila db in the mysql workbench. I am having difficulty on this. – Aman Chaudhary Jul 16 '21 at 11:38

score 6 · Answer 5 · edited Aug 28 '21 at 07:30

6

For Windows7 Use This Link: c:\users\all users\MySql\MySql Server x.x\Data\

Where x.x is the version number of the sql server installed in your machine.

edited Aug 28 '21 at 07:30

Sabito stands with Ukraine

4,271
8
34
56

answered Feb 02 '14 at 07:40

user3256430

81
1
3

score 4 · Answer 6 · edited May 23 '17 at 12:34

4

That should be your {install path}\data e.g. C:\apps\wamp\bin\mysql\mysql5.5.8\data\{databasename}

edited May 23 '17 at 12:34

Community

1
1

answered Oct 18 '12 at 05:29

codingbiz

26,179
8
59
96

score 4 · Answer 7 · answered Oct 17 '13 at 15:01

Just perform a Windows Search for *.myi files on your local partitions. Period.

As I suspectected, they were located inside a program files folder, instead of using a proper data-only folder like most other database managers do.

Why do a my.ini file search, open it with an editor, look-up the path string, make sure you don't alter the config file (!), and then do a second search? Complicated without a shred of added benefit other than to practice touch typing.

Picked this one because it is fast and simple. – Mousa Dirksz Mar 13 '15 at 10:12 — Mousa Dirksz, Mar 13 '15 at 10:12

score 4 · Answer 8 · edited Aug 28 '21 at 07:31

If you're using Win10 with Xampp server installed, then you can find the data folder in C:\xampp\mysql\data

Inside the data folder, each database has its own folder which in turn contains the .frm, .myi and .myd files which represent for a single table in the database.

If for instance, you created a database with the name: myschool and inside the database, you have three tables with the names:

Nursery
Primary
Secondary

Then, you will have for the Nursery table: nursery.frm, nursery.myi and nursery.myd. Same will go for the Primary and Secondary tables. Thus, in the I mentioned here, you will a total of 9 files inside the database folder named myschool.

You can then copy the database folder and use it in your new mysql installation data folder.

score 3 · Answer 9 · answered Mar 01 '16 at 08:25

It's usually in the folder specified below, but ProgramData is usually a hidden folder. To show it, go to control panel search for "folder" then under advanced settings tick show hidden files and click apply. C:/ProgramData/MySQL/MySQL Server 5.5/Data/

score 2 · Answer 10 · edited Feb 19 '17 at 14:20

2

I just installed MySQL 5.7 on Windows7. The database files are located in the following directory which is a hidden one: C:\ProgramData\MySQL\MySQL Server 5.7\Data

The my.ini file is located in the same root: C:\ProgramData\MySQL\MySQL Server 5.7

edited Feb 19 '17 at 14:20

Danny Nimmo

674
7
22

answered Feb 19 '17 at 11:12

Erik

21
1

score 1 · Answer 11 · answered Sep 07 '15 at 09:44

1

in MySQL are
".myd" a database self and
".tmd" a temporal file.
But sometimes I see also ".sql".

It depends on your settings and/or export method.

answered Sep 07 '15 at 09:44

Alexander Khomenko

569
1
5
13

score 0 · Answer 12 · answered Oct 18 '12 at 05:30

1) Locate the my.ini, which store in the MySQL installation folder.

For example,

C:\Program Files\MySQL\MySQL Server 5.1\my.ini

2) Open the “my.ini” with our favor text editor.

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"

#Path to the database root/"
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data

Find the “datadir”, this is the where does MySQL stored the data in Windows.

Muhammad Abbas · Answer 13 · 2017-02-01T15:44:51.850

0

C:\Program Files\MySQL\MySQL Workbench 6.3 CE\sys

paste URLin to window file, and get Tables, Procedures, Functions from this directory

edited Feb 01 '17 at 15:44

answered Feb 01 '17 at 15:33

Muhammad Abbas

129
4

Muzamil Amin · Answer 14 · 2023-07-13T15:10:10.380

For Windows 10 , MySQL 8.0 Follow the below procedure to get the databases:

step 1:go to localdisk(c) if you find ProgramData the follow the below path to get the databases:

C:\ProgramData\MySQL\MySQL Server 8.0\Data

Step 2: go to localdisk(c) click on view.

Step 3: click on hidden files and select then you will see ProgramData and then follow the step 1 then you get the databases

Here you can follow this:

click on this link you will get visuals

Where does MySQL store database files on Windows and what are the names of the files?

14 Answers14

Linked

Related