The 2 a.m. Disk Crisis

It was 2 a.m. and the production root disk was sitting at 94% full. With only 22 GB free on a 338 GB volume, and the usage still creeping up, disaster loomed. An army of voice agents, dozens of them, each running its own script, was scheduled to start dialing in a few hours. Every call they placed writes to the same database. There wasn't enough free space to even take a backup before attempting any operation, and the disk was on track to fill completely long before any resize operation could be completed. The situation was critical, demanding an immediate, safe solution that wouldn't interrupt service.

Fortunately, a second, nearly empty data volume was already mounted on the server. This secondary volume had approximately 294 GB of free space and was essentially idle, presenting a viable target for offloading data. The immediate plan was to move the largest, most active table to this secondary disk, thereby freeing up critical space on the root volume and averting the impending outage.

Strategic Table Migration: The 'Safe Way'

The primary constraint was to avoid any downtime. Simply stopping the MySQL service, copying the table files, and restarting was not an option. The strategy employed was a multi-step process designed for minimal impact, leveraging MySQL's ability to manage table data files. The core idea was to tell MySQL to relocate the table's data files to a new location on the secondary disk and then have it update its internal metadata accordingly. This is distinct from more drastic measures like using mysqldump for a full export/import, which would have been too slow and risked data inconsistency given the table size and the time constraints.

The table in question, call_transcripts within the app_prod database, was a hefty 136 GB. Moving such a large dataset requires careful orchestration. The process began by ensuring the target directory on the new disk was prepared and that MySQL had the necessary permissions to write to it. The commands were executed with precision, each step validated before proceeding to the next.

Step-by-Step Execution

The migration process involved a series of carefully sequenced MySQL commands. The first critical step was to inform MySQL about the intended move without actually performing the data transfer yet. This is achieved using the ALTER TABLE ... DISCARD TABLESPACE command, which detaches the table's data file from its current location in MySQL's internal tracking. However, this command is destructive if not followed by a specific sequence. A more appropriate approach for moving a table to a new disk involves using ALTER TABLE ... RENAME TABLE combined with filesystem operations, or more directly, using ALTER TABLE ... MOVE TABLESPACE in newer MySQL versions, but the approach described here relies on a more fundamental, widely compatible method involving filesystem manipulation and telling MySQL where to find the data.

A more robust and commonly used method for moving a table to a different disk without downtime involves using ALTER TABLE ... ENGINE=InnoDB; followed by ALTER TABLE ... FORCE;. This forces a table rebuild. If the innodb_file_per_table setting is enabled (which is standard practice), this rebuild can be directed to a new location. However, the method described in the source focuses on a direct filesystem move, which can be riskier if not handled with extreme care regarding locking and consistency.

A safer, albeit more involved, approach that *does* avoid downtime and symlinks involves these steps:

  1. Prepare the target directory: Create a new directory on the secondary disk where the table's data file will reside. For example, if the new disk is mounted at /mnt/data, you might create /mnt/data/mysql/app_prod/.
  2. Enable monitoring: Keep a close eye on the primary disk's free space and the table's size throughout the process.
  3. Initiate the move with locking: Use ALTER TABLE app_prod.call_transcripts ENGINE=InnoDB;. This command, when innodb_file_per_table is ON, will essentially prepare the table for a rebuild. Crucially, it acquires a strong lock on the table.
  4. Copy the data file: While the table is locked, use the cp command to copy the actual .ibd file (and related files like .frm) from the original MySQL data directory to the new directory on the secondary disk. This is the core data transfer step.
  5. Update MySQL's metadata: After the copy completes, use ALTER TABLE app_prod.call_transcripts DISCARD TABLESPACE;. This tells MySQL to release its reference to the old data file.
  6. Move the copied file to the correct MySQL location: Manually move the copied .ibd file from your temporary staging area on the new disk to the correct subdirectory within MySQL's data directory structure on the new disk (e.g., /mnt/data/mysql/app_prod/call_transcripts.ibd).
  7. Import the table into its new location: Use ALTER TABLE app_prod.call_transcripts IMPORT TABLESPACE;. This command tells MySQL to now use the data file located at the new path.
  8. Verify and unlock: Once IMPORT TABLESPACE completes successfully, the table should be accessible from its new location. The lock acquired by the initial ALTER TABLE command will be released.

This method ensures that the table remains accessible (though potentially with brief periods of reduced performance during the copy and import phases) and avoids the need for a full service restart or lengthy downtime. The key is that MySQL is aware of the table's data file and can be instructed to use a new file at a new location.

The author's specific commands involved using ALTER TABLE app_prod.call_transcripts ENGINE=InnoDB;, which, when innodb_file_per_table is enabled, forces a table rebuild. This rebuild can be directed to a new location. The process would then involve copying the table's data file (e.g., call_transcripts.ibd) to the new disk, and then using MySQL commands to discard the old tablespace and import the new one. The critical aspect is that MySQL remains online throughout the data copy, and the actual switchover is managed by the ALTER TABLE statements, minimizing the window of unavailability.

Post-Migration and Monitoring

After the table was successfully moved and MySQL was confirmed to be operating correctly with the data on the new disk, the immediate crisis was averted. The free space on the root disk increased significantly, providing breathing room for essential operations like backups and system updates. Continuous monitoring of disk space and database performance remained paramount. The voice agents started their operations as scheduled, writing new call records to the database without interruption. The successful migration prevented potential data loss and avoided the costly downtime that a longer, more traditional migration would have entailed.

The author also noted the importance of having a secondary volume readily available and properly mounted. This proactive infrastructure setup was the crucial enabling factor that turned a potential catastrophe into a manageable, albeit urgent, task. The experience underscored the value of anticipating storage needs and having contingency plans in place, especially for databases with high write loads.

This incident serves as a potent reminder of the fragility of production systems when resource limits are pushed. While the specific commands and sequence are vital, the underlying principle is about leveraging MySQL's features to perform data file management operations with minimal service disruption. The choice to avoid symlinks is also significant; while they can sometimes offer a quick fix, they can introduce complexities and potential issues with database file handling, especially during upgrades or with certain storage engines. A direct move, while more complex, offers greater stability and reliability.