Turns out MySQL’s InnoDB engine isn’t (by default) very enthusiastic to give back disk space if after mass deletions (say, a DELETE * or a TRUNCATE TABLE).
The fix is a little unsettling, but it’s also simple (and with a modestly size db – fast).
Back up the db (you should doing this anyway, but now is a nice time to do it again.)
mysqldump -uroot -p --opt --skip-lock-tables -f --quote-names --databases yourDB > yourDB.sql
Confirm you made a backup. Then drop the databases you just backed up (I told you there was an un-nerving part).
Shutdown the MySQL server – whatever your preferred method is. I like to click ‘stop’ in Webmin, maybe you like the command line.
Remove all innodb files (it’s pretty obvious which ones they are)
Restart the MySQL server
Reimport your database
mysql -uroot -p < yourDB.sql
While you’re here – hop over to my.cnf and add
innodb_file_per_table so next time
OPTIMIZE TABLE will actually do what you expect it to do.
Today’s meeting free. I’ve blocked off the entire day to work on 1 thing. A little voice told me to hit play on my All
Too Much Joy playlist. Perfect.
If I hit the milestone early, I think I’ll pull
UHF off the Netflix.
My favorite lyric from today’s TMJ all discography listening session comes from “You Can’t Die from a Broken Heart” off the “The Its” album.
“You might even forgive me some day / But some day’s too far away / To hold us together today”
Oh sure – I’ve
3 Large Strongspaces backing up all my websites and working files nightly. I’ve also got a 2TB Western Digital drive pulling nightly backups from all the computers in the house. Plus, a couple portable drives for the road.
To that mix, I’ve added a
SentrySafe Fire-Safe Waterproof Data Storage Chest with a 1TB Western Digital portable drive.
The interior of the SentrySafe is about 5″ square – perfect size for the Western Digital drive. Actually – it should fit about 6 of these drives as time goes on.
A couple quick edits to my backup script and the new safe drive is part of the mix.
I feel safer already.
More good stuff from
I’ve been working with the Star Tribune on a number of very exciting new projects.
The first of these project is an ebook version of
Pinecone Passions -the short romance story written by the Star Tribune community for Valentines day, 2011.
It’s now available in the Amazon Kindle store for $2.99 – with other ebook stores coming soon.
Pinecone Passions in Amazon’s Kindle store