As long as my site has been around, it’s been powered by MySQL as the back-end for the database that drives the site. I didn’t want to deal with plain-text markdown files for blogposts, so yes — I do still use a database!
I was recently thinking about this requirement, as upgrading my server’s Linux distribution would upgrade the built-in version to MySQL 8.0 — and I wasn’t looking forward to that (even though this would probably be fine?).
All of this for a site that probably didn’t even need a dedicated database service running. I knew I didn’t need MySQL, because as a native app developer I’ve been using alternatives like SQLite for years.
My only concern was scaling — and this server doesn’t really need to scale, because it’s a simple site with a ton of caching and Cloudflare in front of it. If my site goes down due to too much traffic, so be it. I’m afraid there isn’t much I can do that isn’t expensive for me to set up. So for this setup, SQLite is a good fit. It absolutely is not for all sites, obviously.
So finally I migrated to a SQLite database instead, and removed the MySQL package from my server entirely. This has improved the memory usage of the server, and allowed me to omit the “make a dump of the database” part of my backup process from the list of steps required.
All I had to do was dump my database and run:
./mysql2sqlite nicoverbruggen_dump.sql | sqlite3 database.sqlite
I made sure everything worked as expected — made a few minor tweaks — and then re-deployed the site. And here we are! You’re looking at a page that connected to the tiny little SQLite database to fetch this post’s contents. Mission accomplished!
A special shout out has to go to the mysql-to-sqlite3 Python tool and mysql2sqlite script, which allowed me to easily migrate from a SQL dump to a .sqlite file.