inside the mind of a linux admin

How to shrink an ibdata1 file with minimal MySQL downtime

The default mySQL configuration for InnoDB database tables creates a massive storage file called ‘ibdata1’. Basically, the ibdata1 file contains the table data of your InnoDB tables. In large production environments, this file can grow to be extremely large. On some of the servers I administer, I’ve seen this file exceed sizes of 30GB. Fixing the file size obviously has the effect of limiting the total amount of data which can be stored in InnoDB tables, so that’s not a viable option.

The ibdata1 file is by default ‘auto-growing’, so it will inflate as more data is put into InnoDB tables. After records are deleted from InnoDB tables, the file will contain pages marked as “free” which could be used for future data, but the file itself is unshrinkable.

The inability to shrink this file 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. I’ve come up with a solution to do this with minimal downtime for the mySQL service (the length of time it takes for a normal service restart).

To do this, it’s necessary that you have enough disk space available to double your mySQL storage footprint. We’re in essence going to be spawning up a new service in a temporary ‘staging’ area, and then importing the data back in using the file-per-table option.

CAUTION: Be sure you have backups of your data before performing these operations. It is possible that something could go horribly wrong and ruin your day/week/month/year.

Steps to shrink ibdata1 file

IMPORTANT: Before shrinking the ibdata file, be sure my.cnf is configured to use separate files per innoDB table:

sed ‘/innodb_file_per_table/d’ -i /etc/my.cnf
echo ‘innodb_file_per_table’ >> /etc/my.cnf

1. Get list of all InnoDB databases

mysql> SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.tables WHERE ENGINE = ‘InnoDB’

2. Dump all of the databases to /root/all-databases.sql

/usr/bin/mysqldump –extended-insert –all-databases –add-drop-database –disable-keys –flush-privileges –quick –routines –triggers > /root/all-databases.sql

3. Prepare a secondary venue for which we can perform our magic

mkdir /var/lib/mysql2 # create a new staging area
rsync -avz /var/lib/mysql/mysql /var/lib/mysql2 # copy the mysql database w/grants
chown -R mysql.mysql /var/lib/mysql2 # set permissions correctly

4. Spin up the new mysqld instance, allowing it to create a fresh ibdata1 environment

Notes: to avoid conflicts we’re using temporary pid/socket/error files here, and skipping networking binding of 3306

/usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql2 –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/var/lib/mysql2/error.log –pid-file=/var/lib/mysql2/ –skip-networking –socket=/var/lib/mysql2/dirty.sock

5. Check out our new environment, ensure there’s no databases other than information_schema and mysql

# ls -lah /var/lib/mysql2

total 29M
drwxr-xr-x 3 mysql mysql 4.0K Aug 2 08:46 ./
drwxr-xr-x 28 root root 4.0K Aug 2 08:39 ../
srwxrwxrwx 1 mysql mysql 0 Aug 2 08:46 dirty.sock=
-rw-rw—- 1 mysql mysql 7.2K Aug 2 08:46 error.log
-rw-rw—- 1 mysql mysql 18M Aug 2 08:41 ibdata1
-rw-rw—- 1 mysql mysql 5.0M Aug 2 08:46 ib_logfile0
-rw-rw—- 1 mysql mysql 5.0M Aug 2 08:41 ib_logfile1
drwx–x–x 2 mysql mysql 4.0K Jun 27 07:27 mysql/
-rw-rw—- 1 mysql mysql 5 Aug 2 08:46

# mysql -S /var/lib/mysql2/dirty.sock

mysql> show databases;
| Database |
| information_schema |
| mysql |
2 rows in set (0.00 sec)

6. In order for a successful import, we need to be able to drop the log tables. To do this we need to temporarily disable them:

mysql> SET @old_log_state = @@global.general_log;
mysql> SET GLOBAL general_log = ‘OFF’;
mysql> ALTER TABLE mysql.general_log ENGINE = MyISAM;
mysql> SET @old_log_state = @@global.slow_query_log;
mysql> SET GLOBAL slow_query_log = ‘OFF’;
mysql> SET GLOBAL log_slow_queries = ‘OFF’;
mysql> ALTER TABLE mysql.slow_query_log ENGINE = MyISAM;

7. While still in the console for the secondary server, disable foreign key settings and import, then enable foreign key checks again

# mysql -S /var/lib/mysql2/dirty.sock
mysql> SOURCE /root/all-databases.sql;

8. Finally, shut down both servers and move the new mysql directory into place. Upon restart, your conversion and shrinkage should be successful.

# service mysql stop
# killall mysqld
# mv /var/lib/mysql /var/lib/mysql.old
# mv /var/lib/mysql2 /var/lib/mysql
# service mysql start

Note: logging will automatically be turned back on provided you have the appropriate settings defined in your my.cnf

Related Posts

synergy: How to enable crypto (encryption) and generate SSL certificate

The newer Linux versions of the popular mouse/keyboard sharing application “synergy” now has built in encryption. Here’s how to configure it: Just simply passing the –enable-crypto flag on your synergy server without having a proper SSL certificate will result in the inability to connect to clients and generate an error message similar to this in […]

Read More

Change Number Pad Delete (dot) key from a comma in Ubuntu Linux

I recently purchased a new keyboard and updated to the latest Ubuntu, I’m also an avid user of the number pad for quick input when dealing with spreadsheets or accounting. I found that my num pad’s delete key (“.”) was outputting a comma (“,”) instead. Pretty annoying? I agree, but this can be very easily […]

Read More


  • Jan Schmidt on Friday, November 9, 2012

    Thanks! Worked perfectly – except for one warning that I don’t have a mysql.slow_query_log. Maybe 5.1.66 is too old.

  • XXaK on Thursday, November 22, 2012

    Perfect walkthrough except I’m out of space on the disk where mysql data is stored… 🙂

  • Andrés on Wednesday, June 8, 2016

    Very clever solution! thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.