This is a known bug in MySQL: 1341. It's been going on for 8 years now.
You might want to try a workaround, posted on StackOverflow:
That ibdata1
isn't shrinking is a particularly annoying feature of MySQL. The ibdata1
file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump.
But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1
will not grow as large.
It was a while ago I did this. However, to setup your server to use separate files for each table you need to change my.cnf
in order to enable this:
[mysqld]
innodb_file_per_table=1
http://dev.mysql.com/doc/refman/5.5/en/multiple-tablespaces.html
As you want to reclaim the space from ibdata1
you actually have to delete the file:
- Do a
mysqldump
of all databases, procedures, triggers etc
- Drop all databases except the mysql-db
- Stop mysql
- Delete
ibdata1
and ib_log
files
- Start mysql
- Restore from dump
When you start MySQL in step 5 the ibdata1
and ib-log
files will be recreated.
Now you're fit to go. When you create a new database for analysis, the tables will be located in separate ibd*
files, not in ibdata1
. As you usually drop the database soon after, the ibd*
files will be deleted.
http://dev.mysql.com/doc/refman/5.1/en/drop-database.html
Or, if you want to keep the database running whili you do this, you could try this:
Enable the innodb_file_per_table option without shutting down the database. The idea is:
- Configure your original database as master. Unless your database is already using
binlogs for security, this is the only step that will require restarting MySQL.
- Make a backup of the original database using Xtrabackup.
- Restore the backup and run a second instance of MySQL.
- Run mysqldump on the second instance.
- Stop the second instance, but do not delete it yet.
- Create a new database and start the third instance of MySQL with the enabled option
innodb_file_per_table.
- Restore the dump by feeding it into the third instance of MySQL.
- Configure the third instance as slave and run the replication.
- When the initial replication finishes and the slave catches up with the master,
reconfigure your clients to use the new instance.
- That's it. You can stop the first instance now and delete it.
A detailed article can be found here.