Ditching MySQL in favor of SQLLite3 for Writefreely
I previously chose to run MySQL because of the added challenge of managing it. I love challenges. However, it has become a burden because not only do I need to set it up when I have a new VPS, but also it consumes a lot of memory and I don't need that. I now prefer to keep things simple.
Funnily enough, by choosing to keep things simple, I'm making things a bit challenging for myself yet again. I've spun a new VPS on Hetzner and got a Writefreely instance up. Next is figuring out how to restore the data.
When I migrated from AWS->DO, I dumped the MySQL DB from AWS and restored it into DO. That's not possible now that we're changing the SQL engine. Luckily, I found mysql2sqlite. A savior!
It wasn't a smooth process because that tool is old and there were some INSERT statements that contain binary values as such:
INSERT INTO `table` VALUES (1,_binary '<BINARY>',_binary '<BINARY>');
So, I just dump the posts table:
sudo mysqldump --skip-extended-insert --no-create-info --compact writefreely posts > dump_$(date -u +"%Y-%m-%d_%H%M%S")_posts-only.sql
Convert it into sqlite format:
./mysql2sqlite dump_2025-01-01_085842_posts-only.sql | pbcopy
Then run the INSERT statements against the sqlite3 DB in my Hetzner VPS.
Voila! We're back in business.