Running Ghost with MySQL on a Raspberry Pi 2

I'm a big fan of cloud-based hosting. As a matter of fact, this website (based on Ghost) used to be hosted on Microsoft Azure. This worked really well for a while, especially when combined with automated builds and deployment from a connected git repository on BitBucket. What I didn't like about Microsoft Azure is that its hard to predict costs. Despite only moderate traffic (300+ visitors a day), the cost of hosting on Azure varied between 12 and 35 dollars a month (with spikes up to 50 dollars).

Looking for an excuse to try something else, I recently got my hands on a Raspberry Pi 2 to replace my Windows-based home automation server. I was impressed with the ability of this tiny, cheap (about 50 bucks) device to function as a lightweight server. A Raspberry Pi 2 only pulls a few watts of power and doesn't have any moving parts (like a fan or a hard drive). This makes it quite reliable and energy-efficient, and is ideal for simple server-tasks, like home automation and serving simple websites. I quickly purchased another Raspberry Pi 2 and decided to try to get my blog to run on it.

1. Getting up-and-running

Getting a Raspberry Pi 2 up-and-running out of the box is really simple. Purchase or acquire the following hardware:

  • A Raspberry Pi 2
  • A HDMI-cable to connect the Raspberry Pi to a monitor
  • A micro SD-card of sufficient size (at least 4 GB) of a reliable brand
  • A box to case the Raspberry Pi in. I opted for a green case and cover from Adafruit
  • A keyboard and mouse with a USB-dongle
  • A micro USB power-adapter;

Since the Raspberry doesn't come with any storage - that's what you need the SD-card for - you need to download an operating system of your preference. I opted for Raspbian, the default Debian-based OS for the Raspberry. More information on installing Raspbian (with NOOBS) can be found here, but the gist of it is that you need to copy the contents of the zip-file to a formatted SD-card and plug it into the Raspberry's SD-card slot. Connect the HDMI-cable, and the system will boot.

Generally speaking, configuring Debian, and installing packages, works best from the command-line. So open a terminal window (top left of the screen) and continue from there.

2. Securing the installation

Raspbian is already pretty secure out of the box. But there's a few things that we can optimize before we continue. Begin by changing the configuration and password. Enter:

sudo raspi-config

A number of thing should be changed here:

  • Update your timezone and keyboard settings, if you need to
  • Set a descriptive hostname to identify it on your network and connect to with SSH (Advanced > Hostname)
  • Change the Pi-password to something else, so that the default password stops working
  • Enable SSH so you can connect to the Raspberry Pi from another machine (using e.g. Putty).
  • If you are confident that you're not going to use the desktop, but will configure everything from the command-line (or through SSH from another machine), disable 'Boot to desktop' to free up more resources. You can also set the memory split to 16, so that more RAM is freed for system-use (Advanced > Memory Split)

In addition to changing the password for the default Pi-user, I opted to remove this account altogether and replace it with a root user with a custom name. This requires only a few commands, but is a bit outside of the scope of this post. You can read more in Matt Wilcox' excellent post, which also inspired parts of this post.

If you don't feel like setting up an extra monitor and keyboard/mouse for the Raspberry Pi, you can download Putty and connect with SSH to its local IP-address or hostname. You will be prompted to log in with a valid root account ('pi' and your password), after which you can proceed entering commands.

3. Updating Node.js

Ghost runs on Node.js, preferably in the 0.10.x-range. But since Node.JS has seen a major spike in new versions (it is currently at 4.3.1), I decided to try if I could get the site running on the latest version instead. Fair warning; this is supported, but not recommended by the Ghost-team:

$ cd /home/pi/Downloads 
$ wget *or specify a more recent version*
$ tar -xvf node-v4.3.1-linux-armv7l.tar.xz
$ cd node-v4.3.1-linux-armv7l
$ sudo cp -R * /usr/local
$ node -v

That's it! The final command should yield the version you just installed (e.g. 4.3.1 in this example). Node.js will start automatically start on boot, so there's nothing else you need to do here.

4. Installing MySQL

By default, Ghost uses SQLite3 to store data in a local db-file (/content/db). This works pretty well for a basic blog, but a more advanced blog benefits from using MySQL (or another data-provider) to store data separately from the site in a shared or dedicated MySQL-instance. Thankfully, it's easy to set this up. Begin by installing the MySQL package:

sudo apt-get install mysql-server

Once installed, access MySQL and create a database for your blog:

$ mysql -u root -p
Enter password: ***********
mysql> CREATE DATABASE [databasename];
mysql> USE [databasename];

Create a user for this database, so Ghost doesn't have to access the database with a root-account:

mysql> CREATE USER '[username]'@'localhost' IDENTIFIED BY '[password]';
mysql> GRANT ALL PRIVILEGES ON [databasename].* TO '[username]'@'localhost'
mysql> quit

Run the following command and answer the questions as per your preferences to further lock down MySQL:

sudo mysql_secure_installation

5. Installing Ghost

Installing Ghost is easy, getting it to work is a bit harder. But let's start with the install. From the command line:

$ cd /home/pi/Downloads
$ wget
$ unzip -d ghost
$ cd ghost

Normally, you would have to run 'npm install --production' now to install the required dependencies. I ran into quite a few difficulties here, as Ghost depends on SQLite3 and fails to build because this package isn't available for the ARM-architecture of the Raspberry. You either have to build from the source directly (which turned out to be a headache) or work around it. Because I was going to use MySQL instead of SQLite3 anyways, I decided to remove the dependency by hacking it out Certainly not a very elegant solution, but it worked for me. In the installation-folder of Ghost, open package.json and remove the line that references SQLite3 (Ctrl+W):

$ sudo nano package.json

Remove the lines that reference SQLite3 and save (Ctrl+X). Open npm-shrinkwrap.json:

$ sudo nano npm-shrinkwrap.json

Locate the reference to SQLite3 (Ctrl+W) and remove the entire tree below it (Ctrl+K). Save (Ctrl+X). After this, Ghost can be installed locally with the following command. The former disables a version-check, while the latter actually installs Ghost and downloads all dependencies:

$ GHOST_NODE_VERSION_CHECK=false && npm install --production

Because we're going to connect to a local MySQL instance, we have to change the configuration in config.js. Open the file (sudo nano config.js), and change the configuration for the 'Production'-environment to:

database: {
    client: 'mysql',
    connection: {
        host: 'localhost',
        user: '[username in MySQL]',
        password: '[pass in MySQL]' ,
        database: '[database name in MySQL]',
        charset: 'utf8'
    fileStorage: true,
    debug: false

Also make sure to change the site URL (e.g. localhost or some public URL), the server's IP and (optionally) a port and - if you need to - the configuration for sending mails. Ghost can send e-mails locally even without this configuration, however. Save the file (config.js) and start Ghost for the 'production' environment:

$ npm start --production

If all goes well, Ghost will migrate and seed the database with tables and happily inform you that it's running from the specified IP. From there you can proceed by installing a template or import data from an existing Ghost-instance using the import/export functionality in the admin. It only took me a few minutes to migrate my blog from Azure to my local Raspberry.

6. Backing up your data

Having a locally hosted blog is nice and all, but it's good practice to back up your data frequently. A Raspberry is obviously less reliable than a cloud-based SaaS-solution like Azure. You can either manually export your blog data periodically, or run a cronjob to back-up your database. Create a script:

$ sudo nano /home/pi/

Add the following to create a SQL-dump of your database and rsync the files of your site:

mysqldump -u [MySQL admin user] -h localhost -p[MySQL adamin password] [database] | gzip database.sql.gz
rsync -rtc /home/pi/ghost [destination folder to backup to to]

Save the file (Ctrl+X) and make sure that this file can be executed:

$ sudo chmod +x /home/pi/

You can instruct Debian to run this script periodically by setting up a global cronjob:

$ sudo crontab -e

Add the following line to create a backup of the database and the site every night, at 1:00 AM:

0 1 * * *      /home/pi/

You can make this script as fancy as you like. To limit the impact of SD-card corruption, or a hardware failure, I decided to write the backup to a file-share from a local NAS. This allows me to quickly restore the site and data on a new SD-card, or even a spare Raspberry Pi 2.

7. Finishing touches

A Raspberry is very tiny, so its easy to stick them somewhere invisible. I put them in a closet, and added velcro strips so that I can easily remove or add Raspberry's (for maintainance or replacement).

Christiaan Verwijs
Christiaan Verwijs

Scrum Master, Trainer, Developer & founder of Agilistic