91

I installed MySQL on a Mac after downloading its DMG file version 64 bit.

While trying to create a database it gave me error 1006 -- can't create database. After browsing a number of website, it seems due to user ownership setting of MySQL "data directory" location that needs to be changed.

Where is MySQL default data directory? I could not find /var/lib/mysql in localhost.

THANKS.

informatik01
  • 16,038
  • 10
  • 74
  • 104
iwan
  • 7,269
  • 18
  • 48
  • 66
  • You can see this link: GeekHades answer http://stackoverflow.com/questions/4847069/mysql-sock-is-not-created-osx/41605617#41605617 – GeekHades Jan 12 '17 at 05:26

10 Answers10

75

If the software is Sequel pro the default install mysql on Mac OSX has data located here:

/usr/local/var/mysql
SuperBiasedMan
  • 9,814
  • 10
  • 45
  • 73
nextzeus
  • 1,058
  • 8
  • 7
61

See if you have a file located under /etc/my.cnf. If so, it should tell you where the data directory is.

For example:

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
...

My guess is that your mysql might be installed to /usr/local/mysql-XXX.

You may find these MySQL reference manual links useful:

blizzrdof77
  • 363
  • 4
  • 14
paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • thanks a LOT paulsm4, your guess (/usr/local/mysql-xxx) is correct.. Have a good day – iwan Sep 18 '11 at 05:28
  • 7
    /usr/local/var/mysql for me! – Mario Uher Jun 25 '13 at 10:07
  • 172
    `/usr/local/var/mysql` is the case if you've installed `mysql` with `Homebrew` – mrt Jul 24 '13 at 10:35
  • 3
    @MRT cheers! Every few weeks/months I need this information :P – Aldo 'xoen' Giambelluca Feb 19 '14 at 21:14
  • 2
    XAMPP and other tools have their own data locations /Applications/XAMPP/xamppfiles/var/mysql . See http://www.sequelpro.com/docs/where_are_mysqls_files – tgkprog Dec 08 '14 at 16:21
  • i got it on mac from /usr/local/mysql/bin .. THEN ./mysql -u root -p – mercury Apr 02 '17 at 00:14
  • Mine (/etc/my.cnf) is just empty: ------------------ # Default Homebrew MySQL server config [mysqld] # Only allow connections from localhost bind-address = 127.0.0.1 ---------------------- I've installed mysql@5.7 from homebrew and it has not created any /usr/local/var/mysql folder. ... this is the default installation directory: usr/local/Cellar/mysql@5.7 – saran3h Jun 27 '18 at 07:36
17

On M1 and homebrew the "data directory" is:

/opt/homebrew/var/mysql

And the main path is:

/opt/homebrew/Cellar/mysql
Kernel
  • 661
  • 7
  • 10
16

As suggested, I edited this message to place a proper answer.

The 'physical' location of the MySQL database is under /usr/local/mysql

The mysql is a symlink to the current active MySQL installation, in my case the exact folder is mysql-5.6.10-osx10.7-x86_64.

Inside that folder you'll see another data folder, inside it are RESTRICTED folders with your databases.

You can't actually see the size of your databases (that was my issue) from the Finder because the folder are protected, you can though see from the terminal with sudo du -sh /usr/local/mysql/data/{your-database-name} and like this you'll get a nice formatted output with the size.

In those folder you have different files with all the folders present in your database, so it's safer to check the db's folder to get a size.

That's it. Enjoy!

informatik01
  • 16,038
  • 10
  • 74
  • 104
Arthur Kovacs
  • 1,710
  • 2
  • 17
  • 24
  • I take it all back, the databases are there, but the get info on the parent directory returned erroneous results. Get Info on the specific db folders will return all that occupied space. – Arthur Kovacs Apr 20 '13 at 09:11
14

Well, if yo don't know where is my.cnf (such Mac OS X installed with homebrew), or You are looking found others choices:

ps aux|grep mysql
abkrim            1160   0.0  0.2  2913068  26224   ??  R    Tue04PM   0:14.63 /usr/local/opt/mariadb/bin/mysqld --basedir=/usr/local/opt/mariadb --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/opt/mariadb/lib/plugin --bind-address=127.0.0.1 --log-error=/usr/local/var/mysql/iMac-2.local.err --pid-file=iMac-2.local.pid

You get datadir=/usr/local/var/mysql

abkrim
  • 3,512
  • 7
  • 43
  • 69
8

Check where is the root folder of mysql with:

mysql_config
Julio Marins
  • 10,039
  • 8
  • 48
  • 54
3

If you are using macOS {mine 'High Sierra'} and Installed XAMPP

You can find mysql data files;

Go to : /Applications/XAMPP/xamppfiles/var/mysql/

Elshan
  • 7,339
  • 4
  • 71
  • 106
2

If you install MySQL via homebrew on MacOS, you might need to delete your old data directory /usr/local/var/mysql. Otherwise, it will fail during the initialization process with the following error:

==> /usr/local/Cellar/mysql/8.0.16/bin/mysqld --initialize-insecure --user=hohoho --basedir=/usr/local/Cellar/mysql/8.0.16 --datadir=/usr/local/var/mysql --tmpdir=/tmp
2019-07-17T16:30:51.828887Z 0 [System] [MY-013169] [Server] /usr/local/Cellar/mysql/8.0.16/bin/mysqld (mysqld 8.0.16) initializing of server in progress as process 93487
2019-07-17T16:30:51.830375Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2019-07-17T16:30:51.830381Z 0 [ERROR] [MY-013236] [Server] Newly created data directory /usr/local/var/mysql/ is unusable. You can safely remove it.
2019-07-17T16:30:51.830410Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-07-17T16:30:51.830540Z 0 [System] [MY-010910] [Server] /usr/local/Cellar/mysql/8.0.16/bin/mysqld: Shutdown complete (mysqld 8.0.16)  Homebrew.
ck1910
  • 151
  • 2
  • 5
1

If you are using Homebrew to install MySQL@5.7, the location is

/usr/local/Homebrew/var/mysql

I don't know if the location is the same for other versions.

Aerendir
  • 6,152
  • 9
  • 55
  • 108
0

if its homebrew install with m1 chip then its located under

/opt/homebrew/opt/mysql
Ramya Musunuru
  • 177
  • 1
  • 5