Guide: Ghost blog on Fly.io using MySQL (part 2)

This part 2 in a series about using Ghost on Fly.io. In part 1, we setup a Ghost blog with SQLite as the database. In this post we'll be setting up a MySQL database and configuring our Ghost app to use it.

By the end we'll have a system that looks like:

Step 1: Create a MySQL database

The details on how to create a MySQL database are here: https://fly.io/docs/app-guides/mysql-on-fly/

There are some things you should keep in mind:

• The MySQL database will run on a single VM. If that VM crashes then you'll have to start it again.
• This is NOT managed MySQL. This is a self-hosted MySQL image on a VM. If you need something resilient, then there are plenty of services out there.

Before configuring Ghost, verify that your MySQL database works

The way I do that is to ssh into the machine running the MySQL image, logging into the database and confirming it works:

# SSH into the app
fly ssh console -a <mysql app name>

# From inside the vm run:
mysql <my_database_name> -u root -p
Enter password:

# use the password you set for MYSQL_ROOT_PASSWORD

# run a command like "SHOW DATABASES" just to see that things work
mysql> SHOW DATABASES;

Step 2: Configure Ghost to use MySQL

The next steps are to update you apps fly.toml and set some secrets.

Update the fly.toml with some Ghost config

In the last post we configured the fly.toml to use a SQLite database. We'll modify the env database bit to look like the following:

# fly.toml

app = <ghost app name>
primary_region = 'sea'

[env]
  ##### MYSQL Database
  database__client = 'mysql'
  # database__connection__user = '' # set as secret
  # database__connection__password = '' # set as secret
  database__connection__host = '<mysql app name>.internal'
  database__connection__port = '3306'
  database__connection__database = '<database name>'
  database__connection__useSSL = 'false'
  database__useSSL = 'false'

...

Set some secrets:

Then we need to set a few secrets on our Ghost app.

fly secrets set --stage database__connection__user=root database__connection__password=[[MYSQL_ROOT_PASSWORD]] -a <ghost app name>

This will set secrets on your app for the next deploy. I'm using the root user and the root password (that's what worked for me).

Final Example:

So for example, let's say I created an app called my-ghost-blog-db and I created a database in it called my_db . Then my fly.toml might look like:

# fly.toml

app = 'my-ghost-blog'
primary_region = 'sea'

[build]
  image = 'ghost:5.81.0'

[env]
  ##### MYSQL Database
  database__client = 'mysql'
  # database__connection__user = '' # set as secret
  # database__connection__password = '' # set as secret
  database__connection__host = 'my-ghost-blog-db.internal'
  database__connection__port = '3306'
  database__connection__database = 'my_db'
  database__connection__useSSL = 'false'
  database__useSSL = 'false'
  url = 'https://my-ghost-blog.fly.dev'

[http_service]
  internal_port = 2368
  force_https = true
  auto_start_machines = true
  min_machines_running = 0
  processes = ['app']

[[vm]]
  memory = '1gb'
  cpu_kind = 'shared'
  cpus = 1

And I'd run something like:

fly secrets set --stage database__connection__user=root database__connection__password=mysecurepassword1234 -a my-ghost-blog

Deploy and check that it works

Run fly deploy for your Ghost app and run fly logs in a separate app to check that it's connecting to the database properly.