Mysql to Sqlite3
This guide is for users wishing to migrate their WriteFreely instance from a SQL database to SQLite3. The steps involved can be used for any similar migration.
The reason I decided to move my database was that I run it as a single user instance for myself only. I don’t have thousands of posts, nor will I, and I am interested in running a few other services on the same little server. Most of them use or support PostgreSQL, while WriteFreely currently supports SQL or SQLite3. I don’t really want to run more than one database instance if I don’t have to.
Dump Your DB
We will start by dumping the existing SQL database. Then make a copy of that to work on, this way you have a backup if some issue comes up.
On the existing server run as root:
mysqldump --complete-insert=TRUE --extended=insert=FALSE --all-databases > dump.sql.
Then copy that down to your local machine:
scp hostname:/path/to/dump.sql ..
The copy on the server will remain as a backup, do not overwrite it.
Create the New DB
WriteFreely comes with schema files for both supported databases. The command syntax is sqlite3 new-database.extension < sqlite.sql.
On your local machine run:
sqlite3 writefreely.sqlite3 < sqlite.sql.
Convert the Dump
MySQL and SQLite are not perfectly cross compatible, so a little script to convert the dump is required.
I tried to do this myself using sed, vim and even vscode with no luck.
I found an awesome script at https://github.com/dumblob/mysql2sqlite which worked perfect.
On your local machine, assuming a nix based system,
curl https://raw.githubusercontent.com/dumblob/mysql2sqlite/master/mysql2sqlite. Then make it executable
chmod +x mysql2sqlite.
Populate the Database
mysql2sqlite dump.sql | sqlite3 writefreely.sqlite3. You may get a few warnings, I did but they seemed not to affect the resulting database.
Change Your Server Over
Copy the new database file up
scp writefreely.sqlite3 hostname:/path/to/writefreely/..
Then log in to your server and as root:
- Edit your config file to use sqlite3
[database] type = sqlite3 filename = writefreely.sqlite3
- You can leave the other database settings as a backup until you confirm it is working.
- Restart your writefreely instance, i.e.
systemctl restart writefreely.service.
- Check for errors with
journalctl -f -u writefreely
That’s it, if all went well you can stop and uninstall mysql. Also, fun fact, the first half of this post survived the migration.