HOWTO run the latest Ghost with a private MySQL instance on Opalstack

sean

As of v5.21, Ghost is no longer compatible with MariaDB and will not run with SQLite in production mode - it will only run with MySQL v8 or higher. Opalstack doesn't provide a managed MySQL service at this time so if you want to run the latest Ghost you'll need to run your own private MySQL instance as a custom app.

The guide that follows is a modified version of a procedure originally written by @nick. The main differences are:

The shell commands have been broken up into discrete numbered steps.
The various config files and scripts are created using heredocs.
Start and stop scripts are included for the MySQL app.
Node.js is installed in a subdirectory "node" of the Ghost app with all modules installed globally.
The Ghost instance is installed in a subdirectory "ghost" of the Ghost app.
Email configuration is included for Ghost.

The guide will set up 2 applications: a MySQL server running on a custom port and a Ghost instance. The various commands use the following placeholders which you'll need to replace with your own values:

GHOSTUSER: shell user name
GHOSTAPP: nginx proxy app 1
GHOSTPORT: the port assignment for GHOSTAPP
DBAPP: nginx proxy app 2
DBPORT: the port assignment for DBAPP
DBAPPROOTPWD: mysql root user password
GHOSTDBNAME: ghost database name
GHOSTDBUSER: ghost db user name
GHOSTDBUSERPWD: ghost db user password
YOURDOMAIN.COM: domain to use for the app
SMTPSERVER: your Opalstack SMTP server, either smtp.us.opalstack.com or smtp.de.opalstack.com.
MAILBOX: your Opalstack mailbox name
MAILBOXPWD: your Opalstack mailbox password

Pre-installation

Create a new shell user, e.g. GHOSTUSER
Create two NGINX proxy port apps GHOSTAPP and DBAPP, noting the assigned ports for each.
Add your site domain.
Create a site to serve GHOSTAPP on YOURDOMAIN.COM with Let's Encrypt SSL enabled.
Creata a mailbox and email address.
SSH to your server using shell user GHOSTUSER. The rest of the procedure is presented as commands run as your shell user.

Setup MySQL Instance

Create the MySQL config file:

cd ~/apps/DBAPP
mkdir -p {etc,var,tmp}
cat << EOF > ~/apps/DBAPP/etc/my.cnf
[client]
port = DBPORT
socket = /home/GHOSTUSER/apps/DBAPP/var/mysql.sock

[mysqld]
port = DBPORT
socket = /home/GHOSTUSER/apps/DBAPP/var/mysql.sock
tmpdir = /home/GHOSTUSER/apps/DBAPP/tmp
datadir = /home/GHOSTUSER/apps/DBAPP/data
innodb_log_group_home_dir = /home/GHOSTUSER/apps/DBAPP/data
log_error = /home/GHOSTUSER/logs/apps/DBAPP/maria.log
pid_file = /home/GHOSTUSER/apps/DBAPP/var/pid
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[mysqld_safe]
log-error = /home/GHOSTUSER/apps/DBAPP/maria.log
pid-file = /home/GHOSTUSER/apps/DBAPP/var/pid
EOF

Download and setup MySQL:

cd ~/apps/DBAPP
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.33-el7-x86_64.tar.gz
tar xzf mysql-8.0.33-el7-x86_64.tar.gz --strip-components=1
~/apps/DBAPP/bin/mysqld --defaults-file=/home/GHOSTUSER/apps/DBAPP/etc/my.cnf --initialize-insecure --user=$USER --datadir=$PWD/data --tmpdir=$PWD/tmp

Start the MySQL instance:

nohup $HOME/apps/DBAPP/bin/mysqld_safe --defaults-file=$HOME/apps/DBAPP/etc/my.cnf --socket=$HOME/apps/DBAPP/var/mysql.sock > $HOME/logs/apps/DBAPP/nohup.out 2>&1 &

~/apps/DBAPP/bin/mysql -P DBPORT -S $HOME/apps/DBAPP/var/mysql.sock -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'DBAPPROOTPWD';
create database GHOSTDBNAME;
create user 'GHOSTDBUSER'@'localhost' identified by 'DBAPPUSERPWD';
grant usage on *.* to 'GHOSTDBUSER'@'localhost';
grant all on GHOSTDBNAME.* to 'GHOSTDBUSER'@'localhost';
FLUSH PRIVILEGES"

Create the start and stop scripts:

cat << EOF > ~/apps/DBAPP/start
#!/bin/bash
trap '' HUP
nohup /home/GHOSTUSER/apps/DBAPP/bin/mysqld_safe --defaults-file=/home/GHOSTUSER/apps/DBAPP/etc/my.cnf --socket=/home/GHOSTUSER/apps/DBAPP/var/mysql.sock > /home/GHOSTUSER/logs/apps/DBAPP/nohup.out 2>&1 &
EOF
chmod +x ~/apps/DBAPP/start

cat << EOF > ~/apps/DBAPP/stop
#!/bin/bash
/home/GHOSTUSER/apps/DBAPP/bin/mysqladmin -u root -P DBPORT -S /home/GHOSTUSER/apps/DBAPP/var/mysql.sock -p shutdown
EOF
chmod +x ~/apps/DBAPP/stop

Make a note of the following commands which you will use to start and stop the database server when needed:
- Start: ~/apps/DBAPP/start
- Stop: ~/apps/DBAPP/stop

Setup Ghost Instance

Get and unpack Nodejs v16.20.2:

cd ~/apps/GHOSTAPP/
mkdir node
wget https://nodejs.org/download/release/latest-v16.x/node-v16.20.2-linux-x64.tar.xz
tar xf node-v16.20.2-linux-x64.tar.xz --strip 1 -C $PWD/node

Export for Node PATH, update NPM and install some Ghost requirements via SCL:

export PATH=$HOME/apps/GHOSTAPP/node/bin:$HOME/apps/GHOSTAPP/node/node_modules/.bin:$PATH
scl enable devtoolset-10 -- npm install -g npm@9.8.1
scl enable devtoolset-10 -- npm install -g @vscode/sqlite3
scl enable devtoolset-10 -- npm install -g ghost-cli@latest

Install the Ghost instance:

mkdir ./ghost && cd ./ghost
~/apps/GHOSTAPP/node/node_modules/.bin/ghost install local --port GHOSTPORT --log file --no-start

Replace the sqlite3 package:

rm -r ~/apps/GHOSTAPP/ghost/current/node_modules/sqlite3
cp -r ~/apps/GHOSTAPP/node/node_modules/@vscode/sqlite3 ~/apps/GHOSTAPP/ghost/current/node_modules/

Create the Ghost config file for production, disable default config files:

cat << EOF > ~/apps/GHOSTAPP/ghost/config.production.json
{
  "url": "https://YOURDOMAIN.COM",
  "server": {
    "host": "127.0.0.1",
    "port": GHOSTPORT },
  "database": {
    "client": "mysql",
    "connection": {
      "host": "127.0.0.1",
      "port": DBPORT,
      "user": "GHOSTDBUSER",
      "password": "GHOSTDBUSERPWD",
      "database": "GHOSTDBNAME"
    }
  },
  "paths": {
    "contentPath": "/home/GHOSTUSER/apps/GHOSTAPP/ghost/content/"
  },
  "logging": {
    "level": "info",
    "rotation": {
      "enabled": true
    },
    "transports": [
      "file",
      "stdout"
    ],
    "path": "/home/GHOSTUSER/logs/apps/GHOSTAPP/"
  },
 "mail": {
   "transport": "SMTP",
   "options": {
     "host": "SMTPSERVER",
     "port": 587,
     "secure": false,
     "auth": {
       "user": "MAILBOX",
       "pass": "MAILBOXPWD"
     }
   }
 }
}
EOF
find ~/apps/GHOSTAPP/ghost/versions/*/core/shared/config/env/ -type f -name "*.json" -exec sh -c 'mv "$1" "${1%.json}.json-backup"' _ {} \;

Create start and stop scripts:

cat << EOF > ~/apps/GHOSTAPP/start
#!/bin/bash
PATH=~/apps/GHOSTAPP/node/bin:\$PATH
NODE_ENV=production ~/apps/GHOSTAPP/node/bin/ghost start -d ~/apps/GHOSTAPP/ghost --no-setup-linux-user
EOF
chmod +x ~/apps/GHOSTAPP/start

cat << EOF > ~/apps/GHOSTAPP/stop
#!/bin/bash
PATH=~/apps/GHOSTAPP/node/bin:$PATH
~/apps/GHOSTAPP/node/bin/ghost stop -d ~/apps/GHOSTAPP/ghost --no-setup-linux-user
EOF
chmod +x ~/apps/GHOSTAPP/stop

Start Ghost:

~/apps/GHOSTAPP/start

Immediately visit your Ghost admin URL https://YOURDOMAIN.COM/ghost/ and create your admin user. If this process appears to hang then after about 15-20 seconds click the button again and you should be directed to a Ghost login page.

Create a cron job to keep the Ghost instance running:

M=$((RANDOM % 10))
(crontab -l 2>/dev/null; echo "0$M,1$M,2$M,3$M,4$M,5$M * * * * ~/apps/GHOSTAPP/start > /dev/null 2>&1") | crontab -

Make a note of the following commands which you will use to start and stop the Ghost instance when needed:
- Start: ~/apps/GHOSTAPP/start
- Stop: ~/apps/GHOSTAPP/stop

At this point the setup is complete and you can go to https://YOURDOMAIN.COM/ghost/ to make whatever site-specific configuration changes you want.

Updates and maintenance

To run the ghost command for updates and other maintenance tasks, first set your shell path by running the following command:

export PATH=$HOME/apps/GHOSTAPP/node/bin:$PATH

You'll then be able to run ghost for updates etc, for example:

export PATH=$HOME/apps/GHOSTAPP/node/bin:$PATH
cd $HOME/apps/GHOSTAPP/ghost
ghost backup --no-setup-linux-user
ghost update --no-setup-linux-user

More ghost commands are documented at: https://ghost.org/docs/ghost-cli/. Note that you must include the --no-setup-linux-user option when running ghost commands.