How to clear the ibdata1 file when using MySQL InnoDB storage engine

I recently ran into the following error when trying to write to an InnoDB table on a MySQL database:

ERROR 1114 (HY000): The table 'test' is full

After poking around I realized that the tables had so many transactions, that the ibdata1 file had grown to >10GB. It had actually consumed all the available hard drive space on its partition.

Because I was going to be refactoring a bunch of the code that makes use of the database anyway, I wanted to start fresh.

First, I exported all the data in the database to my local machine. Then, on the server, I did a mysqldump so that I could easily restore it later:

$ /usr/bin/mysqldump -u dbuser -p dbname > /tmp/mysqldump.sql

Then, I ran the following commands to make sure I had all user grants in place:

$ mkdir /var/lib/mysql_grants
$ cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
$ chown -R mysql:mysql /var/lib/mysql_grants

Next, using my MySQL credentials, I dropped all databases except for mysql

mysql> DROP DATABASE `test`
mysql> DROP DATABASE `myotherdatabase`

I then shut down MySQL on there server

$ sudo /etc/init.d/mysqld stop

I moved the ib_logfile* and ibdata* files to the tmp directory before later deleting them

$ sudo mv /var/lib/mysql/ibdata1 /tmp/ibdata1
$ sudo mv /var/lib/mysql/ib_logfile1 /tmp/ib_logfile1
$ sudo mv /var/lib/mysql/ib_logfile0 /tmp/ib_logfile0

I restarted MySQL, which created new files in their place (~10MB this time)

$ sudo /etc/init.d/mysqld start

I then reimported the databases I had previously backed up using mysqldump

$ /usr/bin/mysqldump -u dbuser -p dbname < /tmp/mysqldump.sql

After that, I was able to connect again and run queries without any trouble!