So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for some more space.
This datadir is a file system used ONLY as mysql's datadir so there wasn't anything else to free up.
I had a 4 gig innodb test table that wasn't part of the replication stream so I figured I'd try something to see if it'd work, and being a test environment I wasn't too worried if things went horribly wrong.
Here's the steps I took
- Flushed the table I was about to move
- Placed a read lock on it (even though nothing was writing to it and it wasn't in the replication stream)
- Copied the .frm and .ibd over to a filesystem w/ some spare room
- Unlocked the table
- Truncated that table - this freed up enough space for the optimize to finish have replication start chugging along again.
- Stop slaving/shutdown mysql
- Copy the file out of tmp back to the data dir
- Restart mysql
Nothing shows up in the .err log, things look good. I connect and use mydb; and see the table I was messing with in show tables. But, if I try
select * from testtable limit 10;
I get the error
ERROR 1146 (42S02): Table 'mydb.testtable' doesn't exist
From what I can tell so far I can read from all the other tables just fine and replication started back up w/o any complaints.
Is there anything I can do to recover from this point? I can rebuild it from scratch if need be but was curious what others thought about this venture in general. Was there anything about the series of steps I took that would have ended up w/ more flawless results?
What if this wasn't a test server I couldn't just 'do it live' and see what happens? What would have the best way to free up space temporarily on a production slave if I had to like that?