Moving a 136 GB MySQL table to another disk at 2 a.m. (the safe way, not a symlink)

Shahab Gohar — Sat, 25 Jul 2026 05:41:30 +0000

Heads up: the database, table, and column names in this post are dummies. I've swapped the real production names for generic ones (app_prod, call_transcripts, call_records, and so on) so nothing internal leaks. Every command, number, and step is exactly what I ran.

It was 2 a.m. and the production root disk was sitting at 94%. Twenty-two gigabytes free on a 338 GB volume, and still creeping up. In a few hours an army of voice agents, dozens of them, each running its own script, was scheduled to start dialing, and every call they place writes to the same database. There wasn't even enough free space to take a backup before touching anything, and the disk was going to 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, doing basically nothing. So the plan was easy to say and a little scary to run: get the biggest table off the root disk and onto that volume, without deleting a single row, and without the shortcut that would've quietly blown up a week later.

Here's exactly how it went.

First: what was actually eating the disk

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

Table	On-disk `.ibd`	Rows	Notes
`call_transcripts`	136 GB	~4.7 M	one TEXT transcript blob per call
`call_records`	112 GB	~33 M	index-heavy, most of it is indexes

call_transcripts alone was about 40% of the entire root disk. It ingests 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 only question was how to do it without corrupting anything.

The trap: don't symlink the `.ibd`

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 bunch 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 save, 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 in production.

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

The right 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 it scans the data directory plus any directories listed in innodb_directories, matches each .ibd to its tablespace by that space_id, and rewrites the recorded path in the data dictionary. Because the dictionary is updated, the move survives restarts and later DDL. This is the behavior documented in the MySQL 8.0 manual for moving tablespace files while the server is offline.

Two rules matter, and both bite if you skip them:

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.
A file-per-table .ibd can only be moved into a directory named after its schema. My 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 I could prepare with the server running, I did first, so the actual downtime was just 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

Now 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 just fails to start, with an error that sends you hunting 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

Then 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 app 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", no tablespace warnings. The voice agents started on schedule.

The result

Metric	Before	After
Root `/` used	303 GB (94%)	167 GB (52%)
Root `/` free	22 GB	157 GB
`call_transcripts.ibd`	on root disk	on data volume

From 94% to 52% by moving a single file, database back up after ~11 minutes down, and not one row deleted.

Rollback, because it's 2 a.m. and you're human

The reason this is safe to try 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, 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 I wrote down afterward

It's headroom, not a cure. The other 112 GB table and the ongoing 35 to 40 GB/day of churn still live on the root disk. It'll climb again. Keep watching df -h / and plan the real capacity work instead of treating the move as the finish line.

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 ~4.7 M rows every night. 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.

If you'd rather skip the 2 a.m. narration and just keep the steps, there's a cleaner reference version on my site: Moving a MySQL InnoDB table to another disk.

Day to day I build and run automation, CRM, and AI systems for small teams. Most of it is quiet, well-tested plumbing; the rest is nights like this one, and staying calm through those is half the job. If that's the kind of help you need, I'm at shahabgohar.dev.

DEV Community: Shahab Gohar