In MySQL 5.1.6 there’s a new INFORMATION_SCHEMA table.
Currently, it only has information on files for NDB but we’re hoping to change that in a future release (read: I think it would be neat).
This table is a table generated by the MySQL server listing all the different files that are/could be used by a storage engine. Three (may) be table to file mappings (or not) depending on the engine.
Basically, NDB does files like so:
A table is stored in a tablespace.
A tablespace has datafiles.
Datafiles are of a set size.
Space is allocated in datafiles to tables in a unit called an extent.
If you don’t have any free extents you cannot have new tables store data on disk.
If you don’t have any free extents you may still be able to add data to a table as there may be free space in an extent allocated to that table.
Logs (used for crash recovery) are stored in logfiles.
logfiles are part of logfile groups.
A tablespace uses a logfile group for logging.
Try the following bits of code and running SELECT * from INFORMATION_SCHEMA.FILES between each statement.
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;
ALTER LOGFILE GROUP lg1
ADD UNDOFILE 'undofile02.dat'
INITIAL_SIZE = 4M
ENGINE=NDB;
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 12M
ENGINE NDB;
ALTER TABLESPACE ts1
ADD DATAFILE 'datafile02.dat'
INITIAL_SIZE = 4M
ENGINE=NDB;
CREATE TABLE t1
(pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
TABLESPACE ts1 STORAGE DISK
ENGINE=NDB;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (0, 0, 0);
SELECT * FROM t1;
DROP TABLE t1;
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile.dat'
ENGINE = NDB;
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile02.dat'
ENGINE = NDB;
DROP TABLESPACE ts1
ENGINE = NDB;
DROP LOGFILE GROUP lg1
ENGINE =NDB;
For a point of interest, these examples are taken from the ndb_dd_basic test (which can be found in mysql-test/t/ndb_dd_basic.test)
