A MySQL server was initialized with separate UNDO tablespaces. Users complain that when they roll back large transactions, the time to process the reverts takes too long. The DBA would like to move…

Question

Options

• AYes. Shut down the mysqld process, enable the transportable_tablespace option, and move the UNDO directory to the SSD.
• BYes. Shut down, copy the UNDO tablespaces to the new location, and change the innodb_undo_directory value in my.cnf.
• CNo. The UNDO tablespaces must remain on the same file system as the system tablespaces.

• A
  8% (3)
• B
  75% (27)
• C
  17% (6)

Explanation

Moving InnoDB UNDO tablespaces to an SSD is possible by shutting down MySQL cleanly, physically copying the UNDO tablespace files (named undo_001, undo_002, etc.) to the target SSD path, then updating innodb_undo_directory in my.cnf to point to the new location before restarting - MySQL reads this variable at startup to locate the UNDO tablespaces.

Why A is wrong: The transportable_tablespace feature (used via FLUSH TABLES ... FOR EXPORT) applies to user tablespaces for import/export between servers, not for relocating UNDO tablespaces within the same instance. It's the wrong tool for this job.

Why C is wrong: MySQL explicitly supports separating UNDO tablespaces from the system tablespace via innodb_undo_directory - that's the whole point of initializing with separate UNDO tablespaces in the first place.

Memory tip: Think of UNDO tablespaces like any other file you'd move - Stop, Copy, Config (shut down → copy files → update config). The key variable to remember is innodb_undo_directory; if you can name it, you can set it to any path, including an SSD mount point.

No community discussion yet for this question.