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

Question

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.

Answer 1

Unfortunately, Yes you have to do every database. I have a step-by-step process I wrote up back on Oct 29, 2010 in StackOverflow as well as thorough explanation why there is nothing else you can do.

You also asked

Is it even possible to mysqldump half the databases to reclaim space, or do you really have to do every database ?

You cannot reclaim half. It is an all-or-nothing decision. It is good to keep in mind what lives in ibdata1 when innodb_file_per_table is disabled :

• Table Data
• Table Indexes
• MVCC (Multiversioning Concurrency Control) Data
  • Rollback Segments
  • Undo Space
• Table Metadata (Data Dictionary)
• Double Write Buffer (background writing to prevent reliance on OS caching)
• Insert Buffer (managing changes to non-unique secondary indexes)
• See the Pictorial Representation of ibdata1

Still, if you want to know which InnoDB tables are in ibdata1 and which are in their own tablespace file (.ibd), here is what you do:

STEP 01 : Run this query in the MySQL client

SELECT COUNT(1) InnoDBTableCount
FROM information_schema.tables
WHERE engine='InnoDB';

STEP 02 : Collect the number of .ibd files in the OS

ls -lR | grep -c ".ibd$"

STEP 03 : Subtract those values in the OS

Here is the shell script to compute these things

cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.tables WHERE engine='InnoDB'"
IB_TOT=`mysql -uroot -p... -ANe"${SQLSTMT}"`
IB_IBD=`ls -lR | grep -c ".ibd$"`
(( IB_SYS = IB_TOT - IB_IBD ))
echo ${IB_SYS }

Here is what you get:

• IB_TOT : Number of InnoDB Tables in the MySQL Instance
• IB_IBD : Number of InnoDB Tables Residing Outside ibdata1
• IB_SYS : Number of InnoDB Tables Residing Inside ibdata1