Understanding and Managing Undo Tablespaces in MySQL 8.0

Before discussing undo tablespaces, the article introduces the Undo Log as MySQL's reverse‑operation log that records the logical steps needed to roll back a transaction; a background thread cleans up committed undo entries so the log can be reused.

Undo related concepts

undo log segment – a reusable collection of undo logs, occupied by a single transaction at a time.

rollback segment – the physical storage area for undo logs.

undo tablespace – the on‑disk representation of a rollback segment after it is detached from the system tablespace.

The relationship can be visualised as: undo log → undo log segment → rollback segment → undo tablespace .

Undo Log development history

MySQL 5.5 and earlier: undo logs lived in the shared system tablespace (ibdata1), causing I/O concentration and uncontrolled growth.

MySQL 5.6: undo logs were moved to a separate undo tablespace, dispersing I/O load.

MySQL 5.7: introduced automatic shrinkage of undo tablespaces.

MySQL 8.0: added SQL‑level management of undo tablespaces and several new parameters.

Improvements in MySQL 8.0

Two default undo tablespaces are created at startup (undo_01 and undo_02). They cannot be dropped directly via SQL because their names are reserved.

The innodb_rollback_segments parameter now applies per tablespace, allowing up to 128 rollback segments per undo tablespace.

The innodb_undo_log_truncate parameter is enabled by default, letting MySQL automatically shrink undo tablespaces.

The previously deprecated innodb_undo_tablespaces dynamic‑management variable has been removed.

Managing Undo Tablespaces

1. Create an undo tablespace

mysql> CREATE UNDO TABLESPACE undo_ts1 ADD DATAFILE 'undo_ts1.ibu';

Result:

Query OK, 0 rows affected (0.47 sec)

2. View undo tablespaces

SELECT * FROM information_schema.innodb_tablespaces WHERE SPACE_TYPE='undo'\G

Typical output shows the space ID, name, row format, page size, etc.

3. Change the storage directory

SELECT @@innodb_undo_directory;

@@innodb_undo_directory | ./

To move the undo tablespace, stop MySQL, set a new directory, move the files, and restart.

4. Delete an undo tablespace

ALTER UNDO TABLESPACE undo_ts2 SET INACTIVE;

DROP UNDO TABLESPACE undo_ts2;

Both commands return “Query OK”.

5. Move an undo tablespace

Stop the MySQL service.

Set innodb_undo_directory to the new path.

Move the existing undo files to the new directory.

Start the MySQL service.

After moving, verify with:

SELECT file_name, file_type, tablespace_name, status FROM files WHERE file_type='undo log';

which lists the undo files in their new location.

Conclusion

The article summarises how MySQL 8.0 has matured its undo tablespace handling, offering better I/O distribution, automatic shrinking, and flexible per‑tablespace configuration, making undo management more robust for high‑concurrency workloads.