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/temp.pid –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 temp.pid

# 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> SET FOREIGN_KEY_CHECKS=0;
mysql> SOURCE /root/all-databases.sql;
mysql> SET FOREIGN_KEY_CHECKS=1;

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

Share on Facebook

Tweet