idb location and portability

[vjp...@at.biostrategist.dot.dot.com]

When I load stuff into mysql it goes into three files called idb (well, 2/3
are indixes). Is the location of these files restircted? Can they be
separated? (ie different data, different dbs). Can they be ported to another
machine without taking an entire day to load the data fresh from text to
mysql? eg, I can give someone an MS Access mdb file and they can run it as is.


- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus
blog: panix.com/~vjp2/ruminatn.htm - = - web: panix.com/~vjp2/vasos.htm
facebook.com/vasjpan2 - linkedin.com/in/vasjpan02 - biostrategist.com
---{Nothing herein constitutes advice. Everything fully disclaimed.}---

[The Natural Philosopher]

On 30/06/2020 19:45, vjp...@at.BioStrategist.dot.dot.com wrote:
> When I load stuff into mysql it goes into three files called idb (well, 2/3
> are indixes).

Indices (old school)
indexes (modern pidgin)

--
"And if the blind lead the blind, both shall fall into the ditch".

Gospel of St. Mathew 15:14

[Axel Schwenke]

On 30.06.2020 20:45, vjp...@at.BioStrategist.dot.dot.com 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.

[vjp...@at.biostrategist.dot.dot.com]

Thanks. It gives me a clue how to start.