Migrating Ghost from SQLite to MySQL

I’ve been using Ghost with SQLite for a year and a half and I haven’t had any issues related to SQLite at all. I would even say this is a very good choice for Ghost and I realize now that I’ve made a post about it.

I want to switch back to MySQL because I feel more confident using it. Especially for backups.

My current setup is a LXD host server with a bunch of containers managed by Ansible. My Ghost container has access to an existing MySQL container, so I created a new database in a few seconds. All my backup workflow is already functional for MySQL, so switching really does not cost me much. That may not be the case for everyone though! If your Ghost website is hosted on a single server, SQLite is still a good choice.

Anyway, as expected it’s the exact same operation, but reversed.

Step 1: Export your content to a JSON file. This includes everything except images.

Step 2: Create a MySQL database and a MySQL user for Ghost

Step 3: Update your config.production.json file.

Before:

"database": {
    "client": "sqlite3",
    "connection": {
      "filename": "./content/data/ghost.db"
    }
  }

After:

"database": {
    "client": "mysql",
    "connection": {
      "host": "mysql.host",
      "port": 3306,
      "user": "ghost",
      "password": "thisisnotmypasswordbtw",
      "database": "ghost"
    }
  }

Step 4: Restart Ghost.

For me it was:

root@ghost ~# systemctl restart ghost

Step 5: Create a new user and login to the admin

Step 6: Go the lab and click “Delete all content”. This deletes the pre-included posts and stuff.

Step 7: Import your JSON file in the lab. This should import back everything. If you created a user with the same email address it will soft-fail, but don’t worry! Just make sure to complete your profile again.

That’s it, everything should be the same as before, since the media files haven’t moved.

Make sure to setup a regular automated backup of the MySQL database. 🤓