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

innodbMySQLmysqldump

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.

Best Answer

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}

If you have partitioned tables, then do this:

cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.partitions 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 as .ibd Files
IB_SYS : Number of InnoDB Tables Residing Inside ibdata1

STEP #1

Add the option to my.cnf

[mysqld]
innodb_file_per_table = 1

STEP #2

If you are running MySQL 5.6, login to mysql and run

mysql> SET GLOBAL innodb_file_per_table = 1;

If you are not running MySQL 5.6, restart mysqld

# service mysql restart

STEP #3

Write a script to create NULL ALTER TABLE commands

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"

SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Extract_InnoDB_From_ibdata1.sql

STEP #4

View the script

less Extract_InnoDB_From_ibdata1.sql

If you are OK with it, then run it

mysql ${MYSQL_CONN} < Extract_InnoDB_From_ibdata1.sql

CAVEAT

This will not shrink ibdata1. It merely extracts all InnoDB tables from it. ibdata1 will no longer grow beyond the 20GB due to the tables themselves.

If you want to shrink ibdata1, please refer to my post How do I shrink the innodb file ibdata1 without dumping all databases?

Best Answer

Related Solutions

Mysql – Copying MySQL tables, InnoDB tables not recognized

MySQL – Changing from system tablespace to file per table

STEP #1

STEP #2

STEP #3

STEP #4

CAVEAT

GIVE IT A TRY !!!

Related Question