CODEWITHSUNDEEP
mysql
Konrad
Konrad

Reputation: 941

Where does MySQL store data?

Where does MySQL store data?

SELECT @@datadir returns var/lib/mysql - but that can't be it. I have a 4 GB database called bot, but all the files in the bot subdirectory (var/lib/mysql/bot) have only 280KB. Where is the rest?

99% of the database's size is a text column in one of the tables. MySQL stores it in separate files, but does it create one file per record?

I found a big file in the var/lib/mysql directory named ibdata1 - its size is over 8GB - what is it?

(There are other databases in the var/lib/mysql directory).

Upvotes: 57

Views: 154277

Answers (6)

Alon G
Alon G

Reputation: 3383

as @PhilHarvey said, you can use mysqld --verbose --help | grep datadir

Upvotes: 1

Leo Chapiro
Leo Chapiro

Reputation: 13979

From here:

Windows

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

For Example, C:\Program Files\MySQL\MySQL Server 5.1\my.ini

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

Linux

  1. Locate the my.cnf with the find / -name my.cnf command.
yongmo@myserver:~$ find / -name my.cnf
find: /home/lost+found: Permission denied
find: /lost+found: Permission denied
/etc/mysql/my.cnf
  1. View the my.cnf file like this: cat /etc/mysql/my.cnf
yongmo@myserver:~$ cat /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
[mysqld]
#
# * Basic Settings
#
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket  = /var/run/mysqld/mysqld.sock
port   = 3306
basedir  = /usr
datadir  = /var/lib/mysql
tmpdir  = /tmp
language = /usr/share/mysql/english
skip-external-locking
  1. Find the “datadir”, this is where does MySQL stored the data in Linux system.

Upvotes: 43

Gamage Pavithra Sankalpa
Gamage Pavithra Sankalpa

Reputation: 19

In mysql server 8.0, on Windows, the location is C:\ProgramData\MySQL\MySQL Server 8.0\Data

Upvotes: -1

Skorunka František
Skorunka František

Reputation: 5450

I just installed MySQL Server 5.7 on Windows 10 and my.ini file is located here c:\ProgramData\MySQL\MySQL Server 5.7\my.ini.

The Data folder (where your dbs are created) is here C:/ProgramData/MySQL/MySQL Server 5.7\Data.

Upvotes: 1

Victor P
Victor P

Reputation: 1612

In version 5.6 at least, the Management tab in MySQL Workbench shows that it's in a hidden folder called ProgramData in the C:\ drive. My default data directory is

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

. Each database has a folder and each table has a file here.

Upvotes: 14

Giles
Giles

Reputation: 1687

Reading between the lines - Is this an innodb database? In which case the actual data is normally stored in that directory under the name ibdata1. This file contains all your tables unless you specifically set up mysql to use one-file-per-table (innodb-file-per-table)

Upvotes: 24

Related Questions