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> 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!