There is absolutely no way to reduce the size of ibdata1 in-place. As for the already present data pages and index pages in ibdata1 from obsolete tables, you could let mysql overwrite them with transactions over time.

RECOMMENDATION #1

You must migrate all data out and reload with an empty ibdata1.

I have already addressed this in StackOverflow back in Oct 29, 2010 (Steps Outlined).

I have also addressed this in other posts in the DBA StackExchange

• Jul 19, 2012 : whether a big (but not used) ibdata1 slow anything down
• Aug 29, 2012 : Removing/overwriting sensitive data located in already deleted records in ibdata files
• Apr 19, 2012 : MySql - Clean ibdata1 (Steps Outlined)

If you cannot do this in Production at this time, you will have to present this as a case study for the need to do so. Here is what you do:

• Setup a Staging DB Server
  • No initial data
  • innodb_file_per_table configured
• Take mysqldump of Production (use --single-transaction)
• Load the mysqldump into Staging

Please note that ibdata1 will not grow dramatically because data and index pages for each InnoDB table will reside in its own personal tablespace file.

For example, the table mydb.mytable will reside in ibdata1. All you will have is /var/lib/mysql/mydb/mytable.frm as an external manifestation of the table.

Once you load the Staging DB Server, the table will look like this on disk:

• /var/lib/mysql/mydb/mytable.frm
• /var/lib/mysql/mydb/mytable.ibd

Only data dictionary elements of the table are left behind in ibdata1.

That will give you a look at what production will look like when you perform the InnoDB Cleanup.

RECOMMENDATION #2

Once you have performed InnoDB CleanUp in Production, you may be concerned with old data trapped within each individual .ibd file. Cleaning up an .ibd file is much easier.

To cleanup the InnoDB table mydb.mytable, run one of the following:

• ALTER TABLE mydb.mytable ENGINE=InnoDB;
• OPTIMIZE TABLE mydb.mytable;

That's it. The only difference is that OPTIMIZE TABLE may try to run ANALYZE TABLE under the hood. It may get bypassed for an InnoDB table.

RECOMMENDATION #3

If the goal is to truly stomp over all unused pages in ibdata1, here is something you may want to try out on a Staging Server:

STEP01) Extract all InnoDB tables from ibdata1. (Since you have already done so, I will write this up for the benefit of others):

SQLTORUN="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQLTORUN="${SQLTORUN} FROM information_schema.tables WHERE engine='InnoDB'"
mysql -uroot -p...  -ANe"${SQLTORUN}" > ExtractInnoDB.sql
mysql -uroot -A < ExtractInnoDB.sql

STEP02) Create a large temp table in MyISAM called test.junkman:

CREATE TABLE test.junkman (a INT) ENGINE=MyISAM;
INSERT INTO test.junkman VALUES (0);
INSERT INTO test.junkman VALUES SELECT * FROM test.junkman; ## run this line 30 times

When done, you will generate a 7 GB MyISAM table with no indexes:

mysql> SELECT data_length/power(1024,3) TableSize
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> AND table_name='junkman';
+-----------+
| TableSize |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)

mysql>

This should take about 10-15 minutes to create.

STEP03) Disable innodb_file_per_table

Goto /etc/my.cnf and comment it out

[mysqld]
#innodb_file_per_table

then service mysql restart

STEP04) Create InnoDB table test.junkman2

CREATE TABLE test.junkman2 LIKE test.junkman;
ALTER TABLE test.junkman2 ENGINE=InnoDB;

This will create test.junkman2 inside ibdata1

STEP05) In another SSH session run this watch

watch ls -l /var/lib/mysql/ibdata1

STEP06) Load test.junkman INTO test.junkman2

INSERT INTO test.junkman2 SELECT * FROM test.junkman;

STEP07) Repeat STEP06 until the size of ibdata1 actually changes in the other SSH session

STEP08) Uncomment innodb_file_per_table in /etc/my.cnf to re-enable it

[mysqld]
innodb_file_per_table

STEP09) service mysql restart

STEP10) TRUNCATE TABLE test.junkman2; DROP TABLE test.junkman2;

STEP11) TRUNCATE TABLE test.junkman; DROP TABLE test.junkman;

I've never tried this before (this was just a working theory although the 7GB MyISAM stunt was real). Go give it a try and let me what happens.

You may find this surprising, but did you know that there are several moving parts to ibdata1? Even with innodb_file_per_table enabled, here the classes of information stored in ibdata1

• Data Dictionary
• Double Write Buffer (support data consistency; used for Crash Recovery)
• Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
• Rollback Segments
• Undo Space (where the most uncontrolled growth can happen)

Pictorial Representation of InnoDB Architecture

What can make ibdata1 grow suddenly?

According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

• Lots of Transactional Changes
• Very Long Transactions
• Lagging Purge Thread

As long as your total InnoDB dataset is relatively small and you would like to shrink ibdata1, you can do the following:

STEP 01: Schedule downtime

STEP 02: mysqldump all databases to /root/MySQLData.sql

mysqldump -uroot -p --all-databases --routines --triggers > /root/MySQLData.sql

STEP 03: Run SET GLOBAL innodb_fast_shutdown = 0;

STEP 04: Drop all databases except the mysql and information_schema database

STEP 05: service mysql stop

STEP 06: rm -f /var/lib/mysql/ib*

STEP 07: service mysql start (recreates ibdata1, ib_logfile0, ib_logfile1)

STEP 08: Login to mysql

STEP 09: At mysql prompt, run mysql> source /root/MySQLData.sql

That's it. I have done this many, many times : Howto: Clean a mysql InnoDB storage engine?

From here, you just have to live with the weird growth due to transactions.

Give it a Try !!!