Where does mysql store data?
MysqlMysql Problem Overview
Where does mysql store data? I found out (by using mysql>SELECT @@datadir ) that it's in var/lib/mysql - but that can't be it. I have a quite big database (4 GB) called 'bot', but all the files in the 'bot' subdirectory (var/lib/mysql/bot) have only 280KB. Where is the rest?
One more thing - 99% of the database's size is a text column in one of the tables - I know that 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? (btw, there are other databases as well in the var/lib/mysql directory).
Mysql Solutions
Solution 1 - Mysql
From here:
Windows
- Locate the my.ini, which store in the MySQL installation folder.
For Example, C:\Program Files\MySQL\MySQL Server 5.1\my.ini
- 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
- 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
- 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
- Find the
“datadir”
, this is where does MySQL stored the data in Linux system.
Solution 2 - Mysql
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)
Solution 3 - Mysql
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.
Solution 4 - Mysql
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
.
Solution 5 - Mysql
as @PhilHarvey said, you can use mysqld --verbose --help | grep datadir
Solution 6 - Mysql
In mysql server 8.0, on Windows, the location is C:\ProgramData\MySQL\MySQL Server 8.0\Data