Ghost Database Management — SQLite vs MySQL, Backups and Maintenance

In this tutorial, you'll learn Ghost CMS database management — choosing between SQLite and MySQL, performing automated backups and restores, migrating between database engines, and routine maintenance to keep your database healthy.

What You'll Learn

• SQLite vs MySQL: when to use each
• Ghost's default database (SQLite) structure and limitations
• Configuring MySQL for production Ghost sites
• Automated backup strategies using cron
• Restoring a database from backup
• Migrating from SQLite to MySQL
• Database maintenance: VACUUM, OPTIMIZE, and indexing
• Monitoring database size and performance
• Troubleshooting common database issues

Why It Matters

Your database stores every post, page, member, subscription, and setting. If the database becomes corrupted, a backup fails, or performance degrades due to unoptimized queries, your entire site is affected. A production Ghost site requires a proper database strategy — choosing the right engine, setting up automated backups, and performing regular maintenance. Without it, you risk data loss, slow page loads, and extended downtime during recovery.

Real-World Use

A Ghost publisher with 50,000 members and 2,000 posts starts experiencing slow admin panel loads and occasional 502 errors. The site uses SQLite, which was fine during development. As the dataset grew, SQLite's write-lock contention caused queries to queue up. The solution: migrate to MySQL with proper connection pooling, set up automated hourly backups via cron to an S3 bucket, and schedule weekly OPTIMIZE TABLE commands. The site returns to normal performance and data is now protected against loss.

Learning Path

flowchart LR
  A["Advanced Configuration"] --> B["Database Management
You are here"]:::current
  B --> C["Upgrading Ghost"]
  C --> D["Security"]

  classDef current fill:#38bdf8,color:#0f172a,stroke-width:2px

SQLite vs MySQL

Ghost supports two database engines: SQLite (default, development) and MySQL (recommended for production).

SQLite

MySQL

When to Switch

You should migrate from SQLite to MySQL when:

• Your site has more than 10,000 posts or 50,000 members
• You experience slow admin panel response
• You need multi-user concurrent access
• You are deploying to production with expected traffic growth

Ghost Database Structure

The SQLite database file lives at content/data/ghost.db. Key tables include:

Configuring MySQL for Ghost

Installing MySQL

sudo apt update
sudo apt install mysql-server -y
sudo mysql_secure_installation

Creating the Ghost Database and User

CREATE DATABASE ghost_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'ghost_user'@'localhost' IDENTIFIED BY 'strong-password-here';
GRANT ALL PRIVILEGES ON ghost_production.* TO 'ghost_user'@'localhost';
FLUSH PRIVILEGES;

Ghost Configuration for MySQL

Update config.production.json:

{
  "database": {
    "client": "mysql",
    "connection": {
      "host": "127.0.0.1",
      "port": 3306,
      "user": "ghost_user",
      "password": "strong-password-here",
      "database": "ghost_production"
    },
    "pool": {
      "min": 2,
      "max": 10
    }
  }
}

MySQL Tuning for Ghost

Create /etc/mysql/conf.d/ghost.cnf:

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
query_cache_type = 0
max_connections = 100
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Backup Strategies

For SQLite

Stop Ghost, copy the database file, restart Ghost:

ghost stop
cp content/data/ghost.db content/data/ghost.db.backup.$(date +%Y%m%d)
ghost start

For MySQL — Automated Backups Using mysqldump

Create a backup script at /home/ghost/backup.sh:

#!/bin/bash
BACKUP_DIR="/home/ghost/backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_NAME="ghost_production"
DB_USER="ghost_user"
DB_PASS="strong-password-here"

mkdir -p $BACKUP_DIR

# Dump database
mysqldump --user=$DB_USER --password=$DB_PASS $DB_NAME \
  --single-transaction --routines --triggers --quick \
  | gzip > $BACKUP_DIR/ghost_$TIMESTAMP.sql.gz

# Delete backups older than 30 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete

echo "Backup completed: ghost_$TIMESTAMP.sql.gz"

Make it executable and schedule via cron:

chmod +x /home/ghost/backup.sh
crontab -e

Add:

0 */2 * * * /home/ghost/backup.sh  # Every 2 hours
0 3 * * * /home/ghost/backup.sh    # Also at 3 AM daily

Off-site Backups

Copy backups to S3 or another remote location:

#!/bin/bash
# After mysqldump completes
aws s3 cp $BACKUP_DIR/ghost_$TIMESTAMP.sql.gz s3://my-ghost-backups/

Restoring a Database

Restore MySQL from Backup

gunzip < ghost_20260628_030000.sql.gz | mysql --user=ghost_user --password ghost_production

Or using the backup script incrementally:

1. Stop Ghost: ghost stop
2. Restore the database from the latest backup
3. Restart Ghost: ghost start
4. Verify data integrity by checking posts, members, and settings in the admin panel

Restore SQLite

ghost stop
cp content/data/ghost.db.backup.20260628 content/data/ghost.db
ghost start

Migrating from SQLite to MySQL

Ghost provides a built-in migration command:

# 1. Ensure MySQL is running and the database/user exist
# 2. Configure MySQL in config.production.json
# 3. Run migration
ghost migrate

The migration Process:

1. Reads all data from the SQLite database
2. Transforms schemas from SQLite format to MySQL format
3. Inserts all records into the MySQL database
4. Updates the configuration to use MySQL
5. Backs up the old SQLite file

Manual Migration Steps

If ghost migrate fails, perform a manual migration:

1. Export data using Ghost's admin API:

GET /ghost/api/admin/db/

2. Import into the new database:

POST /ghost/api/admin/db/

Database Maintenance

SQLite Maintenance

ghost stop
sqlite3 content/data/ghost.db "VACUUM;"
sqlite3 content/data/ghost.db "PRAGMA integrity_check;"
ghost start

The VACUUM command reclaims disk space from deleted records. Run it monthly on active sites.

MySQL Maintenance

-- Analyze tables for query optimizer
ANALYZE TABLE posts;
ANALYZE TABLE members;
ANALYZE TABLE members_subscriptions;

-- Optimize tables to reclaim space
OPTIMIZE TABLE posts;
OPTIMIZE TABLE tags;
OPTIMIZE TABLE members;

-- Check table status
SHOW TABLE STATUS;

Schedule these commands via cron weekly.

Monitoring Database Size

SELECT 
  table_name AS `Table`,
  round(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`
FROM information_schema.TABLES
WHERE table_schema = 'ghost_production'
ORDER BY (data_length + index_length) DESC;

Common Mistakes

1. Using SQLite in production: SQLite cannot handle concurrent writes. If multiple members subscribe simultaneously or multiple authors publish at the same time, SQLite locks up and requests queue. Migrate to MySQL before launching a production site.

2. Skipping backups: Setting up Ghost without automated backups means a single corrupted database wipes out all content. Always configure automated off-site backups before going live.

3. Storing backups on the same server as the database: If the server fails, both the database and its backups are lost. Store backups on a separate service (S3, another server, or local machine).

4. Restoring from backup without testing: A backup that has never been tested is not a backup. Monthly, restore the most recent backup to a staging environment and verify all content, members, and settings are intact.

5. Neglecting database maintenance: Over time, DELETE operations leave fragmented space, indexes degrade, and query performance slows. Monthly VACUUM (SQLite) or OPTIMIZE (MySQL) maintains performance.

6. Not monitoring database size: An unexpectedly large database can fill the disk, causing Ghost to crash. Set up disk usage alerts at 80% capacity.

Practice Questions

1. When should you migrate from SQLite to MySQL in Ghost? Answer: When the site has more than 10,000 posts or 50,000 members, experiences slow admin panel response due to write contention, requires multi-user concurrent access, or is deploying to production with expected traffic growth.

2. How do you perform an automated MySQL backup in Ghost? Answer: Create a bash script using mysqldump with --single-Transaction (to avoid locking), compress with gzip, save to a backup directory, and schedule via cron (e.g., every 2 hours). Copy backups off-site to S3 or similar.

3. What is the VACUUM command and why is it needed? Answer: VACUUM is a SQLite command that rebuilds the database file, reclaiming disk space from deleted records and defragmenting the file. It should be run monthly on SQLite-based Ghost sites to maintain performance and control database file size.

4. Challenge: Set up a full database management system for a production Ghost site. Install and configure MySQL with optimized settings, create a cron-based backup script with off-site S3 storage, write a restore procedure document, schedule monthly OPTIMIZE TABLE commands, and set up disk usage monitoring at 80% capacity.

FAQ

Mini Project

Your task: Implement a complete database management system for a Ghost production site.

1. Install and configure MySQL with optimal settings for Ghost (buffer pool, log file, Connection Pool).
2. Create the ghost_production database and user with proper permissions.
3. Write an automated backup script with daily cron schedule and 30-day retention.
4. Configure off-site backup storage using S3 or equivalent.
5. Write a restoration procedure document with step-by-step instructions.
6. Set up a monthly VACUUM/OPTIMIZE schedule.
7. Test the backup and restore process on a staging environment.
8. Set up disk usage monitoring with alerts at 80% capacity.

This exercise gives you a production-grade database management system for Ghost.

What's Next

Now that database management is in place, learn about upgrading Ghost:

Continue to Lesson 37: Upgrading Ghost — Safe upgrade procedures, testing, and rollback.

Related lessons:

• Security — Hardening and protection
• Production Deployment — Deploy Ghost for scale