Re: idb location and portability

On 30.06.2020 20:45, [email protected] wrote:
> When I load stuff into mysql it goes into three files called idb

Not necessarily. And never .idb

Only if your tables use the InnoDB engine. And only if you have configured
it to use one file per table, then InnoDB stores all data (and indices) that
belong to a table into a single <tablename>.ibd File. If you use partitions,
then each partition is handled as if it was a table (read: each partition is
a .ibd file of its own).

> Is the location of these files restircted?

Data files for tables are typically located in a subdirectory of the datadir
with the subdirectory name = name of the "database" container. But you can
specify a directory when you create the table:

https://dev.mysql.com/doc/refman/8.0/en/innodb-create-table-external.html

> Can they be separated? (ie different data, different dbs).

I don't understand that question. Maybe you want to read the manual:

https://dev.mysql.com/doc/refman/8.0/en/symbolic-links.html

> Can they be ported to another > machine without taking an entire day to load the data fresh from text to
> mysql?

InnoDB tables have ties to the global tablespace (undo log records, data
dictionary) and to the redo log(s). You cannot copy just one data file.
Still there are ways to copy all or single tables. The manual tells:

https://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html

> eg, I can give someone an MS Access mdb file and they can run it as is.

Access ist not a database. Also this would be the analogon to copying the
complete MySQL data directory. This of course works for MySQL as well.