MySQL on OS X – Can’t Find MYD Files in MAMP

mac os xmyisamMySQLmysql-5.5

Try as I may I can't find the data files for my MAMP installation!

FRM files are here:

/Applications/MAMP/db/mysql/database_name/

but, there are no corresponding MYD or MYI files anywhere.
The tables definitely have content.

Is it possible that this version of MySQL creates MYD/MYI files that are not the same as the table name? I've been at this for hours.
Any help is appreciated.

OS X Mavericks.
MySQL 5.5.34

+--------------------+---------+
| engine             | tblsize |
+--------------------+---------+
| CSV                |       0 |
| InnoDB             |   16384 |
| MEMORY             |       0 |
| MyISAM             |    4096 |
| PERFORMANCE_SCHEMA |       0 |
| total              |       0 |
+--------------------+---------+
6 rows in set, 1 warning (0.38 sec)

+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

+------------------------------+
| @@global.datadir             |
+------------------------------+
| /Applications/MAMP/db/mysql/ |
+------------------------------+
1 row in set (0.00 sec)

Best Answer

MyISAM still uses .MYD and .MYI.

The only way you can see .frm files only and no other table file would be this:

  • All tables are using InnoDB
  • innodb_file_per_table disabled (all table data would be inside ibdata1)

If you can log into MySQL, run this

SELECT IFNULL(engine,'total') engine,(data_length+index_length) tblsize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
GROUP BY engine WITH ROLLUP;
SELECT @@global.innodb_file_per_table;
SELECT @@global.datadir;
SELECT table_schema,table_name,engine,(data_length+index_length) tblsize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
ORDER BY engine,table_schema,table_name;

These will fully reveal your data and storage engine situation.

UPDATE 2015-05-01 19:48 EST

Based on the output of the queries I gave you

  • 4KB worth of MyISAM
  • 16KB worth of InnoDB
  • @@global.innodb_file_per_table = 0 means all the table and index space for your InnoDB tables are inside /Applications/MAMP/db/mysql/ibdata1

To see your MyISAM tables, run this:

SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
AND engine='MyISAM';

To see your MEMORY tables, run this:

SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
AND engine='MEMORY';

To see your InnoDB tables, run this:

SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
AND engine='InnoDB';

To get the count of the tables by engine:

SELECT table_schema,COUNT(1) table_count
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
GROUP BY engine;

To get the count of the tables by engine in each database:

SELECT table_schema,engine,COUNT(1) table_count
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
GROUP BY table_schema,engine;
Related Question