MySQL Disk Full: Moving a 136 GB InnoDB Table to Another Disk

At 2 a.m. the production root filesystem sat at 94% full: 22 GB free on a 338 GB volume, and still climbing. In a few hours an army of voice agents, dozens of them, each running a different script, was scheduled to start dialing, and every call they place writes to the same database. There wasn't enough free space to take a backup before touching anything, and the disk would fill on its own long before I could resize it.

One thing saved the night. A second, nearly empty data volume was already mounted on the box, roughly 294 GB free and doing almost nothing. The plan wrote itself: get the biggest table off the root disk and onto that volume, without deleting a single row and without the risky shortcut most people reach for first. Below is exactly what I did, why the obvious trick would have quietly blown up, and the supported path that held.

What was actually eating the disk

Before moving anything, I wanted to know precisely where the space had gone. One database, app_prod, accounted for 264 GB of the 303 GB in use, and inside it two InnoDB tables did nearly all the damage:

call_transcripts alone was about 40% of the entire root disk. It takes in roughly 35 to 40 GB a day and gets trimmed back to a three-day window by a nightly retention job, so it's huge but bounded. That made it the obvious thing to relocate: move just its .ibd file onto the spare volume and the root disk breathes again, no data touched. The question was how to do it without corrupting anything.

Why not just symlink it

The first idea everyone has is to move the file and drop a symlink in its place. Don't. Symlinking an individual InnoDB .ibd file is unsupported and genuinely unsafe.

InnoDB rebuilds the tablespace file during a number of ordinary operations, OPTIMIZE, some ALTERs, TRUNCATE. When it does, it deletes your symlink and writes a fresh, real file back in the original location, silently refilling the disk you were trying to protect, usually at the worst possible moment. Crash recovery and most backup tools don't expect a symlinked tablespace either. MySQL tolerates symlinking a whole database directory, but never a single tablespace file. It's the kind of fix that works in the demo and detonates a week later in production.

There's a proper mechanism for this, and it has existed since MySQL 8.0: innodb_directories.

The supported way: innodb_directories

InnoDB doesn't identify a tablespace by its path. It identifies it by a space_id baked into the file header. At startup, InnoDB scans the data directory plus any directories listed in innodb_directories, matches each .ibd to its tablespace by that space_id, and updates the recorded path in the data dictionary. Because the dictionary is rewritten to the new location, the move survives restarts and later DDL. This is the behavior the MySQL 8.0 manual documents for moving tablespace files while the server is offline.

Two rules matter, and both bite if you ignore them. First, the directory has to stay in the config permanently, InnoDB needs it at every startup to locate the file, and without it recovery can't find the tablespace. Second, a file-per-table .ibd can only be moved into a directory whose name matches its schema. Our table lives in the app_prod schema, so the destination had to be .../mysql-data/app_prod/, not just any folder. Miss that and InnoDB won't adopt the file.

For this move the specifics were:

• Tablespace app_prod/call_transcripts, space_id = 348 on this instance (yours will differ).
• Old path: /var/lib/mysql/app_prod/call_transcripts.ibd
• New path: /mnt/data-volume/mysql-data/app_prod/call_transcripts.ibd

The procedure

Two phases. Everything that can be prepared with the server running, I did first, so the actual downtime was only the file copy.

Prep, with zero downtime

Create the destination on the data volume, owned by mysql, using the schema-named subdirectory:

mkdir -p /mnt/data-volume/mysql-data/app_prod
chown -R mysql:mysql /mnt/data-volume/mysql-data
chmod 750 /mnt/data-volume/mysql-data /mnt/data-volume/mysql-data/app_prod

Then the gotcha almost no tutorial mentions: on Ubuntu, mysqld runs under an AppArmor profile. If the new path isn't whitelisted, MySQL is denied access and simply fails to start, with an error that sends you looking in the wrong place for an hour. Add the paths to /etc/apparmor.d/local/usr.sbin.mysqld (which the main profile includes):

/mnt/data-volume/mysql-data/ r,
/mnt/data-volume/mysql-data/** rwk,

apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

Register the new directory in /etc/mysql/mysql.conf.d/mysqld.cnf under [mysqld]. This takes effect on the next restart and has to stay there for good:

[mysqld]
innodb_directories = "/mnt/data-volume/mysql-data"

One more reboot-safety detail. The volume is mounted via /etc/fstab with nofail, which means on boot MySQL could start before the mount is ready and then fail to find its tablespace. A small systemd drop-in at /etc/systemd/system/mysql.service.d/require-datavolume.conf forces the ordering:

[Unit]
RequiresMountsFor=/mnt/data-volume

Follow that with systemctl daemon-reload so the drop-in is picked up. Nothing so far has touched the running database.

Cutover, about 11 minutes of downtime

Here's the honest cost: moving one table still means stopping the whole instance. The file can only be moved safely while MySQL is fully offline, so the portal is down for the length of the copy. I did a clean shutdown first so all dirty pages flush and there's no crash recovery to sit through on the way back up.

# Clean shutdown so every dirty page is flushed and no crash recovery is needed
mysql -e "SET GLOBAL innodb_fast_shutdown=0;"
systemctl stop mysql

# mv copies to the target and unlinks the source ONLY after the copy fully
# succeeds, so the original is never at risk mid-transfer. (~136 GB, ~11 min.)
mv /var/lib/mysql/app_prod/call_transcripts.ibd \
   /mnt/data-volume/mysql-data/app_prod/call_transcripts.ibd

chown mysql:mysql /mnt/data-volume/mysql-data/app_prod/call_transcripts.ibd
chmod 640         /mnt/data-volume/mysql-data/app_prod/call_transcripts.ibd

systemctl start mysql

The choice of mv over cp is deliberate and load-bearing. A cross-volume mv copies to the target and only unlinks the source after the copy fully succeeds, so the original is never at risk mid-transfer. It also guarantees the file ends up in exactly one place, which is the whole game: if InnoDB's startup scan ever finds two files with the same space_id, it refuses to start. Never leave a copy behind in the old datadir.

Verify

After MySQL came back up, I confirmed the data dictionary points at the volume and the blob reads back intact:

-- The recorded path now points at the data volume
SELECT t.SPACE, t.NAME, d.PATH
FROM information_schema.INNODB_TABLESPACES t
JOIN information_schema.INNODB_DATAFILES d USING(SPACE)
WHERE t.NAME = 'app_prod/call_transcripts';
-- returns: 348 | app_prod/call_transcripts | /mnt/data-volume/mysql-data/app_prod/call_transcripts.ibd

-- Table reads fine and the blob comes back intact
SELECT id, call_id, LENGTH(transcript) FROM call_transcripts LIMIT 1;

The error log showed a clean start, "ready for connections", with no tablespace warnings. The voice agents started on schedule.

The result

From 94% to 52% by moving a single file, with the database serving traffic again after roughly 11 minutes down and not one row deleted.

Rollback, if it goes sideways

The reason this is safe to attempt on a bad night: the data is only ever in one place, so backing out is symmetrical. If MySQL fails to start after the move, stop it, move the file home, drop the config line, and start again.

systemctl stop mysql
mv /mnt/data-volume/mysql-data/app_prod/call_transcripts.ibd \
   /var/lib/mysql/app_prod/call_transcripts.ibd
chown mysql:mysql /var/lib/mysql/app_prod/call_transcripts.ibd
# remove the innodb_directories line from mysqld.cnf, then:
systemctl start mysql

Lessons and follow-ups

This bought headroom; it isn't a cure. A few things I noted for the days after, and they're the parts worth stealing whether or not you ever relocate a tablespace.

It's headroom, not a fix. The other 112 GB table and the ongoing 35 to 40 GB a day of churn still live on the root disk. The root will climb again. Keep watching df -h / and plan the real capacity work rather than treating the move as the end of it.

Index the retention delete. The nightly job that trims the table to three days runs a delete keyed on date_entered:

DELETE FROM call_transcripts WHERE date_entered < (UTC_TIMESTAMP() - INTERVAL 3 DAY)

That column had no index, so the delete was a full table scan across roughly 4.7 million rows every night, which is both slow and a needless load spike. The fix is a safe online DDL:

ALTER TABLE call_transcripts ADD INDEX idx_call_transcripts_date_entered (date_entered);

Batching the delete in chunks (a loop of DELETE ... LIMIT 5000) also keeps undo and redo from spiking on a big purge. And once a tablespace is relocated, be careful with casual OPTIMIZE or ALTER rebuilds on it until you've confirmed they respect innodb_directories, the same rebuild behavior that makes symlinks unsafe is worth a second thought here too.

Frequently asked questions

Can I symlink a MySQL .ibd file to another disk?

No, not safely for a single tablespace. InnoDB rewrites the .ibd on operations like OPTIMIZE, ALTER, and TRUNCATE, which replaces the symlink with a fresh real file back on the original disk and silently refills it. Use innodb_directories instead.

How do I move a single InnoDB table to another disk in MySQL 8.0?

Stop the instance cleanly, move the .ibd into a directory named after its schema on the target disk, register that directory in innodb_directories, and restart. InnoDB matches the file by its space_id and updates the data dictionary to the new path.

Does moving an InnoDB tablespace require downtime?

Yes. Even for one table the file can only be moved while the whole instance is stopped, so the database is down for the length of the copy. Here, 136 GB across volumes took about 11 minutes.

Will the move survive a restart and future ALTERs?

Yes, provided innodb_directories stays in the config permanently. InnoDB rescans it at every startup and finds the tablespace by space_id. Remove that line and recovery can't locate the file.

What if the .ibd ends up in two places?

MySQL won't start. If the scan finds two files with the same space_id, it errors out on duplicate tablespaces. That's exactly why you mv the file instead of copying it.