One could wonder why would anyone want to have its ibdata in system tablespace? Well there are some "bugs" (in versions prior to 5.5) and performance issues that cause mysql lock-ups when using innodb_file_per_table and doing truncate table or drop table or even drop database queries with large buffer pools. In a nutshell MySQL will do a whole buffer pool lock and scan on each of the above mentioned commands effectively stoping the entire MySQL server until it scans over the entire buffer pool for references to those dropped/truncated tables.

When given choice and when I do know the pattern of data input to my tablespace i will chose system tablespace.

Recently I was a victim of my own system and uncontrolled input of data to my InnoDB table space which leaved me with almost 36Gb of garbage data over one night. Naturally i truncated the data, but it left me with 37Gb ibdata file.

So here are the steps I've taken to reduce this system tablespace.

First of all backup!

I highly recommend Xtrabackup from Percona for this task, yes it will require additional 37Gb of space for backup but this is by far best point in time backup for MySQL.

innobackupex $backupdir

And replace the backupdir with your preferred location

Let's create a working environment, say you have a lots of space on /home

mkdir /home/inno-reduce
cd /home/inno-reduce

Ok, next stop dumping all the databases that have innodb tables inside, for this I've created a small bash script a while ago..

#!/bin/bash
CWD=`pwd`
mysql -e "SELECT distinct(table_schema) FROM information_schema.TABLES where ENGINE = 'InnoDB'" > $CWD/innodb-databases.list
sed "/table_schema/d" $CWD/innodb-databases.list > $CWD/tmp
mv $CWD/tmp $CWD/innodb-databases.list
for database in `cat $CWD/innodb-databases.list`; do
    echo "dumping data from $database to $CWD/$database.sql"
    /usr/bin/mysqldump --triggers --routines $database > $CWD/$database.sql;
done

Now would be a good time to cut off any application using MySQL (for example turn off Apache, or drop the packets in firewall). Just copy and paste this code in step1-dump.sh file, make it executable and run it. It will produce a .sql dump files in your current directory (hopefully we are still on /home/inno-reduce)

Step2 script will iterate trough that dumped data files and drop all the databases that have innodb tables inside.

#!/bin/bash
CWD=`pwd`

for database in `ls $CWD/*.sql|awk -F'/' '{print $NF}'|cut -d. -f1`; do
    echo "droping database $database"
    mysqladmin drop $database
done

Again just copy and paste this code in step2-drop.sh, make it executable and run it. At this point There shouldn't be any InnoDB tables in MySQL server.

Now, it is a good time to shutdown your MySQL service and rm ibdata file. On typical CentOS system this would be done with

service mysqld stop
rm -f /var/lib/mysql/ibdata1

If you are not running CentOS and default MySQL install or if you have ibdata file on some other path… please adjust the above. Starting MySQL service at this point would "regenerate" ibdata file with its smallest size increment as defined in my.cnf lets do so:

service mysqld start

And now for the third script

#!/bin/bash
CWD=`pwd`

for database in `ls $CWD/*.sql|awk -F'/' '{print $NF}'|cut -d. -f1`; do
    mysqladmin create $database
    mysql $database < $CWD/$database.sql
done

As before copy and paste this script into step3-import.sh (remember to place this file at the same directory as other two, and dumps), make it executable and run it.

Voila! your data is back in, ibdata reduced and MySQL up&running.

Remember to restore any access to your MySQL service (starting Apache, clearing firewall drops, etc..)