1

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)
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.