Practical MySQL Tablespace and Partitioning - Part 3: Managing UNDO and Temporary Tablespaces
Part 3 covers UNDO and temporary tablespace management in MySQL 8.0.
These two areas are operationally important: UNDO affects transaction rollback and MVCC behavior, while temporary tablespace growth can become a capacity issue in busy environments.
Move UNDO Tablespaces
Typical workflow:
- Inspect current undo files.
- Set
innodb_fast_shutdown=0. - Stop MySQL.
- Move undo files to target directory.
- Configure
innodb-undo-directory. - Start MySQL and validate.
Command pattern:
ls -lrth /var/lib/mysql/undo*
SHOW GLOBAL VARIABLES LIKE 'innodb_fast_shutdown';
SET GLOBAL innodb_fast_shutdown = 0;
sudo systemctl stop mysqld
sudo mv /var/lib/mysql/undo_* /var/lib/mysql/innodb/
sudo chown -R mysql:mysql /var/lib/mysql
Configuration snippet:
innodb-undo-directory=/var/lib/mysql/innodb/
Validate UNDO Move
sudo systemctl start mysqld
Then check:
SHOW GLOBAL VARIABLES LIKE 'innodb_undo%';
Resize Temporary Tablespace
If you need to cap or tune temporary tablespace growth, configure innodb-temp-data-file-path.
Example:
innodb-temp-data-file-path=ibtmp1:12M:autoextend:max:2G
Verification queries:
SELECT @@innodb_temp_tablespaces_dir;
SELECT @@innodb_temp_data_file_path;
SELECT file_name, tablespace_name, initial_size,
total_extents * extent_size AS totalsizebytes,
data_free, maximum_size
FROM information_schema.files
WHERE tablespace_name='innodb_temporary'\G
Practical Guidance
- Use predictable directory standards for easier backup and incident response.
- Keep ownership/permissions checks in every move procedure.
- Always validate both config variables and actual file placement.
In Part 4, I move to file-per-table and general tablespace operations.