How to Shrink the InnoDB System Tablespace (ibdata1) in MySQL

In InnoDB the data is organized in tablespaces, which are actual physical files on disk. The system tablespace (ibdata1) stores metadata, doublewrite buffer, change buffer, and undo logs, and can also contain table and index data when innodb_file_per_table is disabled.

1. Introduction

Before attempting to "lose weight" we need to know which files are involved.

System tablespace

Contains InnoDB metadata, doublewrite buffer, change buffer, and undo logs. When innodb_file_per_table is not set, new tables and indexes are also stored here. The default system tablespace file is ibdata1 , whose size and number of files can be defined by innodb_data_file_path .

File‑per‑table tablespace

Enabled by innodb_file_per_table . Each newly created table gets its own .ibd file. This option is on by default since MySQL 5.6.7.

General tablespace

Created with CREATE TABLESPACE , it can hold multiple tables and be placed outside the data directory.

UNDO tablespace

Stores undo logs. By default undo logs reside in the system tablespace, but the number of UNDO tablespaces can be set with innodb_undo_tablespaces (modifiable only at instance initialization, though MySQL 8.0 allows dynamic changes).

Temporary tablespace

Shared temporary tablespace stores non‑compressed user‑created temporary tables and internal temporary tables. Its path, name, size, and attributes are defined by innodb_tmp_data_file_path , defaulting to ibtmp1 in the data directory.

2. Problem Statement

A client reported that in MySQL 5.7 the ibdata1 file kept growing because UNDO tablespace and related parameters were not enabled. The file size needed to be reduced.

3. "Weight‑loss" Plan

Since ibdata1 contains metadata, change buffer, doublewrite buffer, and undo logs, it cannot shrink automatically. The only way is to export the logical data, recreate the instance, and import the data back.

Pre‑reduction size

The original ibdata1 was 512 MiB.

[root@10-186-61-119 data]# ll
total 2109496
-rw-r----- 1 mysql mysql        56 Jun 14 14:26 auto.cnf
-rw-r----- 1 mysql mysql       409 Jun 14 14:26 ib_buffer_pool
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:35 ibdata1
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:35 ib_logfile0
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:35 ib_logfile1
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:32 ib_logfile2
-rw-r----- 1 mysql mysql   12582912 Jun 14 14:26 ibtmp1
...

Full backup

Use mysqldump (Xtrabackup also backs up ibdata1 ).

/data/mysql/3309/base/bin/mysqldump -uroot -p \
 -S /data/mysql/3309/data/mysqld.sock \
 --default-character-set=utf8mb4 \
 --single-transaction --hex-blob \
 --triggers --routines --events --master-data=2 \
 --all-databases > /data/full_$(date +%F).sql

Stop MySQL service

systemctl stop mysql_3309

Remove old instance

[root@10-186-61-119 data]# rm -rf /data/mysql/3309
[root@10-186-61-119 data]# rm -rf /etc/systemd/system/mysql_3309.service

Create new instance

Re‑install MySQL on the same port and configure the following parameters:

innodb_undo_tablespaces = 3

innodb_max_undo_log_size = 4G

innodb_undo_log_truncate = 1

innodb_file_per_table = 1

Resulting directory layout:

[root@10-186-61-119 ~]# ll /data/mysql/3309
total 4
drwxr-x--- 2 mysql mysql    6 Jun 14 14:51 backup
...

Start MySQL service

[root@10-186-61-119 ~]# systemctl start mysql_3309
[root@10-186-61-119 ~]# ps -ef | grep 3309
mysql 7341 1 0 14:52 ? 00:00:01 /data/mysql/3309/base/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf.3309 --daemonize

Import backup

[root@10-186-61-119 data]# /data/mysql/3309/base/bin/mysql -uroot -p \
 -S /data/mysql/3309/data/mysqld.sock < full_2023-06-14.sql

Verification

After the procedure ibdata1 shrank from 512 MiB to 128 MiB.

[root@10-186-61-119 data]# ll
total 1747000
-rw-r----- 1 mysql mysql        56 Jun 14 14:52 auto.cnf
-rw-r----- 1 mysql mysql       422 Jun 14 14:52 ib_buffer_pool
-rw-r----- 1 mysql mysql 134217728 Jun 14 14:57 ibdata1
...

Congratulations, the ibdata1 file has been successfully reduced!

4. Production‑environment Recommendations

For production systems a less aggressive approach is advised:

Provision a new server as a replica, enable innodb_file_per_table and appropriate UNDO parameters.

Take a logical full backup of the primary.

Restore the backup to the replica and set up replication.

Promote the replica to primary.

5. UNDO‑related Parameter Settings

Note: MySQL 5.7 does not support online or offline separation of UNDO tablespaces; they must be defined at initialization.

# Control the amount of UNDO tablespace data used by InnoDB (default 0 = stored in system tablespace)
innodb_undo_tablespaces = 3

# Set the UNDO tablespace size threshold
innodb_max_undo_log_size = 4G

# Enable truncation of UNDO tablespaces that exceed the threshold
innodb_undo_log_truncate = 1

References

Tablespaces: https://dev.mysql.com/doc/refman/5.7/en/innodb-system-tablespace.html