Part 2 focuses on operational procedures for resizing the InnoDB system tablespace.

The system tablespace is not something we should change casually. The safest approach is to validate current configuration, use controlled shutdown behavior, and verify results after restart.

Pre-Change Checks

Start with current state verification.

systemctl status mysqld
cat /etc/my.cnf

SHOW GLOBAL VARIABLES LIKE 'innodb_data%';
SHOW VARIABLES LIKE 'innodb_autoextend_increment';
SHOW GLOBAL VARIABLES LIKE 'innodb_fast_shutdown';

Why Set innodb_fast_shutdown=0

Before structural storage changes, use a slow shutdown so InnoDB can fully flush and merge internal state.

SET GLOBAL innodb_fast_shutdown = 0;
SHOW GLOBAL VARIABLES LIKE 'innodb_fast_shutdown';

Expected result should show value 0.

Option A: Grow with Autoextend

If your last data file in innodb_data_file_path includes autoextend, InnoDB can grow automatically in increments.

Configuration pattern:

innodb-data-file-path=ibdata1:12M:autoextend

Increment control:

SHOW VARIABLES LIKE 'innodb_autoextend_increment';

Option B: Add a New Data File

When you want explicit file layout expansion:

  1. Stop MySQL.
  2. Update innodb-data-file-path in /etc/my.cnf.
  3. Ensure autoextend is only on the last data file.
  4. Start MySQL.
  5. Validate variables and files.

Example configuration:

innodb-data-home-dir=/var/lib/mysql/innodb/
innodb-data-file-path=ibdata1:12M;ibdata2:12M:autoextend

Post-Change Validation

systemctl status mysqld
ls -lrth /var/lib/mysql/innodb

SHOW GLOBAL VARIABLES LIKE 'innodb_data%';
SHOW VARIABLES LIKE 'innodb_autoextend_increment';

You should confirm that the new file path and home directory values match configuration.

Operational Notes

  • Keep one clear rollback plan before editing storage paths.
  • Avoid mixing multiple storage changes in one restart window.
  • Document old and new layouts for future troubleshooting.

In Part 3, I cover moving UNDO tablespaces and resizing temporary tablespace safely.

<
Previous Post
Practical MySQL Tablespace and Partitioning - Part 1: InnoDB Tablespace Fundamentals
>
Next Post
Practical MySQL Tablespace and Partitioning - Part 3: Managing UNDO and Temporary Tablespaces