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

sean

(the original version of this procedure is available here)

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:

It uses our built-in Ghost installer for the initial installation.
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.
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 a new Ghost app GHOSTAPP, noting its assigned port.
Create a NGINX proxy port app DBAPP, noting its assigned port.
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

Delete the initial "ghost" instance subdirectory:

cd ~/apps/GHOSTAPP
rm -rf ghost

Create a new "ghost" instance subdirectory and install Ghost into it:

source ./setenv
mkdir ghost 
cd ghost    
ghost install local --port GHOSTPORT --log file --no-start

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

Restart the app:

~/apps/GHOSTAPP/restart

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.
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:

source ~/apps/GHOSTAPP/setenv

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

source ~/apps/GHOSTAPP/setenv
cd $HOME/apps/GHOSTAPP/ghost
ghost backup
ghost update

More ghost commands are documented at: https://ghost.org/docs/ghost-cli/.