HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
filetablespaceinnodbsystemwaymysqlitsowndeterminenot

Problem

I have a MySQL development server that uses InnoDB tables in some of the databases. It was originally setup with the defaults so all data went into the system tablespace (e.g the main ibdata1 file). Along the way, I enabled innodb_file_per_table so new database tables get into their own files.

I now want to reclaim disk space from dropping some development databases. I know I have to mysqldump and re-import those databases that are in the system tablespace. What is the best way to determine which ones to focus on?

I don't see anything obvious in the information_schema database. I could manually poke around the server with an SSH shell, but I was looking for a more automated or elegant way to do that.

In some older databases, I only see .frm files in the database directory, in newer ones I see .frm and .ibd files. That seems to be the key to know where the data is ...

Sanity Check: Is it even possible to mysqldump half the databases to reclaim space, or do you really have to do every database? There are 54 databases inside this server that use InnoDB tables.

Note: Using MySQL v5.1 on UNIX.

Solution

Not relevant to readers using very old versions of MySQL, but on more recent versions of MySQL you can use:
SELECT i.name FROM information_schema.INNODB_SYS_TABLES i WHERE i.space = 0;
to see which tables are in the shared table space and use:
SELECT i.name FROM information_schema.INNODB_SYS_TABLES i WHERE i.space <> 0;
to see which ones are in their own files.

Context

StackExchange Database Administrators Q#33906, answer score: 4

Revisions (0)

No revisions yet.