mysql by 29 comments

Where does MySQL stored the data in my harddisk?

Here i provide a simple user guide to find out where does MySQL database stored the data in our hard disk, both in Windows and Linux.

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

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.

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

2) viewthe my.cnf file 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

3) Find the “datadir”, this is where does MySQL stored the data in Linux system.

29 comments on “Where does MySQL stored the data in my harddisk?”

  1. I am not a computer tech, but using MacBook pro, Safari, opening a website I get the following error: Replace Query: 1114: The table ‘swsessions’ is full in ./__swift/library/Database/class.SWIFT_Database.php:1110. Any help as to how to fix the problem appreciated. I recently did a maintenance clean up on my computer and may have deleted something in error.

    Reply

  4. where i can see the query executed from JPA in MySQL, How to log the queries to FILE

    Reply

  6. Hi
    Can you explain why mySQL Administrator reports a db index & data of 22MB but the “datadir” is only a series of files of 10KB and the are all *frm files

    Reply

  7. On my file it says:

    # These are commonly set, remove the # and set as required.
    # basedir = …..
    # datadir = …..
    # port = …..
    # server_id = …..

    Do you know where is it commonly set to be?

    BTW, I am using the community edition. Thanks

    Reply

  10. Yes I found mine,
    Does it work if we copy the database folder and use it in different system??

    Reply

  11. Please let me know, where I can find in mac system

    Reply

  12. On Linux, perhaps, the following can help (I mean, if the server is already running)

    –datadir=/var/lib/mysql

    [root@myhost ~]# ps -ef | grep mysql
root     29632     1  0 15:33 pts/13   00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
mysql    29692 29632  0 15:33 pts/13   00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock
root     29729 27270  0 15:35 pts/13   00:00:00 grep mysql
[root@myhost ~]#
    Reply

  13. Hello, I’m using MySQL 5 on Windows 7 and my database was stored here : C:\ProgramData\MySQL\MySQL Server 5.5\data

    Reply

  14. Hello,

    I found the post usefull, but the information I am searching is how can I alocate diferents databases in diferents path? with the same mysql instance?

    thanks

    Reply

  15. Is mysql server stored all data in flat file structure ?, please share it.

    Reply

  16. Hi this is rajeshkannan i am new to java with mysql.
    i am using a server which has database name: redmine_wrk
    There is a table name attachments in that database.Which has encrypted file path column.
    I dont know the key for that but i know the path from which i need to download the file.
    But i don’t know how to download file its in path: /var/www/html/redmine/files
    its very urgent can any one please help me .

    Thanks in advance

    Reply

  17. hey this is is of no use I think. Since then I copied the database from the location which you have intimidated and pasted it on another computer which has same mysql location. The positive side is that it shows the database and the tables in query browser but when I open the table it shows “got error 1 from storage engine error no 1030”

    Reply

  18. For Windows 7, it actually stored the ini into:
    C:\ProgramData\MySQL
    and not into
    C:\Program Files\MySQL

    Reply 
    1.  Thank you very much.  your answere was very useful.
      Reply

    2. hey,
      i made a php script to copy data from mysql to notepad in csv format.
      where does it store in my computer.

      Reply

      1. also i didn’t find C:\ProgramData\MySQL path in my C drive.

        Reply

    4. Thankyou – just saved me a massive headache recovering data from an old hard-drive – could not figure out where my MySQL data files had gone and there they are.

      Reply

  20. However, some storage engine can specify its own data file . i.e,innodb-data-file-path.
    By default,the table definitins and data are all located in datadir .

    Reply

  21. Thanks, but the files do not seem to grow as I add more data to the tables – So is the data really there or are they just the table definitions?

    Reply

  23. Thanks. really helpful article to view my tables and other data that MySQL stores in my hard-disk.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *