0

I'm a beginner here and I can't seem to find the files. My professor wants me to send these files specifically to her, but when I "export data" from the workbench, it only generates .sql files. Where do I get the .myd, .myi and .frm files she's looking for.

Thank you

Yonah Karp
  • 581
  • 7
  • 22
  • Why? She should be asking for .sql dumps. It's a very strange requirement. She shouldn't even be assuming you're using MyISAM as the table format, which is what is implied by these file extenstions. – user207421 May 20 '16 at 00:43
  • Yeah, it is.. here's the quote from the assignment. `Submit your entire project directory and database files as one zip file below. (Note: your MySQL tables are stored on your machine in files with extensions .frm, .myd, .myi - see: http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html )` I looked at the site she referenced, but no luck – Yonah Karp May 20 '16 at 00:45
  • What exactly is the assignment about? – user207421 May 20 '16 at 00:58
  • 1
    @EJP For my own sanity I'm going to presume it's a course on how databases were used in the late 1990s. – tadman May 20 '16 at 01:15
  • It's a web programming course. The first part is PHP and the second is ASP.NET. Compared to ASP, PHP seems like it's from the late 1990s – Yonah Karp May 20 '16 at 01:17
  • @ImmersionULTD You're much too kind to PHP. I would say it is from the Mesozoic era: barely a programming language at all. It is somewhat horrifying to me that someone with a title of 'professor' is teaching it at all. It's a toy at best. – user207421 May 20 '16 at 01:41

2 Answers2

3

Don't worry about those. That's just how MySQL stores the data internally. If you read the documentation carefully you'll soon learn that you shouldn't depend on those files for backups anyway, they're likely to be in an inconsistent state if the server's running.

The best way to make a backup for small to medium-sized databases is the mysqldump command. There's a number of options that can be applied but the most important on a busy database is --single-transaction which produces a consistent point-in-time snapshot.

The SQL data you get from that, which can be saved to a .sql file, is sufficient to recreate the database and is a backup.

You can restore with the mysql command-line tool, or if you're not comfortable with that, the MySQL Workbench program.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • Thanks I gathered that through my searches, however the professor specifically stated she wants these files... Any clue where I'd find them on my Mac? – Yonah Karp May 20 '16 at 00:38
  • 1
    Your professor is asking for some really odd things. Their location depends on how you installed MySQL, how it's configured, and a host of other factors. Additionally, using InnoDB you may not even have much data in these, there's a master database file that has the bulk of the data. – tadman May 20 '16 at 00:46
  • here's the quote from the assignment. `Submit your entire project directory and database files as one zip file below. (Note: your MySQL tables are stored on your machine in files with extensions .frm, .myd, .myi - see: http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html )` I looked at the site she referenced, but no luck – Yonah Karp May 20 '16 at 00:47
  • 1
    Professionally I'm disappointed that a professor would recommend anyone use MyISAM, it has a reputation for being terrible under load, extremely prone to complete destruction when a database server crashes, and is only included as an engine for backwards compatibility purposes. InnoDB is the de-facto way to go. If you're looking to pass your course, the pragmatic solution here is to query with `SHOW VARIABLES LIKE 'datadir'` to see where your data is being stored, archive it, send it and move on. – tadman May 20 '16 at 00:50
  • @tadman I agree completely. You're forced to use MyISAM for mysql/general_log, and I find the table crashes regularly. – user207421 May 20 '16 at 00:51
  • 1
    Found it in the usr folder. It's a hidden folder on mac and that;s why it wasn't showing up in spotlight searches :/ Thanks for the help! – Yonah Karp May 20 '16 at 01:13
2

Somewhere there is a data directory under a directory with MySQL in the filename, and under data is your own database directory. Files are in there. But if you haven't used MyISAM as the table format, the files will have different extensions, e.g. .idb for InnoDB.

You can find it via the query mentioned in @tadman's comment, or from a shell:

find / -name data -print

although you will probably get a number of matches.

You will have to stop the MySQL server before you can copy the files.

user207421
  • 305,947
  • 44
  • 307
  • 483
  • This is useful if the server isn't running or won't start for some reason. You can also look in `/etc/my.cnf` if the config has been changed, or brute-force find it like that. – tadman May 20 '16 at 01:02
  • Found it! Thank you for the help. It was in the usr folder which is hidden in Mac, so it wasn't coming up in regular searches – Yonah Karp May 20 '16 at 01:14