While I was copying /var/lib/mysql from one box to the other (it's a lousy way to do it, but it works), I noticed that there's a file called ibdata1 which is quite a bit bigger than any other file.

Apparently it's for all InnoDB tables and it only grows in size, it never decreases.

It can only be trimmed like so: http://forum.percona.com/index.php/m/1437/

A very cumbersum way:
mysqldump all databases

Remove the ibdata1 file.

Start the mysqld process.
It will now recreate the ibdata1 table space since it recognizes that it is missing.

Import the sql dump from step 1 and you are back again.

If you're using the innodb_file_per_table option, then each InnoDB table gets its own .ibd file, so you can recover freed-up space in a table by rebuilding just that one table. The easiest way to rebuild an InnoDB table is to run the command, "ALTER TABLE tablename ENGINE=InnoDB;". However, this will build the new copy of the table on disk before dropping the old one, so you'll need to have enough free disk space to keep the entire second copy of the table. If you're tight on disk space on the database server (which is probably why you want to free up the space trapped in the InnoDB tablespace in the first place), then you can mysqldump the table to another machine with more free space and then re-import it back into the database server. It's still a pain to have to do that, but it's a lot better than having to dump ALL of your tables.

Access denied for user 'debian-sys-maint'@'localhost'
Had to find the password on the origin system in /etc/mysql/debian.cnf

By AJ ONeal

If you loved this and want more like it, sign up!

Did I make your day?
Buy me a coffeeBuy me a coffee  

(you can learn about the bigger picture I'm working towards on my patreon page )